Hibernate标准与自我加入 [英] Hibernate Criteria with self join

问题描述

上周一直在学习Hibernate，Spring和JPA，并试图为以下场景创建一个Criteria：

假设我有两个表：

游戏

PlayedGame

• id


• account_ref - >引用某个帐户表


• game_id - >引用游戏

实体映射：

 游戏{
 
 @id 
长ID; 
 
 @OneToMany（mappedBy =game）
 Set< Player>玩家; 
 
} 
 
 PlayedGame {
 
 @id 
 Long id; 
 
长账户名称; 
 
 @ManyToOne 
 @JoinColumn（name =game_id，referencedColumnName =id）
游戏游戏; 
 
 $ / code> 

现在我想要查询以下场景：
- I想要找到特定玩家（P）玩过的所有游戏（A）是其中的一部分。更具体地说，属于同一游戏的两个人

在SQL中，可以用类似的查询来完成：

  SELECT DISTINCT p1。* FROM Player as p1 
 INNER JOIN Player as p2 ON p1.game_id = p2.game_id 
 WHERE p1.account_ref = P AND p2.account_ref = A 
  

使用Hibernate中的Criteria可以完成这项工作吗？

解决方案

也许可以使用Hibernate的Criteria API，但不能直接前进。

简单情况下需要连接两次相同的关联路径（一个用于 A ，另一个用于 P ）：

<$标准gameCriteria =（（HibernateEntityManager）em）.getSession（）。createCriteria（Game.class）;

Criteria playedGamesOfACriteria = gameCriteria.createCriteria（playedGames，pga）;
条件accountOfACriteria = playedGamesOfACriteria.createCriteria（account，a）;
accountOfACriteria.add（Restrictions.idEq（a.id））;

Criteria playedGamesOfPCriteria = gameCriteria.createCriteria（玩过游戏，pgp）;
条件accountOfPCriteria = playedGamesOfPCriteria.createCriteria（account，p）;
accountOfPCriteria.add（Restrictions.idEq（p.id））;

返回gameCriteria.list（）;


由于 HHH-879 。

但是您可以使用JPA查询：

 查询q = em.createQuery（
select g
 +from g
 +join g.playedGames pga
 +join pga.account a
 +join g.playedGames pgp
 +join pgp.account p
 +其中a = ？1和p =？2
）; 
 q.setParameter（1，a）; 
 q.setParameter（2，p）; 
 return q.getResultList（）; 
  

这个代码更少。

另外：有些人认为Criteria API是不建议使用。

Have been learning Hibernate, Spring and JPA the last week and got stuck on trying to create a Criteria for the following scenario:

Let's say I have 2 tables:

Game

• id

PlayedGame

• id
• account_ref -> reference to some account table
• game_id -> reference to the game

Entity mapping:

Game {

 @id
 Long id;

 @OneToMany(mappedBy = "game")
 Set<Player> players;

}

PlayedGame {

 @id
 Long id;

 Long account_ref;

 @ManyToOne
 @JoinColumn(name = "game_id", referencedColumnName = "id")
 Game game;
}

Now I want to query for the following scenario: - I want to find all games a specific player (P) has played where player (A) was a part of it. More specifically that two people belonging to the same game

In SQL this could be done with something like (which the query should be):

SELECT DISTINCT p1.* FROM Player as p1 
INNER JOIN Player as p2 ON p1.game_id=p2.game_id 
WHERE p1.account_ref=P AND p2.account_ref=A 

Can this be done neatly with Criteria in Hibernate?

解决方案

Maybe possible with Hibernate's Criteria API, but not straight forward.

A simple case would require the same association path to be join twice (one for A and one for P):

Criteria gameCriteria = ((HibernateEntityManager) em).getSession().createCriteria(Game.class);

Criteria playedGamesOfACriteria = gameCriteria.createCriteria("playedGames", "pga");
Criteria accountOfACriteria = playedGamesOfACriteria.createCriteria("account", "a");
accountOfACriteria.add(Restrictions.idEq(a.id));

Criteria playedGamesOfPCriteria = gameCriteria.createCriteria("playedGames", "pgp");
Criteria accountOfPCriteria = playedGamesOfPCriteria.createCriteria("account", "p");
accountOfPCriteria.add(Restrictions.idEq(p.id));

return gameCriteria.list();

This won't work due to HHH-879.

But you can use a JPA query:

Query q = em.createQuery(
        "select g "
        + "from Game g "
        + "join g.playedGames pga "
        + "join pga.account a "
        + "join g.playedGames pgp "
        + "join pgp.account p "
        + "where a = ?1 and p = ?2"
);
q.setParameter(1, a);
q.setParameter(2, p);
return q.getResultList();

This is even less code.

Also: Some consider the Criteria API to be deprecated.

这篇关于Hibernate标准与自我加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！