JPA的API对复杂查询的支持不是很友好,所以需要执行我们自己手写的SQL。
JPA提供了@Query注解,通过标注在interface上进行查询
例如
String sql = "select * from UserInfo where UserName like :name";
@Query(value=sql,useNative=true)
List<UserInfo> queryUserInfo(String name);
但是如果我们期望返回的类型没有和跟数据库做映射,就会抛出转换异常
org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type ......
比如
String sql = "select UserId,UserName from UserInfo where UserName like :name"
@Query(value=sql,useNative=true)
List<UserInfoDto> queryUserInfo(String name);
此时需要使用EntityManager
来进行处理。
@PersistenceContext
EntityManager entityManager;
String sql = "select UserId,UserName from UserInfo where UserName like :name";
Query nativeQuery = entityManager.createNativeQuery(sql);
nativeQuery.setParameter("name", "%杨%");
nativeQuery.unwrap(NativeQueryImpl.class)
.setResultTransformer(Transformers.aliasToBean(UserInfoDto.class));
List<UserInfoDto> list = query.getResultList();
那么可以对这一类操作进行封装
@Service
public class NativeQueryHelper {
@PersistenceContext
private EntityManager entityManager;
public <T> List<T> nativeQuery(String sql, Map<String,Object> param,Class<T> transferClass){
Query nativeQuery = entityManager.createNativeQuery(sql);
if (!CollectionUtils.isEmpty(param)){
param.forEach((k,v)->nativeQuery.setParameter(k, v));
}
nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(transferClass));
return nativeQuery.getResultList();
}
public <T> T nativeQuerySingleResult(String sql, Map<String,Object> param,Class<T> transferClass){
Query nativeQuery = entityManager.createNativeQuery(sql);
if (!CollectionUtils.isEmpty(param)){
param.forEach((k,v)->nativeQuery.setParameter(k, v));
}
nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(transferClass));
return (T) nativeQuery.getSingleResult();
}
}
参考:[Spring Data JPA 调用原生(Native) SQL 查询_浅尚湖间的博客-CSDN博客_findallbynativesql](