springboot + springDataJpa 用法小记

最新推荐文章于 2022-06-16 20:01:07 发布

粗体鱼

最新推荐文章于 2022-06-16 20:01:07 发布

阅读量291

点赞数

分类专栏： spring全家桶文章标签： springboot jpa

本文链接：https://blog.csdn.net/tangfeng61/article/details/111570489

版权

spring全家桶专栏收录该内容

18 篇文章 0 订阅

订阅专栏

1、自定义查询语法进行分页

定义 repository接口，同时继承JpaSpecificationExecutor，然后即可使用Repository.findAll(specification, pageable); 进行分页

public interface IAppJdOrderRecordRepository extends JpaRepository<AppJdOrderRecord, Long>, JpaSpecificationExecutor<AppJdOrderRecord>

Repository.findAll(specification, pageable);

示例：

    /**
     * 分页查询用户的订单数据 repository 接口 要特别继承 JpaSpecificationExecutor<AppTaobaoOrderRecord> 如：
     * public interface IAppTaobaoOrderRecordRepository extends JpaRepository<AppTaobaoOrderRecord, Long>, JpaSpecificationExecutor<AppTaobaoOrderRecord> 
     */
    @Override
    public Page<AppJdOrderRecord> pageJdOrderList(Long userId,
                                                  List<Integer> validCodeList, List<Integer> vccValidCodeList,
                                                  AppMallOrderTimeSortEnum sortEnum, Sort.Direction descOrAscEnum,
                                                  String startTime, String endTime,
                                                  Integer pageSize, Integer pageNo,
                                                  Integer pushAcs) {

        //分页信
        //页码：前端从1开始，jpa从0开始
        sortEnum = sortEnum == null ? AppMallOrderTimeSortEnum.sort_tk_create_time : sortEnum;
        final String sortTimeType = sortEnum.getJdType();

        List<Predicate> predicateList = Lists.newArrayList();
        Specification<AppJdOrderRecord> specification = (Root<AppJdOrderRecord> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> {
            if (userId != null) {
                Expression<Long> userIdEx = root.get("userId").as(Long.class);
                Predicate equal = cb.equal(userIdEx, userId);
                predicateList.add(equal);
            }

            if (CollectionUtils.isNotEmpty(validCodeList)) {
                Expression<Integer> validCodeEx = root.get("validCode").as(Integer.class);
                Predicate in = validCodeEx.in(validCodeList);
                predicateList.add(in);
            }

            if (CollectionUtils.isNotEmpty(vccValidCodeList)) {
                Expression<Integer> validCodeEx = root.get("vccValidCode").as(Integer.class);
                Predicate in = validCodeEx.in(vccValidCodeList);
                predicateList.add(in);
            }

            if (!StringUtils.isEmpty(startTime) && !StringUtils.isEmpty(endTime)) {
                try {
                    Expression<Date> timeEx = root.get(sortTimeType).as(Date.class);

                    Date start = DateUtils.parseDate(startTime);
                    Date end = DateUtils.parseDate(endTime);
                    Predicate between = cb.between(timeEx, start, end);
                    predicateList.add(between);
                } catch (ParseException e) {
                    log.error("京东订单分页查询，时间解析异常：startTime:{}- endTime:{}", startTime, endTime);
                }
            }

            if (pushAcs != null) {
                Expression<Integer> pushAcsEx = root.get("pushAcs").as(Integer.class);
                predicateList.add(cb.equal(pushAcsEx, pushAcs));
            }

            Predicate and = cb.and(predicateList.toArray(new Predicate[predicateList.size()]));
            return and;
        };

        //查询
        pageNo = pageNo == null ? 0 : pageNo - 1 <= 0 ? 0 : pageNo - 1;
        Sort.Order order = new Sort.Order(descOrAscEnum, sortEnum.getJdType());
        Sort sort = new Sort(Lists.newArrayList(order));
        Pageable pageable = new PageRequest(pageNo, pageSize, sort);
        Page<AppJdOrderRecord> all = jdOrderRecordRepository.findAll(specification, pageable);
        return all;

    }

2、hql 语法

本质和写sql 差别不大，重点是 not null 判断的处理，另外一种是返回指定字段时，接收可以用数组表示，这一点比较麻烦

@Query(value = "SELECT sum(CASE " +
            "when vcc_tk_status = 1 then promoter_pre_fee " +
            "when vcc_tk_status = 2 then promoter_fee  " +
            "else promoter_pre_fee end) waitReturnCash " +
            "from app_promoter_order_record " +
            "WHERE ( (follower_user_id=:userId and promoter_user_id is null ) or promoter_user_id=:userId ) " +
            "and if(:paidStart is not null and :paidEnd is not null ,tk_paid_time >= :paidStart and tk_paid_time < :paidEnd,1=1)  " +
            "and vcc_tk_status in (1,2) " +
            "and if(:pushAcs is not null ,push_acs = :pushAcs,1=1) "
            , nativeQuery = true)
    BigDecimal sumWaitReturnCash(@Param("userId") Long userId, @Param("pushAcs") Integer pushAcs,
                                 @Param("paidStart") String paidStart, @Param("paidEnd") String paidEnd);





@Query(value = "select follower_user_id ,sum(`promoter_fee`) promoter_fee ,return_cash_type ,vcc_env " +
            "from  app_promoter_order_record  " +
            "where vcc_tk_status = 2 and push_acs = 1 " +
            "and if(?1 is not null and ?2 is not null,tk_earning_time >= ?1 and tk_earning_time < ?2,1=1) " +
            "and return_cash_type in ('Shop','Share') " +
            "group by follower_user_id,return_cash_type having sum(promoter_fee) > 0 ",
            nativeQuery = true)
    List<Object[]> sumReturnCashByMonthGroupByFollowerUserId(String startTime, String endTime);