Android rawquery with dynamic Where clause

Question

I'm attempting to perform a SQL query in Android like this one:

SELECT * FROM Flashcards WHERE (category = 'Anatomy' OR category = 'Surgery') AND (difficulty = 'Easy' OR difficulty = 'Medium');

The number of categories and difficulties is determined at runtime by user selection. So far I've tried using raw query with query parameters, but I feel like I am fighting the framework in this case:

String query = "SELECT * FROM Flashcards WHERE (?) AND (?) ORDER BY RANDOM() LIMIT 1";
    Cursor cursor = db.rawQuery(query, queryParameters);

The query parameters I've tried passing in include:

category = 'Anatomy' OR category = 'Surgery'

And:

category = Anatomy OR category = Surgery

The query keeps returning no results. The same query performed on the same database externally does return results.

Answer 1

Try:

String query = "SELECT * FROM Flashcards WHERE category = ? OR category = ? ORDER BY RANDOM() LIMIT 1";
Cursor cursor = db.rawQuery(query, queryParameters);

FYI, parameters places should be depicted with ? sign.

Update:

As you want to pass field name and values run time, you can build formatted string, something like:

String query = "SELECT * FROM Flashcards WHERE %s AND %s ORDER BY RANDOM() LIMIT 1";
Cursor cursor = db.rawQuery(String.format(query, queryParameters));

where you have to build queryParameters values with something like category = 'Anatomy' OR category = 'Surgery' as you have mentioned.

Answer 2

I've always found concatenating strings for raw queries to be error prone and tedious. I have recently published the first draft of fluent API for sqlite operations on Android. With it you can write code like this:

public void getFlashcards(SQLiteDatabase db, List<String> categories, List<String> difficulties) {
  Cursor c = select()
      .from("Flashcards")
      .where(column("category").in(nArgs(categories.size())), categories.toArray())
      .where(column("difficulty").in(nArgs(difficulties.size())), difficulties.toArray())
      .orderBy("RANDOM()")
      .limit(1)
      .perform(db);

  // do something useful with the cursor
}

private Expression[] nArgs(int size) {
  return Collections.nCopies(size, Expressions.arg()).toArray(new Expression[size]);
}

This code will execute the following query:

SELECT * FROM Flashcards WHERE (category IN (?, ?)) AND (difficulty IN (?, ?)) ORDER BY RANDOM() LIMIT 1;

The number of ? args depends on the size of the categories and difficulties collections passed to the function. As a bonus point, since the query uses bound args instead of raw strings, your input will be properly escaped and the prepared statement for this query is more friendly to the cache inside the SQLiteDatabase.

The API is still not perfect, but IMO beats the code based on String.format(). Give it a spin: android-db-commons.

Answer 3

Try this

Select * from table_name where contains(fieldname,' "value1" OR "value2" OR "value3"')

Everything else you have looks fine .This worked for me in the past. Add as many OR as your want

Okay ' like and instep are supported by SQLite

SELECT *  FROM TABLE  WHERE instr(column,' "value1" OR "value2" OR "value3"'  );

SELECT *  FROM TABLE  WHERE like(column,' "value1" OR "value2" OR "value3"') 

Also, keep in mind that LIKE is case-insensitive, whereas instr is case-sensitive.