[学习]SQLite操作（二）

今天学习了数据库的简单的查询操作。
现在还没有用上高级的查询，而是普通的查询。

今天这个是接着昨天的，使用昨天创建的表。

查询使用 query 函数，看函数定义：

  /**
     * Query the given table, returning a {@link Cursor} over the result set.
     *
     * @param table The table name to compile the query against.
     * @param columns A list of which columns to return. Passing null will
     *            return all columns, which is discouraged to prevent reading
     *            data from storage that isn't going to be used.
     * @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 table.
     * @param selectionArgs You may include ?s in selection, which will be
     *         replaced by the values from selectionArgs, in order that they
     *         appear in the selection. The values will be bound as Strings.
     * @param groupBy A filter declaring how to group rows, formatted as an SQL
     *            GROUP BY clause (excluding the GROUP BY itself). Passing null
     *            will cause the rows to not be grouped.
     * @param having A filter declare which row groups to include in the cursor,
     *            if row grouping is being used, formatted as an SQL HAVING
     *            clause (excluding the HAVING itself). Passing null will cause
     *            all row groups to be included, and is required when row
     *            grouping is not being used.
     * @param orderBy 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 {@link Cursor} object, which is positioned before the first entry. Note that
     * {@link Cursor}s are not synchronized, see the documentation for more details.
     * @see Cursor
     */
    public Cursor query(String table, String[] columns, String selection,
            String[] selectionArgs, String groupBy, String having,
            String orderBy) {

        return query(false, table, columns, selection, selectionArgs, groupBy,
                having, orderBy, null /* limit */);
    }

参数含义：

• table 要查询的表名；
• columns 要返回的列的列表。传递null将返回所有列，这是不鼓励使用 null 参数，以防止从存储中读取不使用的数据；
• selection 声明要返回哪些行的筛选器，也就是 SQL 里面的 WHERE 子句（WHERE本身除外）。传递null将返回给定表的所有行；
• selectionArgs 专门用于替换 selection 中的 ? 的位置；
• groupBy 声明如何对行进行分组的筛选器，格式为SQL group BY子句（不包括组本身）。传递null将导致行不分组；
• having 如果正在使用行分组，则筛选器将声明游标中要包含的行组格式化为SQL HAVING子句（HAVING本身除外）。传递null将导致包含所有行组，并且在不使用行分组时是必需的。
• orderBy 如何对行进行排序，格式化为SQL order BY子句（不包括order本身）。传递null将使用默认的排序顺序，这可能是无序的。

下面看具体的使用：

// 如果要进行读取操作，首先获取其对应的对象
val db = dbHelper.readableDatabase

接下来就是组装上面说的参数：

val projection = arrayOf(BaseColumns._ID, FeedReaderContract.FeedEntry.COLUMN_NAME_TITLE, FeedReaderContract.FeedEntry.COLUMN_NAME_SUBTITLE)
val selection = "${FeedReaderContract.FeedEntry.COLUMN_NAME_TITLE} = ?"
val selectionArgs = arrayOf("语言")
val sortOrder = "${FeedReaderContract.FeedEntry.COLUMN_NAME_SUBTITLE} DESC"

剩下的就使用 null 代替。接下来拿到 Cursor 对象：

val cursor = db.query(FeedReaderContract.FeedEntry.TABLE_NAME, projection, selection, selectionArgs, null, null, sortOrder)

然后从里面拿到自己想要的数据：

val itemIds = mutableListOf<String>()
with(cursor) {
    while (moveToNext()) {
        val itemId = getString(getColumnIndexOrThrow(FeedReaderContract.FeedEntry.COLUMN_NAME_SUBTITLE))
                itemIds.add(itemId)
        }
}

这样就可以看到所有 title 为“语言”的 subtitle 了。