JPA how to join these tables

Question

I use JPA(JQL) on Glassfish, given these tables as an example below how can I query to get

"All customer rows who has an OrderId of which its shipperID is bigger than 2"

It will be something with a join but couldn't figure out which one and how

This image is taken from w3schools, but represents my situation well.

enter image description here

If you are using JPA and want java entities back, you might want to show the entities and their mappings. The jPQL could look like "select c from Customer c join c.orders order where order.shipperid > 2" — Chris, May 29 '13 at 02:48
Why the downvote and the close votes, what is wrong with this question, just comment how it can be improved instead of downvoting like a monkey cause u have no better things to do in life — Spring, May 29 '13 at 07:23
@Spring, perhaps the downvoters had their reasons (thats for them to answer). But I don't really think insulting them is achieving anything. The more constructive way would be to follow the advice of such as the first comment above, show your Entity classes, and then people can suggest a JPQL query for those classes ... because after all JPA/JPQL is based around CLASSES not TABLES. — DataNucleus, May 29 '13 at 07:36
@DataNucleus you are right and this is not a valid reason to close and downvote a question, The more constructive way is to point what is missing, What do you achive by closing it? if I knew JPA I wouldn't ask it — Spring, May 29 '13 at 07:42
@DataNucleus are you agree with answer below? I dont see anything special in my entities, they just represent my tables as they are. — Spring, May 29 '13 at 07:45
If you don't know JPA then read one of the many many tutorials, and you'd get the idea that it is class-oriented not just some way of executing some SQL; Stackoverflow has a FAQ in the header that explains what this site is for (and that defines why I voted to close, not that I need to explain it). The "answer" is based around executing SQL, and that is not what a class-based persistence API is designed for ... so no, *if you are using JPA* it is not the "solution", just a cause of (future) problems. — DataNucleus, May 29 '13 at 07:51
@DataNucleus kudos nucleus, so you caught me on crime and close the question :D thanks for the tips btw — Spring, May 29 '13 at 08:22

score 2 · Accepted Answer · edited May 23 '17 at 12:07

2

Something like this for the SQL?

SELECT  *
FROM    customertable AS c
        INNER JOIN ordertable AS o 
            ON c.customerid = o.customerid
WHERE   o.shipperid > 2

Depending on your SQL provider, you may not need the AS for aliasing tables. You can go further by selecting the customer rows only:

SELECT c.*

And further still by selecting unique rows:

SELECT DISTINCT c.*

Here is a link to another post about executing the SQL in JPA:

Execute some arbitrary sql in the current transaction in JPA 2.0

edited May 23 '17 at 12:07

Community

1
1

answered May 28 '13 at 15:30

twoleggedhorse

4,938
4
23
38

While conceptually DISTINCT is easier if I needed `SELECT DISTINCT c.*`I would use `IN` or `EXISTS`. – Conrad Frix May 28 '13 at 16:39
@twoleggedhorse tnx, should i use join or inner join there? – Spring May 29 '13 at 07:11
@Spring The rdbms will automatically read 'join' as 'inner join' but I prefer to write it out fully, plus it makes it easier to read in my opinion. – twoleggedhorse May 29 '13 at 08:33
@twoleggedhorse thansk I asked the question more clear way here, could you check http://stackoverflow.com/questions/16808695/jpa-how-to-join-these-entities – Spring May 29 '13 at 08:34

JPA how to join these tables

1 Answers1