data.jpa.repository 数据库查询

动态查询与分页实现

最新推荐文章于 2024-09-14 21:01:13 发布

danchejikafo

最新推荐文章于 2024-09-14 21:01:13 发布

阅读量507

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/danchejikafo/article/details/78060547

本文介绍了一种基于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);
    }