hibernate

Hibernate – CASE WHEN in HQL – ParameterNode cannot be cast to SelectExpression

If you want to use SQL USE … WHEN … ELSE expression in HQL (Hibernate Query Language), you may faced to this:

java.lang.ClassCastException: org.hibernate.hql.internal.ast.tree.ParameterNode cannot be cast to org.hibernate.hql.internal.ast.tree.SelectExpression

This exception is raised during parsing SQL to HQL. If you will check method getFirstThenNode() from class CaseNode.java in package org.hibernate.hql.internal.ast.tree, you are able to see casting to SelectExpression. And there is our problem. If you want to use parameter in query, you have to define a data type for this parameter. If your query contain more WHEN statements with parameters, you just define this casting of parameter for first one.

Example

SELECT DISTINCT tableA.Id, tableB.Code
FROM TableFirst tableA
LEFT JOIN TableSecond tableB ON
tableB.CODE =
CASE
  WHEN tableA.Type = 0 THEN ?
  WHEN tableA.Type = 1 THEN ?
  WHEN tableA.Type = 2 THEN ?
  WHEN tableA.Type = 3 THEN ?
END

Now we have to know,  which parameters will be taken to query and cast first binding parameter to this data type.

Let’s say that we want to compare Code from TableSecond with string codes, so our parameters will be strings.

Fixed version

SELECT DISTINCT tableA.Id, tableB.Code
FROM TableFirst tableA
LEFT JOIN TableSecond tableB ON
tableB.CODE =
CASE
  WHEN tableA.Type = 0 THEN CAST (? AS STRING)
  WHEN tableA.Type = 1 THEN ?
  WHEN tableA.Type = 2 THEN ?
  WHEN tableA.Type = 3 THEN ?
END

I hope it will help you and save your time.

 

Aleš Laňar
Senior Engineer Software ve společnosti CA Technologies

Leave a Reply

Your email address will not be published. Required fields are marked *