Android: SQLite query with selection WHERE clause is not processed

Question

I am trying to limit the database result by defining a SQL WHERE clause in the selection string of the query of the ContentResolver.

Cursor cursor = getContentResolver().query(uri, null, getSelectionString(), null, null);

...

public String getSelectionString() {
    // TODO Replace latitude and longitude with database reference.
    StringBuilder string = new StringBuilder();
    string.append("latitude >= ").append(northEast.getLatitudeE6() / 1e6);
    string.append(" AND ");
    string.append("latitude <= ").append(southWest.getLatitudeE6() / 1e6);
    string.append(" AND ");
    string.append("longitude >= ").append(southWest.getLongitudeE6() / 1e6);
    string.append(" AND ");
    string.append("longitude <= ").append(northEast.getLongitudeE6() / 1e6);
    return string.toString();
}

The database columns are defined as follows ...

public class CustomDatabase {
    public static final class Contract {
        public static final String COLUMN_NAME = "name";
        public static final String COLUMN_LATITUDE = "latitude";
        public static final String COLUMN_LONGITUDE = "longitude";
    }
}

...

I am not particularly sure that I can inspect the query sent in cursor. If so, it does not contain the WHERE clause I sent:

SQLiteQuery: SELECT * FROM custom_db ORDER BY number ASC

Here is an example of the selection string:

latitude >= 48.203927 AND latitude <= 48.213851 AND longitude >= 16.36735 AND longitude <= 16.377648

Questions:

Is the WHERE clause syntactically correct?
Do I need apostrophs?
Am I forced to use both parameters (selection, selectionArgs) at a time in a query?
Where can I debug the whole query?

EDIT:

Here is the query() method of the ContentProvider ...

public class CustomProvider extends ContentProvider {

    @Override
    public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
        switch (URI_MATCHER.match(uri)) {
        case URI_CODE_LOCATIONS:
            return mCustomDatabase.getLocations();
        }
        return null;
    }

... obviously, as biegleux guessed, I forgot to pass the parameters. Doh!
Here is the current implementation of the database method ...

public class CustomSQLiteOpenHelper extends SQLiteOpenHelper {

    public Cursor getLocation() {
        return mDatabaseHelper.getReadableDatabase().query(
            CustomSQLiteOpenHelper.TABLE_NAME,
            null, null, null, null, null, null);
}

Do you suggest that I change the method signature to the following to pass all parameters? Am I not exposing to much of the database interface this way?

public Cursor getLocations(String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {
    return mDatabaseHelper.getReadableDatabase().query(
            CustomSQLiteOpenHelper.TABLE_NAME,
            columns, selection, selectionArgs, groupBy, having, orderBy);
}

Nice. You need to rewrite your comment as an answer so I can grant you the correct response. — JJD
– JJD, Commented Jul 14, 2012 at 14:26
BTW, not related to your issue, but a possible (slight) performance improvement, you could simplify the query by using the BETWEEN keyword. latitude BETWEEN #### AND #### AND longitude BETWEEN #### AND #### — Barak
– Barak, Commented Jul 14, 2012 at 14:55
@Barak That is nice. I was not aware of this alternative. Thank very much, it works. — JJD
– JJD, Commented Jul 14, 2012 at 15:05

biegleux · Accepted Answer · 2012-07-14 14:44:10Z

2

Ok, so it seems query() method of your provider is causing problems. Make sure it looks like following.

@Override
public abstract Cursor query (Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) {
...

// run the query
db.query(db, projection, selection, selectionArgs, groupBy, having, sortOrder, limit);

You are not forced to use selectionArgs parameter, but with it code is more readable. To debug a query you can use

SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
Log.e(TAG, qb.buildQuery(projection, selection, selectionArgs, groupBy, having, sortOrder, limit);

EDIT: If you have a ContentProvider implemented you don't need to expose getLocations() method as you/users can use ContentProvider's query() method. You should pass at least those arguments whose can be passed in query() method and those are projection, selection, selectionArgs and sortOrder.

edited Jul 14, 2012 at 14:44

answered Jul 14, 2012 at 14:37

biegleux

13.3k11 gold badges47 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JJD Over a year ago

Thank you. You comment was very helpful! So I limit the parameters to the following: getLocations(String[] columns, String selection, String[] selectionArgs, String orderBy) as I only can pass those through Cursor.query(). Correct?

biegleux Over a year ago

Yes, exactly. This way you always get the same result regardless of which method of query() or getLocations() you call.

Barak · Accepted Answer · 2012-07-14 13:32:43Z

1

Shouldn't that line be:

Cursor cursor = getContentResolver().query(uri, null, getSelectionString(), null, null);

Note the added parens after getSelectionString to indicate it's a method call, not a string (although I do wonder why that wouldn't throw an error as getSelectionString wouldn't exist as a string if my theory is correct...).

edited Jul 14, 2012 at 13:32

answered Jul 14, 2012 at 13:27

Barak

16.4k9 gold badges54 silver badges86 bronze badges

Collectives™ on Stack Overflow

Android: SQLite query with selection WHERE clause is not processed

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related