JPA : How to convert a native query result set to POJO class collection
I am using JPA in my project.
I came to a query in which I need to make join operation on five tables. So I created a native query which returns five fields.
Now I want to convert the result object to java POJO class which contains the same five Strings.
Is there any way in JPA to directly cast that result to POJO object list ??
I came to the following solution ..
@NamedNativeQueries({
@NamedNativeQuery(
name = "nativeSQL",
query = "SELECT * FROM Actors",
resultClass = db.Actor.class),
@NamedNativeQuery(
name = "nativeSQL2",
query = "SELECT COUNT(*) FROM Actors",
resultClass = XXXXX) // <--------------- problem
})
Now here in resultClass, do we need to provide a class which is actual JPA entity ? OR We can convert it to any JAVA POJO class which contains the same column names ?
JPA provides an SqlResultSetMapping that allows you to map whatever returns from your native query into an Entity or a custom class.
EDIT JPA 1.0 does not allow mapping to non-entity classes. Only in JPA 2.1 a ConstructorResult has been added to map return values a java class.
Also, for OP's problem with getting count it should be enough to define a result set mapping with a single ColumnResult
I have found a couple of solutions to this.
Using Mapped Entities (JPA 2.0)
Using JPA 2.0 it is not possible to map a native query to a POJO, it can only be done with an entity.
For instance:
Query query = em.createNativeQuery("SELECT name,age FROM jedi_table", Jedi.class);
@SuppressWarnings("unchecked")
List<Jedi> items = (List<Jedi>) query.getResultList();
But in this case, Jedi , must be a mapped entity class.
An alternative to avoid the unchecked warning here, would be to use a named native query. So if we declare the native query in an entity
@NamedNativeQuery(
name="jedisQry",
query = "SELECT name,age FROM jedis_table",
resultClass = Jedi.class)
Then, we can simply do:
TypedQuery<Jedi> query = em.createNamedQuery("jedisQry", Jedi.class);
List<Jedi> items = query.getResultList();
This is safer, but we are still restricted to use a mapped entity.
Manual Mapping
A solution I experimented a bit (before the arrival of JPA 2.1) was doing mapping against a POJO constructor using a bit of reflection.
public static <T> T map(Class<T> type, Object[] tuple){
List<Class<?>> tupleTypes = new ArrayList<>();
for(Object field : tuple){
tupleTypes.add(field.getClass());
}
try {
Constructor<T> ctor = type.getConstructor(tupleTypes.toArray(new Class<?>[tuple.length]));
return ctor.newInstance(tuple);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
This method basically takes a tuple array (as returned by native queries) and maps it against a provided POJO class by looking for a constructor that has the same number of fields and of the same type.
Then we can use convenient methods like:
public static <T> List<T> map(Class<T> type, List<Object[]> records){
List<T> result = new LinkedList<>();
for(Object[] record : records){
result.add(map(type, record));
}
return result;
}
public static <T> List<T> getResultList(Query query, Class<T> type){
@SuppressWarnings("unchecked")
List<Object[]> records = query.getResultList();
return map(type, records);
}
And we can simply use this technique as follows:
Query query = em.createNativeQuery("SELECT name,age FROM jedis_table");
List<Jedi> jedis = getResultList(query, Jedi.class);
JPA 2.1 with @SqlResultSetMapping
With the arrival of JPA 2.1, we can use the @SqlResultSetMapping annotation to solve the problem.
We need to declare a result set mapping somewhere in a entity:
@SqlResultSetMapping(name="JediResult", classes = {
@ConstructorResult(targetClass = Jedi.class,
columns = {@ColumnResult(name="name"), @ColumnResult(name="age")})
})
And then we simply do:
Query query = em.createNativeQuery("SELECT name,age FROM jedis_table", "JediResult");
@SuppressWarnings("unchecked")
List<Jedi> samples = query.getResultList();
Of course, in this case Jedi needs not to be an mapped entity. It can be a regular POJO.
Using XML Mapping
I am one of those that find adding all these @SqlResultSetMapping pretty invasive in my entities, and I particularly dislike the definition of named queries within entities, so alternatively I do all this in the META-INF/orm.xml file:
<named-native-query name="GetAllJedi" result-set-mapping="JediMapping">
<query>SELECT name,age FROM jedi_table</query>
</named-native-query>
<sql-result-set-mapping name="JediMapping">
<constructor-result target-class="org.answer.model.Jedi">
<column name="name"/>
<column name="age"/>
</constructor-result>
</sql-result-set-mapping>
And those are all the solutions I know. The last two are the ideal way if we can use JPA 2.1.
Yes, with JPA 2.1 it's easy. You have very useful Annotations. They simplify your life.
First declare your native query, then your result set mapping. Write your POJO class to refer to (not included here for brevity). Last but not least: create a method in a DAO (for example) to call the query. This worked for me in a dropwizard (1.0.0) app.
First declare a native query in an entity class:
@NamedNativeQuery (
name = "domain.io.MyClass.myQuery"
query = "Select a.colA, a.colB from Table a"
resultSetMapping = "mappinMyNativeQuery") // must be the same name as in the SqlResultSetMapping declaration
Underneath you can add the resultset mapping declaration:
@SqlResultSetMapping(
name = "mapppinNativeQuery", // same as resultSetMapping above in NativeQuery
classes = {
@ConstructorResult(
targetClass = domain.io.MyMapping.class
columns = {
@ColumnResult( name = "colA", type = Long.class),
@ColumnResult( name = "colB", type = String.class)
}
)
}
)
Later in a DAO you can refer to the query as
public List<domain.io.MyMapping> findAll() {
return (namedQuery("domain.io.MyClass.myQuery").list());
}
That's it.
链接地址: http://www.djcxy.com/p/63700.html上一篇: Windows上Mono的良好IDE