Java 休眠条件子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3738555/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Hibernate Criteria Subquery
提问by Gonzalo
I need to do this SQL query with detachedCriteria:
我需要使用 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)
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:
这个想法是从不属于用户的游戏中获取 id。我用 detachedCriteria 尝试了 10 种不同的方法，但我得到了“未知实体：空”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));
Setting also the projections to return only the id of the games.
还设置投影以仅返回游戏的 id。
Any ideas? I think Hibernate has some trouble joining the queries with no alias. Adding alias works but the results are quite wrong.
有任何想法吗？我认为 Hibernate 在加入没有别名的查询时遇到了一些麻烦。添加别名有效，但结果非常错误。
采纳答案by atrain
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
那应该有帮助
回答by karen
Try
尝试
SELECT g.id FROM users_games ug join ug.game g
WHERE NOT EXISTS (SELECT 1 FROM WHERE ug.user_id = 1)
回答by philip
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.
我希望这有帮助。
