Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 10 posts ] 
Author Message
 Post subject: Hibernate 4 returns list of null object for native sql query
PostPosted: Thu Oct 26, 2017 9:48 am 
Newbie

Joined: Thu Oct 26, 2017 9:44 am
Posts: 5
I've recently upgrade from hibernate 3.5 to 4.3.11 with Spring 4.3.9 Before the upgrade everything was working fine. After the upgrade I get this error.
Environment: Java 8, Tomcat 7.0.23, Hibernate 4.3.11, Spring 4.3.9, MSSQL Server 2008,

While executing the following sql query through hibernate, we are getting list of null object with correct size in the list but object are null.

Query:
select DISTINCT(HIERARCHY_ID) from BASETYPE_HIERARCHY_MAPPING
where BASETYPE_ID IN (select BASETYPE_ID from BASETYPE_GROUP_MAPPING
where GROUP_ID IN (select GROUP_ID from USER_GROUP_MAPPING where USER_ID like(select ID from USER where USERID='7')))

Java Code:
String sqlQuery="select DISTINCT(HIERARCHY_ID) from BASETYPE_HIERARCHY_MAPPING where BASETYPE_ID IN "
+ "(select BASETYPE_ID from BASETYPE_GROUP_MAPPING where GROUP_ID IN "
+ "(select GROUP_ID from USER_GROUP_MAPPING where USER_ID=(select ID from USER where USERID=:userId)))";
Query query = session.createSQLQuery(sqlQuery);
query.setParameter("userId", userId);
List<String> typeId = query.list();

In result list of null objects.I have found similar type of issue for HQL(solution was mistake in mapping) but its simple sql query.


Top
 Profile  
 
 Post subject: Re: Hibernate 4 returns list of null object for native sql query
PostPosted: Thu Oct 26, 2017 11:06 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1532
Location: Romania
Try to debug it and see what the JDBC ResultSet contains. Most likely, the column was not fetched properly. Try checking the column case or run the query in the SQL console to see if it works.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate 4 returns list of null object for native sql query
PostPosted: Fri Oct 27, 2017 5:56 am 
Newbie

Joined: Thu Oct 26, 2017 9:44 am
Posts: 5
vlad wrote:
Try to debug it and see what the JDBC ResultSet contains. Most likely, the column was not fetched properly. Try checking the column case or run the query in the SQL console to see if it works.

I have already done that, SQL query is working fine in SQL console. I have already mention that , it was working in hibernate 3.5, after upgrading the version hibernate 4.4 it is not working.


Top
 Profile  
 
 Post subject: Re: Hibernate 4 returns list of null object for native sql query
PostPosted: Fri Oct 27, 2017 8:02 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1532
Location: Romania
Is it the HIERARCHY_ID really a String? That's a curious thing.

Otherwise, the SQL query looks fine. You should debug and see why it is not working because there is no indication why it shouldn't run as expected.

By the way, Hibernate 4 is no longer supported, so, if you want to benefit from bug fixes, you will have to upgrade to 5.2.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate 4 returns list of null object for native sql query
PostPosted: Fri Oct 27, 2017 9:13 am 
Newbie

Joined: Thu Oct 26, 2017 9:44 am
Posts: 5
vlad wrote:
Is it the HIERARCHY_ID really a String? That's a curious thing.

Otherwise, the SQL query looks fine. You should debug and see why it is not working because there is no indication why it shouldn't run as expected.

By the way, Hibernate 4 is no longer supported, so, if you want to benefit from bug fixes, you will have to upgrade to 5.2.


Yes it is String Type.

Solved the problem using hibernate scalar, by specifying the return type of column in query.
addScalar()

Following changes are done in java code to solve the issue:
Code:
String sqlQuery="select DISTINCT(HIERARCHY_ID) from BASETYPE_HIERARCHY_MAPPING where BASETYPE_ID IN "
                        + "(select BASETYPE_ID from BASETYPE_GROUP_MAPPING where GROUP_ID IN "
                        + "(select GROUP_ID from USER_GROUP_MAPPING where USER_ID=(select ID from USER where USERID=:userId)))";

Query query = session.createSQLQuery(sqlQuery).addScalar("HIERARCHY_ID", StringType.INSTANCE);
query.setParameter("userId", userId);
List<String> typeId = query.list();


But in our project lot of sql queries used, so I need to change in all queires any better soluation instead of this addScalar() or why its mandatory from hibernate 4, it was working fine in 3.5. Why we should mention type for every column ? As per Hibernate Documentation it's not mandatory.


Top
 Profile  
 
 Post subject: Re: Hibernate 4 returns list of null object for native sql query
PostPosted: Fri Oct 27, 2017 10:11 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1532
Location: Romania
Quote:
But in our project lot of sql queries used, so I need to change in all queires any better soluation instead of this addScalar() or why its mandatory from hibernate 4, it was working fine in 3.5. Why we should mention type for every column ? As per Hibernate Documentation it's not mandatory.


I tested it with Hibernate 5.2, and it works just fine. I won't test it for 4.x because, as I told you before, it's no longer supported.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate 4 returns list of null object for native sql query
PostPosted: Tue Oct 31, 2017 9:58 am 
Newbie

Joined: Thu Oct 26, 2017 9:44 am
Posts: 5
vlad wrote:
I tested it with Hibernate 5.2, and it works just fine. I won't test it for 4.x because, as I told you before, it's no longer supported.


Thanks for quick reply. I have tested on 5.1.10 still its getting same issue. I can't upgrade to 5.2 because lot of changes in 5.2 version JPA queries & all.


Top
 Profile  
 
 Post subject: Re: Hibernate 4 returns list of null object for native sql query
PostPosted: Tue Oct 31, 2017 11:05 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1532
Location: Romania
Only Hibernate 5.2 issues will be fixed, so you have to provide a replicating test case for 5.2.

Sometimes, fixes get backported, so it might get into 5.1 or 5.0. However, there's little chance it will be backported to 4.x.

If you want to get this integrated like ASAP, then send us a Pull Request with a fix.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
 Post subject: Re: Hibernate 4 returns list of null object for native sql query
PostPosted: Wed Nov 01, 2017 3:55 am 
Newbie

Joined: Thu Oct 26, 2017 9:44 am
Posts: 5
vlad wrote:
Only Hibernate 5.2 issues will be fixed, so you have to provide a replicating test case for 5.2.

Sometimes, fixes get backported, so it might get into 5.1 or 5.0. However, there's little chance it will be backported to 4.x.

If you want to get this integrated like ASAP, then send us a Pull Request with a fix.


Problem is sloved. I have upgraded to hibernate 5.2.10 with Spring 4.3.9 & also change the dialect org.hibernate.dialect.SQLServerDialect to org.hibernate.dialect.SQLServer2012Dialect


Top
 Profile  
 
 Post subject: Re: Hibernate 4 returns list of null object for native sql query
PostPosted: Wed Nov 01, 2017 4:59 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1532
Location: Romania
I'm glad you got it fixed.

_________________
If you liked my answer, you are going to love my High-Performance Java Persistence book and my blog as well.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 10 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.