Check for duplicates in SQLite database before adding row

Question

I'm trying to add a row to my database in android with the following code, but I want to make sure that the row does not already exist, how would i do that?

void addKey(String key, String value, String table) {

            SQLiteDatabase db = this.getWritableDatabase();

            ContentValues values = new ContentValues();
            values.put(KEY_KEY, key);
            values.put(KEY_VALUE, value);

            // Inserting Row
            db.insert(table, null, values);
            db.close(); // Closing database connection          
    }

I tried using the following code but I couldn't get it to work

public boolean check(String key, String value, String table) {
        SQLiteDatabase db = this.getWritableDatabase();
        String sql = "select * from " + table + " where key= " + key + " and value= " + value;
        Cursor cur = db.rawQuery( sql, new String[0] );

        if(cur.getCount() == 0)
        {
            cur.close();
            db.close();
            return false;
        }

        else{
            cur.close();
            db.close();
            return true;
        }

    }

The error:

12-04 16:34:46.867: E/AndroidRuntime(2743): Caused by: android.database.sqlite.SQLiteException: no such column: hi: , while compiling: select * from tableOne where key= hi and value= baby
12-04 16:34:46.867: E/AndroidRuntime(2743):     at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
12-04 16:34:46.867: E/AndroidRuntime(2743):     at android.database.sqlite.SQLiteCompiledSql.compile(SQLiteCompiledSql.java:92)
12-04 16:34:46.867: E/AndroidRuntime(2743):     at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:65)
12-04 16:34:46.867: E/AndroidRuntime(2743):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:83)
12-04 16:34:46.867: E/AndroidRuntime(2743):     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:49)
12-04 16:34:46.867: E/AndroidRuntime(2743):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:42)
12-04 16:34:46.867: E/AndroidRuntime(2743):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1428)
12-04 16:34:46.867: E/AndroidRuntime(2743):     at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1396)

Use parameters... Like key=? assuming that key is string

Selvin
– Selvin

2012-12-04 21:32:40 +00:00
Commented Dec 4, 2012 at 21:32 — Selvin
– Selvin, Commented Dec 4, 2012 at 21:32

Literate Corvette · Accepted Answer · 2020-08-12 19:15:43Z

2

You have to either format the string correctly in SQL (which involves quoting the string itself, and any quotes in the string), or use parameters to pass the string into the database:

String sql = "SELECT * FROM " + table + " WHERE key=? AND value=?";
Cursor cur = db.rawQuery(sql, new String[] { key, value });

If only the value changes, do you really want to insert another record with the same key? Or would you rather update that record?

edited Aug 12, 2020 at 19:15

Literate Corvette

4364 silver badges10 bronze badges

answered Dec 4, 2012 at 21:36

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Check for duplicates in SQLite database before adding row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related