android - 使用 SQLiteQueryBuilder buildUnionSubQuery() 方法

Question

我有 2 个表，如下所示:

Table1

_id | _key | _mime_type | _hash
---------------------------

和

Table2

_id | _path | _mimetype
-------------------

我可以通过我的内容提供者 query() 方法中的以下内容执行 UNION 查询来获取包含我尝试获取的结果集的有效游标:

String unionQuery = " SELECT "
                        + Table1._ID + " AS _id, "
                        + Table1.QUICK_KEY + " AS _key, "
                        + Table1.MIME_TYPE + " AS _mime,"
                        + Table1.HASH + " AS _hash,"
                        + "NULL as _path_to_file"
                        + " FROM " + Table1
                        + " UNION "
                        + " SELECT "
                        + Table2._ID + " AS _id, "
                        + "NULL AS _key, "
                        + Table2.MIME_TYPE + " AS _mime,"
                        + "NULL AS _hash,"
                        + Table2.PATH + " as _path_to_file"
                        + " FROM " + Table2;

                return db.rawQuery(unionQuery, null);

我不想手动编写上面的语句，而是想使用 SQLiteQueryBuilder 类中的 buildUnionSubQuery() 方法。

buildUnionSubQuery() 文档位于 http://developer.android.com/reference/android/database/sqlite/SQLiteQueryBuilder.html

我一直在尝试使用该方法，但没有成功创建我手动编写的 SQL 语句。

我尝试过的代码示例(我尝试了大约 10 种不同的东西)是: SQLiteQueryBuilder cloudQueryBuilder = new SQLiteQueryBuilder(); SQLiteQueryBuilder localQueryBuilder = new SQLiteQueryBuilder();

                String typeDiscriminatorColumn = "_table";

                Set<String> cloudColumns = new HashSet<String>();
                cloudColumns.add(FileInfos.column(Table1._ID));
                cloudColumns.add(FileInfos.column(Table1.QUICK_KEY));
                cloudColumns.add(FileInfos.column(Table1.MIME_TYPE));
                cloudColumns.add(FileInfos.column(Table1.HASH));

                cloudQueryBuilder.setTables(Table1);
                String cloudTableQuery = cloudQueryBuilder.buildUnionSubQuery(typeDiscriminatorColumn, projection, cloudColumns, 0, Table1, selection, null, null);

使用该代码，这是我记录查询语句时得到的结果:

SELECT quickkey, NULL AS _id, NULL AS hash, NULL AS created, NULL AS mimetype, NULL AS _id, NULL AS _mimetype, NULL AS _path FROM Table1

我尝试过的其他几个示例最终得到了相同的结果，其中几乎所有表列都是“NULL AS”

但我的目标是让查询语句(针对 UNION 查询中的每个 SELECT 语句)匹配我手动编写的联合语句。

列顺序无关紧要。

感谢任何帮助。

编辑:

我知道 buildUnionQueries() 用于添加多个 SELECT 语句，但问题是 SELECT 语句没有像我期望的那样创建(这意味着我错误地使用了 buildUnionSubquery() 方法，这就是我正在寻求帮助)

编辑:

除了排序似乎不起作用之外，以下代码大部分都有效:

SQLiteQueryBuilder cloudQueryBuilder = new SQLiteQueryBuilder();
SQLiteQueryBuilder localQueryBuilder = new SQLiteQueryBuilder();

