安卓SQLite

例子：以下用了自己的设计模式（Crime是我的数据对象）

1. 准备一个类来描述数据库对象及其项（非必要，设计模式）

public class CrimeDbSchema {

    public static final class CrimeTable{
        public static final String CrimeName = "crime";

        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";
        }
    }



}

2. 设计数据库类

public  class CrimeBaseHelper extends SQLiteOpenHelper{

    private static int VERSION = 1;
    private static String DATEBASE_NAME = "crimebase.db";


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


    //该方法当名字对应的数据库不存在时,会创建
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL("create table " + CrimeTable.CrimeName + "(" +
                " _id integer primary key autoincrement, " +
                CrimeTable.Cols.UUID + ", " +
                CrimeTable.Cols.TITLE + ", " +
                CrimeTable.Cols.DATE + ", " +
                CrimeTable.Cols.SOLVED + ")");

    }

    //该方法当数据库版本变迁时会升级(注意是版本)
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }
}

3. 设计生成sql数据语句方法

    //转换为数据部分sql语句
    private static ContentValues getContentValues(Crime crime) {
        ContentValues values = new ContentValues();
        values.put(CrimeTable.Cols.UUID, crime.getyID().toString());
        values.put(CrimeTable.Cols.TITLE, crime.getyTitle());
        values.put(CrimeTable.Cols.DATE, crime.getyDate().getTime());
        values.put(CrimeTable.Cols.SOLVED, crime.getCrimeFlag() ? 1 : 0);
        return values;
    }

4. 实例化数据库对象（根据自己继承设计的数据库对象类）

private SQLiteDatabase mDatabase = new CrimeBaseHelper(mContext).getWritableDatabase();

5. 插入

        ContentValues values = getContentValues(c);//调用转换为数据部分sql语句
        mDatabase.insert(CrimeTable.CrimeName, null, values);

6. 更新

    //更新sql
    public void updateCrime(Crime crime) {
        String uuidString = crime.getyID().toString();
        ContentValues values = getContentValues(crime);//调用转换为数据部分sql语句
        mDatabase.update(CrimeTable.CrimeName, values,
                CrimeTable.Cols.UUID + " = ?",
                new String[] { uuidString });
    }

7. 继承设计查询类

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.setyTitle(title);
        crime.setyDate(new Date(date));
        crime.setCrimeFlag(isSolved != 0);

        return crime;
    }

}

8. 设计查询方法

    //查询crime记录，返回查询对象
    //可根据项的段和相应的字找到相应的项
//    private Cursor queryCrimes(String whereClause, String[] whereArgs) {
    private CrimeCursorWrapper queryCrimes(String whereClause, String[] whereArgs) {
        Cursor cursor = mDatabase.query(
                CrimeTable.CrimeName,
                null, // Columns - null selects all columns
                whereClause,
                whereArgs,
                null, // groupBy
                null, // having
                null // orderBy
        );
        return new CrimeCursorWrapper(cursor);
    }


}

9. 查询表的项遍历

        List<Crime> crimes = new ArrayList<>();
        CrimeCursorWrapper cursor = queryCrimes(null, null);//调用查询crime记录方法，返回查询对象
        try {
            cursor.moveToFirst();//指向表单第一个项
            while (!cursor.isAfterLast()) {//如果不为空
                crimes.add(cursor.getCrime());//将表单项数据添加到列表
                cursor.moveToNext();//项下移
            }
        } finally {
            cursor.close();//不用要关闭，不用动一个关一次，全部用好才关
        }
        return crimes;