SpringDataJPA中使用Specification进行表连接多条件分页动态查询

一直使用springboot开发，很久没用过jpa了，最近公司的项目用jpa，这就用到了Specification进行多表连接多条件动态查询，想了想还是简单的总结一下。废话不多说，具体实现如下：

1.定义接口，继承JpaRepository<X,X>,JpaSpecificationExecutor<Project>

public interface ProjectsRepository  extends JpaRepository<Project, Long>, JpaSpecificationExecutor<Project> {
    //...
    @Override
    default List<Project> findAll(Specification<Project> spec) {
        // TODO Auto-generated method stub
        return null;
    }
}

2.service层(UserDetails userDetails这个参数是我根据功能自定义的，可以去掉)

public interface ProjectsService {
    public Page<Project> queryByDynamicSQLPage(Project projectSearch, Integer page, Integer size, UserDetails userDetails);
}

3.Impl实现类，多表关联了用 Join<Organization, Project> join = root.join(“organization”, JoinType.LEFT);

@Service
public class ProjectsServiceImpl implements ProjectsService {
    @Autowired
    ProjectsRepository repository;

      @Override
    public Page<Project> queryByDynamicSQLPage(Project projectSearch,Integer page,Integer size,UserDetails userDetails){//动态查询条件的排序分页
        Sort sort = new Sort(Sort.Direction.ASC, "id");
        Pageable pageable = new PageRequest(page, size, sort);
        return repository.findAll(this.getWhereClause(projectSearch,userDetails),pageable);
    }

   /**查询条件动态组装
     * 动态生成where语句
     * 匿名内部类形式
     * @param projectSearch
     * @return
     */
    private Specification<Project> getWhereClause(final Project projectSearch,UserDetails userDetails){
        return new Specification<Project>() {
            @Override
            public Predicate toPredicate(Root<Project> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                Predicate predicate = cb.conjunction();//动态SQL表达式
                List<Expression<Boolean>> exList = predicate.getExpressions();//动态SQL表达式集合
                if( projectSearch.getCheckprojectstatus() != null && !"".equals(projectSearch.getCheckprojectstatus()) ){
                    exList.add(cb.equal(root.get("checkprojectstatus"), projectSearch.getCheckprojectstatus()));
                }
                if( projectSearch.getProjectArea() != null && !"".equals(projectSearch.getProjectArea()) ){
                    exList.add(cb.equal(root.get("projectArea"), projectSearch.getProjectArea()));
                }
                if( projectSearch.getProjectName() != null && !"".equals(projectSearch.getProjectName()) ){
                    exList.add(cb.like(root.<String>get("projectName"), "%" + projectSearch.getProjectName() + "%"));
                }
                if( projectSearch.getPrincipal() != null && !"".equals(projectSearch.getPrincipal()) ){
                    exList.add(cb.like(root.<String>get("principal"), "%" + projectSearch.getPrincipal() + "%"));
                }
                if( projectSearch.getSubjectOriented() != null && !"".equals(projectSearch.getSubjectOriented()) ){
                    exList.add(cb.equal(root.get("subjectOriented"), projectSearch.getSubjectOriented()));
                }
                if( projectSearch.getTeachingSection() != null && !"".equals(projectSearch.getTeachingSection()) ){
                    exList.add(cb.equal(root.get("teachingSection"), projectSearch.getTeachingSection()));
                }
                if( projectSearch.getJobCategory() != null && !"".equals(projectSearch.getJobCategory()) ) {
                    exList.add(cb.equal(root.get("jobCategory"), projectSearch.getJobCategory()));
                }
                    Join<Organization, Project> join = root.join("organization", JoinType.LEFT);
                    exList.add(cb.equal(join.get("login"), userDetails.getUsername()));
                return predicate;
            }
        };
    }
}

4.Controller控制层

@GetMapping("/XXXXXX")
    @Timed
    @PreAuthorize("hasRole(\""+ AuthoritiesConstants.XXXXX +"\")")
    public Map<String, Object> queryProject(@RequestBody Project search, Integer page, Integer rows,@AuthenticationPrincipal UserDetails userDetails) {
        int pageBegin = page - 1;
        Page<Project> list = projectsService.queryByDynamicSQLPage(search, pageBegin, rows,userDetails);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("rows", list.getContent());//将所有数据返回list
        map.put("total", list.getTotalElements());//返回元素总数
        return map;
    }