I am trying to fetch data which contains specific string but query is not working, following is method for fetching data.
public Cursor getSearch(String item) {
SQLiteDatabase db = this.getReadableDatabase();
String mQuery = "SELECT * FROM hadiths WHERE text LIKE %"+item.toString()+"%";
Cursor cursor = db.rawQuery(mQuery, null);
return cursor;
}
Logcat shows following error.
Caused by: android.database.sqlite.SQLiteException: near "%": syntax error (code 1): , while compiling: SELECT * FROM hadiths WHERE text LIKE %fast%
I know that the wildcard %% and string variable item is causing issue but how do I use string variable along with wildcard?
SQLite doesn't support native variable syntax, but you can achieve virtually the same using an in-memory temp table.
Queries with leading wildcards generally are slow because they are not sargable. If by chance they pick up an index, they will usually full scan the index. Below is one example of a typical query that you might see when using a leading wildcard.
In SQL, there are only two defined wildcard characters: _ : When used as a wildcard, an underscore represents a single character. For example, s_mmy would match sammy , sbmmy , or sxmmy . % : The percentage sign wildcard represents zero or more characters.
Edit:
As mentioned below by Jiří, parameters should be used to help prevent SQL injection issues.
In order to add parameters you could do something similar to this:
String mQuery = "SELECT * FROM hadiths WHERE text LIKE ?”;
String param1 = “%” + item + ”%”;
Cursor cursor = db.rawQuery(mQuery, new String [] {param1});
In order to add another parameter:
String mQuery = "SELECT * FROM hadiths WHERE text LIKE ? AND Name = ?”;
String param1 = “%” + item + ”%”;
String param2 = name;
Cursor cursor = db.rawQuery(mQuery, new String [] {param1, param2});
This code is a bit cumbersome, but it is to illustrate that the parameters must be added in the order in which they are should be added to the query.
see SQLite documentation: https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase
Original answer here for posterity. WARNING Dangerous SQL injection issue!
You need to add single quotes to the query.
String mQuery = "SELECT * FROM hadiths WHERE text LIKE '%"+item+"%'";
Take a look at the SQLite docs: https://www.tutorialspoint.com/sqlite/sqlite_like_clause.htm
Note: There is no need to use toString()
since "item" is already of type String
.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With