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.

Answer 1

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];
    ...
}

Answer 2

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));