Hibernate building HQL queries

Question

I just read hibernate reference and they say that you should use constans for HQL queries. However that is not always possible, for example if you do search function and have 10 criterias (not jpa criterias, just columns you are searching by). I bet you can do some hacky HQL that is transformed into badly performing SQL, but I feel that is not the best choice.

I do know you can use The Criteria API, but its not as powerfull as HQL and some people just don't like it (I'm one of them).

How you do it in Yours applications?

I mean code like (its just an example!)

String hql = "from Biuro where ";
List parameters = ...
if (dateFrom!=null){
   hql += " dateFrom>=? ";
   parameters.add(dateFrom);
}

Answer 1

You can use parameters (eg ":orderNumber"), which will be compiled into a PreparedQuery. The drawback is that in some databases (eg PostgreSQL), a query execution plan is constructed during the query preparation. That means that it cannot use value distribution statistics (as parameter values are not known during query preparation), and in some cases may produce underoptimized execution plan.

I don't know why hibernate reference suggests that thing, we are widely using parameters in HQL in our project, amd we are widely using Criteria API. The motto is don't optimize until you have something to optimize. First create the application, and then see if there are some performance problems which you can eliminate by using constants.