Hibernate DB2 Returns First Charector while using NativeQuery

Question

I am facing the following problem while using Hibernate Native Query to fetch data from DB2 CHAR field.

I have a table structure and data as follows.

 CNTRL_4 COLUMN_ID PARAM
 ------- --------- --------------------
       1         1 10                  
       1         2 12                  
       1         3 true                
       2         1 10                  
       2         2 13                  
       2         3 false               
       3         1 10                  
       3         2 16                  
       3         3 true

CNTRL_4 and COLUMN_ID is number, But PARAM is CHAR(10). If I run the following query manually (From Eclipse SQL Plugin) it fetches the proper result.

SELECT CNTRL_4, COLUMN_ID, VALUE AS PARAM FROM MY_TABLE_NAME

But if I run the same query with Java Code (Hibernate Native Query) Then I am getiing following Result.

[{CNTRL_4=1, COLUMN_ID=1, PARAM=1}, 
 {CNTRL_4=1, COLUMN_ID=2, PARAM=1}, 
 {CNTRL_4=1, COLUMN_ID=3, PARAM=t}, 
 {CNTRL_4=2, COLUMN_ID=1, PARAM=1}, 
 {CNTRL_4=2, COLUMN_ID=2, PARAM=1}, 
 {CNTRL_4=2, COLUMN_ID=3, PARAM=f}, 
 {CNTRL_4=3, COLUMN_ID=1, PARAM=1}, 
 {CNTRL_4=3, COLUMN_ID=2, PARAM=1}, 
 {CNTRL_4=3, COLUMN_ID=3, PARAM=t}]

Please look at the PARAM data, it is only the first character.

I am using following Java Code

String sql = "SELECT CNTRL_4, COLUMN_ID, VALUE AS PARAM FROM MY_TABLE_NAME";
SQLQuery query = hibernateUtil.getCurrentSession().createSQLQuery(sql);
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
List<Map<String, Object>> data = (List<Map<String, Object>>) query.list();
System.out.println(data);

I also searched on Google and found this blog post, which is same as my problem. As per as I can remember I had solved this problem by typecasting on SQL query. But right now I forget that what I did and not able to solve it now.

Please help.

UPDATE: Problem solved by type casting. Please see my answer.

score 4 · Accepted Answer · answered Dec 02 '15 at 07:38

By type casting we can solve the problem as follows

SELECT CNTRL_4, COLUMN_ID, RTRIM(CAST (VALUE AS VARCHAR(10))) AS PARAM FROM MY_TABLE_NAME

Explanation:

Since VALUE column is defined as CHAR on DB2 and Hibernate is checking the type of the filed while using transformer Criteria.ALIAS_TO_ENTITY_MAP, then it is casting the data to Charecter. So it is returning the first character only.

But while selecting, if we change the type to VARCHAR then my problem is getting solved.

Please let me know if there is any disadvantages.

score 2 · Answer 2 · answered Dec 01 '15 at 11:46

2

Try without the transformer, so change your query to:

String sql = "SELECT CNTRL_4, COLUMN_ID, VALUE AS PARAM FROM MY_TABLE_NAME";
SQLQuery query = hibernateUtil.getCurrentSession().createSQLQuery(sql);
List data = query.list();
System.out.println(data);

That should just pass the query to the underlying DB2 driver, so it should work, unless the DB2 driver doesn't recognize the false value as a String.

answered Dec 01 '15 at 11:46

Vlad Mihalcea

142,745
71
566
911

Thanks for the answer. But it will be dificult to fetch the column because my column name are dynamic and is being decided from another query. For simplicity of problem I did not mention on my question. Though I have figured out the solution. We can type cast to VARCHAR. Please see my answer. – Partha Sarathi Ghosh Dec 02 '15 at 07:39

Hibernate DB2 Returns First Charector while using NativeQuery

2 Answers2