Hibernate Criteria子查询 [英] Hibernate Criteria Subquery

问题描述

  SELECT g.id FROM游戏g 
 WHERE NOT EXISTS（
 SELECT 1 FROM users_games ug WHERE ug.user_id = 1 AND g.id = ug.game_id）
  

这个想法是从不属于用户的游戏中获取ID。
我使用detachedCriteria尝试了10种不同的方法，但是我得到了Unknown entity：nullMappingException
代码应该如下所示：

  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在加入没有别名的查询时遇到了一些麻烦。
添加别名有效，但结果是错误的。

解决方案

您需要添加一个别名，如下所示：

  DetachedCriteria子查询= DetachedCriteria.forClass（UserGame.class，ug）
 .addAlias（ug.user ，user）
 .add（Restrictions.eq（user.id，1））
 .addAlias（ug.game，game）
 .add（限制.eqProperty（ game.id， u.id））; 
  

这应该有所帮助

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.

解决方案

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

这篇关于Hibernate Criteria子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！