JPA执行NativeSQL

young 1,813 2022-11-07

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](

hibernate