使用Android中的Sqlite存储数据

在移动开发中，如果需要在客户端保留比较多的数据，并且可能使用多种条件对数据进行查询，更改，删除的操作，这时 SharedPreferences 就显得力不从心了。如何解决这个问题呢？移动平台（Android 和 iOS)给出的答案都是使用小型数据库 SQLite。

本文演示在 Android 中操作 SQLite 数据库。

建立 DataHelper

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) {
    }
}

建表操作

@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("数据表重建错误");
    }
}

插入数据

@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("插入数据失败");
    }
}

查询数据

@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);
    }
}

更新数据

@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);
}

删除数据

@OnClick(R.id.btnDelete) void onDelete() {
    try {
        SQLiteDatabase db = databaseHelper.getWritableDatabase();
        db.delete(TABLE_NAME, " title = '游记 - 桂林'", null);
        setTitle("删除一条记录");
    } catch (SQLException e) {
        setTitle("数据删除失败");
    }
}

删除表

@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("数据表删除错误");
    }
}