You can configure a native SQL query to return POJOs in a single line of code using the JavaBeanResult utility class:
JavaBeanResult.setQueryResultClass(query, SomeBeanClass.class);
The JavaBeanResult utility class will do the job but it relies on the developer to align the field selected in their SQL query with the constructor arguments for their bean. For example, for the code below to work, SupplierBean must have a constructor with three arguments with types compatible with the SQL result types for columns id, name, and version. JavaBeanResult uses the EclipseLink ConversionManager to convert each SQL result value to the declared type of the corresponding constructor argument. If it can't you'll get an exception.
Query query = getEntityManager()
.createNativeQuery("SELECT ID, NAME, VER FROM SUPPLIER");
JavaBeanResult.setQueryResultClass(query, SupplierBean.class);
List<SupplierBean> results = query.getResultList();
public SupplierBean(int id, String name, Long version) {
--Shaun
Complete source for JavaBeanResult.java:
/*******************************************************************************
* Copyright (c) 2010 Oracle. All rights reserved.
* This program and the accompanying materials are made available under the
* terms of the Eclipse Public License v1.0 and Eclipse Distribution License v. 1.0
* which accompanies this distribution.
* The Eclipse Public License is available at http://www.eclipse.org/legal/epl-v10.html
* and the Eclipse Distribution License is available at
* http://www.eclipse.org/org/documents/edl-v10.php.
*
* @author shsmith
******************************************************************************/
package org.eclipse.persistence.example.jpa.nativesql.constructor;
import java.lang.reflect.Constructor;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.List;
import javax.persistence.Query;
import org.eclipse.persistence.exceptions.ConversionException;
import org.eclipse.persistence.internal.helper.ConversionManager;
import org.eclipse.persistence.internal.sessions.AbstractRecord;
import org.eclipse.persistence.internal.sessions.AbstractSession;
import org.eclipse.persistence.jpa.JpaHelper;
import org.eclipse.persistence.queries.DatabaseQuery;
import org.eclipse.persistence.queries.QueryRedirector;
import org.eclipse.persistence.sessions.Record;
import org.eclipse.persistence.sessions.Session;
/***
* This class is a simple query redirector that intercepts the result of a
* native query and builds an instance of the specified JavaBean class from each
* result row. The order of the selected columns musts match the JavaBean class
* constructor arguments order.
*
* To configure a JavaBeanResult on a native SQL query use:
* JavaBeanResult.setQueryResultClass(query, SomeBeanClass.class);
* where query is either a JPA SQL Query or native EclipseLink DatabaseQuery.
*
* @author shsmith
*
*/
public final class JavaBeanResult implements QueryRedirector {
private static final long serialVersionUID = 3025874987115503731L;
protected Class<?> resultClass;
public static void setQueryResultClass(Query query, Class<?> resultClass) {
JavaBeanResult javaBeanResult = new JavaBeanResult(resultClass);
DatabaseQuery databaseQuery = JpaHelper.getDatabaseQuery(query);
databaseQuery.setRedirector(javaBeanResult);
}
public static void setQueryResultClass(DatabaseQuery query,
Class<?> resultClass) {
JavaBeanResult javaBeanResult = new JavaBeanResult(resultClass);
query.setRedirector(javaBeanResult);
}
protected JavaBeanResult(Class<?> resultClass) {
this.resultClass = resultClass;
}
@SuppressWarnings("unchecked")
public Object invokeQuery(DatabaseQuery query, Record arguments,
Session session) {
List<Object> results = new ArrayList<Object>();
try {
Constructor<?> javaBeanClassConstructor =
(Constructor<?>) resultClass.getDeclaredConstructors()[0];
Class<?>[] constructorParameterTypes =
javaBeanClassConstructor.getParameterTypes();
List<Object[]> rows = (List<Object[]>) query.execute(
(AbstractSession) session, (AbstractRecord) arguments);
for (Object[] columns : rows) {
Object[] constructorArgs = new Object[constructorParameterTypes.length];
if (columns.length != constructorParameterTypes.length) {
throw new ColumnParameterNumberMismatchException(
resultClass);
}
for (int j = 0; j < columns.length; j++) {
Object columnValue = columns[j];
Class<?> parameterType = constructorParameterTypes[j];
// convert the column value to the correct type--if possible
constructorArgs[j] = ConversionManager.getDefaultManager()
.convertObject(columnValue, parameterType);
}
results.add(javaBeanClassConstructor.newInstance(constructorArgs));
}
} catch (ConversionException e) {
throw new ColumnParameterMismatchException(e);
} catch (IllegalArgumentException e) {
throw new ColumnParameterMismatchException(e);
} catch (InstantiationException e) {
throw new ColumnParameterMismatchException(e);
} catch (IllegalAccessException e) {
throw new ColumnParameterMismatchException(e);
} catch (InvocationTargetException e) {
throw new ColumnParameterMismatchException(e);
}
return results;
}
public final class ColumnParameterMismatchException extends
RuntimeException {
private static final long serialVersionUID = 4752000720859502868L;
public ColumnParameterMismatchException(Throwable t) {
super(
"Exception while processing query results-ensure column order matches constructor parameter order",
t);
}
}
public final class ColumnParameterNumberMismatchException extends
RuntimeException {
private static final long serialVersionUID = 1776794744797667755L;
public ColumnParameterNumberMismatchException(Class<?> clazz) {
super(
"Number of selected columns does not match number of constructor arguments for: "
+ clazz.getName());
}
}
}
3 comments:
the JavaBeanResult is wonderful, yet, What if the bean constructor require entity as parameter,.
we know that its possible in NEW constructor() to put Entities.
e.g
we have entitities, User and Address, we want to wrap it in a java bean. called UserAddress.
UserAddress would be
public class UserAddress implements Serializable{
public UserAddress(User usr,Address add){}
}
we could see that the constructor require entities, not scalars.
its posible if we use jpql
select new UserAddress(a,b) from User a,Address b where ....
is it possible?, cud you show how to work arround this.
the native query would be something like this:
select a.*, b.* from User a, Address b where .....
Tq.
This is a pretty cool example.
I was looking for the longest time to find an easy way on how to assign a POJO or a POJO-List to a query result without ResulSetMapping or "re-packing" the result into the required pojo.
Andreas
I believe there's a feature proposed for JPA 2.1 that should provide something like this.
--Shaun
Post a Comment