SpringBoot JPA实践之EntityManage查询返回自定义DTO的代理实现

JPA 动态代理

admin 发布于：2020-05-01 22:14:39

阅读：loading

根据前文中的代码实现示例，将实现一个接口代理查询返回自定义DTO的实现，与JPA自带的@Query查询实现类似，主要是将SQL查询出的结果集转换为普通Bean对象，依赖EntityManager对象提供的方法，自动将查询的结果集映射为Map<String,Object>、DTO、List<Map<String,Object>>、List<DTO>，具体使用参考如下。

SQL查询结果集返回为Map结构

package cn.chendd.example.jpa.user.repository;

import cn.chendd.example.jpa.nativequery.annotions.Query;
import cn.chendd.example.jpa.nativequery.annotions.QueryResult;
import cn.chendd.example.jpa.nativequery.enums.ResultSetType;
import cn.chendd.example.jpa.user.dao.NaiveQueryForMapDao;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

/**
* @author chendd
* @date 2020/5/6 23:19
*/
public interface MapResultSqlQuery {

    @Query(sql = "select * from user limit 1" , entityManager = NaiveQueryForMapDao.class ,
            resultSet = @QueryResult(resultSetType = ResultSetType.Map))
    Map<String , Object> queryForMap();

    @Query(sql = "select * from user" , entityManager = NaiveQueryForMapDao.class ,
            resultSet = @QueryResult(resultSetType = ResultSetType.Map))
    List<Map<String , Object>> queryForList();

    @Query(sql = "select * from user where name = :name " , entityManager = NaiveQueryForMapDao.class ,
            resultSet = @QueryResult(resultSetType = ResultSetType.Map))
    List<Map<String , Object>> queryForList(@Param("name") String name);

    @Query(sql = "select * from user where (name = :name or :name is null) " +
            "and (:sexFlag is null or sex in (:sexList))" , entityManager = NaiveQueryForMapDao.class ,
            resultSet = @QueryResult(resultSetType = ResultSetType.Map))
    List<Map<String, Object>> queryForList(@Param("name") String name , @Param("sexFlag") String sexFlag, @Param("sexList") List<String> sexList);
}

SQL查询结果集返回为DTO结构

package cn.chendd.example.jpa.user.repository;
 
import cn.chendd.example.jpa.nativequery.annotions.Query;
import cn.chendd.example.jpa.nativequery.annotions.QueryResult;
import cn.chendd.example.jpa.user.dao.NaiveQueryForBeanDao;
import cn.chendd.example.jpa.user.dto.UserResultDto;
import org.springframework.data.repository.query.Param;
 
import java.util.List;
 
 /**
  * @author chendd
  * @date 2020/5/11 22:36
  */
public interface BeanResultSqlQuery {
 
     @Query(sql = "select 88911 id , 'chendd' userName , now() createTime , 88911.006 amount , " +
             "            3.1415926 pi , 1 status", entityManager = NaiveQueryForBeanDao.class)
     UserResultDto queryForBean();
 
     @Query(sql = "select 88911 id , 'chendd' userName , now() createTime , 88911.006 amount , " +
             " 3.1415926 pi , 1 status" , entityManager = NaiveQueryForBeanDao.class)
     List<UserResultDto> queryForList();
 
     @Query(sql = "select * from (" +
         "select 1 id , 'chen11' userName , now() createTime " +
          " union all " +
          "select 2 id , 'chen22' userName , now() createTime " +
          " union all " +
          "select 3 id , 'chen33' userName , now() createTime " +
          " union all " +
          "select 4 id , 'chen44' userName , now() createTime " +
          ") newTab" , entityManager = NaiveQueryForBeanDao.class
          , resultSet = @QueryResult(include = {"id" , "userName" , "createTime"}))
     List<UserResultDto> queryForListForPart();
 
     @Query(sql = "select * from (" +
         "select 1 id , 'chen11' userName , now() createTime " +
          " union all " +
          "select 2 id , 'chen22' userName , now() createTime " +
          " union all " +
          "select 3 id , 'chen33' userName , now() createTime " +
          " union all " +
          "select 4 id , 'chen44' userName , now() createTime " +
          ") newTab where id > :id and (:name is null or userName like concat(:name,'%'))"
          , entityManager = NaiveQueryForBeanDao.class
          , resultSet = @QueryResult(include = {"id" , "userName" , "createTime"}))
     List<UserResultDto> queryForListForParam(@Param("id") Long userId , @Param("name") String userName);
 
     @Query(sql = "select a.name userName from ${value(spring.jpa.properties.hibernate.default_catalog) , after(.)}user a " +
             " where 1=1 limit :limit"
             , entityManager = NaiveQueryForBeanDao.class
             , resultSet = @QueryResult(include = "userName")
     )
     List<UserResultDto> queryForListForVariableParam(@Param("limit") Long limit);
 
}

DTO代码定义

package cn.chendd.example.jpa.user.dto;
 
import cn.chendd.example.jpa.nativequery.annotions.Column;
import lombok.Data;
import org.hibernate.type.StringType;
 
import java.math.BigDecimal;
import java.sql.Timestamp;
 
/**
 * UserResultDto
 *
 * @author chendd
 * @date 2020/5/6 0:29
 */
@Data
public class UserResultDto {
 
     @Column
     private Long id;
     @Column(name = "userName" , type = StringType.class)
     private String userName;
     @Column
     private Timestamp createTime;//或者Date类型
     @Column
     private BigDecimal amount;
     @Column
     private Double pi;
     @Column
     private Boolean status;
 
}