在移动开发中,如果需要在客户端保留比较多的数据,并且可能使用多种条件对数据进行查询,更改,删除的操作,这时 SharedPreferences 就显得力不从心了。如何解决这个问题呢?移动平台(Android 和 iOS)给出的答案都是使用小型数据库 SQLite。
本文演示在 Android 中操作 SQLite 数据库。
建立 DataHelper
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| public class DatabaseHelper extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "Diary.db"; private static final int DATABASE_VERSION = 1;
public static final String TABLE_NAME = "diary"; public static final String ID = "id"; public static final String TITLE = "title"; public static final String BODY = "body";
public DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); }
@Override public void onCreate(SQLiteDatabase db) { }
@Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } }
|
建表操作
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
| @OnClick(R.id.btnCreateTable) void onCreateTable() {
SQLiteDatabase db = databaseHelper.getWritableDatabase(); String sql = "CREATE TABLE " + TABLE_NAME + " ( id integer primary key autoincrement, " + TITLE + " text not null, " + BODY + " text not null " + ");"; Log.i(TAG, "create Table = " + sql);
try { db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME); db.execSQL(sql); setTitle("数据表成功重建"); } catch (SQLException e) { setTitle("数据表重建错误"); } }
|
插入数据
1 2 3 4 5 6 7 8 9 10 11 12
| @OnClick(R.id.btnInsert) void onInsert() {
SQLiteDatabase db = databaseHelper.getWritableDatabase(); String sql1 = "insert into " + TABLE_NAME + " (" + TITLE + ", " + BODY + ") values('游记 - 桂林', '桂林山水甲天下');"; try { db.execSQL(sql1); setTitle("插入数据成功"); } catch (SQLException e) { setTitle("插入数据失败"); } }
|
查询数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
| @OnClick(R.id.btnQuery) void onQuery() {
SQLiteDatabase db = databaseHelper.getReadableDatabase(); String col[] = { "id", TITLE, BODY }; Cursor cur = db.query(TABLE_NAME, col, null, null, null, null, null); Integer num = cur.getCount(); setTitle(Integer.toString(num) + " 条记录"); try { while (cur.moveToNext()) { Log.d(TAG, "" + cur.getInt(0)); Log.d(TAG, cur.getString(1)); Log.d(TAG, cur.getString(2)); } } catch(SQLException e) { Log.e(TAG, "query fail", e); } }
|
更新数据
1 2 3 4 5 6
| @OnClick(R.id.btnUpdate) void onUpdate() { SQLiteDatabase db = databaseHelper.getWritableDatabase(); ContentValues args = new ContentValues(); args.put(TITLE, "游记 - 桂林 Update"); db.update(TABLE_NAME, args, "id = 1", null); }
|
删除数据
1 2 3 4 5 6 7 8 9
| @OnClick(R.id.btnDelete) void onDelete() { try { SQLiteDatabase db = databaseHelper.getWritableDatabase(); db.delete(TABLE_NAME, " title = '游记 - 桂林'", null); setTitle("删除一条记录"); } catch (SQLException e) { setTitle("数据删除失败"); } }
|
删除表
1 2 3 4 5 6 7 8 9 10
| @OnClick(R.id.btnDrop) void onDrop() { SQLiteDatabase db = databaseHelper.getWritableDatabase(); String sql = "drop table " + TABLE_NAME; try { db.execSQL(sql); setTitle("数据表成功删除:" + sql); } catch (SQLException e) { setTitle("数据表删除错误"); } }
|