Android(安卓)SQlite数据库常规操作
Sqlite是一款轻型的数据库,它包含在一个相对小的C库中,它的设计目标是嵌入式的,由于它占用资源非常少,可能只需要几百K的内存就可以了,并且支持Windows/Linux/Unix等等主流的操作系统,同时可以和很多种程序语言相结合,比如:C#/Java/php等,所以在嵌入式设备中特别受欢迎,这一点也正好符合android的开发要求,所以在Android开发中经常要用到该数据库。
2. 运用Sqlite,一般要有一个SQLiteOpenHelper来辅助创建数据库,连接数据库。
eg:
public class DatabaseHelper extends SQLiteOpenHelper {public static final String DBNAME = "cbg_download.db";public static final int VERSION = 1;// public DatabaseHelper(Context context) {////必须通过super调用父类当中的构造函数 //super(context,DBNAME,null,VERSION);//} //创建多项数据库连接 public DatabaseHelper(Context context,String name){ this(context,name,VERSION); } public DatabaseHelper(Context context,String name,int version){ this(context, name,null,version); } //在SQLiteOepnHelper的子类当中,必须有该构造函数 public DatabaseHelper(Context context, String name, CursorFactory factory, int version) { //必须通过super调用父类当中的构造函数 super(context, name, factory, version); } @Overridepublic void onCreate(SQLiteDatabase db) {//存在数据库则不会调用。进行初始操作db.execSQL("CREATE TABLE IF NOT EXISTS priaseTable(id integer primary key autoincrement,clickId varchar(100),clickType varchar(100),clickCount INTEGER)");}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// TODO Auto-generated method stubdb.execSQL("DROP TABLE IF EXISTS filedownlog");onCreate(db);}}注意:
(1)DatabaseHelper 继承SQLiteOpenHelper。必须实现3点,
构造函数:用于初始化数据库,创建数据库
public void onCreate(SQLiteDatabase db) :用于创建表。在调getReadableDatabase或getWritableDatabase时,会判断指定的数据库是否存在,不存在则调SQLiteDatabase.create创建, onCreate只在数据库第一次创建时才执行
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion):用于数据库更新操作
3.//单例模式Dao 类,做一个对数据表进行操作的类
eg:
package cdv.cq.mobilestation.Activity.praise;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.widget.Toast;//单例模式public class Dao {private static Dao dao = null;private Context context;private Dao(Context contex) {this.context = contex;}public static Dao getInstance(Context context) {if (dao == null) {dao = new Dao(context);}return dao;}// 连接数据库public SQLiteDatabase getConnection() {SQLiteDatabase sqLiteDatabase = null;try {sqLiteDatabase = new DatabaseHelper(context, DatabaseHelper.DBNAME).getReadableDatabase();} catch (Exception e) {e.printStackTrace();}return sqLiteDatabase;}public void createTable(SQLiteDatabase sqLiteDatabase){sqLiteDatabase.execSQL("CREATE TABLE IF NOT EXISTS priaseTable(id integer primary key autoincrement,clickId varchar(100),clickType varchar(100),clickCount INTEGER)");}public synchronized int isHasInfors(String clickId, String clickType) {SQLiteDatabase database = getConnection();int id = -1;Cursor cursor = null;try {cursor = database.query("priaseTable", null,"clickId=? AND clickType=?", new String[] { clickId,clickType }, null, null, null);if (cursor.moveToFirst()) {id = cursor.getInt(0);}} catch (Exception e) {e.printStackTrace();} finally {if (null != database) {database.close();}if (null != cursor) {cursor.close();}}return id;}//增public synchronized void insert(String clickId, String clickType,int count) {SQLiteDatabase database = getConnection();// int id = dao.isHasInfors(clickId,clickType);// if(id == 0){// ContentValues cv = new ContentValues();// cv.put("clickId", clickId);// cv.put("clickType", clickType);// cv.put("clickCount", 1);// database.insert("priaseTable", null, cv);// String sql =// "insert into priaseTable(clickId,clickType,clickCount) values(?,?,?)";// Object[] info ={clickId,clickType,"1"};// database.execSQL(sql,info);// }// else{// //更改数据操作//// }try {String sql = "insert into priaseTable(clickId,clickType,clickCount) values(?,?,?)";Object[] info = { clickId, clickType, count};database.execSQL(sql, info);} catch (Exception e) {e.printStackTrace();} finally {if (null != database) {database.close();}}}// 改public synchronized void update(int Id, boolean isAdd) {SQLiteDatabase database = getConnection();int count = 0;try{Cursor curor = database.rawQuery("select * from priaseTable where id =?",new String[]{String.valueOf(Id)});if(curor.moveToFirst()) count = curor.getInt(3);if(isAdd){String sql = "update priaseTable set clickCount=? where id = ?";Object[] info = {++count,Id};database.execSQL(sql,info);//这是在新的线程打开的,Toast只能在主线程修改UI界面//Toast.makeText(context, "点赞加1成功",2*1000).show();}else{String sql = "update priaseTable set clickCount=? where id = ?";Object[] info = {--count,Id};database.execSQL(sql,info);//Toast.makeText(context, "点赞减1成功",2*1000).show();}}catch(Exception e){e.printStackTrace();}finally{if (null != database) {database.close();}}}//查 点赞数public synchronized int queryPraise(String id){SQLiteDatabase database = getConnection();int count = 0;try{Cursor curor = database.rawQuery("select from priaseTable where id =?", new String[]{id});while(curor.moveToFirst()) count = curor.getInt(4);}catch(Exception e){e.printStackTrace();}finally{if (null != database) {database.close();}}return count;}}注意:
(1)sqlite 数据库中的表 列号是从0开始的,
eg:Cursor curor = database.rawQuery("select * from priaseTable where id =?",new String[]{String.valueOf(Id)});
while(curor.moveToFirst())
count = curor.getInt(3); //一共4列
(2)做成一个DAO类单例模式可以很好的对数据库中的表进行操作
(3)增删改查功能跟SQLserver语句一样的但是要注意:不必记太多的android自带的方法
增删改::database.execSQL(sql, info); 直接对数据库进行操作
查询用: database.query(sql, info)用query()方法可以直接返回一个游标进行相关操作
更多相关文章
- 一步一步学习Android(安卓)TV/盒子开发(一)
- Android(安卓)重学系列 有趣的工具--智能指针与智能锁
- Android之Matrix用法
- Android层接收串口数据
- Android(安卓)view实例化
- Android(安卓)逆向工程 —— Android(安卓)原生程序逆向 1
- Android(安卓)RxJava 实际应用讲解:网络请求出错重连(结合Retrofit
- Android(安卓)— 之内容提供器(Content Provider)
- 【Android(安卓)数据库框架总结,总有一个适合你!】