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 bo

Solution 1:

Lazy way but tried and true:

Stringquery="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.

Solution 2:

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

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

Solution 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 !

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