编辑
Android数据库(SQLite)操作记录
本文访问次数:0
  1. 1. 定义架构和契约
  2. 2. 定义表创建语句和移除语句
  3. 3. 定义数据库帮助类
  4. 4. 打开数据库
  5. 5. 插入一条数据
  6. 6. 读取数据
  7. 7. 删除一条数据
  8. 8. 更新一条数据
  9. 9. 更新和插入(替换)一条数据

基本操作如下

定义架构和契约

使用契约(Contract)定义表结构

public final class SortContract {
    private SortContract() {
    }

    public static class SortEntry implements BaseColumns{
        public static final String TABLE_NAME = "sort";
        public static final String COLUMN_NAME_ID = "id";
        public static final String COLUMN_NAME_NAME = "name";
        public static final String COLUMN_NAME_ORDER = "sort_order";
        public static final String COLUMN_NAME_HIDDEN = "hidden";
    }
}

定义表创建语句和移除语句

private static final String TYPE_TEXT = " TEXT";
private static final String TYPE_INT = " INT";
private static final String TYPE_TINY_INT = " TINYINT";
private static final String COMMA = ",";

private static final String SQL_CREATE_TABLE = "CREATE TABLE " + SortContract.SortEntry.TABLE_NAME + " (" + SortContract.SortEntry._ID + " INTEGER PRIMARY KEY," + SortContract.SortEntry.COLUMN_NAME_ID + TYPE_TEXT + COMMA + SortContract.SortEntry.COLUMN_NAME_NAME + TYPE_TEXT + COMMA + SortContract.SortEntry.COLUMN_NAME_ORDER + TYPE_INT + COMMA + SortContract.SortEntry.COLUMN_NAME_HIDDEN + TYPE_TINY_INT + " )";

private static final String SQL_DROP_TABLE = "DROP TABLE IF EXISTS "+ SortContract.SortEntry.TABLE_NAME;

定义数据库帮助类

public class SortOpenHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "FoodSort.db";
    private static final int DATABASE_VERSION = 0;

    public SortOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(SQL_CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        sqLiteDatabase.execSQL(SQL_DROP_TABLE);
        onCreate(sqLiteDatabase);
    }

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

打开数据库

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);

    setContentView(R.layout.activity_sort);
    mOpenHelper = new SortOpenHelper(this);
    mDatabase = mOpenHelper.getWritableDatabase();
}

插入一条数据

private void insertSortModel(FoodSortModel model,SQLiteDatabase database){
    if(model == null || database == null){
        return;
    }
    ContentValues values = new ContentValues();
    values.put(SortContract.SortEntry.COLUMN_NAME_ID,model.id);
    values.put(SortContract.SortEntry.COLUMN_NAME_HIDDEN,model.isHidden);
    values.put(SortContract.SortEntry.COLUMN_NAME_NAME,model.name);
    values.put(SortContract.SortEntry.COLUMN_NAME_ORDER,model.order);
    database.insert(SortContract.SortEntry.TABLE_NAME,null,values);
}

第二个参数将指示框架在 ContentValues 为空(即,您没有 put 任何值)时执行哪些操作。如果指定列名称,则框架将插入一行并将该列的值设置为 null。如果指定 null(就像此代码示例中一样),则框架不会在没有值时插入行。

读取数据

private List<FoodSortModel> readAllData(SQLiteDatabase database) {
    List<FoodSortModel> result = new ArrayList<FoodSortModel>();
    if (database == null) {
        return result;
    }
    String[] columns = new String[]{SortContract.SortEntry.COLUMN_NAME_ID, SortContract.SortEntry.COLUMN_NAME_HIDDEN, SortContract.SortEntry.COLUMN_NAME_NAME, SortContract.SortEntry.COLUMN_NAME_ORDER};

    String sortOrder = SortContract.SortEntry.COLUMN_NAME_ORDER + " ASC";

    Cursor cursor = database.query(
            SortContract.SortEntry.TABLE_NAME,
            columns,
            null,//selection
            null,//selection arguments
            null,//group by
            null,//having
            sortOrder
    );
    cursor.moveToFirst();
    while (cursor.moveToNext()) {
        FoodSortModel model = new FoodSortModel(
                cursor.getInt(cursor.getColumnIndex(SortContract.SortEntry.COLUMN_NAME_ORDER)),
                cursor.getString(cursor.getColumnIndex(SortContract.SortEntry.COLUMN_NAME_ID)),
                cursor.getString(cursor.getColumnIndex(SortContract.SortEntry.COLUMN_NAME_NAME)),
                cursor.getInt(cursor.getColumnIndex(SortContract.SortEntry.COLUMN_NAME_ORDER)) == 1
        );
        result.add(model);
    }
    cursor.close();
    return result;
}

删除一条数据

private void deleteModel(SQLiteDatabase database,FoodSortModel model){
    String selection = SortContract.SortEntry.COLUMN_NAME_ID + " = ?";
    String[] arguments = { model.id };
    database.delete(SortContract.SortEntry.TABLE_NAME,selection,arguments);
}

更新一条数据

private void updateModel(SQLiteDatabase database,FoodSortModel model){
    ContentValues values = new ContentValues();
    values.put(SortContract.SortEntry.COLUMN_NAME_HIDDEN, model.isHidden ? 1 : 0);
    values.put(SortContract.SortEntry.COLUMN_NAME_NAME, model.name);
    values.put(SortContract.SortEntry.COLUMN_NAME_ORDER, model.order);
    String selection = SortContract.SortEntry.COLUMN_NAME_ID + " = ?";
    String[] arguments = new String[]{model.id};

    int count = database.update(SortContract.SortEntry.TABLE_NAME,values,selection,arguments);
}

更新和插入(替换)一条数据

如果存在,则更新,否则插入一条数据

private void replaceMode(SQLiteDatabase database,FoodSortModel model){
    ContentValues values = new ContentValues();
    values.put(SortContract.SortEntry.COLUMN_NAME_ID,model.id);
    values.put(SortContract.SortEntry.COLUMN_NAME_HIDDEN, model.isHidden ? 1 : 0);
    values.put(SortContract.SortEntry.COLUMN_NAME_NAME, model.name);
    values.put(SortContract.SortEntry.COLUMN_NAME_ORDER, model.order);

    database.replace(SortContract.SortEntry.TABLE_NAME,null,values);
}
没有任何评论