Shulou(Shulou.com)06/02 Report--

Editor to share with you how to achieve JPQL related query, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to understand it!

Talk about related queries from one-to-many queries and from many-to-one queries.

Physical Team: the team.

Physical Player: players.

There is an one-to-many relationship between teams and players.

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; / * * team name * / @ Column (name= "name", length=32) private String name / * * owned player * / @ OneToMany (mappedBy= "team", cascade=CascadeType.ALL,fetch=FetchType.LAZY) private Set players = new HashSet (); / / the getter/setter method / / is omitted below. }

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 / * player * @ author Luxh * / @ Entity @ Table (name= "player") public class Player {@ Id @ GeneratedValue private Long id; / * * player name * / @ Column (name= "name") private String name / * * team * / @ ManyToOne (cascade= {CascadeType.MERGE,CascadeType.REFRESH}) @ JoinColumn (name= "team_id") private Team team; / / omits the getter/setter method / /. }

1. From the party of One to the party of Many:

To find out which team the player belongs to, you can use the following statement:

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

Or use the following statement:

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

The above two statements are equivalent, and the resulting SQL statement is as follows:

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?

You can see team inner join to player in the SQL statement. Inner join requires that the expression on the right must have a return value.

You cannot use the following statement:

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

You can't use t.players.name to get values from a collection, you need to use join or in.

2. From the party of Many to the party of One:

To find out all the players under a team, you can use the following query:

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

Or use the following statement:

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

The two query statements are equivalent, and the resulting SQL statement is as follows: (two SQL are generated)

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

For queries associated from Many to One, you can also use the following query statement:

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

The SQL generated by this statement is as follows: (two SQL were generated)

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

The above associative queries from Many to One all produce two SQL, and you can use join fetch to generate only one SQL statement. The query statement is as follows:

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

The SQL generated by this query is as follows:

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=? The above is all the contents of the article "how to implement the related query of JPQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.