spring data jpa多条件查询

主要是工作中用到的关于jpa的条件查询，使用了Specification的Predicate(断言查询)条件查询代替原生sql或@Query注解：

• 记录一下工作时的代码片段，这里用到了一个嵌套的子查询

/** * 展示列表和条件查询(多条件查询) * @param status * @param createTime * @param orderNum * @param modelName * @param pageable * @return */@Overridepublic Page<OrderVO> getAllOrderTaking(String status, String createTime, String orderNum, String modelName, Pageable pageable) {    Specification<Order> specification = new Specification<Order>() {      @Override      public Predicate toPredicate(Root<Order> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {        List<Predicate> predicates = new ArrayList<>();        Predicate p1 = criteriaBuilder.equal(root.get("status"),Order.Status.valueOf("待审核"));        Predicate p2 = criteriaBuilder.equal(root.get("status"),Order.Status.valueOf("处理中"));        Predicate p3 = criteriaBuilder.equal(root.get("status"),Order.Status.valueOf("重做"));        Predicate predicate1 = null;        Predicate predicate2 = null;        if(StringUtils.isNotEmpty(modelName) || StringUtils.isNotEmpty(status) || StringUtils.isNotEmpty(createTime) || StringUtils.isNotEmpty(orderNum)){          if(null != status){            predicates.add(criteriaBuilder.equal(root.get("status"),Order.Status.valueOf(status)));          }          if(null != modelName){            Subquery<String> categoryName = criteriaQuery.subquery(String.class);            Root<Category> categoryRoot = categoryName.from(Category.class);            categoryName.select(categoryRoot.get("categoryNum")).where(criteriaBuilder.like(categoryRoot.get("name"),"%"+modelName+"%"));            Subquery<String> categoryNum = criteriaQuery.subquery(String.class);            Root<Goods> goodsRoot = categoryNum.from(Goods.class);            categoryNum.select(goodsRoot.get("id")).where(goodsRoot.get("categoryNum").in(categoryName));            predicate1 = criteriaBuilder.in(root.get("goodsId")).value(categoryNum);          }          if(null != createTime && !"".equals(createTime)){            try {              if(StringUtils.isEmpty(status)){                predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("createTime"), new SimpleDateFormat(DATE_FORMAT).parse(createTime + TIME_MIN)));                predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("createTime"), new SimpleDateFormat(DATE_FORMAT).parse(createTime + TIME_MAX)));                predicates.add(criteriaBuilder.or(p1,p2,p3));              }else{                predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("createTime"), new SimpleDateFormat(DATE_FORMAT).parse(createTime + TIME_MIN)));                predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("createTime"), new SimpleDateFormat(DATE_FORMAT).parse(createTime + TIME_MAX)));              }            } catch (ParseException e) {              throw new RuntimeException("日期格式化失败!");            }          }          if(null != orderNum && !"".equals(orderNum)){            predicate2 = criteriaBuilder.like(root.get("orderNum"), "%"+orderNum+"%");          }          if(StringUtils.isNotEmpty(orderNum) & StringUtils.isNotEmpty(modelName)){            if(StringUtils.isEmpty(status)){              predicates.add(criteriaBuilder.or(predicate1,predicate2));              predicates.add(criteriaBuilder.or(p1,p2,p3));            }else{              predicates.add(criteriaBuilder.or(predicate1,predicate2));            }          }        }else{          predicates.add(criteriaBuilder.or(p1,p2,p3));        }        return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));      }    };    Page<Order> page = orderRepository.findAll(specification,pageable);    //组装vo实体    List<OrderVO> orderVOS = new ArrayList<>();    page.getContent().stream().forEach(order -> {      OrderVO orderVO = new OrderVO();      Goods goods = goodsRepository.findOne(order.getGoodsId());      orderVO.setOrder(order);      Category category = categoryRepository.findByCategoryNum(goods.getCategoryNum());      orderVO.setBrand(category.getParent().getName());      orderVO.setModel(category.getName());      String usersec = Crypt.crypt(order.getKkmoveId(),secretKey);      orderVO.setUsersec(usersec);      orderVO.setKkmoveShop(kkmoveShop);      orderVO.setMaterial(goods.getMaterial().getName());      orderVO.setTechnology(goods.getTechnology().getName());      orderVOS.add(orderVO);    });    Page<OrderVO> voPage = new PageImpl<>(orderVOS,pageable,page.getTotalElements());    return voPage;  }

• 代码部分解析

//categoryName是我们需要查询的列(字段)Subquery<String> categoryName = criteriaQuery.subquery(String.class);//Root（实体对应的数据库表）Root<Category> categoryRoot = categoryName.from(Category.class);//通过单表的查询需要的列并添加where条件        categoryName.select(categoryRoot.get("categoryNum")).where(criteriaBuilder.like(categoryRoot.get("name"),"%"+modelName+"%"));//同样categoryNum是我们需要查询的列(字段)，但不在同在一张表中Subquery<String> categoryNum = criteriaQuery.subquery(String.class);Root<Goods> goodsRoot = categoryNum.from(Goods.class);            categoryNum.select(goodsRoot.get("id")).where(goodsRoot.get("categoryNum").in(categoryName));predicate1 = criteriaBuilder.in(root.get("goodsId")).value(categoryNum);

• 枚举类型的转换

Predicate p1 = criteriaBuilder.equal(root.get("status"),Order.Status.valueOf("待审核"));

• 查询时间段

private final static String TIME_MIN = " 00:00:00 000";private final static String TIME_MAX = " 23:59:59 999";private final static String DATE_FORMAT = "yyyy-MM-dd hh:mm:ss SSS";//查询是否在两个时间段之间predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("createTime"), new SimpleDateFormat(DATE_FORMAT).parse(createTime + TIME_MIN)));                predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("createTime"), new SimpleDateFormat(DATE_FORMAT).parse(createTime + TIME_MAX)));