ZeroCool ZeroCool - 2 years ago 84
Android Question

Check if some string is in SQLite database

I have some trouble with a SQLite database with 1 table and 2 columns,

. I extended
and made a constructor:

public MyDatabase(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);

I need to check whether some string is in the database (in column
). I tried to modify the code from answer provided by Benjamin and dipali, but I used
and I can't get it to work. The method that I have in mind receives the string to search for as a parameter and returns a boolean if string is in the database.

public boolean someMethod(String s)

In addition, I tried to put the check on a background thread with
because I have 60 strings to check.

Answer Source

TABLE_NAME and COLUMN_WORD should be self-explanatory.

public boolean someMethod(String s) {
    SQLiteDatabase db = getReadableDatabase();
    String[] columns = new String[] {COLUMN_WORD};
    String where = COLUMN_WORD + " = ?";
    String[] whereArgs = new String[] {s};

    // select column_word from table where column_word = 's' limit 1;
    Cursor cursor = db.query(TABLE_NAME, columns, where, whereArgs, null, null, null, "1");
    if (cursor.moveToFirst()) {
        return true; // a row was found
    return false; // no row was found

You can do this in the background, but I don't think for a query like this it's even necessary.


There are some improvements that should be made to the above for the sake of correctness. For one thing, the Cursor should be closed since it is no longer being used. A try-finally block will ensure this:

Cursor cursor = db.query(...);
try {
    return cursor.moveToFirst();
} finally {

However, this method doesn't need to obtain a whole `Cursor. You can write it as follows and it should be more performant:

public boolean someMethod(String s) {
    SQLiteDatabase db = getReadableDatabase();
    String sql = "select count(*) from " + TABLE_NAME + " where "
            + COLUMN_WORD " + = '" + s + "'";
    SQLiteStatement statement = db.compileStatement(sql);

    try {
        return statement.simpleQueryForLong() > 0;
    } finally {

You could add a catch block and return false if you think it's possible (and valid) to encounter certain exceptions like SQLiteDoneException. Also note that now s is being inlined in the query, so you need to make sure s isn't something malicious (for example by using Databaseutils.sqlEscapeString())

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download