Hibernate sql query not displaying boolean values ​​correctly?

Question

Doing the following through any SQL client

SELECT FALSE, TRUE; 

returns

 f, t 

However, this SQL query:

 SQLQuery qry = session.createSQLQuery("SELECT FALSE, TRUE;"); Object[] result = (Object[]) qry.uniqueResult(); 

returns an Object array containing two Boolean objects:

 [false, false] 

I am using postgres 9.0-801.jdbc4 with hibernation.

How to return corresponding boolean using hibernate sql query?

Answer 1

Try changing the definition for your boolean property in the mapping file:

 <property name="someBoolean" column="SOME_COLUMN" type="true_false"></property> 

or if you use annotations, add this:

 @Type(type="true_false") 

This should help, because by default hibernate displays the boolean property as BIT, but not as CHAR (1), and I think that is why you have such a problem. (see hibernate display types ).

EDIT:

If you need to select boolean using SQLQuery (as a split value not as an entity field), you can use this approach:

 SQLQuery q = session.createSQLQuery("SELECT FALSE a, TRUE b;"); q.addScalar("a", new TrueFalseType()); q.addScalar("b", new TrueFalseType()); Object[] result = (Object[]) q.uniqueResult(); 

As you can see, in this case you need to explicitly indicate the type of the received value so that sleep mode can correctly convert it. I tested this approach in MySQL and everything works (except that in my case I set the type to BooleanType (), because MySQL works with booleans as 1/0).

Hibernate uses three boolean formats:

1) "Y / N" → CHAR (1) type ansi sql → "yes_no" type of sleep mode → class YesNoType;

2) "T / F" → CHAR (1) → "true_false" hibernation type → class TrueFalseType;

3) "1/0" → BIT ansi sql type → "boolean" type of sleep mode → class BooleanType;

So, you need to choose the right type for your database (as I understand it, and, as I wrote in my example, you should use TrueFalseType).