[JPA] 相关参数Impl实现层

Impl层的一段代码详解:

public PageBean<SumSellCardVo> xxxxxx(Integer current, Integer rowCount, HashMap<String, String> searchParams) {
        //criteriaBuilder用于构建CriteriaQuery的构建器对象
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        //criteriaQuery包含查询语句的各个部分，如where、max、sum、groupBy、orderBy等
        CriteriaQuery<SumSellCardVo> criteriaQuery = criteriaBuilder.createQuery(SumSellCardVo.class);
        //获取查询实例的属性，select * from sum_recharge_card
        Root<SumSellCard> root = criteriaQuery.from(SumSellCard.class);
        //相当于select rechargeCardDate,merchantNo cardTypeNum,sum(rechargeNum) as rechargeNum,sum(rechargeCost) as rechargeCost,sum(rechargeMoney) as rechargeMoney,sum(totalMoney) as totalMoney from sum_recharge_card中select 与 from之间的部分


        criteriaQuery.multiselect(
                root.get("merchantNo"),
                root.get("sellCardDate"),
                root.get("cardTypeNum"),
                criteriaBuilder.sum(root.get("sellCount").as(Long.class)).alias("sumSellCount"),
                criteriaBuilder.sum(root.get("deposit").as(Long.class)).alias("sumDeposit"),
                criteriaBuilder.sum(root.get("actAmt").as(Long.class)).alias("sumActAmt")
        );

        //封装条件查询
        List<Predicate> predicateList = new ArrayList<>();
        if (StringUtil.isNotEmpty(searchParams.get("cardTypeNum"))) {
            predicateList.add(criteriaBuilder.equal(root.get("cardTypeNum"), searchParams.get("cardTypeNum")));
        }
        if(ConstantParams.USER_MERT.equals(TokenHelper.getUserType())){
            //商户账号
            predicateList.add(criteriaBuilder.equal(root.get("merchantNo"), TokenHelper.getMerchantNo()));
        }
        if (StringUtil.isNotEmpty(searchParams.get("startDate"))) {

            predicateList.add(criteriaBuilder.greaterThanOrEqualTo(root.get("sellCardDate"), searchParams.get("startDate")));
//            predicateList.add(criteriaBuilder.greaterThanOrEqualTo(root.get("sellCardDate"), searchParams.get("startDate")));
        }
        if (StringUtil.isNotEmpty(searchParams.get("endDate"))) {
            predicateList.add(criteriaBuilder.lessThanOrEqualTo(root.get("sellCardDate"), searchParams.get("endDate")));
//            predicateList.add(criteriaBuilder.lessThanOrEqualTo(root.get("sellCardDate"), searchParams.get("endDate")));
        }

        //封装分组条件
        List<Expression<?>> groups = new ArrayList<>();
//        groups.add(root.get("merchantNo"));
        groups.add(root.get("sellCardDate"));
        groups.add(root.get("cardTypeNum"));

        //封装排序条件
        List<Order> orders = new ArrayList<>();
        orders.add(criteriaBuilder.desc(root.get("sellCardDate")));
        orders.add(criteriaBuilder.asc(root.get("cardTypeNum")));

        Predicate[] predicates = new Predicate[predicateList.size()];

//select merchantNo,sellCardDate,cardTypeNum,sum(sell_count) as sumSellCount,sum(deposit) as sumDeposit,sum(act_amt) as sumActAmt from xxx表名称 WHERE  xxx条件  GROUP BY merchantNo ,sellCardDate,cardTypeNum  ORDER BY sell_card_date,card_type_num;      这个大概就是 这个sql 的实现           
criteriaQuery.where(criteriaBuilder.and(predicateList.toArray(predicates))).groupBy(groups).orderBy(orders);

        //封装分页配置
        long count = entityManager.createQuery(criteriaQuery).getResultList().size();
        rowCount = PageUtils.initRowCount(rowCount);
        current = PageUtils.initPageCount(rowCount, current, count);

        //sql查询对象
        TypedQuery<SumSellCardVo> createQuery = entityManager.createQuery(criteriaQuery);
        //设置分页参数
        createQuery.setFirstResult((current - 1) * rowCount);
        createQuery.setMaxResults(rowCount);
        List<SumSellCardVo> resultList = createQuery.getResultList();

        //初始化分页
        PageBean<SumSellCardVo> pageBean = new PageBean<>();
        pageBean.setCurrent(current);
        pageBean.setRowCount(rowCount);
        pageBean.setTotal(count);
        pageBean.setRows(resultList);

        return pageBean;

select merchantNo,sellCardDate,cardTypeNum,sum(sell_count) as sumSellCount,sum(deposit) as sumDeposit,sum(act_amt) as sumActAmt from xxx表名称 WHERE  xxx条件  GROUP BY merchantNo ,sellCardDate,cardTypeNum  ORDER BY sell_card_date,card_type_num;      

 

JPA CriteriaBuilder中的一些运算的使用

最近使用jpa时，需要使用订单中的金额除以单价算出每个订单的数量，然后求和。找了好多资料才解决，在此整理一下。
首先了解一下CriteriaBuilder的一些运算

// Create path and parameter expressions:
  Expression<Integer> path = country.get("population");
  Expression<Integer> param = cb.parameter(Integer.class);

  // Addition (+)
  Expression<Integer> sum1 = cb.sum(path, param); // expression + expression
  Expression<Integer> sum2 = cb.sum(path, 1000); // expression + number
  Expression<Integer> sum3 = cb.sum(1000, path); // number + expression

  // Subtraction (-)
  Expression<Integer> diff1 = cb.diff(path, param); // expression - expression
  Expression<Integer> diff2 = cb.diff(path, 1000); // expression - number
  Expression<Integer> diff3 = cb.diff(1000, path); // number - expression

  // Multiplication (*)
  Expression<Integer> prod1 = cb.prod(path, param); // expression * expression
  Expression<Integer> prod2 = cb.prod(path, 1000); // expression * number
  Expression<Integer> prod3 = cb.prod(1000, path); // number * expression

  // Division (/)
  Expression<Integer> quot1 = cb.quot(path, param); // expression / expression
  Expression<Integer> quot2 = cb.quot(path, 1000); // expression / number
  Expression<Integer> quot3 = cb.quot(1000, path); // number / expression

  // Modulo (%)
  Expression<Integer> mod1 = cb.mod(path, param); // expression % expression
  Expression<Integer> mod2 = cb.mod(path, 1000); // expression % number
  Expression<Integer> mod3 = cb.mod(1000, path); // number % expression

  // Math(abs, exp, sqrt)
  Expression<Integer> abs = cb.abs(param); // 求绝对值ABS(expression)
  Expression<Integer> neg = cb.neg(path); // 求相反数 -expression
  Expression<Integer> sqrt = cb.sqrt(cb.literal(100)); //求平方根 SQRT(expression)
 

由于CriteriaBuilder提供的加减乘除方法的名字和平常使用的不太一样，所以用了好久才找出来。
单字段求和可以直接使用、

CriteriaBuilder cb = em.getCriteriaBuilder();
Expression<Number> sum = cb.sum(root.get(字段名)).alias(别名)

前边也说了需求是用金额（amount）除以单价（unitPrice），然后求和，所以这时需要先用amount除以unitPrice

Expression<Number> quot = cb.quot(root.get("amount"), root.get("unitPrice")); 

算出数量后就可以使用sum求和了。

cb.sum(quot)

 https://blog.csdn.net/qq_21965899/article/details/109691081  图片借鉴地址