Performing a LEFT OUTER JOIN using JPQL and JPA

Posted by Arsenalist on November 20, 2007

This entry was posted on November 20, 2007 at 2:05 pm and is filed under java, tech. Tagged: @onetoone, database, ejb, hibernate, java persistence api, jpa, jpql, orm, sql, toplink, toplink essentials. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

9 Responses to “Performing a LEFT OUTER JOIN using JPQL and JPA”

Tech Per said

November 21, 2007 at 2:34 pm
Hi, nice example of left outer join. You write:

…an installment of documenting something that should be already documented.

I don’t know if you mean that it isn’t documented, because it is. Quite well, actually. In part 4.4.5.2 in the persistence part of the specifaction of EJB3.0. This is JSR-220.
Web 2.0 Announcer said

November 21, 2007 at 3:13 pm
Performing a LEFT OUTER JOIN using JPQL and JPA

[...]Haven’t made a tutorial-type post in a while so here’s an installment of documenting something that should be already documented. The problem at hand is s very common one: how do you do a LEFT OUTER JOIN on two tables (entities) when you’re …
Verdeen said

November 21, 2007 at 4:47 pm
Good example, the bridge from SQL to ORM query languages such as HQL and JPQL is often too confusing and the underlying SQL that is generated is vastly different from the actual query. There needs to be a reverse engineering tool from SQL to JPQL.
666snippets said

November 21, 2007 at 9:18 pm
*searches JPQL tutorials*
666snippets said

November 21, 2007 at 9:20 pm
looking at this increments my love for iBatis
vardomn said

November 21, 2007 at 10:55 pm
c4torcadomtr
Jhoss said

December 4, 2007 at 10:36 pm
Hi, I’m from Bolivia, and I want to know JPQL, a basic example… please…
Mike said

January 20, 2008 at 9:34 pm
Great Left Outer Join Example, Thank you!
Nate said

April 18, 2008 at 12:58 pm
Thank you for the easy to follow example. My situation, however, is a bit more complex. I’m joining on dates and in the first table, the date is represented as an integer (yyyyMMdd) and in the second, as a date field.

I’ve tried everything including using the getter/setter methods to convert the Date to a Long and back, but I always get a “java.sql.SQLException: [SQL0401] Comparison operator = operands not compatible.” as the SQL it generates seems to be be comparing the data types as they’re stored in the database and I’m not sure how to get JPA/Toplink to do otherwise.