android sqlite 完全用replace替代 insert 和 update 操作

• android 中 sqlite数据库相信大家或多或少都使用过。我也一样。之前只知道 insert,update,query,delete并不知道有replace

• 不清楚replace()的，可以看下这个来自博客园，和这个。这两篇都讲了replace()方法的使用。但是，并不是很详细。

  ---

• 特别是，他们都没有强调，如果要使用replace，一定要有一个primary key，并且，该字段，是唯一区分到底是应该被update还是应该被insert的条件。

打个比方：如果你的数据库中，只有两个字段 name 和 age 。而且，你是使用name作为primarykey的。那么。第一次，你表中没有任何的数据，调用replace("张三","22");那么就向你的表中插入了这条记录。然后，你再次调用replace("张三","22");这时候，你表中的数据不变。过了一会，你调用replace("张三","29");,那么，你的表中，依然是一条记录。只不过age字段变成29了而已。过了一会，你调用replace("Tom",32);。那么，你的表中就有两条记录了。分别是：张三,29 和 Tom,32。

为了避免产生误导，我说明一下replace()方法的api不是这样使用的，我这么写，是为了更容易理解。

• 下面，我给出我写的一个DbHelper和一个DbDao。里面有replace()方法的实际使用方式。
  
  • SQLiteOpenHelper .java

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.apkfuns.logutils.LogUtils;

class LockDbHelper extends SQLiteOpenHelper {
    // If you change the database schema, you must increment the database version.
    private static final int DATABASE_VERSION = 1+1+1+1;
    private static final String DATABASE_NAME = "lockAppSet.db";
    private static final String TEXT_TYPE = " TEXT";
    private static final String INTEGER_TYPE = " INTEGER";
    private static final String COMMA_SEP = ",";
    public static final String PRIMARY_KEY = " PRIMARY KEY";
    private static final String SQL_CREATE_ENTRIES =
            // id + title + content + date
            "CREATE TABLE IF NOT EXISTS " + LockColumns.TABLE_NAME + " (" +
                    /*LockColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +*/
                    LockColumns.COLUMN_PACKAGE_NAME + TEXT_TYPE + PRIMARY_KEY + COMMA_SEP +
                    LockColumns.COLUMN_IS_LOCKED + INTEGER_TYPE +
                    " )";

    private static final String SQL_DELETE_ENTRIES =
            "DROP TABLE IF EXISTS " + LockColumns.TABLE_NAME;

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

    public void onCreate(SQLiteDatabase db) {
        LogUtils.e("create table SQL = " + SQL_CREATE_ENTRIES);
        db.execSQL(SQL_CREATE_ENTRIES);
    }

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // This database is only a cache for online data, so its upgrade policy is
        // to simply to discard the data and start over
        db.execSQL(SQL_DELETE_ENTRIES);
        onCreate(db);
    }

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

* 字段常量

final class LockColumns {
    // To prevent someone from accidentally instantiating the contract class,
    // make the constructor private.
    private LockColumns() {
    }

    static final String TABLE_NAME = "appLockTable"; // table name
    static final String COLUMN_PACKAGE_NAME = "package_name"; //
    static final String COLUMN_IS_LOCKED = "locked"; // is locked 1,locked 0 unlocked
}

• Dao.java

public class LockDao {

    /**
     * insert
     *
     * @param c    context
     * @param info domain
     * @return row
     */
    public static long replace(@NonNull Context c, @NonNull LockInfo info) {
        // Gets the data repository in write mode
        LockDbHelper mDbHelper = new LockDbHelper(c);
        SQLiteDatabase db = mDbHelper.getWritableDatabase();
        //noinspection TryFinallyCanBeTryWithResources
        try {
            // Create a new map of values, where column names are the keys
            ContentValues values = new ContentValues();
            values.put(LockColumns.COLUMN_PACKAGE_NAME, info.packageName);
            values.put(LockColumns.COLUMN_IS_LOCKED, info.locked);
            // Insert the new row, returning the primary key value of the new row
            long replace = db.replace(LockColumns.TABLE_NAME, null, values);
            LogUtils.e("replace = " + replace);
            return replace;
        } finally {
            db.close();
            mDbHelper.close();
        }
    }

    /**
     * insert
     *
     * @param c    context
     * @param info domain
     * @return row
     */
    public static long insert(@NonNull Context c, @NonNull LockInfo info) {
        // Gets the data repository in write mode
        LockDbHelper mDbHelper = new LockDbHelper(c);
        SQLiteDatabase db = mDbHelper.getWritableDatabase();
        //noinspection TryFinallyCanBeTryWithResources
        try {
            // Create a new map of values, where column names are the keys
            ContentValues values = new ContentValues();
            values.put(LockColumns.COLUMN_PACKAGE_NAME, info.packageName);
            values.put(LockColumns.COLUMN_IS_LOCKED, info.locked);
            // Insert the new row, returning the primary key value of the new row
            return db.insert(LockColumns.TABLE_NAME, null, values);
        } finally {
            db.close();
            mDbHelper.close();
        }
    }

    /**
     * insert
     *
     * @param c        context
     * @param dataList list of LockInfo
     * @return boolean
     */
    public static boolean insert(@NonNull Context c, @NonNull List<LockInfo> dataList) {
        // Gets the data repository in write mode
        LockDbHelper mDbHelper = new LockDbHelper(c);
        SQLiteDatabase db = mDbHelper.getWritableDatabase();
        //noinspection TryFinallyCanBeTryWithResources
        try {
            long all = 0;
            for (LockInfo info : dataList) {
                // Create a new map of values, where column names are the keys
                ContentValues values = new ContentValues();
                values.put(LockColumns.COLUMN_PACKAGE_NAME, info.packageName);
                values.put(LockColumns.COLUMN_IS_LOCKED, info.locked);
                // Insert the new row, returning the primary key value of the new row
                all += db.insert(LockColumns.TABLE_NAME, null, values) > 0 ? 1 : 0;
            }
            LogUtils.e("insert size = " + dataList.size() + " , all = " + all);
            return all == dataList.size();
        } finally {
            db.close();
            mDbHelper.close();
        }
    }


    /**
     * delete one
     *
     * @param c           context
     * @param packageName packageName
     * @return row
     */
    public static int delete(@NonNull Context c, @NonNull String packageName) {
        LockDbHelper mDbHelper = new LockDbHelper(c);
        SQLiteDatabase db = mDbHelper.getReadableDatabase();
        // noinspection TryFinallyCanBeTryWithResources
        try {
            // Define 'where' part of query.
            String selection = LockColumns.COLUMN_PACKAGE_NAME + " = ?";
            // Specify arguments in placeholder order.
            String[] selectionArgs = {packageName};
            // Issue SQL statement.
            return db.delete(LockColumns.TABLE_NAME, selection, selectionArgs);
        } finally {
            db.close();
            mDbHelper.close();
        }
    }
}

• 其实还应该有一些其他的包，不然运行会直接报错的。但是这里放上这些，主要是演示一下db.replace()方法的实际用法。