Group By in ContentResolver in Ice Cream Sandwich

问题:

I am making a query on the Android Contacts ContentProvider. I need a Group By clause. In Gingerbread and Honeycomb, I do something like this to search phone numbers and emails at the same time:

(The actual WHERE clause is much more complicated as it includes types checks. This is a simplification, but it yields the same result)

String request = Phone.NUMBER + " LIKE ? OR " + Email.DATA + " LIKE ?";
String[] params = new String["%test%", "%test%"];

Cursor cursor = getContentResolver().query(
    Data.CONTENT_URI,
    new String[] { Data._ID, Data.RAW_CONTACT_ID },
    request + ") GROUP BY (" + Data.RAW_CONTACT_ID,
    params, "lower(" + Data.DISPLAY_NAME + ") ASC");

The injection of the ')' finishes the WHERE clause and allow the insertion of a GROUP BY clause.

However, in Ice Cream Sandwich, it appears that the ContentProvider detects this and adds the correct number of parenthesis to prevent my injection. Any other way of doing this in a single cursor query?

Edit

Currently, I have removed the GROUP BY, and added a MatrixCursor to limit the impact, but I'd rather have a real cursor:

MatrixCursor result = new MatrixCursor(new String[] { Data._ID, Data.RAW_CONTACT_ID });
Set<Long> seen = new HashSet<Long>();
while (cursor.moveToNext()) {
    long raw = cursor.getLong(1);
    if (!seen.contains(raw)) {
        seen.add(raw);
        result.addRow(new Object[] {cursor.getLong(0), raw});
    }
}

回答1:

You could create a custom Uri such that when your UriMatcher in your ContentProvider gets it, you can insert your group by clause and then execute the raw sql directly on the database.

回答2:

first off all excuse my POOR English! I'm new to Java/Android, started with 4.2.1 and fight with that too almost 2 days, then i start reading some more details about SQLiteQueryBuilder the query part is pretty much that what u are looking for ;)

it have:

public Cursor query (SQLiteDatabase db, String[] projectionIn, String selection, String[] selectionArgs, String groupBy, String having, String sortOrder)

the query "function" of the Content Provider only gives you:

query(Uri uri, String[] projection, String selection,String[] selectionArgs, String sortOrder)

here u can trick around, i will post you my code snip:

    @Override
public Cursor query(Uri uri, String[] projection, String selection,
        String[] selectionArgs, String sortOrder) {
    SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
    final SQLiteDatabase db = mOpenHelper.getReadableDatabase();
/* a String is a Object, so it can be null!*/
    String groupBy = null;
    String having = null;

    switch (sUriMatcher.match(uri)) {
...
...
...
        case EPISODES_NEXT:
        groupBy = "ShowID";
        queryBuilder.setTables(EpisodenTable.TableName);
        break;
    default:
        throw new IllegalArgumentException("Unknown URI " + uri);
    }

    Cursor c = queryBuilder.query(db, projection, selection, selectionArgs,
            groupBy, having, sortOrder);
    c.setNotificationUri(getContext().getContentResolver(), uri);
    return c;
}

thats its!

here the code i use to execute:

        Cursor showsc = getContext().getContentResolver().query(
            WhatsOnTVProvider.CONTENT_EPISODES_NEXT_URI,
            EpisodenTable.allColums_inclCount,
            String.valueOf(Calendar.getInstance().getTimeInMillis() / 1000)
                    + " < date", null, null);