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.
Publicat de la
Etichete: , ,

7 comentarii:

  1. sai12 octombrie 2018 la 00:31

    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
  2. Unknown17 octombrie 2018 la 00:00

    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
  3. LENIN17 octombrie 2018 la 00:23

    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
  4. sai13 martie 2019 la 04:49

    You got an extremely helpful website I actually have been here reading for regarding an hour. I’m an initiate and your success is incredibly a lot of a concept on behalf of me.
    Microsoft Azure online training
    Selenium online training
    Java online training
    Python online training
    uipath online training

    RăspundețiȘtergere
  5. nisha31 mai 2020 la 06:19

    Thanks for posting this blog. the blog is very interesting while studying this concept.

    Data Science Training Course In Chennai | Data Science Training Course In Anna Nagar | Data Science Training Course In OMR | Data Science Training Course In Porur | Data Science Training Course In Tambaram | Data Science Training Course In Velachery

    RăspundețiȘtergere
  6. rocky26 iulie 2020 la 09:01

    Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot.
    python training in chennai

    python course in chennai

    python online training in chennai

    python training in bangalore

    python training in hyderabad

    python online training

    python training

    python flask training

    python flask online training

    python training in coimbatore


    RăspundețiȘtergere
  7. Kanika13 octombrie 2020 la 00:31

    Good Post! , it was so good to read and useful to improve my knowledge as an updated one, keep blogging. After seeing your article I want to say that also a well-written article with some very good information which is very useful for the readers....thanks for sharing it and do share more posts like this.
    Salesforce Online Training

    RăspundețiȘtergere

Abonați-vă la: Postare comentarii (Atom)