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

Answer 2

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.

Answer 3

There are two basic ways you can do this:

1. Query the database. Manually check if the data already exists then added it if it doesn't exist.
2. 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.

Answer 4

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.

1. 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.

2. 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