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.
General rule is the following:
select
contains single expression and it's an entity, then result is that entityselect
contains single expression and it's a primitive, then result is that primitiveselect
contains multiple expressions, then result is Object[]
containing the corresponding primitives/entitiesSo, in your case list
is a List<Object[]>
.
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;
}
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.
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);
}
}
}
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.
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;
}
Source: Stackoverflow.com