我在我的项目中使用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 ddl-auto的好处:
Disable hibernate validation by removing the hibernate.ddl-auto. If not doing this, hibernate can complain about missing table in database.
Create a pojo with @Entity for the custom result set without table mapping, something like:
@Getter
@Setter
@Entity
public class MyCustomeResult implements Serializable {
@Id
private Long id;
@Column(name = "name")
private String name;
}
In repository, use the entity to map directly from query.getResultList()
public List<MyCustomeResult> findByExampleCustomQuery(Long test) {
String sql = "select id, name from examples where id =:test";
Query query = entityManager.createNativeQuery(sql, MyCustomeResult.class);
return query.setParameter("test", test).getResultList();
}
在这种情况下，使用“数据库视图”这样的实体也就是不可变实体是非常容易的。
正常的实体
@Entity
@Table(name = "people")
data class Person(
@Id
val id: Long = -1,
val firstName: String = "",
val lastName: String? = null
)
视图类实体
@Entity
@Immutable
@Subselect("""
select
p.id,
concat(p.first_name, ' ', p.last_name) as full_name
from people p
""")
data class PersonMin(
@Id
val id: Long,
val fullName: String,
)
在任何存储库中，我们都可以像这样创建查询函数/方法:
@Query(value = "select p from PersonMin p")
fun findPeopleMinimum(pageable: Pageable): Page<PersonMin>
