JPA使用map或者自定义实体类接收@Query查询回来的数据
接手到一个比较旧的项目,使用的是jpa,在做报表类的功能时,很多数据是多表关联查询出来的,现有的pojo无法与之关联,而自定义的实体与数据库中的表无关联的话使用@Query查询出来的数据无法映射到实体,不用实体接收的话会返回List<Object[]>,但是当某一个字段是null的话,Object[]中就会缺少那个字段,所以无法根据数组下标来自行映射,查询很多资料,最后找到一些方法
1. 使用接口接收数据
定义接收数据的接口(经多次测试,接口方法取名随意,会按照实际查询出来的字段顺序自动映射)
编写Repository的方法
测试
成功获取到数据,然后可以自己手动赋值到要用的实体类,或者存放在Map中
2. 通过EntityManager 的nativeQuery设置参数,用List
query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
List resultList = query.getResultList();
for (Object o : resultList) {
Map row = (Map) o;
String a = row.get("a").toString();
String b = row.get("b").toString();
String city = row.get("city").toString();
}
- 使用@NameNativeQuery和@SqlResultSetMapping自定义复杂查询(挺复杂,没用过)
@NamedNativeQuery(
name = "queryByEntIdAndRoleIds",
resultClass = UserAndRoleModel.class,
query = "select u.id as userId, u.login_name as loginName, u.user_name as userName, r.`name` as roleName, r.id as roleId " +
"from user u " +
"inner join r_user_role ur on u.id = ur.euser_id " +
"inner join role r on r.id = ur.role_id " +
"where u.state = 2 and u.frozen_state = 0 " +
" and u.ent_id = :entId and ur.role_id in (:roleIds) "
,
resultSetMapping = "userAndRoleModelMapping")
@MappedSuperclass
@SqlResultSetMapping(
name = "userAndRoleModelMapping",
classes = @ConstructorResult(
targetClass = UserAndRoleModel.class,
columns = {
@ColumnResult(name = "userId", type = Long.class),
@ColumnResult(name = "loginName", type = String.class),
@ColumnResult(name = "userName", type = String.class),
@ColumnResult(name = "roleId", type = Long.class),
@ColumnResult(name = "roleName", type = String.class),
}
)
)
@AllArgsConstructor
@NoArgsConstructor
@Setter
@Getter
@ToString
public class UserAndRoleModel {
private Long userId;
private String loginName;
private String userName;
private Long roleId;
private String roleName;
}