Using native-sql in Hibernate - how to handle results

Question

I have couple of tables, where I cannot use hibernate mappings to define associations (or I don't know how to do them).. so I have decided to use nativesql when trying to query for join results.. query is something like this..

 String sql = "SELECT p.lastName, p.firstName, p.middleName, p.deactivateDate, p.id, p.userId, p.userRole, TO_CHAR(MAX(au.timestamp),'MM/DD/YYYY') as \"last_Login\", ROUND(SYSDATE-MAX(au.timestamp))as \"days_Elapsed\" 
              FROM LLPersonnel p LEFT OUTER JOIN LoginAudit au ON p.userId = au.userAccount AND UPPER(au.operation) = 'SUCCESS'" 
              WHERE p.deactivateDate is null AND p.userRole is not null GROUP BY p.lastName, p.firstName, p.middleName, p.deactivateDate, p.id, p.netId, p.llRole" 
              ORDER BY 1, 2, 3";

    SQLQuery qry = sessionFactory.getCurrentSession().createSQLQuery(sql);
            qry.list();

the issue is qry.list() returns object array and I can't seem to cast it to any other object. I mean I have created a dummy object with constructor like this..

DummyObject(lastName, firstName, middleName, deactivatedate,id,userId,userRole,last_Login, days_Elapsed)

and tried to cast the list like..

List dummyList = Listqry.list();

but this doesn't work.. when I try to access DummyObject from dummyList, I get cannot casr object to DummyObject exception.

Norman · Accepted Answer · 2011-11-04 04:39:35Z

8

That code should return you a list of Object[]. You can create a class and call .addEntity() and it will populate the entity for you.

Alternately, if you want to, you can just manipulate the Object[] array for each row yourself. If you know the data types in advance, you can just case the Strings, Integers, etc.

I do this often:

List<Object[]> list = (List<Object []>) q.list();
for (Object[] row : list) {
    String lastName = (String) row[0];
    ...
}

answered Nov 4, 2011 at 4:39

Norman

4533 silver badges5 bronze badges

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

2 Comments

RKodakandla Over a year ago

thanks Norman.. I followed the second approach.. works like magic!

Lawrence Over a year ago

Indeed Thanks Norman. So simple I am going to knock my head to the door out of never-ending stupidity... :-O Thanks dude! You saved me.

Muhammed şahsuvaroğlu · Accepted Answer · 2011-11-04 08:58:22Z

1

you can use like this

java.util.List temp = hibernateTemplate.find(
                "select u from User u where  u.username='" + username + "'");

      //  Client postClient = new Client();
      //  postClient.UsernameAsssertion("http://", username);

        if (temp.size() > 0) {
            return assembler.buildUserFromUserEntity((User) temp.get(0));

answered Nov 4, 2011 at 8:58

Muhammed şahsuvaroğlu

4792 gold badges8 silver badges22 bronze badges

Collectives™ on Stack Overflow

Using native-sql in Hibernate - how to handle results

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related