SpringData JPA中复杂sql查询总结

SpringData JPA框架中本人比较喜欢用以下几种方式进行复杂sql查询，大多数场景下足够用了。

方式1（Query注解,hql语局，适用于查询指定条件的数据）

分页查询：

//可以自定义整个实体(Page<User>)，也可以查询某几个字段（Page<Object[]>），和原生sql几乎一样灵活。
@Query(value = "select b.roomUid from RoomBoard b where b.userId=:userId and b.lastBoard=true order by  b.createTime desc")
Page<String> findRoomUidsByUserIdPageable(@Param("userId") long userId, Pageable pageable);
Pageable pageable = new PageRequest(pageNumber,pageSize);
Page<String> page = this.roomBoardRepository.findRoomUidsByUserIdPageable(userId,pageable);
List<String> roomUids = page.getContent();

分组查询：

// Reposirtory层代码
@Query(value = "SELECT dispatch_order_type dispatchOrderType, SUM(real_count * list_price) cost  from house_files_configuration_details WHERE room_id = ?1 GROUP BY dispatch_order_type", nativeQuery = true)
List<Object> RoomPerTypeCostInformation(String roomId);

// service层
 List<Object> list = houseFilesConfigurationDetailsRepository.RoomPerTypeCostInformation(roomId);
    if (!CollectionUtils.isEmpty(list)) {
        list.forEach(item -> {
            // 每个对象中的所有属性
            Object[] obj = (Object[]) item;
            objMap.put(obj[0].toString(), String.format("%.0f", (double) obj[1]));
        });
    }

方式2（使用entityManager，适用于动态sql查询）

分页、分组查询：

     StringBuilder sql = new StringBuilder("SELECT ")
            .append("dispatch_order_type dispatchOrderType, ")
            .append("room_name roomName, ")
            .append("goodsname goodsname, ")
            .append("list_price listPrice, ")
            .append("real_count realCount, ")
            .append("supplier_name supplierName ")
            .append("FROM house_files_configuration_details ")
            .append("WHERE ")
            .append(HomeTypeEnum.FIELD_MAP.get(param.getHomeType()))
            .append(" = :code ");
    Optional.ofNullable(param.getRoomName())
            .ifPresent((v) -> sql.append("AND room_name = :roomName "));
    Optional.ofNullable(param.getType())
            .ifPresent((v) -> sql.append("AND dispatch_order_type = :dispatchOrderType "));
    sql.append("LIMIT :index, :size");

    //参数赋值
    Query query = entityManager.createNativeQuery(sql.toString());
    query.setParameter("code", param.getCode());
    Optional.ofNullable(param.getRoomName())
            .ifPresent((v) -> query.setParameter("roomName", v));
    Optional.ofNullable(param.getType())
            .ifPresent((v) -> query.setParameter("dispatchOrderType", v));
    query.setParameter("index", param.getPageNumber() * param.getSize());
    query.setParameter("size", param.getSize());

    // 字段映射
    return query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(ConfigurationListDTO.class))
            .addScalar("dispatchOrderType", StandardBasicTypes.STRING)
            .addScalar("roomName", StandardBasicTypes.STRING)
            .addScalar("goodsname", StandardBasicTypes.STRING)
            .addScalar("listPrice", StandardBasicTypes.DOUBLE)
            .addScalar("realCount", StandardBasicTypes.DOUBLE)
            .addScalar("supplierName", StandardBasicTypes.STRING)
            .list();

注意：Transformers的三种结果转换说明：
1.Transformers.ALIAS_TO_ENTITY_MAP //把输出结果转换成map
2.Transformers.TO_LIST //把结果按顺序排进List
3.Transformers.aliasToBean(target) //把结果通过setter方法注入到指定的对像属性中

方式3（使用Specification接口，适用于动态sql查询）

分页查询：

// 使用Sort类制定排序规则
Sort sort = new Sort(Sort.Direction.DESC, "id");

// 使用PageRequest类制定分页规则
/*
 *page:当前页
 *size:每一页展示数据数量
 *sort:上文Sort类制定的排序规则
 */
 PageRequest pageRequest = PageRequest.of(page, size, sort);
 
// 使用lambda表达式实现
        Specification<ModelDi> spec = (Specification<ModelDi>) (root, criteriaQuery, cb) -> {
           //使用集合可以应对多字段查询的情况
            List<Predicate> predicates = new ArrayList<>();
            predicates.add(cb.equal(root.get("username"), code));
            return cb.and(predicates.toArray(new Predicate[predicates.size()]))
        };
        // 根据查询条件，获取最终分页数据
		Page<ModelDi> data = ModelDiRepository.findAll(spec, pageRequest);
		return data;