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

Posted on 2012-06-02 14:54 CN.programmer.Luxh 阅读(17459) 评论(6) 编辑收藏举报

　　从一关联到多的查询和从多关联到一的查询来简单说说关联查询。

　　实体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的一方：

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

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

　　或者使用以下语句：

1	`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要求右边的表达式必须有返回值。

　　不能使用以下语句：

1	`SELECT` `DISTINCT` `t` `FROM` `Team t` `WHERE` `t.players.name` `LIKE` `:name`

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

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

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

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

　　或者使用以下语句：

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

　　这两条查询语句是等价的，产生的SQL语句如下：(产生了两条SQL)

Hibernate: 
    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=?
Hibernate: 
    select
        team0_.id as id2_0_,
        team0_.name as name2_0_ 
    from
        team team0_ 
    where
        team0_.id=?

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

1	`SELECT` `p` `FROM` `Player p` `WHERE` `p.team.id = :id`

　　这条语句产生的SQL如下：(产生了两条SQL)

Hibernate: 
    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语句。查询语句如下：

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

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

Hibernate: 
    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=?