Menu
I'm trying to write an HQL query to select objects which contain an object in a child collection.
Example:
Contest Object
ContestID
ContestName
RequiredCountries -> one to many collection of Country objects
Country Object
CountryCode
CountryName
The sql equivalent of what i want:
SELECT * FROM CONTEST C
WHERE C.CONTESTID IN(SELECT CONTESTID FROM CONTEST_COUNTRY CC INNER JOIN COUNTRY CTRY ON
CC.COUNTRYCODE = CTRY.COUNTRYCODE WHERE COUNTRYCODE='USA')
OR
SELECT * FROM CONTEST C
WHERE EXISTS(SELECT CONTESTID FROM CONTEST_COUNTRY CC INNER JOIN COUNTRY CTRY ON
CC.COUNTRYCODE = CTRY.COUNTRYCODE WHERE COUNTRYCODE='USA' AND CC.CONTESTID=C.CONTESTID)
I have this hql, which works, but seems like not a good solution-
from Contest C
where (from Country where CountryCode = :CountryCode) = some elements(C.RequiredCountries)
I also consider joining with Country, but since I don't have an object class to represent the relationship, I wasn't sure how to join in HQL.
Anyone have any ideas or suggestions? This should be easy.
856
Hibernate Query Language (HQL) is an object-oriented query language, similar to SQL, but instead of operating on tables and columns, HQL works with persistent objects and their properties. HQL queries are translated by Hibernate into conventional SQL queries, which in turns perform action on database.
Unlike SQL, HQL uses classes and properties in lieu of tables and columns. HQL supports polymorphism as well as associations, which in turn allows developers to write queries using less code as compared to SQL.
The easiest way is to either create the hql string based on the value (if subServiceId is existent or not) or use the criteria api, thus you will just have to add one extra equals filter in case of subServiceId presence. The question should be changed on how to add dynamic conditions to hql based on variable presence.
The Hibernate Query Language (HQL) and Java Persistence Query Language (JPQL) are both object model focused query languages similar in nature to SQL. JPQL is a heavily-inspired-by subset of HQL. A JPQL query is always a valid HQL query, the reverse is not true however.
try this:
from Contest Ct, Country Cr
where Cr.CountryCode = :CountryCode
and Cr.Country in elements(Ct.RequiredCountries)
Related article
51
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
