跳转到内容


JPA的查询语言—JPQL的关联查询

JPA Java JPQL

  • 您无法回复此主题
No replies to this topic

#1 冰力

    Administrator

  • 总版主
  • 1280 帖子数:

发表于 2016/12/22 00:56:20

从一关联到多的查询和从多关联到一的查询来简单说说关联查询。
实体Team:球队。
实体Player:球员。
球队和球员是一对多的关系。

Team.java:
				
package com.cndatacom.jpa.entity;				
				
import java.util.HashSet;				
import java.util.Set;				
				
import javax.persistence.CascadeType;				
import javax.persistence.Column;				
import javax.persistence.Entity;				
import javax.persistence.FetchType;				
import javax.persistence.GeneratedValue;				
import javax.persistence.Id;				
import javax.persistence.OneToMany;				
import javax.persistence.Table;				
				
				
/**				
* 球队				
* @author Luxh				
*/				
@Entity				
@Table(name="team")				
public class Team{				
				
	@Id				
	@GeneratedValue				
	private Long id;				
					
	/**球队名称*/				
	@Column(name="name",length=32)				
	private String name;				
					
	/**拥有的球员*/				
	@OneToMany(mappedBy="team",cascade=CascadeType.ALL,fetch=FetchType.LAZY)				
	private Set<Player> players = new HashSet<Player>();				
				
	//以下省略了getter/setter方法 				
				
	//......				
}				



Player.java:
				
package com.cndatacom.jpa.entity;				

import javax.persistence.CascadeType;				
import javax.persistence.Column;				
import javax.persistence.Entity;				
import javax.persistence.GeneratedValue;				
import javax.persistence.Id;				
import javax.persistence.JoinColumn;				
import javax.persistence.ManyToOne;				
import javax.persistence.Table;				

/**				
* 球员				
* @author Luxh				
*/				
@Entity				
@Table(name="player")				
public class Player{				
					
	@Id				
	@GeneratedValue				
	private Long id;				
					
	/**球员姓名*/				
	@Column(name="name")				
	private String name;				
					
	/**所属球队*/				
	@ManyToOne(cascade={CascadeType.MERGE,CascadeType.REFRESH})				
	@JoinColumn(name="team_id")				
	private Team team;				
					
	//以下省略了getter/setter方法				
				
		//......				
}				


1、从One的一方关联到Many的一方:

查找出球员所属的球队,可以使用以下语句:

SELECT DISTINCT t FROM Team t JOIN t.players p where p.name LIKE :name


或者使用以下语句:

SELECT DISTINCT t FROM Team t,IN(t.players) p WHERE p.name LIKE :name


上面两条语句是等价的,产生的SQL语句如下:
				
select				
	distinct team0_.id as id0_,				
	team0_.name as name0_				
from				
	team team0_				
inner join				
	player players1_				
		on team0_.id=players1_.team_id				
where				
	players1_.name like ?				



从SQL语句中可以看到team inner join 到player。inner join要求右边的表达式必须有返回值。

不能使用以下语句:
SELECT DISTINCT t FROM Team t  WHERE t.players.name LIKE :name

不能使用t.players.name这样的方式从集合中取值,要使用join或者in才行。

2、从Many的一方关联到One的一方:

查找出某个球队下的所有球员,可以使用以下查询语句:

SELECT p FROM Player p JOIN p.team t WHERE t.id = :id


或者使用以下语句:

SELECT p FROM Player p, IN(p.team) t WHERE t.id = :id


这两条查询语句是等价的,产生的SQL语句如下:(产生了两条SQL)
				
	select				
		player0_.id as id1_,				
		player0_.name as name1_,				
		player0_.team_id as team3_1_				
	from				
		player player0_				
	inner join				
		team team1_				
			on player0_.team_id=team1_.id				
	where				
		team1_.id=?				

 		 
	select				
		team0_.id as id2_0_,				
		team0_.name as name2_0_				
	from				
		team team0_				
	where				
		team0_.id=?				


从Many关联到One的查询,还可以使用以下的查询语句:

SELECT p FROM Player p WHERE p.team.id = :id
这条语句产生的SQL如下:(产生了两条SQL)
				
	select				
		player0_.id as id1_,				
		player0_.name as name1_,				
		player0_.team_id as team3_1_				
	from				
		player player0_				
	where				
		player0_.team_id=?				
Hibernate:				
	select				
		team0_.id as id0_0_,				
		team0_.name as name0_0_				
	from				
		team team0				


以上从Many到One的关联查询都产生了两条SQL,还可以使用join fetch只产生一条SQL语句。查询语句如下:

SELECT p FROM Player p JOIN FETCH p.team t WHERE t.id = :id


这条查询语句产生的SQL如下:

	select
		player0_.id as id1_0_,
		team1_.id as id2_1_,
		player0_.name as name1_0_,
		player0_.team_id as team3_1_0_,
		team1_.name as name2_1_
	from
		player player0_
	inner join
		team team1_
			on player0_.team_id=team1_.id
	where
		team1_.id=?