SPEL与SpringDataJPA结合实现动态模糊查询

     SpringDataJPA众所周知可以简化代码量，体现面向对象的开发思路。单表查询仅需要几行代码搞定，主子表查询时梳理好@JoinColumn的关系也很方便。弊端在于对于前端的动态查询参数，常规方法需要继承JpaSpecificationExecutor接口在代码中处理sql，但业务代码中混入sql让我的强迫症很难受，所以在我接的新项目中决心解决掉这个问题。

     项目结构  AngularJS kendoUI +SpringDataJPA  Mysql  springBoot1.4.5

//前端分页动态查询条件
searchParams: {"entryId":"11","itemNo":"11","plotRatio":"11"}
page: 0
size: 50
_: 1592962768977

//也有可能时这样的
searchParams: {"itemNo":"11"}
page: 0
size: 50
_: 1592962768978

 框架对查询条件的封装如上

//Controller接口如下
@RequestMapping(value = "/getManagePodPageBySearch",
            method = RequestMethod.GET,
            params = {"page","size"},
            produces = MediaType.APPLICATION_JSON_VALUE)
    public ResponseEntity<Page<TWmsItemdataManagePodDTO>> getManagePodPageBySearch(@RequestParam (required = false) String searchParams,
                                                                                   Pageable pageable) {
        Map<String, Object> searchMap = JSON.parseObject(searchParams);
        return ResponseEntity.ok(tWmsBatchInfoService.getItemdataManagePodPageBySearch(searchMap,pageable));
    }

 controller代码拿到查询条件后不要着急解析，扔给Service去做

//ServiceImpl代码 
public Page<TWmsItemdataManagePodDTO> getItemdataManagePodPageBySearch(Map<String, Object> searchMap, Pageable pageable) {
        Pageable pageableAndSort =  JPAPageUtil.creatSortByIdASC(pageable);
//带时间参数的，特殊处理一下
//    Map<String, Object> searchMap = MapString2Date(searchMap)
        List<TWmsItemdataManagePodDTO> list =  invStockunitRepository.getItemdataManagePodPageBySearch(searchMap);
   //省略业务代码若干
   //list无需处理也可以直接Repository返回Page
   return new PageImpl<>(list ,pageableAndSort,list.size());
}


//工具类中单独处理时间格式
    public static Map<String, Object> MapString2Date(Map<String,Object> searchMap) {
       //根据前端字段做调整
        if (!ObjectUtil.isEmpty(searchMap.get("endDate"))) {
            Date endDate = string2Date(searchMap.get("endDate").toString());
            searchMap.put("endDate", endDate);
        } else {
            searchMap.remove("endDate");
        }
        if (!ObjectUtil.isEmpty(searchMap.get("startDate"))) {
            Date startDate = string2Date(searchMap.get("startDate").toString());
            searchMap.put("startDate", startDate);
        } else {
            searchMap.remove("startDate");
        }
        return searchMap;
    }

//工具类中单独处理时间格式
@SneakyThrows
    public static Date string2Date(String dateString) {
        String strDateFormat = "yyyy-MM-dd";
        if (dateString.length()>11){
            strDateFormat="yyyy-MM-dd HH:mm:SS";
        }
        SimpleDateFormat sdf = new SimpleDateFormat(strDateFormat);
        return sdf.parse(dateString);
    }

 Service只做业务处理，继续把Map<String, Object> searchMap扔给Repository接口类，重点出现了！！！

//Repository接口类
//多表动态模糊查询组装DTO方法
@Query("select new com.test.test.crud.dto.TWmsItemdataManagePodDTO(i.id,i.mdItemdata.name,i.mdItemdata.skuNo,i.batchNo,i.invUnitload.mdStoragelocation.name,i.amount,i.mdItemdata.tWmsItemdata.boxCount) " +
            "from InvStockunit i where " +
            " (i.batchNo like concat('%', :#{#search['entryId']},'%') or :#{#search['entryId']} is null)" +
            " and (i.itemDataId like concat('%', :#{#search['itemNo']},'%') or :#{#search['itemNo']} is null) ")
    List<TWmsItemdataManagePodDTO> getItemdataManagePodPageBySearch(@Param("search") Map<String, Object> search);

//单表动态模糊带时间查询
@Query("select t from TWmsBatchInfo t where " +
            "(t.id like  concat('%',:#{#search['id']},'%' )or :#{#search['id']} is null)  " +
            "and (t.mdItemdata.skuNo like concat('%', :#{#search['itemNo']},'%')or :#{#search['itemNo']} is null)" +
            "and (t.mdItemdata.name like concat('%', :#{#search['itemName']},'%') or :#{#search['itemName']} is null)" +
            "and (t.supplier like concat('%', :#{#search['supplier']},'%') or :#{#search['supplier']} is null)" +
            "and (t.modifiedDate >=:#{#search['startDate']} or :#{#search['startDate']} is null)" +
            "and (t.modifiedDate <=:#{#search['endDate']} or :#{#search['endDate']} is null)")
    Page<TWmsBatchInfo> getPageBySearch(@Param("search") Map<String, Object> search, Pageable pageable);

这里划几个重点：

1.利用spel语法#{#Map['key']} 从Map<String, Object> search中取出查询条件
2.利用spel语法object or object  is  null  构造动态查询
3.利用like  concat('%',String,'%')  构造模糊查询
4.利用new  DTO() 接收连表查询返回值

//DTO类
@Data
public class TWmsItemdataManagePodDTO extends BaseDTO {
    private static final long serialVersionUID = 1L;

    private String  id;

    private String  itemName;

    private String  itemNo;

    private String  receiveNo;

    private String plotRatio;

    private String storageName;

    private Integer amount;

    private Integer boxCount;

    public TWmsItemdataManagePodDTO() {
    }

    public TWmsItemdataManagePodDTO(String id, String itemName, String itemNo, String receiveNo, String storageName, Integer amount, Integer boxCount) {
        this.id = id;
        this.itemName = itemName;
        this.itemNo = itemNo;
        this.receiveNo = receiveNo;
        this.storageName = storageName;
        this.amount = amount;
        this.boxCount = boxCount;
    }
}