Filtering concrete data with SQLite

Question

I have this simple sqlite database:

_id  ----- name -----  age  -----  phone

01        John         34           111
02        Mary         44           222
03        Anna         53           333

To show all the contacts in my list, I am using the cursor as follows:

public Cursor loadCursorContacts (){

    String[] columns = new String[] {CN_ID, CN_NAME, CN_PHONE, CN_AGE};

    return db.query(TABLE_NAME, columnas, null, null, null, null, CN_AGE+" ASC");

}

My intention is to show only contacts with age over 40 years. I'm looking for and I can find the way to do it with rawQuery (although I have not managed to apply it to a cursor), but I can not find the way to do it with the sqlite syntax db.query, as I have in my code.

What would be the correct way to do it?

Thanks in advance.

Answer 1

You need to use the 3d and 4th argument of the query() method like this:

db.query(
    TABLE_NAME, 
    columnas, 
    "age > ?", 
    new String[] {String.valueOf(40)}, 
    null, null, CN_AGE+" ASC"
);

The 3d argument is the WHERE part of the SQL statement (without the keyword WHERE) and may include the placeholders ? which will be replaced by the items of the array in the 4th argument.
So the placeholder ? in the 3d argument will be replaced by "40" and the statement will eventually be:

SELECT <columnas> FROM <TABLE_NAME> WHERE age > 30