SQLite dynamic query

问题:

I have a SQLite database, and what i want to do is that an user selects a filter. For example i have a database of books, and a user just want to look data from "Agata christies books"..

So i made a spinner with the options to select, and then with an intent i pass the field selected to another activity which executes the query select.

My question is, how can i make the dynamic query? Think that i have more than 1 filter, how can i make the clause WHERE depending on the data i passed from the other activity by an intent?

thanks

回答1:

Lazy way but tried and true:

String query = "Select id FROM books WHERE 1=1"
if (condition1) query+= " AND name="+theName;
if (condition2) query+= " AND author="+theAuthor;
if (condition3) query+= " AND price="+thePrice;

If you have full control of options aka via spinners, this is safe. If its an edittext, use preparedStatements and bind the arguments to avoid SQLI.

回答2:

public Cursor rawQuery (String sql, String[] selectionArgs)

Generate String SQL with ? for binding and add arguments in selectionArgs.

回答3:

Not sure this is the smartiest way to do it, but assume you know in advance you can have 1 integer filters (price) and 1 string filter (author name), I'd try :

SELECT * FROM BOOKS WHERE (price<0 OR AND BOOKS.price = price ) AND (author="" OR BOOKS.author = author);

I'm not an SQLite expert, please check syntax. The trick is here to set price < 0 if the filter is not set (hence all lines are taken into account since condition price<0 is true), and set author as an empty string to not to filter on the author (SELECT will not filter out these lines since condition is true).

This will work !

回答4:

boolean filterName = false;
boolean filterPrice = false;
ArrayList<String> selectionArgs = new ArrayList<String>();
String query = "SELECT * FROM BOOKS WHERE 1=1";
if(filterName) {
    selectionArgs.add(searchString);
    query += " AND NAME = ?";
}
if(filterPrice) {
    selectionArgs.add(priceString);
    query += " AND PRICE= ?";
}

Cursor c = m_Database.rawQuery(query, (String[])selectionArgs1.toArray());