android - 房间数据库无法返回 group_concat 列
问题描述
我正在尝试在房间中创建一个 DatabaseView 以从多个表中获取所需的数据。这是我的数据库视图:
@DatabaseView("SELECT site.name AS address, group_site.name AS groupName, group_site.member_id AS memberId " +
"FROM site, group_site " +
"INNER JOIN groupsite_join_site " +
"ON site.id = groupsite_join_site.site_id AND group_site.id = groupsite_join_site.group_site_id " +
"UNION SELECT preloaded_site.name AS address, preloaded_group_site.name AS groupName, preloaded_group_site.memberId AS memberId " +
"FROM preloaded_site, preloaded_group_site " +
"INNER JOIN preloaded_groupsite_join_site ON preloaded_site.id = preloaded_groupsite_join_site.site_id AND preloaded_site.id = preloaded_groupsite_join_site.site_id"
)
public class SiteDetail {
long memberId;
String address;
String groupName;
}
这是我的 DAO:
@Dao
public interface SiteDetailDao {
@Query("SELECT address, group_concat(groupName, ', '), memberId FROM sitedetail WHERE memberId = :memberId OR memberId = -1 GROUP BY address LIMIT 10")
LiveData<List<SiteDetail>> getSiteDetailForMember(long memberId);
}
视图是按预期创建的,但是,视图上的查询将所有 groupNames 都返回为 null。我已经检查了在 sqlite 本身上运行查询的 DB Browser 查询。DB Browser 返回预期的数据。一个包含 10 条记录的列表,每条记录如下:
|id| address | groupName |
| 1|youtube.com|videos, entertainment, streaming|
为什么会这样?房间的group_contact有问题吗?这是 SiteDetailDao_Impl:
@Override
public LiveData<List<SiteDetail>> getSiteDetailForMember(final long memberId) {
final String _sql = "SELECT address, group_concat(groupName, ', '), memberId FROM sitedetail WHERE memberId = ? OR memberId = -1 GROUP BY address LIMIT 10";
final RoomSQLiteQuery _statement = RoomSQLiteQuery.acquire(_sql, 1);
int _argIndex = 1;
_statement.bindLong(_argIndex, memberId);
return __db.getInvalidationTracker().createLiveData(new String[]{"sitedetail"}, false, new Callable<List<SiteDetail>>() {
@Override
public List<SiteDetail> call() throws Exception {
final Cursor _cursor = DBUtil.query(__db, _statement, false);
try {
final int _cursorIndexOfAddress = CursorUtil.getColumnIndexOrThrow(_cursor, "address");
final int _cursorIndexOfMemberId = CursorUtil.getColumnIndexOrThrow(_cursor, "memberId");
final List<SiteDetail> _result = new ArrayList<SiteDetail>(_cursor.getCount());
while(_cursor.moveToNext()) {
final SiteDetail _item;
_item = new SiteDetail();
final String _tmpAddress;
_tmpAddress = _cursor.getString(_cursorIndexOfAddress);
_item.setAddress(_tmpAddress);
final long _tmpMemberId;
_tmpMemberId = _cursor.getLong(_cursorIndexOfMemberId);
_item.setMemberId(_tmpMemberId);
_result.add(_item);
}
return _result;
} finally {
_cursor.close();
}
}
@Override
protected void finalize() {
_statement.release();
}
});
}
解决方案
发布的内容SiteDetailDao_Impl
表明没有生成代码来获取 result-column 的值group_concat(groupName, ', ')
。我猜这是因为它没有简单的名称。因此,正如@sergiytikonov 在他的评论中所建议的那样,您需要为该列提供别名。就像是:
group_concat(groupName, ', ') AS concatName
要支持新别名,请创建另一个类(您喜欢的名称):
public class SiteInfo {
long memberId;
String address;
String concatName;
}
并将您的查询更改为返回LiveData<List<SiteInfo>>
:
@Query("SELECT address, group_concat(groupName, ', ') AS concatName, memberId FROM sitedetail'" +
" WHERE memberId = :memberId OR memberId = -1 GROUP BY address LIMIT 10")
LiveData<List<SiteInfo>> getSiteDetailForMember(long memberId);
推荐阅读
- jquery - 为 CatComplete 设置 jquery 数组
- android - 发布版本时 app:mergeReleaseResources 异常
- linux - 外部服务登录链接 asp core
- javascript - 向 ajax url 提供 csv 文件输入以显示在 html 表上
- security - 除了 Meltdown 和 Spectre 之外,还有其他处理器漏洞吗?
- java - JAVA 贪吃蛇游戏错误。蛇在 3 步后停止
- mysql - 带有列表变量的熊猫查询
- vba - 根据单元格内容更改 Word 表格单元格背景颜色。
- python - 我可以将 Django 模型对象作为单例获取吗?
- android - 以编程方式更改键盘输入语言