Tuesday, 4 September 2012

Dynamic HQL (almost)

Every now and then I come across a query that I can not figure out with hibernate's criteria api. So I have to fall back on trustee HQL. Writing a dynamic query in HQL leads to tons on if statements and string concatenations in the dao. Heres an alternative that I have stumbled accross recently.

Heres a query to illustrate some (psuedo) dynamic HQL
SELECT car FROM Car car
WHERE (:showRedCars    = false OR car.color = 'red')
OR    (:showBlackCars  = false OR car.color = 'black')
OR    (:showOrangeCars = false OR car.color = 'orange')
In the query above :showRedCars, :showBlackCars and :showOrangeCars are standard HQL parameters, these act us gaurds to the actual where statements. If showRedCars is set to true then car.color = 'red' will be applied. If :showRedCars is set to false then the statement is skipped.

You are not limited to jusr true/false values for example

  OR (:regYear <> 2001 OR car.color ='silver')

In the above query if regYear  is set to 2001 then the will return cars with silver color. 
ref: http://stackoverflow.com/questions/10640736/dynamic-hql-query-using-hql-expressions-rather-than-criteria

No comments:

Post a Comment