Using native-sql in Hibernate - how to handle results

Question

I have several tables where I cannot use sleep mappings to define associations (or I don't know how to make them). Therefore, I decided to use nativesql when trying to query the results of the union. 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 problem in qry.list () is returning an array of objects, and I cannot show it to any other objects. I mean, I created a dummy object with a constructor like this.

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

and tried to make a list like ..

List dummyList = Listqry.list ();

but that will not work. When I try to access a DummyObject from a dummyList, I cannot receive a casr object to exclude a DummyObject.

Answer 1

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

Alternatively, if you want, you can simply manipulate the Object [] array for each row yourself. If you know the data types in advance, you can simply use strings, integers, etc.

I often do this:

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

Answer 2

you can use this as

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