Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

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

 
Avor Nadal
Ranch Hand
Posts: 151
Java Netbeans IDE Postgres Database
  • Mark post as helpful
  • send pies
  • 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.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic