• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JPQL: multiple value comparison (or row/tuple comparison).

 
Ranch Hand
Posts: 157
Netbeans IDE Postgres Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello:

In PostgreSQL I'm used to compare multiple values applying the so called "row value constructor syntax" in ANSI or "row/tuple comparison". For example:


If I didn't this, I would have to write a long query like this one:


In addition to being less verbose, the first form allows me to use static queries and pass the rows/tuples as parameters. This is really useful.

In JPQL, however, I'm facing serious problems to make it work. Using Hibernate as the JPA provider I've been able to compare one tuple:


But as soon as I use the operator IN, the query fails. Is there any way to achieve this in JPQL? If there isn't, What about Hibernate?

I believe that I tested all this time ago. But if my memory serves me well, the only way to make it work was to group all these fields in an @Embeddable class during the entity design, and use instances of such class as parameters of the query. However this isn't of any help if the fields that are part of the row/tuple to compare come from different entities.

Thank you!

PS: I forgot to mention that the Hibernate documentation explains how to use this comparison when the rows/tuples come from a sub-select: http://docs.jboss.org/hibernate/orm/4.2/manual/en-US/html/ch16.html#queryhql-tuple . However it doesn't comment anything about those cases which you write the rows/tuples by hand in.
 
Time is mother nature's way of keeping everything from happening at once. And this is a tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic