38

I'm creating a complex query with multiple tables and need to list the result. Usually, I'm using the EntityManager and map the result to the JPA-Representation:

UserEntity user = em.find(UserEntity.class, "5");

Then I can access all values as the user UserEntity class defines it. But how can I access the field-values returned from a native, multiple-table query? What I get is a List of Objects. That's fine so far, but what "is" that Object? Array? Map? Collection? ...

//simpleExample
Query query = em.createNativeQuery("SELECT u.name,s.something FROM user u, someTable s WHERE s.user_id = u.id");
List list = query.getResultList();

//do sth. with the list, for example access "something" for every result row.

I guess the answer is quite simple, but most examples out there just show the usage when directly casting to a targetClass.

PS: In the example I could use the class-mappings of course. But in my case someTable is not managed by JPA, and therefore I don't have the entity nor do I have a class-representation of it, and since I'm joining like 20 tables, I don't want to create all the classes just to access the values.

Roman C
  • 49,761
  • 33
  • 66
  • 176
dognose
  • 20,360
  • 9
  • 61
  • 107

8 Answers8

66

General rule is the following:

  • If select contains single expression and it's an entity, then result is that entity
  • If select contains single expression and it's a primitive, then result is that primitive
  • If select contains multiple expressions, then result is Object[] containing the corresponding primitives/entities

So, in your case list is a List<Object[]>.

axtavt
  • 239,438
  • 41
  • 511
  • 482
58

Since JPA 2.0 a TypedQuery can be used:

TypedQuery<SimpleEntity> q = 
        em.createQuery("select t from SimpleEntity t", SimpleEntity.class);

List<SimpleEntity> listOfSimpleEntities = q.getResultList();
for (SimpleEntity entity : listOfSimpleEntities) {
    // do something useful with entity;
}
gprest
  • 683
  • 5
  • 4
13

If you need a more convenient way to access the results, it's possible to transform the result of an arbitrarily complex SQL query to a Java class with minimal hassle:

Query query = em.createNativeQuery("select 42 as age, 'Bob' as name from dual", 
        MyTest.class);
MyTest myTest = (MyTest) query.getResultList().get(0);
assertEquals("Bob", myTest.name);

The class needs to be declared an @Entity, which means you must ensure it has an unique @Id.

@Entity
class MyTest {
    @Id String name;
    int age;
}
GFonte
  • 451
  • 6
  • 10
9

The above query returns the list of Object[]. So if you want to get the u.name and s.something from the list then you need to iterate and cast that values for the corresponding classes.

MGPJ
  • 1,062
  • 1
  • 8
  • 15
2

I had the same problem and a simple solution that I found was:

List<Object[]> results = query.getResultList();
for (Object[] result: results) {
    SomeClass something = (SomeClass)result[1];
    something.doSomething;
}

I know this is defenitly not the most elegant solution nor is it best practice but it works, at least for me.

Dharman
  • 30,962
  • 25
  • 85
  • 135
0

Here is the sample on what worked for me. I think that put method is needed in entity class to map sql columns to java class attributes.

    //simpleExample
    Query query = em.createNativeQuery(
"SELECT u.name,s.something FROM user u,  someTable s WHERE s.user_id = u.id", 
NameSomething.class);
    List list = (List<NameSomething.class>) query.getResultList();

Entity class:

    @Entity
    public class NameSomething {

        @Id
        private String name;

        private String something;

        // getters/setters



        /**
         * Generic put method to map JPA native Query to this object.
         *
         * @param column
         * @param value
         */
        public void put(Object column, Object value) {
            if (((String) column).equals("name")) {
                setName(String) value);
            } else if (((String) column).equals("something")) {
                setSomething((String) value);
            }
        }
    }
aborskiy
  • 1,337
  • 2
  • 10
  • 12
0

What if you create a bean with all required properties and cast the result using Java 8+ streams?

Like this:

public class Something {

    private String name;
    private String something;

    // getters and setters
}

And then:

import javax.persistence.Query;

...

Query query = em.createNativeQuery("SELECT u.name,s.something FROM user u, someTable s WHERE s.user_id = u.id", Something.class);
List<?> list = query.getResultList();

return list
         .stream()
         .map(item -> item instanceof Something ? (Something) item : null)
         .collect(Collectors.toList());

That way, you don't need to return List<Object[]> nor hide the warning with @SuppressWarnings("unchecked")

Ps.:

1 - I know that this post is very old. But... I'm here in 2021, so others will be coming here too =)

2 - This is wrong or bad practice? Let me know :D

Alexandre
  • 564
  • 4
  • 6
  • 1
    This will cause `NullPointerException`, because `map` doesn't allow returning a `null` – darw Sep 23 '21 at 16:31
-1

You can also update your hibernate to a version greater than 5.4.30.final