Specification 自定义查询

在使用JPA遇到较复杂的或需要更加灵活的查询场景时，可以使用Specification拼接查询。

Demo

实体类：

@Data
public class Task{
    
    private Integer id;

    private String title;
    
    private Integer type;
    
    private String content;
    
    private Date createTime;
    
    private Integer version;
    
}

查询条件（SearchForm）：

@Data
public class SearchForm{
    
    private Integer id;
    
    // title 和 content 的查询关键字
    private String keyword;
    
    private List<Integer> types;
    
    private String orderByCreate = "DESC";
    
    private Integer version;
}

拼接复杂查询的方法：

@Autowired
private TaskRepository taskRepository;

public List<Task> findAll(SearchForm form) {
    List<Task> taskList = taskRepository.findAll(this.getSpecificationForTask(form));
    return taskList;
}

private Specification<Task> getSpecificationForTask(SearchForm form) {
    // dynamite sql
    return (root, query, builder) -> {
        // for params
        List<Predicate> predicates = new ArrayList<>();
        // 精准匹配
        if (form.getId() != null) {
            predicates.add(builder.equal(root.get("id"), form.getId()));
        }
        // 模糊查询 + or条件
        if (!StringUtils.isEmpty(form.getKeyword())) {
            predicates.add(builder.or(like(root.get("title"), "%" + form.getKeyword() + "%"), like(root.get("content"), "%" + form.getKeyword() + "%")));
        }
        // 大于等于
        if (form.getVersion() != null) {
            predicates.add(builder.greaterThanOrEqualTo(root.get("version"), form.getVersion()));
        }
        // in范围查询
        if (!CollectionUtils.isEmpty(form.getTypes())) {
            CriteriaBuilder.In<Object> taskType = builder.in(root.get("type"));
            form.getTypes().forEach(vendNoIn::value);
            predicates.add(taskType);
        }
        // 排序
        List<Order> orderList = new ArrayList<>();
        if(form.getOrderByCreate().equals("DESC")) {
            orderList.add(builder.desc(root.get("createTime")))
        } else {
            orderList.add(builder.asc(root.get("createTime")))
        }
        // 支持多重排序
        orderList.add(builder.asc(root.get("id")))
        return  query.orderBy(orderList).where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
    };
}

repository：

public interface TaskRepository extends JpaSpecificationExecutor<Task> {

}

tips

Root<T> root：获取实体类的属性
CriteriaQuery<?> query：通过query实现查询
CriteriaBuilder builder：拼接查询条件

JPA Specification 自定义查询

Specification 自定义查询

Demo

tips