Hibernate 4 returns list of null object for native sql query

Question

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.

Similar Issue reference link: Hibernate returns list of nulls although executed SQL returns values

Vipul Jain · Accepted Answer · 2017-10-24 09:11:31Z

0

You are getting null because you column in not getting mapped here. You can do this step here. Make a class to get HIERARCHY_ID, like this

public class ABC{

    private long id;
}

and then write query like this

@Query("SELECT new com.abc.abc.ABC (t.id) from table t")

This will give you list of ABC object and I think it will work.

answered Oct 24, 2017 at 9:11

Vipul Jain

1,5153 gold badges16 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jitendra Nandre Over a year ago

You are saying to use HQL instead of SQL, but In hibernate we can execute sql queries & get the list result.

Jitendra Nandre · Accepted Answer · 2017-10-26 13:40:12Z

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.

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

Collectives™ on Stack Overflow

Hibernate 4 returns list of null object for native sql query

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related