Spring Data JPA 使用Map接收原生SQL查询

使用原生SQL的原因：

1、由于在DAO中继承Repository，必须使用自定义实体类型；对接其他系统数据库，只做数据查询，并不想在本项目创建实体。

2、使用@Query方法，如果查询条件复杂，并不好用；

创建工具类：

@Component
@Transactional
public class EntityManagerUtil {

    static Logger logger = LoggerFactory.getLogger(EntityManagerUtil.class);

    @PersistenceContext
    private EntityManager entityManager;

    //返回map
    public List> getListMap(String sql){
        logger.info("sql:" + sql);
        Query nativeQuery = entityManager.createNativeQuery(sql);
        nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List resultList = nativeQuery.getResultList();
        return resultList;
    }

    //返回自定义实体类
    public List nativeQueryResult(String sql, Class clazz) {
        sql = Normalizer.normalize(sql, Normalizer.Form.NFKC);
        sql = sql.replaceAll(".*([';]+|(--)+).*", "");
        Query query = entityManager.createNativeQuery(sql);
        List queryList = query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(clazz)).list();
        return queryList;
    }

   // 返回数据条数，用作分页
    public Integer getCount(String sql){
        logger.info("sql:" + sql);
        Query nativeQuery = entityManager.createNativeQuery(sql);
        nativeQuery.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        Map map = (Map)nativeQuery.getSingleResult();
        if(map!= null && map.get("data_num") != null) {
            return Integer.parseInt(map.get("data_num").toString());
        }
        return 0;
    }

    //分页工具
    public static Integer getPageStart(Integer pageIndex,Integer pageSize) {
        Integer pageStart = (pageIndex-1) * pageSize;
        return pageStart;
    }

}

在Service中引用使用获取列表和分页数据：

@RequiredArgsConstructor
@Service
@Transactional
public class MapServiceImpl implements MapService {

    @Autowired
    private EntityManagerUtil entityManagerUtil;

    // 查询列表
    @Override
    public List> list() {
        String sql = "SELECT * FROM test ";
        return entityManagerUtil.getListMap(sql);
    }

    //实现分页查询
@Override
    public LimitDataFO listData(VisuMapSamplingSearchFO search, Integer pageIndex, Integer pageSize) {
        LimitDataFO> limitDataFO = new LimitDataFO>();
        String sqlCount = "SELECt COUNT(1) AS data_num FROM table ";
        String sqlSelect = "SELECT * FROM table ";
        StringBuffer sql = new StringBuffer("");
        sql.append(" WHERe 1=1 ");
        limitDataFO.setTotal(entityManagerUtil.getCount(sqlCount + sql.toString()));
        if(dataType == 1) {
            sql.append(" ORDER BY sampleDate DESC ");
        } else if(dataType == 2 ) {
            sql.append(" ORDER BY IF(ISNULL(reportPath),0,1) DESC, IF(ISNULL(reportDate),0,1) DESC ");
        } else if(dataType == 3) {
            sql.append(" ORDER BY IF(ISNULL(reportDate),0,1) DESC ");
        }
        sql.append(" LIMIT ").append(entityManagerUtil.getPageStart(pageIndex, pageSize)).append(",").append(pageSize);
        limitDataFO.setItems(entityManagerUtil.getListMap(sqlSelect + sql.toString()));
        return limitDataFO;
    }

}

分页实体：

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class LimitDataFO {

    private Collection items;

    private long total;

    private int totalPage;

}