HQL dynamic query using hql expressions rather than criteria?

Question

I am trying to write a partially dynamic HQL query without resorting to the Critics API for various reasons. I would like to know if there is an easy way to shorten a constraint using HQL expressions. For example, here is the original query that works fine:

SELECT customer FROM Customer as customer INNER JOIN customer.profile as profile WHERE profile.status IN :statusCodes AND profile.orgId IN :orgIds 

StatusCodes is a list of strings, and orgIds is a list of integers. However, one of them is optional and should not limit the transfer of null instead of the collection. I tried to do it like this:

 SELECT customer FROM Customer as customer INNER JOIN customer.profile as profile WHERE (:statusCodes IS NULL OR profile.status IN :statusCodes) AND (:orgIds IS NULL OR profile.orgId IN :orgIds) 

Unfortunately, this did not work, but is there any other approach that can work, either using different expressions or passing default values?

EDIT: just to be clear, I'm looking for a way to use NamedQuery, rather than dynamically building the query in any way.

SOLUTION: I used additional query parameters to execute it. I created two helper methods:

 private void setRequiredParameter(TypedQuery<?> query, String name, Object value) { query.setParameter(name, value); } private void setOptionalParameter(TypedQuery<?> query, String name, Object value) { query.setParameter(name, value); query.setParameter(name + "Optional", value == null ? 1 : 0); } 

And the request looks like this:

 SELECT customer FROM Customer as customer INNER JOIN customer.profile as profile WHERE (:statusCodesOptional = 1 OR profile.status IN :statusCodes) AND (:orgIdsOptional = 1 OR profile.orgId IN :orgIds) 

Answer 1

If you absolutely must avoid dynamic queries, you can do this with two additional parameters:

 SELECT customer FROM Customer AS customer JOIN customer.profile AS profile WHERE (profile.status IN :statusCodes OR :statusCodeCount = 0) AND (profile.orgId IN :orgIds OR :orgIdCount = 0) 

In your Java code, you should do something like:

 session.getNamedQuery("your.query.name") .setParameterList("statusCodes", statusCodes) .setParameter("statusCodeCount", statusCodes.length) .setParameterList("orgIds", orgIds) .setParameter("orgIdCount", orgIds.length); 

You will need to ensure that arrays are of zero length and not null , or supply additional data if the checks are handled by a null script.

All that said, HQL is really better suited for well-defined (e.g. static) queries. You can work with dynamic parameters; you cannot work with dynamic sorting.

Answer 2

My suggestion is to put all the parameters in the map and build a dynamic query, after the building has set all the parameters necessary for the query before executing, taking the values ​​from the map:

 Map<String, Object> pars = new HashMap<String,Object>(); pars.put("statusCodes", statusCodes); pars.put("orgIds", orgIds); StringBuilder b = "SELECT customer FROM Customer as customer INNER JOIN customer.profile as profile where 1 = 1"; if (statusCodes != null) { b.append(" and profile.status in :statusCodes"); } if (orgIds != null) { b.append(" and profile.orgId in :statusCodes"); } ... Query q = session.createQuery(b.toString()); ... for (String p : q.getNamedParameters()) { q.setParameter(p, pars.get(p)); } 

Of course, some improvements are necessary, for example, to eliminate an exception when parameters are not specified, use a typed parameter if the complexity is greater than a few simple parameters, etc.

Answer 3

You will need to generate the request dynamically:

 StringBuilder hql = new StringBuilder("SELECT customer FROM Customer as customer INNER JOIN customer.profile as profile where 1 = 1") if (statusCodes != null) { hql.append(" and profile.status IN :statusCodes"); } if (orgIds != null) { hql.append(" and profile.orgId IN :orgIds"); } 

Of course, you will also have to set parameters for the query only if they are not null.