CODEWITHSUNDEEP
androidsqlitewhere-clauseandroid-contentresolver
JJD
JJD

Reputation: 51932

Android: SQLite query with selection WHERE clause is not processed

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:

  1. Is the WHERE clause syntactically correct?
  2. Do I need apostrophs?
  3. Am I forced to use both parameters (selection, selectionArgs) at a time in a query?
  4. 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);
}

Upvotes: 2

Views: 6515

Answers (2)

biegleux
biegleux

Reputation: 13247

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.

Upvotes: 2

Barak
Barak

Reputation: 16393

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...).

Upvotes: 1

Related Questions