data.jpa.repository 数据库查询

动态查询与分页实现
本文介绍了一种基于Java的动态查询与分页技术实现方案,通过使用JPQL及Criteria API进行灵活的数据筛选与检索。文章详细展示了如何通过条件判断构建查询逻辑,包括日期范围过滤、字段匹配以及去重等高级特性。

动态查询,分页,含子查询(root.join),去重:

    public Page<Order> searchOrderByPage(String supplierNo, long date, Constant.OrderStatus status, int pageIndex, int pageSize) {
        LocalDateTime localDateTime = ConverterUtil.getTimeByTimestamp(date);
        LocalDateTime start = getStartLocalDateTime(localDateTime);
        LocalDateTime end = getEndLocalDateTime(localDateTime);
        Page<OrderEntity> orderEntities =  orderDao.findAll((root, criteriaQuery, criteriaBuilder) -> {
            Join<OrderEntity,OrderItemEntity> join = root.join(OrderEntity_.orderItems);
            Predicate condition = criteriaBuilder.and(
                    criteriaBuilder.greaterThanOrEqualTo(root.get(OrderEntity_.updateTime),start),
                    criteriaBuilder.lessThanOrEqualTo(root.get(OrderEntity_.updateTime),end)
            );
            if(!StringUtils.isEmpty(supplierNo)) {
                Predicate condition2 = criteriaBuilder.equal(join.get(OrderItemEntity_.supplierNo),supplierNo);
                condition = criteriaBuilder.and(condition,condition2);
            }
            if(status!=null){
                Predicate condition2 = criteriaBuilder.equal(root.get(OrderEntity_.orderStatus),status);
                condition = criteriaBuilder.and(condition,condition2);
            }
            criteriaQuery.distinct(true);
            return criteriaQuery.where(condition).getRestriction();
        },new PageRequest(pageIndex,pageSize));
        return orderEntities.map(ConverterUtil::getOrder);
    }

动态查询且分页:

    public  Page<OprLog> searchOprLogsByPage(String oprName, long startDate, long endDate, Constant.OprLogType oprLogType, int pageIndex, int pageSize) {
        LocalDateTime localDateTimeStart = ConverterUtil.getTimeByTimestamp(startDate);
        LocalDateTime localDateTimeEnd = ConverterUtil.getTimeByTimestamp(endDate);
        Instant start = getStartInstant(localDateTimeStart);
        Instant end = getEndInstant(localDateTimeEnd);
        Page<OprLogEntity> oprLogEntities =  oprLogDao.findAll((root, criteriaQuery, criteriaBuilder) -> {
            Predicate condition = criteriaBuilder.and(
                    criteriaBuilder.greaterThanOrEqualTo(root.get(OprLogEntity_.createTime),start),
                    criteriaBuilder.lessThanOrEqualTo(root.get(OprLogEntity_.createTime),end)
            );
            if(oprName != null){
                Predicate condition2 = criteriaBuilder.equal(root.get(OprLogEntity_.oprName),oprName);
                condition = criteriaBuilder.and(condition,condition2);
            }
            if(oprLogType != null) {
                Predicate condition2 = criteriaBuilder.equal(root.get(OprLogEntity_.oprLogType),oprLogType);
                condition = criteriaBuilder.and(condition,condition2);
            }
            return criteriaQuery.where(condition).getRestriction();
        },new PageRequest(pageIndex,pageSize));
        return oprLogEntities.map(ConverterUtil::getOprLog);
    }


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值