在ContentResolver中使用Group By

缘起

使用ContentProvider查询短信，希望可以在ContentResolver.query中使用Group By ，发现系统并没有提供接口或者可用字段。

探究竟

首先我们来看看query函数：

/**
     * <p>
     * Query the given URI, returning a {@link Cursor} over the result set.
     * </p>
     * <p>
     * For best performance, the caller should follow these guidelines:
     * <ul>
     * <li>Provide an explicit projection, to prevent
     * reading data from storage that aren't going to be used.</li>
     * <li>Use question mark parameter markers such as 'phone=?' instead of
     * explicit values in the {@code selection} parameter, so that queries
     * that differ only by those values will be recognized as the same
     * for caching purposes.</li>
     * </ul>
     * </p>
     *
     * @param uri The URI, using the content:// scheme, for the content to
     *         retrieve.
     * @param projection A list of which columns to return. Passing null will
     *         return all columns, which is inefficient.
     * @param selection A filter declaring which rows to return, formatted as an
     *         SQL WHERE clause (excluding the WHERE itself). Passing null will
     *         return all rows for the given URI.
     * @param selectionArgs You may include ?s in selection, which will be
     *         replaced by the values from selectionArgs, in the order that they
     *         appear in the selection. The values will be bound as Strings.
     * @param sortOrder How to order the rows, formatted as an SQL ORDER BY
     *         clause (excluding the ORDER BY itself). Passing null will use the
     *         default sort order, which may be unordered.
     * @return A Cursor object, which is positioned before the first entry, or null
     * @see Cursor
     */
    public final Cursor query(Uri uri, String[] projection,
            String selection, String[] selectionArgs, String sortOrder) {
        return query(uri, projection, selection, selectionArgs, sortOrder, null);
    }

最有可能可以处理的地方就是selection，我们首先尝试设置 selection ＝ "gourp by thread_id" 执行程序，从错误日志中发现，sql语句经过编译处理加上了括号，像下面这样：

SELECT _id, thread_id, address, person, body, date FROM sms WHERE (type=1) AND (group by thread_id) ORDER BY date DESC

看来要最终得到正确的sql语句，selection的设置需要有技巧性一些。再尝试selection ＝ "0=0) group by (thread_id"，这次成功了！主要是注意括号的处理，以及AND后面必须有一个查询条件，这里我们使用了0=0(此处验证0==0亦可)这个永真查询只是为了保证sql语句的正确性。

最后处理效果如下：

  Uri SMS_PROVIDER = Uri.parse("content://sms/inbox");
  String[] projection = new String[] {
  	"_id", "thread_id", "address",
  	"person", "body", "date"};

  Cursor cursor = context.getContentResolver().query(SMS_PROVIDER,
  	projection, "0=0) group by (thread_id", null, "date desc");

额外赠送

嘿嘿，其实算不上赠送。这篇文来自于一个垃圾短信举报小软件处理过程中遇到的问题。该软件已上传Github ，如有需要可自取。