CODEWITHSUNDEEP
javahibernatehqlorm
WSK
WSK

Reputation: 6198

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

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"

Upvotes: 18

Views: 19668

Answers (5)

Hick
Hick

Reputation: 459

select id, null as column_name, name 
from MyClass

Upvotes: 0

sohrab yousefi
sohrab yousefi

Reputation: 81

You can use nullif() function to generate null.

It accepts two arguments. If arguments are equal, it'll return null value.

Upvotes: 8

Viktoriia Lymareva
Viktoriia Lymareva

Reputation: 11

You can also try TO_CHAR(null). It works fine for HQL- Oracle in constructor new Foo(id, name, attr3) where attr3 - String value

Upvotes: 1

Brian Deterling
Brian Deterling

Reputation: 13734

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 ...

Upvotes: 16

Pascal Thivent
Pascal Thivent

Reputation: 570615

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.

Upvotes: 1

Related Questions