String typeDiscriminatorColumn = "type";

                String[] cloudUnionColumns = {
                        FileInfos._ID,
                        FileInfos.QUICK_KEY,
                        FileInfos.MIME_TYPE,
                        FileInfos.CREATED,
                        FileInfos.HASH,
                        LocalItems.PATH,
                };

                Set<String> cloudColumns = new HashSet<String>();
                cloudColumns.add(FileInfos._ID);
                cloudColumns.add(FileInfos.QUICK_KEY);
                cloudColumns.add(FileInfos.MIME_TYPE);
                cloudColumns.add(FileInfos.HASH);
                cloudColumns.add(FileInfos.CREATED);

                cloudQueryBuilder.setTables(Tables.FILE_INFOS);
                String cloudTableQuery = cloudQueryBuilder.buildUnionSubQuery(typeDiscriminatorColumn, cloudUnionColumns, cloudColumns, 0, "cloud", selection, null, null);

                String[] localUnionColumns = {
                        LocalItems._ID,
                        FileInfos.QUICK_KEY,
                        LocalItems.MIME_TYPE,
                        LocalItems.CREATED,
                        FileInfos.HASH,
                        LocalItems.PATH,
                };
                Set<String> localColumns = new HashSet<String>();
                localColumns.add(LocalItems._ID);
                localColumns.add(LocalItems.PATH);
                localColumns.add(LocalItems.MIME_TYPE);
                localColumns.add(LocalItems.CREATED);

                localQueryBuilder.setTables(Tables.LOCAL_ITEMS);
                String localTableQuery = localQueryBuilder.buildUnionSubQuery(typeDiscriminatorColumn, localUnionColumns, localColumns, 0, "local", selection, null, null);

                Log.i(TAG, "cloud sub query - " + cloudTableQuery);
                Log.i(TAG, "local sub query - " + localTableQuery);

                String[] subQueries = {
                        cloudTableQuery,
                        localTableQuery,
                };
                sortOrder = "created DESC";
                String unionQuery = cloudQueryBuilder.buildUnionQuery(subQueries, sortOrder, null);
                Log.i(TAG, "query - " + unionQuery);

                return db.rawQuery(unionQuery, null);

我返回的示例行:

[_id: 33][quickkey: null][mimetype: image/jpeg][created: 1413327563000][hash: null][_path: /storage/emulated/0/WhatsApp/Media/WhatsApp Images/IMG-20141014-WA0001.jpg]

[_id: 47][quickkey: null][mimetype: image/jpeg][created: 1415751020000][hash: null][_path: /storage/emulated/0/Download/dota_2___clockwerk_by_sheron1030-d855txk.jpg]

我知道这些行是本地项目，因为 quickkey = null 和 hash = null

[_id: 181][quickkey: 88hcvu3t3y832p4][mimetype: image/gif][created: 1414021932000][hash: 86a15752bb550259d89e184bb5930306a9f3c8b390837b6f9b387816b6dcd461][_path: null]

[_id: 257][quickkey: 57b35uy9f12a462][mimetype: image/png][created: 1410831493000][hash: 3ad838b6a5543760b588655ad9a1714c19ecb1b91a326a21282596b2fb650c48][_path: null]

我知道这些行是云项目，因为 path = null

虽然排序不起作用，并且列名称没有别名(我也不知道如何)，因为我希望对“创建的”列进行排序，但它们不会针对整个结果集进行排序。而是分别对每个语句进行排序。

Answer 1

似乎x AS y该方法不支持。至少不是您需要的方式。

typeDiscriminator{Column|Value}

这些参数允许您在结果中有一列，允许您区分一行来自哪个联合子查询:

SELECT
   'one' AS sourceTable
   ...
   FROM table1
UNION SELECT
   'two' AS sourceTable
  ...
  FROM table2

在哪里

• typeDiscriminatorColumn是"sourceTable" ,
• typeDiscriminatorValue是"one"在第一个 unionSubQuery 中，"two"在第二个
• unionColumns两者都是常数，并且必须包括 "sourceTable"

---

unionColumns对比columnsPresentInTable

unionColumns或多或少是最后的 projection .虽然列得到了修改，最重要的是取决于 columnsPresentInTable .但它所做的只是保持不变或用 "NULL AS " + unionColumnEntry 替换它。如果该列不存在。这允许您对具有完全相同列名的表进行联合查询:

SELECT
   column1,
   column2,
   NULL AS column3
   ...
UNION SELECT
   NULL AS column1,
   column2,
   column3,
   ...

通过以下内容:

• unionColumns作为{ "column1", "column2", "column3" }
• columnsPresentInTable作为{ "column1", "column2" }在第一个子查询中，{ "column2", "column3" }在第二个。

NULL AS <whatever>在你的情况下是因为你根本没有相同的列名。并通过 AS 进行映射不幸的是，这不是这种方法的目的。

---

你能做的就是使用稍微手动#buildQuery手动处理 NULL 时的方法和 AS零件

String[] projectionIn = {
   Table1._ID + " AS _id",
   ...
   "NULL as _path_to_file"
}
cloudQueryBuilder.buildQuery(projectionIn, null, null, ...);

或者您自己编写一个类似的方法来实际执行您想要的操作。

引用资料:

• sourcecode of buildUnionSubQuery