SQLiteDatabase: Insert only if the value does not exist (not via raw SQL command)

Question

I know there's an SQL command that goes like this: IF NOT EXISTS, but since Android's SQLiteDatabase class has some fine methods, I was wondering if it's possible to insert a value if it doesn't exist via a method.

Currently I'm using this to insert a String:

public long insertString(String key, String value) {
    ContentValues initialValues = new ContentValues();
    initialValues.put(key, value);
    return db.insert(DATABASE_TABLE, null, initialValues);
}

(db is an instance of SQLiteDatabase.)

I tried the method insertOrThrow() instead of insert(), but it seems it does the same as insert(). That is, if the value is already in the row, it's inserted again so the row now has two values of the same value.

Answer 1

SQLiteDatabase: Insert only if the value does not exist (not via raw SQL command)

Since you don't want to use raw queries you can achieve it that before inserting, just create some function that will test if value is already in database. It could return boolean(or int) and if it return false, you will perform insert query.

A little example:

public int getCount() {
    Cursor c = null;
    try {
        db = Dbhelper.getReadableDatabase();
        String query = "select count(*) from TableName where name = ?";
        c = db.rawQuery(query, new String[] {name});
        if (c.moveToFirst()) {
            return c.getInt(0);
        }
        return 0;
    }
    finally {
        if (c != null) {
            c.close();
        }
        if (db != null) {
            db.close();
        }
    }
}

if (getCount() == 0) {
   //perform inserting
}

if the value is already in the row, it's inserted again so the row now has two values of the same value.

This can be solved by an usage of proper constraints which won't allow you to insert duplicates. Check this:

• Constraints in SQLite