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.

You can use an ORM Library. I've used ORMDroid and you can do a number of SQL queries via methods. http://ormlite.com/sqlite_java_android_orm.shtml — dannyroa, Mar 29 '13 at 17:06

score 18 · Accepted Answer · answered Mar 29 '13 at 17:06

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

How recommended is it to use constraints rather than following your posted code about check before insert? I was testing constraints and I noticed a stack trace being printed to console about cannot insert due to constraint, will this effect anything? App is not crashing and did not notice performance slow down, was just wondering. — JustADev, Sep 26 '16 at 10:10
In my case I have to insert thousands of rows, and executing a separate query for each individual row does not look a wise solution. — Muhammad Saqib, Dec 01 '19 at 14:05

score 15 · Answer 2 · edited Jun 18 '15 at 16:28

You could set CONFLICT_IGNORE behavior for conflict of inserting row:

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

But this depends on constraint. There is more behaviors if you need in future.

Sam · Answer 3 · 2013-03-29T17:24:27.373

There are two basic ways you can do this:

Query the database. Manually check if the data already exists then added it if it doesn't exist.
Use CONSTRAINTs. Define your SQL schema to only allow unique data for this column.

The first approach is easier if you want to perform different actions under different circumstances (or if you aren't already proficient in SQL.) The second approach can be done with much less typing and can be applied universally.

score 3 · Answer 4 · edited Sep 26 '16 at 11:56

The solution is not in Android Api but in Database. if you want to make sure that the string you insert is not already present in the database, there are two possible solutions.

when creating the database make the column (the string which you wanted to insert) as UNIQUE, this will prevent it from entering duplicates into that column. and when you try to insert and is a duplicate it will throw an error; you can handle it via exception handling.
You can create a second method (invoked after every insert; this is costly )that checks for duplicates in table and removes them.

I would go with approach 1.

here is a sample UNIQUE syntax

create table tablename( col1 datatype, col2 datatype, col3 datatype, UNIQUE(col1));

good luck

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

4 Answers4

Linked