Can I select null as a column value in HQL query?

Question

I need a list with three columns. column 1st and 3rd having values while 2nd as null. Can I do it through HQL query?

I need something like this:

select id, null, name from MyClass

Where MyClass as well as underlying table has only two properties/columns ie, "id" and "name"

Answer 1

AFAIK, your query won't return the expected result, it will only return the columns before the null (id in your case). However, the following would work

select id as first, '', name from MyClass

I don't know if this is a bug or just a non supported feature (the HQL help: possible to "select NULL as ..."? thread on the Hibernate forums seems to suggest it's a bug).

I'm not sure I understood why you need this (could a select new expression be an alternative?) but I'm afraid you'll have to use native SQL here.

Answer 2

Another option that seems to work (tested on DB2, MySQL, Oracle, and SQL Server):

select id, cast(null as char), name from ...

You could subclass the Hibernate dialect and abstract it with a custom function:

registerFunction("always_null", 
  new SQLFunctionTemplate(Hibernate.STRING, "cast(null as char)"));

and then use this in your HQL:

select id, always_null(), name from ...