How to translate this JDBC query into JPQL?

This is the query. I hope this would just be simple enough to convert. I'm just new to JPA, specifically JPQL and we wanted to convert this query.

I have started creating the entity objects...

Same for classes TableTwo and TableThree.

I'm confused if you need to use the @OneToMany or @OnetoOne tag to translate that query in JPQL. I guess it won't me that practical to create those tags just for that specific query. This is JPA 2.0 by the way

Andres Delrotti wrote:I hope this would just be simple enough to convert. I'm just new to JPA, specifically JPQL and we wanted to convert this query.

As you are new to JPA, you need a very good resource to refer to if you have doubts, questions and/or looking for some sample code (snippets). The Java Persistence API WikiBook is an excellent resource and when I'm having an issue with JPA, JPQL, or something related it's the first resource I'll check to find a solution. It's really awesome!
I would suggest having a look at several sections of this book: entity mappings, relationships, JPQL query,... and you'll probably discover yourself how this fairly simple query can be converted to JPQL.

Andres Delrotti wrote:I hope this would just be simple enough to convert. I'm just new to JPA, specifically JPQL and we wanted to convert this query.

As you are new to JPA, you need a very good resource to refer to if you have doubts, questions and/or looking for some sample code (snippets). The Java Persistence API WikiBook is an excellent resource and when I'm having an issue with JPA, JPQL, or something related it's the first resource I'll check to find a solution. It's really awesome!
I would suggest having a look at several sections of this book: entity mappings, relationships, JPQL query,... and you'll probably discover yourself how this fairly simple query can be converted to JPQL.

Hope it helps!
Kind regards,
Roel

Thanks for the info. I was able to find the solution because of it.

Follow up question, is that kind of query advisable to use in JPQL? I read that using WHERE instead of JOIN in JPQL makes the statement more "relational database like" rather than object oriented like whats its suppose to be. Is it more recommended to use join in JPQL when selecting from multiple tables?

In JPQL you shouldn't be using JOIN at all. JPQL is an ORM query language and the joining is expected to be implied by inter-object references (OneToMany, ManyToOne, ManyToMany, OneToOne). In such a case, the query doesn't need an explicit join. You simply query to return the head object of the relationship and use WHERE to filter the selection. The JOINED object(s) will be available as single-instance (...ToOne) or collections (...ToMany) members of the head object class. Subject to Lazy Loading constraints, of course!

If you look at the JOIN examples in the Java Persistence WikiBook, using JOIN seems to be not really intuitive. And you can probably write any of those examples without using a JOIN (besides probably the JOIN FETCH example).

Hah! you caught me. I actually do have some explicit JPA joins in one of my apps, but it's a really gnarly sort of query that demanded them. That particular app pretty much exercised every JPA construct you could think of.

Here's what my venerable Pakt Press Pro JPA 2 book says:

The advantage of this (JOIN) operator is that the join can be expressed in terms of the association itself and the query engine will automatically supply the necessary join criteria when it generates the SQL.

If you'll notice the difference between an explicit JPA JOIN and a native SQL JOIN, the native SQL goes like "SELECT x.*, y.* FROM x JOIN y ON x.abc = y.zzz" whereas in JPQL, all you need is "SELECT x FROM X JOIN ON Y", since the ORM already knows what the join columns are.

Tim Holloway wrote:If you'll notice the difference between an explicit JPA JOIN and a native SQL JOIN, the native SQL goes like "SELECT x.*, y.* FROM x JOIN y ON x.abc = y.zzz" whereas in JPQL, all you need is "SELECT x FROM X JOIN ON Y", since the ORM already knows what the join columns are.

I think there might be a functional difference as well. With the native SQL query you have access to the properties of table y. But I doubt if this will be the same with the JPQL query. I think it depends on the fetch strategy of the related object Y and additional queries could be required to fetch the Y objects. You could use JOIN FETCH to fetch the related objects in a single query. But I wonder if a JOIN FETCH can be combined with for example a WHERE clause.

As I said, I've an app that's (ab)used darn near anything JPA can do. Partly because the original data was pretty dirty.

I later replaced that statement with this one:

Which turns the relationship inside out. The schema - if properly designed - would have been a 1-1 relationship between vessel and
"blendgallon", which is how much wine is currently in a vessel. As it is, an empty vessel has no blendgallon mate and there was also no guarantee that
somewhere there weren't multiple blendgallons incorrectly assigned to a vessel. Or that there wouldn't be orphan blendgallons for vessels that had
been removed. Like I said, pretty dirty with no opportunity to clean up and add constraints.

Tim Holloway wrote:As I said, I've an app that's (ab)used darn near anything JPA can do. Partly because the original data was pretty dirty.

That's a very nice example and exactly what I have asked for Have a cow!

Which JPA provider are you using? I assume you are using EclipseLink/TopLink, because according to the Java Persistence WikiBook it's the only JPA provider which supports an alias in JOIN FETCH (or the list might not be up-to-date or the JPA spec might have changed).

Tim Holloway wrote:Actually, I'm using Hibernate JPA and that particular query was formulated about 3 years ago.

According to Hibernate 3.3 Documentation about the Hibernate Query Language it seems to support aliases for JOIN FETCH statements, although it suggests you don't need it in a WHERE (or any other) clause

Hibernate 3.3 Documentation, Associations and joins wrote:A fetch join does not usually need to assign an alias, because the associated objects should not be used in the where clause (or any other clause). The associated objects are also not returned directly in the query results. Instead, they may be accessed via the parent object. The only reason you might need an alias is if you are recursively join fetching a further collection

And it is still mentioned in the Hibernate 5.0 manual as well, so an alias in JOIN FETCH statements is definitely supported in Hibernate.