我在我的项目中使用JPA。
我遇到一个查询，我需要对五个表进行连接操作。因此，我创建了一个返回五个字段的本机查询。
现在我想将结果对象转换为java POJO类，其中包含相同的五个字符串。
在JPA中有任何方法可以直接将结果转换为POJO对象列表吗??
我想出了如下的解决办法。
@NamedNativeQueries({
@NamedNativeQuery(
name = "nativeSQL",
query = "SELECT * FROM Actors",
resultClass = db.Actor.class),
@NamedNativeQuery(
name = "nativeSQL2",
query = "SELECT COUNT(*) FROM Actors",
resultClass = XXXXX) // <--------------- problem
})
现在在resultClass中，我们需要提供一个实际的JPA实体类吗?
或
我们可以将其转换为包含相同列名的任何JAVA POJO类?
首先声明以下注释:
@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface NativeQueryResultEntity {
}
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface NativeQueryResultColumn {
int index();
}
然后按以下方式注释你的POJO:
@NativeQueryResultEntity
public class ClassX {
@NativeQueryResultColumn(index=0)
private String a;
@NativeQueryResultColumn(index=1)
private String b;
}
然后写注释处理器:
public class NativeQueryResultsMapper {
private static Logger log = LoggerFactory.getLogger(NativeQueryResultsMapper.class);
public static <T> List<T> map(List<Object[]> objectArrayList, Class<T> genericType) {
List<T> ret = new ArrayList<T>();
List<Field> mappingFields = getNativeQueryResultColumnAnnotatedFields(genericType);
try {
for (Object[] objectArr : objectArrayList) {
T t = genericType.newInstance();
for (int i = 0; i < objectArr.length; i++) {
BeanUtils.setProperty(t, mappingFields.get(i).getName(), objectArr[i]);
}
ret.add(t);
}
} catch (InstantiationException ie) {
log.debug("Cannot instantiate: ", ie);
ret.clear();
} catch (IllegalAccessException iae) {
log.debug("Illegal access: ", iae);
ret.clear();
} catch (InvocationTargetException ite) {
log.debug("Cannot invoke method: ", ite);
ret.clear();
}
return ret;
}
// Get ordered list of fields
private static <T> List<Field> getNativeQueryResultColumnAnnotatedFields(Class<T> genericType) {
Field[] fields = genericType.getDeclaredFields();
List<Field> orderedFields = Arrays.asList(new Field[fields.length]);
for (int i = 0; i < fields.length; i++) {
if (fields[i].isAnnotationPresent(NativeQueryResultColumn.class)) {
NativeQueryResultColumn nqrc = fields[i].getAnnotation(NativeQueryResultColumn.class);
orderedFields.set(nqrc.index(), fields[i]);
}
}
return orderedFields;
}
}
使用上述框架如下:
String sql = "select a,b from x order by a";
Query q = entityManager.createNativeQuery(sql);
List<ClassX> results = NativeQueryResultsMapper.map(q.getResultList(), ClassX.class);
使用Hibernate:
@Transactional(readOnly=true)
public void accessUser() {
EntityManager em = repo.getEntityManager();
org.hibernate.Session session = em.unwrap(org.hibernate.Session.class);
org.hibernate.SQLQuery q = (org.hibernate.SQLQuery) session.createSQLQuery("SELECT u.username, u.name, u.email, 'blabla' as passe, login_type as loginType FROM users u")
.addScalar("username", StringType.INSTANCE).addScalar("name", StringType.INSTANCE)
.addScalar("email", StringType.INSTANCE).addScalar("passe", StringType.INSTANCE)
.addScalar("loginType", IntegerType.INSTANCE)
.setResultTransformer(Transformers.aliasToBean(User2DTO.class));
List<User2DTO> userList = q.list();
}
参见下面的示例，使用POJO作为伪实体从本机查询检索结果，而不使用复杂的SqlResultSetMapping。只需要两个注释，一个裸露的@Enity和一个假的@Id在你的POJO。@Id可以用于您选择的任何字段，@Id字段可以有重复的键，但不能为空值。
因为@Enity不映射到任何物理表，所以这个POJO被称为伪实体。
环境:eclipselink 2.5.0-RC1, jpa-2.1.0, mysql-connector-java-5.1.14
您可以在这里下载完整的maven项目
本机查询是基于mysql的样本雇员数据库
http://dev.mysql.com/doc/employee/en/employees-installation.html
persistence . xml
<?xml version="1.0" encoding="UTF-8"?><persistence xmlns="http://xmlns.jcp.org/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.1"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
<persistence-unit name="jpa-mysql" transaction-type="RESOURCE_LOCAL">
<class>org.moonwave.jpa.model.pojo.Employee</class>
<properties>
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/employees" />
<property name="javax.persistence.jdbc.user" value="user" />
<property name="javax.persistence.jdbc.password" value="***" />
<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
</properties>
</persistence-unit>
Employee.java
package org.moonwave.jpa.model.pojo;
@Entity
public class Employee {
@Id
protected Long empNo;
protected String firstName;
protected String lastName;
protected String title;
public Long getEmpNo() {
return empNo;
}
public void setEmpNo(Long empNo) {
this.empNo = empNo;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String toString() {
StringBuilder sb = new StringBuilder();
sb.append("empNo: ").append(empNo);
sb.append(", firstName: ").append(firstName);
sb.append(", lastName: ").append(lastName);
sb.append(", title: ").append(title);
return sb.toString();
}
}
EmployeeNativeQuery.java
public class EmployeeNativeQuery {
private EntityManager em;
private EntityManagerFactory emf;
public void setUp() throws Exception {
emf=Persistence.createEntityManagerFactory("jpa-mysql");
em=emf.createEntityManager();
}
public void tearDown()throws Exception {
em.close();
emf.close();
}
@SuppressWarnings("unchecked")
public void query() {
Query query = em.createNativeQuery("select e.emp_no as empNo, e.first_name as firstName, e.last_name as lastName," +
"t.title from employees e join titles t on e.emp_no = t.emp_no", Employee.class);
query.setMaxResults(30);
List<Employee> list = (List<Employee>) query.getResultList();
int i = 0;
for (Object emp : list) {
System.out.println(++i + ": " + emp.toString());
}
}
public static void main( String[] args ) {
EmployeeNativeQuery test = new EmployeeNativeQuery();
try {
test.setUp();
test.query();
test.tearDown();
} catch (Exception e) {
System.out.println(e);
}
}
}
