Twitter

vineri, 5 august 2016

JOINS via Hibernate JPA, PostgreSQL and Spring MVC

Let's suppose the following one-to-many relationship:


INNER JOIN
SELECT columns FROM TABLE_A a INNER JOIN TABLE_B b ON a.pk=b.pk

·         All players from all tournaments (players that participate in tournaments)

JPQL
SELECT a.playername, b.tournamentname
FROM Tournament b INNER JOIN b.players a

Hibernate (from JPQL)
Query:["
select     players1_.player_name as col_0_0_,
                tournament0_.tournament_name as col_1_0_
from tournaments tournament0_
inner join players players1_ on tournament0_.id=players1_.tournament_id
"],
Params:[()]

Native
SELECT a.player_name, b.tournament_name
FROM players a INNER JOIN tournaments b ON b.id = a.tournament_id

Output:
Row: [Rafael Nadal, Roland Garros]
Row: [Roger Federer, Roland Garros]
Row: [David Ferer, Roland Garros]
Row: [Kei Nishikori, BCR ]
Row: [Marin Cilic, BCR ]
Row: [Tommy Haas, Wimbledon]
Row: [Andy Murray, Wimbledon]

·         All tournaments for all players (tournaments that have players)

JPQL
SELECT a.playername, b.tournamentname
FROM Player a INNER JOIN a.tournament b

Hibernate (from JPQL)
Query:["
select     player0_.player_name as col_0_0_,
                 tournament1_.tournament_name as col_1_0_
 from players player0_
inner join tournaments tournament1_ on player0_.tournament_id=tournament1_.id
"],
 Params:[()]

Native
SELECT a.player_name, b.tournament_name
FROM tournaments b INNER JOIN players a ON b.id = a.tournament_id

Output:
Row: [Rafael Nadal, Roland Garros]
Row: [Roger Federer, Roland Garros]
Row: [David Ferer, Roland Garros]
Row: [Kei Nishikori, BCR ]
Row: [Marin Cilic, BCR ]
Row: [Tommy Haas, Wimbledon]
Row: [Andy Murray, Wimbledon]

·         All players that participate to Wimbledon tournament and have their rank smaller or equal to 5

JPQL
SELECT a.playername, a.rank
FROM Tournament b INNER JOIN b.players a
WHERE b.tournamentname = :tournament AND a.rank <= :rank

Hibernate (from JPQL)
Query:["
select     players1_.player_name as col_0_0_,
                players1_.player_rank as col_1_0_
from tournaments tournament0_
inner join players players1_ on tournament0_.id=players1_.tournament_id
where tournament0_.tournament_name=? and players1_.player_rank<=?
"],
 Params:[(1=Wimbledon,2=5)]

Native
SELECT a.player_name, a.player_rank
FROM players a INNER JOIN tournaments b ON b.id=a.tournament_id
WHERE b.tournament_name = :tournament AND a.player_rank <= :rank

Output:
Row: [Andy Murray, 3]

·         All tournaments that have players with rank smaller or equal to 5

JPQL
SELECT b.id, b.tournamentname
FROM Player a INNER JOIN a.tournament b
WHERE a.rank <= :rank

Hibernate (from JPQL)
Query:["
select     tournament1_.id as col_0_0_,
                tournament1_.tournament_name as col_1_0_
from players player0_
inner join tournaments tournament1_ on player0_.tournament_id=tournament1_.id
 where player0_.player_rank<=?
"],
 Params:[(1=5)]

Native
SELECT b.id, b.tournament_name
FROM tournaments b INNER JOIN players a ON b.id = a.tournament_id
WHERE a.player_rank <= :rank

Output:
Row: [1, Roland Garros]
Row: [1, Roland Garros]
Row: [2, Wimbledon]

LEFT JOIN
SELECT columns FROM TABLE_A a LEFT JOIN TABLE_B b ON a.pk=b.pk

·         All players even if they are not in tournaments

JPQL
SELECT a.playername, b.tournamentname
FROM Player a LEFT JOIN a.tournament b

Hibernate (from JPQL)
Query:["
select     player0_.player_name as col_0_0_,
                 tournament1_.tournament_name as col_1_0_
from players player0_
left outer join tournaments tournament1_ on player0_.tournament_id=tournament1_.id
"],
 Params:[()]

Native
SELECT a.player_name, b.tournament_name
FROM players a LEFT JOIN tournaments b ON b.id = a.tournament_id

Output:
Row: [Rafael Nadal, Roland Garros]
Row: [Roger Federer, Roland Garros]
Row: [David Ferer, Roland Garros]
Row: [Kei Nishikori, BCR ]
Row: [Marin Cilic, BCR ]
Row: [Tommy Haas, Wimbledon]
Row: [Andy Murray, Wimbledon]
Row: [Richard Gasquet, null]
Row: [Steve Johnson, null]

·         All tournaments even if they don't have players

JPQL
SELECT a.playername, b.tournamentname
FROM Tournament b LEFT JOIN b.players a

Hibernate (from JPQL)
Query:["
select     players1_.player_name as col_0_0_,
                tournament0_.tournament_name as col_1_0_
from tournaments tournament0_
left outer join players players1_ on tournament0_.id=players1_.tournament_id
"],
 Params:[()]

Native
SELECT a.player_name, b.tournament_name
FROM tournaments b LEFT JOIN players a ON b.id = a.tournament_id

Output:
Row: [Rafael Nadal, Roland Garros]
Row: [Roger Federer, Roland Garros]
Row: [David Ferer, Roland Garros]
Row: [Kei Nishikori, BCR ]
Row: [Marin Cilic, BCR ]
Row: [Tommy Haas, Wimbledon]
Row: [Andy Murray, Wimbledon]
Row: [null, Madrid Open]
Row: [null, Australian Open]
Row: [null, US Open]

RIGHT JOIN
SELECT columns FROM TABLE_A a RIGHT JOIN TABLE_B b ON a.pk=b.pk

·         All tournaments even if they don't have players

JPQL
SELECT a.playername, b.tournamentname
FROM Player a RIGHT JOIN a.tournament b

Hibernate (from JPQL)
Query:["
select     player0_.player_name as col_0_0_,
                tournament1_.tournament_name as col_1_0_
from players player0_
right outer join tournaments tournament1_ on player0_.tournament_id=tournament1_.id
"],
 Params:[()]

Native
SELECT a.player_name, b.tournament_name
FROM players a RIGHT JOIN tournaments b ON b.id = a.tournament_id

Output:
Row: [Rafael Nadal, Roland Garros]
Row: [Roger Federer, Roland Garros]
Row: [David Ferer, Roland Garros]
Row: [Kei Nishikori, BCR ]
Row: [Marin Cilic, BCR ]
Row: [Tommy Haas, Wimbledon]
Row: [Andy Murray, Wimbledon]
Row: [null, Madrid Open]
Row: [null, Australian Open]
Row: [null, US Open]

·         All players even if they are not in tournaments

JPQL
SELECT a.playername, b.tournamentname
FROM Tournament b RIGHT JOIN b.players a

Hibernate (from JPQL)
Query:["
select     players1_.player_name as col_0_0_,
                 tournament0_.tournament_name as col_1_0_
from tournaments tournament0_
right outer join players players1_ on tournament0_.id=players1_.tournament_id
"],
 Params:[()]

Native
SELECT a.player_name, b.tournament_name
FROM tournaments b RIGHT JOIN players a ON b.id = a.tournament_id

Output:
Row: [Rafael Nadal, Roland Garros]
Row: [Roger Federer, Roland Garros]
Row: [David Ferer, Roland Garros]
Row: [Kei Nishikori, BCR ]
Row: [Marin Cilic, BCR ]
Row: [Tommy Haas, Wimbledon]
Row: [Andy Murray, Wimbledon]
Row: [Richard Gasquet, null]
Row: [Steve Johnson, null]

FULL JOIN
SELECT columns FROM TABLE_A a FULL OUTER JOIN TABLE_B b ON a.pk=b.pk

·         All players and tournaments

JPQL
SELECT a.playername, b.tournamentname
FROM Player a FULL JOIN a.tournament b

Hibernate (from JPQL)
Query:["
select     player0_.player_name as col_0_0_,
                tournament1_.tournament_name as col_1_0_
from players player0_
full outer join tournaments tournament1_ on player0_.tournament_id=tournament1_.id
"],
 Params:[()]

Native
SELECT a.player_name, b.tournament_name
FROM players a FULL JOIN tournaments b ON b.id = a.tournament_id

Output:
Row: [Rafael Nadal, Roland Garros]
Row: [Roger Federer, Roland Garros]
Row: [David Ferer, Roland Garros]
Row: [Kei Nishikori, BCR ]
Row: [Marin Cilic, BCR ]
Row: [Tommy Haas, Wimbledon]
Row: [Andy Murray, Wimbledon]
Row: [Richard Gasquet, null]
Row: [Steve Johnson, null]
Row: [null, Madrid Open]
Row: [null, Australian Open]
Row: [null, US Open]

·         All tournaments and players

JPQL
SELECT a.playername, b.tournamentname
FROM Tournament b FULL JOIN b.players a

Hibernate (from JPQL)
Query:["
select     players1_.player_name as col_0_0_,
                tournament0_.tournament_name as col_1_0_
from tournaments tournament0_
full outer join players players1_ on tournament0_.id=players1_.tournament_id
"],
 Params:[()]

Native
SELECT a.player_name, b.tournament_name
FROM tournaments b FULL JOIN players a ON b.id = a.tournament_id

Output:
Row: [Rafael Nadal, Roland Garros]
Row: [Roger Federer, Roland Garros]
Row: [David Ferer, Roland Garros]
Row: [Kei Nishikori, BCR ]
Row: [Marin Cilic, BCR ]
Row: [Tommy Haas, Wimbledon]
Row: [Andy Murray, Wimbledon]
Row: [Richard Gasquet, null]
Row: [Steve Johnson, null]
Row: [null, Madrid Open]
Row: [null, Australian Open]
Row: [null, US Open]

LEFT EXCLUDING JOIN
SELECT columns FROM TABLE_A a LEFT JOIN TABLE_B b ON a.pk=b.pk WHERE b.pk IS NULL

·         All players that are not in tournaments

JPQL
SELECT a.playername, b.tournamentname
FROM Player a LEFT JOIN a.tournament b WHERE b.id IS NULL

Hibernate (from JPQL)
Query:["
select     player0_.player_name as col_0_0_,
                tournament1_.tournament_name as col_1_0_
from players player0_
left outer join tournaments tournament1_ on player0_.tournament_id=tournament1_.id
where tournament1_.id is null
"],
 Params:[()]

Native
SELECT a.player_name, b.tournament_name
FROM players a LEFT JOIN tournaments b ON b.id = a.tournament_id WHERE b.id IS NULL

Output:
Row: [Richard Gasquet, null]
Row: [Steve Johnson, null]

·         All tournaments that don't have players

JPQL
SELECT a.playername, b.tournamentname
FROM Tournament b LEFT JOIN b.players a WHERE a.id IS NULL

Hibernate (from JPQL)
Query:["
select     players1_.player_name as col_0_0_,
                tournament0_.tournament_name as col_1_0_
from tournaments tournament0_
left outer join players players1_ on tournament0_.id=players1_.tournament_id
where players1_.id is null
"],
 Params:[()]

Native
SELECT a.player_name, b.tournament_name
FROM tournaments b LEFT JOIN players a ON b.id = a.tournament_id WHERE a.id IS NULL

Output:
Row: [null, Madrid Open]
Row: [null, Australian Open]
Row: [null, US Open]

RIGHT EXCLUDING JOIN
SELECT columns FROM TABLE_A a RIGHT JOIN TABLE_B b ON a.pk=b.pk WHERE a.pk IS NULL

·         All tournaments that don't have players

JPQL
SELECT a.playername, b.tournamentname
FROM Player a RIGHT JOIN a.tournament b WHERE a.id IS NULL

Hibernate (from JPQL)
Query:["
select     player0_.player_name as col_0_0_,
                tournament1_.tournament_name as col_1_0_
from players player0_
right outer join tournaments tournament1_ on player0_.tournament_id=tournament1_.id
where player0_.id is null
"],
 Params:[()]

Native
SELECT a.player_name, b.tournament_name
FROM players a RIGHT JOIN tournaments b ON b.id = a.tournament_id WHERE a.id IS NULL

Output:
Row: [null, Madrid Open]
Row: [null, Australian Open]
Row: [null, US Open]

·         All players that are not in tournaments

JPQL
SELECT a.playername, b.tournamentname
FROM Tournament b RIGHT JOIN b.players a WHERE b.id IS NULL

Hibernate (from JPQL)
Query:["
select     players1_.player_name as col_0_0_,
                 tournament0_.tournament_name as col_1_0_
from tournaments tournament0_
right outer join players players1_ on tournament0_.id=players1_.tournament_id
where tournament0_.id is null
"],
 Params:[()]

Native
SELECT a.player_name, b.tournament_name
FROM tournaments b RIGHT JOIN players a ON b.id = a.tournament_id WHERE b.id IS NULL

Output:
Row: [Richard Gasquet, null]
Row: [Steve Johnson, null]

FULL OUTER EXCLUDING JOIN
SELECT columns FROM TABLE_A a FULL OUTER JOIN TABLE_B b ON a.pk=b.pk WHERE a.pk IS NULL OR b.pk IS NULL

·         All tournaments that don't have players and all players that don't participate in tournaments

JPQL
SELECT a.playername, b.tournamentname
FROM Player a FULL JOIN a.tournament b WHERE a.id IS NULL OR b.id IS NULL

Hibernate (from JPQL)
Query:["
select     player0_.player_name as col_0_0_,
                tournament1_.tournament_name as col_1_0_
from players player0_
full outer join tournaments tournament1_ on player0_.tournament_id=tournament1_.id
where player0_.id is null or tournament1_.id is null
"],
 Params:[()]

Native
SELECT a.player_name, b.tournament_name
FROM players a FULL JOIN tournaments b ON b.id = a.tournament_id WHERE a.id IS NULL OR b.id IS NULL

Output:
Row: [Richard Gasquet, null]
Row: [Steve Johnson, null]
Row: [null, Madrid Open]
Row: [null, Australian Open]
Row: [null, US Open]

·         Or, reverse fetch

JPQL
SELECT a.playername, b.tournamentname
FROM Tournament b FULL JOIN b.players a WHERE a.id IS NULL OR b.id IS NULL

Hibernate (from JPQL)
Query:["
select     players1_.player_name as col_0_0_,
                tournament0_.tournament_name as col_1_0_
from tournaments tournament0_
full outer join players players1_ on tournament0_.id=players1_.tournament_id
where players1_.id is null or tournament0_.id is null
"],
 Params:[()]

Native
SELECT a.player_name, b.tournament_name
FROM tournaments b FULL JOIN players a ON b.id = a.tournament_id WHERE a.id IS NULL OR b.id IS NULL

Output:
Row: [Richard Gasquet, null]
Row: [Steve Johnson, null]
Row: [null, Madrid Open]
Row: [null, Australian Open]
Row: [null, US Open]

The complete application is available here.

Niciun comentariu:

Trimiteți un comentariu