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.

7 comentarii:

  1. Hello! This is my first visit to your blog! We are a team of volunteers and starting a new initiative in a community in the same niche. Your blog provided us useful information to work on. You have done an outstanding job.
    Best AWS Training in Chennai | Amazon Web Services Training in Chennai

    AWS Training in Bangalore | Amazon Web Services Training in Bangalore

    Amazon Web Services Training in Pune | Best AWS Training in Pune

    RăspundețiȘtergere
  2. Good Post, I am a big believer in posting comments on sites to let the blog writers know that they ve added something advantageous to the world wide web.
    Python training in marathahalli | Python training institute in pune

    RăspundețiȘtergere
  3. Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging.
    advanced excel training in bangalore

    RăspundețiȘtergere
  4. Fantastic work! This is the type of information that should follow collective approximately the web. Embarrassment captivating position Google for not positioning this transmit higher! Enlarge taking place greater than and visit my web situate

    Java training in Chennai | Java training in USA |

    Java training in Indira nagar | Java training in Rajaji nagar

    RăspundețiȘtergere
  5. This is a terrific article, and that I would really like additional info if you have got any. I’m fascinated with this subject and your post has been one among the simplest I actually have read.
    Data Science course in Indira nagar
    Data Science course in marathahalli
    Data Science Interview questions and answers


    RăspundețiȘtergere
  6. This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me.. 

    best rpa training in chennai | rpa online training |
    rpa training in chennai |
    rpa training in bangalore
    rpa training in pune
    rpa training in marathahalli
    rpa training in btm

    RăspundețiȘtergere