Android原生SQL接口

簡(jiǎn)單示例

定義Schema

通過(guò)定義Scheme來(lái)聲明表的表名，表頭等信息。

public class CrimeDbSchema {
    public static final class CrimeTable {
        public static final String NAME = "crimes";

        public static final class Cols {
            public static final String UUID = "uuid";
            public static final String TITLE = "title";
            public static final String DATE = "date";
            public static final String SOLVED = "solved";
        }
    }
}

實(shí)現(xiàn)SQLiteOpenHelper

SQLiteOpenHelper類管理數(shù)據(jù)庫(kù)的創(chuàng)建、升級(jí)、版本號(hào)等。

public class CrimeBaseHelper extends SQLiteOpenHelper {
    private static final int VERSION = 1;
    private static final String DATABASE_NAME = "crimeBase.db";

    public CrimeBaseHelper(Context context) {
        super(context, DATABASE_NAME, null, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table " + CrimeTable.NAME + "(" +
                " _id integer primary key autoincrement, " +
                CrimeTable.Cols.UUID + ", " +
                CrimeTable.Cols.TITLE + ", " +
                CrimeTable.Cols.DATE + ", " +
                CrimeTable.Cols.SOLVED +
                ")"
        );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { }
}

使用SQLiteOpenHelper打開(kāi)數(shù)據(jù)庫(kù)

• 數(shù)據(jù)庫(kù)的生命周期與應(yīng)用一樣，所以應(yīng)該getApplicationContext()來(lái)作為數(shù)據(jù)庫(kù)的Context。
• 調(diào)用getWritableDatabase()時(shí)的流程：
  1. 確認(rèn)目標(biāo)數(shù)據(jù)庫(kù)存在。
  2. 如果不存在就創(chuàng)建數(shù)據(jù)庫(kù)，然后初始化數(shù)據(jù)（調(diào)用onCreate()）。
  3. 如果存在就打開(kāi)并確認(rèn)CrimeDbSchema是否是最新版本。
  4. 如果是舊版就升級(jí)到新版（調(diào)用onUpgrade()）。

// ...
private final Context mContext;
private final SQLiteDatabase mDatabase;
// ...
mContext = context.getApplicationContext();
mDatabase = new CrimeBaseHelper(mContext).getWritableDatabase();
// ...

創(chuàng)建ContentValues類對(duì)象

ContentValues values = new ContentValues();
values.put(CrimeTable.Cols.UUID, crime.getId().toString());
values.put(CrimeTable.Cols.TITLE, crime.getTitle());
values.put(CrimeTable.Cols.DATE, crime.getDate().getTime());
values.put(CrimeTable.Cols.SOLVED, crime.isSolved() ? 1 : 0);

增

insert()第二的參數(shù)是String類型，名為nullColumnHack，當(dāng)values是一個(gè)空ContentValues類型對(duì)象時(shí)，若不給出第二個(gè)參數(shù)，可能會(huì)SQLite會(huì)說(shuō)無(wú)法插入，如果給出一個(gè)String作為uuid，可以使SQLite能插入空ContentValues類型對(duì)象。

// ...
private final SQLiteDatabase mDatabase;
// ...
ContentValues values = getContentValues(c);
mDatabase.insert(CrimeTable.NAME, null, values);

改

// ...
private final SQLiteDatabase mDatabase;
// ...
String uuidString = crime.getId().toString();
ContentValues values = getContentValues(crime);
mDatabase.update(CrimeTable.NAME, values, CrimeTable.Cols.UUID + " = ?", new String[]{uuidString});

查

使用query()查詢，返回類似迭代器的Cursor類對(duì)象。

// ...
private final SQLiteDatabase mDatabase;
// ...
Cursor cursor = mDatabase.query(CrimeTable.NAME, null, whereClause, whereArgs, null, null, null);

刪

// ...
private final SQLiteDatabase mDatabase;
// ...
String uuidString = crime.getId().toString();
ContentValues values = getContentValues(crime);
mDatabase.delete(CrimeTable.NAME, whereClause, whereArgs);

CursorWrapper類

創(chuàng)建

用CursorWrapper類包裝Cursor，使之變得易用。

public class CrimeCursorWrapper extends CursorWrapper {
    public CrimeCursorWrapper(Cursor cursor) {
        super(cursor);
    }

    public Crime getCrime() {
        String uuidString = getString(getColumnIndex(CrimeTable.Cols.UUID));
        String title = getString(getColumnIndex(CrimeTable.Cols.TITLE));
        long date = getLong(getColumnIndex(CrimeTable.Cols.DATE));
        int isSolved = getInt(getColumnIndex(CrimeTable.Cols.SOLVED));
        Crime crime = new Crime(UUID.fromString(uuidString));
        crime.setTitle(title);
        crime.setDate(new Date(date));
        crime.setSolved(isSolved != 0);
        return crime;
    }
}

使用示例1

CrimeCursorWrapper cursor = /* 獲取cursor */;
cursor.moveToFirst();
while (!cursor.isAfterLast()) {
    crimes.add(cursor.getCrime());
    cursor.moveToNext();
}
cursor.close();

使用示例2

CrimeCursorWrapper cursor = /* 獲取cursor */;
try {
    if (cursor.getCount() == 0) {
        return null;
    }
    cursor.moveToFirst();
    return cursor.getCrime();
}
finally {
    cursor.close();
}

注意事項(xiàng)

• Cursor類對(duì)象或CursorWrapper類對(duì)象用完要close()。