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.

you need to specify the column name WHERE category= ? OR cateogry =? — Raghunandan, Jan 23 '14 at 05:03
The number of category = ? conditions will vary depending upon the number of categories selected by the user. — Blake Mumford, Jan 23 '14 at 05:07
+1 from me for " but I feel like I am fighting the framework in this case" part — z atef, Jan 23 '14 at 05:22
I think you don't use simple query .u want to use rawquery.right? — dipali, Jan 23 '14 at 05:31

Paresh Mayani · Accepted Answer · 2014-01-23T05:40:37.057

5

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.

edited Jan 23 '14 at 05:40

answered Jan 23 '14 at 05:10

Paresh Mayani

127,700
71
241
295

Hi, thanks for your answer. I'm aware of how to use rawQuery with query parameters. What I don't know is how to use rawquery with multiple OR conditions which are determined at runtime. Sorry, my fault for not being clear with my question (edited). – Blake Mumford Jan 23 '14 at 05:13
1

Thanks, that's looking like what I need. I'll give it a go now. – Blake Mumford Jan 23 '14 at 05:38

score 5 · Answer 2 · answered Jan 23 '14 at 08:24

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.

z atef · Answer 3 · 2014-01-23T05:40:15.523

1

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.

edited Jan 23 '14 at 05:40

answered Jan 23 '14 at 05:07

z atef

7,138
3
55
50

I tried using CONTAINS but it does not appear to be a recognised SQLite function? – Blake Mumford Jan 23 '14 at 05:18
@Angavar i think you can use Like http://www.tutorialspoint.com/sqlite/sqlite_like_clause.htm – Raghunandan Jan 23 '14 at 05:25
1

I don't know whether SQLite supports `CONTAINS` or not, but you might be interested in `IN` clause. The usage in Android may be troublesome, so I hope [this answer](http://stackoverflow.com/questions/7418849/android-sqlite-in-clause-and-placeholders) can be useful. – Andrew T. Jan 23 '14 at 05:31
1

Contains is an oracle platform function . You did not specify. Anyhow , you can replace contains with "like". Or. "Instr". And see which one will work for best. – z atef Jan 23 '14 at 05:32
1

Good one @andrew.. I also suggested like or instr – z atef Jan 23 '14 at 05:34
Just play around with the quotations. @angavar – z atef Jan 23 '14 at 05:41

score 0 · Answer 4 · answered Aug 31 '14 at 08:10

It's a bug in android's SQLiteDatabase class.

Seems like "category" is a key word in the android SQLiteDatabase code, and makes a query return nothing when written in where clauses on the android side.

I also had this issue and changing my column name from "category" to something else solved the issue.

Android rawquery with dynamic Where clause

4 Answers4

Update:

Linked