How to perform an SQLite query within an Android application?

Question

I am trying to use this query upon my Android database, but it does not return any data. Am I missing something?

SQLiteDatabase db = mDbHelper.getReadableDatabase();
    String select = "Select _id, title, title_raw from search Where(title_raw like " + "'%Smith%'" +
    ")";        
    Cursor cursor = db.query(TABLE_NAME, FROM, 
            select, null, null, null, null);
    startManagingCursor(cursor);
    return cursor;

Rescission

try this for your correct query. https://mcmap.net/q/276271/-search-a-value-from-sqlite-database-and-retrieve-in-listview – Ko

Answer 1

This will return you the required cursor

Cursor cursor = db.query(TABLE_NAME, new String[] {"_id", "title", "title_raw"}, 
                "title_raw like " + "'%Smith%'", null, null, null, null);

Gianna

Do you just pass null if you want the where condition to be "everything"? – Eslinger

@Matt: Yes, simply pass null for no where caluse – Fricative

To improve caching, you can separate selection and selection-arguments like this: ..., "title_raw like ?", new String[]{"'%Smith%'"}, .... Source: android documentation. – Subtangent

@Subtangent Where is the connection between SQLiteDatabase and ContentResolver? The link to SQLite is: developer.android.com/reference/android/database/sqlite/… There they don't say anything about caching. I think separate arguments should be used against sql injection. Otherwise they're not very important, I think. – Nacre

Answer 2

Alternatively, db.rawQuery(sql, selectionArgs) exists.

Cursor c = db.rawQuery(select, null);

Coccidioidomycosis

Answer 3

This will also work if the pattern you want to match is a variable.

dbh = new DbHelper(this);
SQLiteDatabase db = dbh.getWritableDatabase();

Cursor c = db.query(
    "TableName", 
    new String[]{"ColumnName"}, 
    "ColumnName LIKE ?", 
    new String[]{_data+"%"}, 
    null, 
    null, 
    null
);

while(c.moveToNext()){
    // your calculation goes here
}

Leach

Answer 4

I came here for a reminder of how to set up the query but the existing examples were hard to follow. Here is an example with more explanation.

SQLiteDatabase db = helper.getReadableDatabase();

String table = "table2";
String[] columns = {"column1", "column3"};
String selection = "column3 =?";
String[] selectionArgs = {"apple"};
String groupBy = null;
String having = null;
String orderBy = "column3 DESC";
String limit = "10";

Cursor cursor = db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);

Parameters

• table: the name of the table you want to query
• columns: the column names that you want returned. Don't return data that you don't need.
• selection: the row data that you want returned from the columns (This is the WHERE clause.)
• selectionArgs: This is substituted for the ? in the selection String above.
• groupBy and having: This groups duplicate data in a column with data having certain conditions. Any unneeded parameters can be set to null.
• orderBy: sort the data
• limit: limit the number of results to return

Ransom

Answer 5

Try this, this works for my code name is a String:

cursor = rdb.query(true, TABLE_PROFILE, new String[] { ID,
    REMOTEID, FIRSTNAME, LASTNAME, EMAIL, GENDER, AGE, DOB,
    ROLEID, NATIONALID, URL, IMAGEURL },                    
    LASTNAME + " like ?", new String[]{ name+"%" }, null, null, null, null);

Regimen