ZeroCool ZeroCool - 6 months ago 10
Android Question

Check if some string is in SQLite database

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

column_id
and
word
. I extended
SQLiteAssetHelper
as
MyDatabase
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
word
). I tried to modify the code from answer provided by Benjamin and dipali, but I used
SQLiteAssetHelper
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
AsyncTask
because I have 60 strings to check.

Answer

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.

EDIT

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 {
    cursor.close();
}

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 {
        statement.close();
    }
}

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())

Comments