HQL: variable column

hibernate, hql, java

Solution

Does this make sense in your application:

String query =  "select test.col1, test.col2, test.col3" + 
  "from Test test " +
  "where {columnName} = :variableValue ";
Object variableValue = // retrieve from somewhere
String columnName = // another retrieve from somewhere
query = query.replace("{columnName}", columName);
// Now continue as always

This is generally a naive query constructor. You may need to refactor this idea to a separate utility/entity-based class to refine (e.g. SQL injection) the queries before execution.

Problem

I'm able to set variable values for "where" restrictives: ``` Query criteria = session.createQuery( "select test.col1, test.col2, test.col3 "from Test test " + "where test.col = :variableValue "); criteria.setInteger("variableValue", 10); ``` But is it possible to set variable column like this? ``` String variableColumn = "test.col1"; Query criteria = session.createQuery( "select test.col1, test.col2, test.col3 "from Test test " + "where :variableColumn = :variableValue "); criteria.setInteger("variableValue", 10); criteria.setString("variableColumn", variableColumn); ``` This is the result: ``` Exception in thread "main" Hibernate: select .... where ?=? ... org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92) ... at _test.TestCriteria.main(TestCriteria.java:44) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the nvarchar value 'test.col1' to data type int. ... ``` UPDATE (working solution): ``` Query criteria = session.createQuery( "select test.col1, test.col2, test.col3 "from Test test " + "where (:value1 is null OR test.col1 = :value1) AND (:value2 is null OR test.col2 = :value2) " ```

Original source