1.Sqlite简介 Sqlite是一款轻型的数据库,它包含在一个相对小的C库中,它的设计目标是嵌入式的,由于它占用资源非常少,可能只需要几百K的内存就可以了,并且支持Windows/Linux/Unix等等主流的操作系统,同时可以和很多种程序语言相结合,比如:C#/Java/php等,所以在嵌入式设备中特别受欢迎,这一点也正好符合android的开发要求,所以在Android开发中经常要用到该数据库。
2. 运用Sqlite,一般要有一个SQLiteOpenHelper来辅助创建数据库,连接数据库。
eg:
注意:
(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()方法可以直接返回一个游标进行相关操作