Forums Register Login

How to translate this JDBC query into JPQL?

+Pie Number of slices to send: Send
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

Any help is appreciated. Thank you.
+Pie Number of slices to send: Send
 

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
+Pie Number of slices to send: Send
 

Roel De Nijs wrote:

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?
+Pie Number of slices to send: Send
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!
+Pie Number of slices to send: Send
 

Tim Holloway wrote:In JPQL you shouldn't be using JOIN at all.


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).
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
 

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.
(1 cow) 2
+Pie Number of slices to send: Send
Ask and receive:


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.
+Pie Number of slices to send: Send
 

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).
+Pie Number of slices to send: Send
Actually, I'm using Hibernate JPA and that particular query was formulated about 3 years ago.

I haven't used TopLink. Originally I was using Apache OpenJPA. I still keep it as an option.
+Pie Number of slices to send: Send
 

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.
ice is for people that are not already cool. Chill with this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 2096 times.
Similar Threads
How to join two tables having each two fileds in common? (seems like @OneToMany with 2 joinColumns)
variable name for where clause in sql
Special Association Mapping in Hibernate
Hibernate: Running a query using an object
Persisting an AtomicLong field
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 08:17:35.