Hibernate 标准子查询

发布于 2024-09-24 06:17:15 字数 697 浏览 3 评论 0原文

我需要使用 detachedCriteria 执行此 SQL 查询:

SELECT g.id FROM games g
WHERE NOT EXISTS (
    SELECT 1 FROM users_games ug WHERE ug.user_id = 1 AND g.id = ug.game_id)

这个想法是从不属于用户的游戏中获取 id。 我尝试了 10 种不同的 detachedCriteria 方法,但得到了“未知实体:null”MappingException 代码应如下所示:

DetachedCriteria subquery = DetachedCriteria.forClass(UserGame.class, "ug")
   .add(Restrictions.eq("ug.user.id", 1))
   .add(Restrictions.eqProperty("ug.game.id","u.id"));
DetachedCriteria criteria = DetachedCriteria.forClass(Game.class, "g")
   .add(Subqueries.notExists(subquery));

将投影设置为仅返回游戏的 id。

有什么想法吗? 我认为 Hibernate 在加入没有别名的查询时遇到一些问题。 添加别名可以工作,但结果是完全错误的。

I need to do this SQL query with detachedCriteria:

SELECT g.id FROM games g
WHERE NOT EXISTS (
    SELECT 1 FROM users_games ug WHERE ug.user_id = 1 AND g.id = ug.game_id)

The idea is to get the ids from the games that aren't owned by the user.
I tried like 10 different approaches with detachedCriteria but I get the "Unknown entity: null" MappingException
The code should look like:

DetachedCriteria subquery = DetachedCriteria.forClass(UserGame.class, "ug")
   .add(Restrictions.eq("ug.user.id", 1))
   .add(Restrictions.eqProperty("ug.game.id","u.id"));
DetachedCriteria criteria = DetachedCriteria.forClass(Game.class, "g")
   .add(Subqueries.notExists(subquery));

Setting also the projections to return only the id of the games.

Any ideas?
I think Hibernate has some trouble joining the queries with no alias.
Adding alias works but the results are quite wrong.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

梦幻的心爱 2024-10-01 06:17:15

您需要添加一个别名,如下所示:

DetachedCriteria subquery = DetachedCriteria.forClass(UserGame.class, "ug")
   .addAlias("ug.user", "user")
   .add(Restrictions.eq("user.id", 1))
   .addAlias("ug.game", "game")
   .add(Restrictions.eqProperty("game.id","u.id"));

这应该有帮助

You need to add an alias, as follows:

DetachedCriteria subquery = DetachedCriteria.forClass(UserGame.class, "ug")
   .addAlias("ug.user", "user")
   .add(Restrictions.eq("user.id", 1))
   .addAlias("ug.game", "game")
   .add(Restrictions.eqProperty("game.id","u.id"));

That should help

相守太难 2024-10-01 06:17:15

您需要一个投影并指定需要匹配的属性。

DetachedCriteria subquery = DetachedCriteria.forClass(UserGame.class, "ug")
.add(Restrictions.eq("ug.user.id", 1))
.add(Restrictions.eqProperty("ug.game.id","u.id"))
.setProjection(Projections.property("ug.game.id"));

DetachedCriteria criteria = DetachedCriteria.forClass(Game.class, "g")
.add(Property.forName("g.id").notIn(subquery));

我希望这有帮助。

You need a projection and specifies which attribute that needs to be matched.

DetachedCriteria subquery = DetachedCriteria.forClass(UserGame.class, "ug")
.add(Restrictions.eq("ug.user.id", 1))
.add(Restrictions.eqProperty("ug.game.id","u.id"))
.setProjection(Projections.property("ug.game.id"));

DetachedCriteria criteria = DetachedCriteria.forClass(Game.class, "g")
.add(Property.forName("g.id").notIn(subquery));

I hope that helps.

剩一世无双 2024-10-01 06:17:15

尝试

SELECT g.id FROM  users_games ug join ug.game g
WHERE NOT EXISTS (SELECT 1 FROM WHERE ug.user_id = 1)

Try

SELECT g.id FROM  users_games ug join ug.game g
WHERE NOT EXISTS (SELECT 1 FROM WHERE ug.user_id = 1)
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文