Hibernate JPA Native Query ResultSet

Question

I need to get a resultset via executing a native query in Hibernate. Though I am using EntityManager, the query and resultSet may not be entities.

A need to get ResultSet that is of List. Since the select output may vary (read dynamic) i cannot use SqlResultSetMapping.

When i tried the below code i got a result. Since what i am requesting is a single valued result. (Hibernate JPA)

Query query = manager.createNativeQuery("select name from fresher_test where id=1");
List<Object> amount = query.getResultList();

if(amount == null)
 {
    System.out.println("Hey its a null");
    return;
 }
 for(Object e : arr)
 {
   System.out.println(e.toString());
 }

The output was : Alice

When I tried to get more than one select out from the same code with Query as select name,designation from fresher_test where id=1

The output was : [Ljava.lang.Object;@8b1a4f

An object. How do I get the fields from this object?. I tried to typecast object e (List l = (List) e;) to a List but i throws java.lang.ClassCastException: java.lang.String cannot be cast to java.util.List.

This there a way that I could get the values in a List or Array ??

Note : I cannot use a result class/entity since the results could be dynamic.

Slily

List<Object[]> amount = null; amount= query.getResultList(); for(Object[] e : amount) { System.out.println(e[0] +""+ e[1]); } – Slily

Answer 1

When fetching multiple columns, the result is a List<Object[]>. Each Object[] contains one column of the row. So in your case, each Object[] would contain 2 elements, the first one being the name, and the second one being the designation.

Note that getResultList() will never return null. You shouldn't check that.

Quintal

Answer 2

JPA 2.0, if you want your result in a custom bean:

make a bean with specific constructor

package my.package;
public class MyBean{
...
    public MyBean(Long id, String desc, Long id2)

then in your entity o mapping, make your named query like this

@NamedQuery(name = "myNamedQuery", 
    query = "select new my.package.MyBean(e.idSomething, e.description, e.secondId) 
             from DBEntity e")

Bettyannbettye

Answer 3

you can use the following

NativeQueryImpl nativeQuery = (NativeQueryImpl) query; 

nativeQuery.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE); 
    List<Map<String,Object>> result = nativeQuery.getResultList();

Yahrzeit

Thanks Buddy, you saved my day. This solution is working as Wonder. – Unsaddle

How to do it in Hibernate 6 ? – Knudsen

Answer 4

In Hibernate 6:

NativeQuery<Map<String, Object>> nativeQuery = (NativeQueryImpl<Map<String, Object>>) entityManager.createNativeQuery(query);
nativeQuery.setTupleTransformer(NativeQueryMapTransformer.INSTANCE);
List<Map<String,Object>> result = nativeQuery.getResultList();

Mazonson

Answer 5

I was facing the same problem and here how to resolve it :

List<User> records = entityManager.createNamedQuery("latestStatusByUser").getResultList();
List<User> userRecords = new ArrayList<User>();
Iterator it = records.iterator( );

while (it.hasNext( )) {
    Object[] result = (Object[])it.next(); // Iterating through array object 

    userRecords.add(new User(result[0], result[1], result[2], result[3], result[4], result[5], result[6], result[7]));

    }

Antidote