JPA动态参数查询

使用JPA动态拼接查询条件 实现类查询方法

//实现类查询方法
  @Override
  public Page<VehiclePassEntity> selectList(QueryParams queryParams) {
    List<VehiclePassEntity> content = null;
//    拼接分页参数和排序字段
//    或是 PageRequest.of
    PageRequest pageRequest = new PageRequest(queryParams.getPageIndex() - 1,
        queryParams.getPageSize(), Direction.DESC, "passTime");
//    拼接查询条件
    Map map = creatMap(queryParams);
    Specification<VehiclePassEntity> specification = createSpecification(map,queryParams);
//    执行查询
    Page<VehiclePassEntity> all = vehiclePassRepository.findAll(specification, pageRequest);
    return all;
  }
     

参数类型

 
@Data
@ApiModel
public class QueryParams {

  @ApiModelProperty(name = "index", value = "页码")
  private Integer pageIndex = 1;
  @ApiModelProperty(name = "size", value = "页面大小")
  private Integer pageSize = 50;
  @ApiModelProperty(name = "plateNo", value = "车牌号，模糊匹配")
  private String plateNo;
  @ApiModelProperty(name = "weightMin", value = "实载重量最小值")
  private String weightMin;
  @ApiModelProperty(name = "weightMax", value = "实载重量最大值")
  private String weightMax;
  @ApiModelProperty(name = "deviceName", value = "设备名称")
  private String deviceName;
  @ApiModelProperty(name = "isOverWeight", value = "是否超重")
  private Boolean isOverWeight;
  @ApiModelProperty(name = "startTime", value = "开始时间")
  private String startTime;
  @ApiModelProperty(name = "endTime", value = "结束时间")
  private String endTime;
}

参数处理方法

 public static Map creatMap(Object object) {
    Map<String, String> map = null;
    String objStr = JSON.toJSONString(object);
    if (StringUtils.isAllBlank(objStr)) {
      logger.error("拼接的查询条件为空");
      return null;
    }
    map = JSON.parseObject(objStr, Map.class);
    return map;
  }

JPA动态查询，拼接Specification

 private Specification<VehiclePassEntity> createSpecification(Map<String, String> searchMap,
      QueryParams queryParams) {
    return (root, criteriaQuery, criteriaBuilder) -> {
      List<Predicate> predicateList = new ArrayList<>();
      // 设备编码
      if (StringUtils.isNotBlank((String) searchMap.get("deviceIndexCode"))) {
        predicateList.add(criteriaBuilder
            .equal(root.get("deviceIndexCode").as(String.class),
                searchMap.get("deviceIndexCode")));
      }
      // plateNo车牌号
      if (StringUtils.isNotBlank((String) searchMap.get("plateNo"))) {
        predicateList.add(criteriaBuilder
            .like(root.get("plateNo").as(String.class),
                "%" + searchMap.get("plateNo") + "%"));
      }
          // 重量
      if (StringUtils.isNotBlank((String) searchMap.get("weightMin"))) {
        predicateList.add(criteriaBuilder
            .ge(root.get("weight").as(Double.class),
                Double.parseDouble(searchMap.get("weightMin"))));
      }
      // 重量
      if (StringUtils.isNotBlank((String) searchMap.get("weightMax"))) {
        predicateList.add(criteriaBuilder
            .le(root.get("weight").as(Double.class),
                Double.parseDouble(searchMap.get("weightMax"))));
      }
           // 开始时间
      if (!JudgeEmpty.isEmpty(queryParams.getStartTime())) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date startDate = null;
        Date endDate = null;
        try {
          startDate = format.parse(queryParams.getStartTime());
          endDate = format.parse(queryParams.getEndTime());
        } catch (Exception e) {
          logger.error("时间格式化失败");
        }
        predicateList.add(criteriaBuilder.between(root.<Date>get("passTime"), startDate, endDate));
      }
           // 是否超重
      Object isOverWeight = searchMap.get("isOverWeight");
      if (!JudgeEmpty.isEmpty(isOverWeight)) {
        predicateList.add(criteriaBuilder
            .equal(root.get("isOverWeight").as(Boolean.class),
                searchMap.get("isOverWeight")));
      }

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