需求

查询当前部门及子部门数据

环境说明

数据库 部门字段: department_id
字段内容：1,2,3,4,5,6,7,8,9,10
需要查询的内容：7,8,9,10

spring jpa function 代码

Specification<DepartmentEntity> specification = new Specification<DepartmentEntity>() {
    @Override
    public Predicate toPredicate(Root<DepartmentEntity> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder cb) {
        List<Predicate> list = Lists.newArrayList();
        list.add(cb.equal(root.get("tenantId"), user.getTenantId()));
        List<Predicate> or = Lists.newArrayList();
        for (String id : departmentList.get()) {
            Expression<String> findInSetFun = cb.function("FIND_IN_SET", String.class, cb.literal(id), root.get("departmentId"));
            //设置条件 只要返回值 >0 则说明该参数存在于目标字符串中
            or.add(cb.notEqual(findInSetFun, "0"));
        }
        return cb.and(list.toArray(new Predicate[list.size()]));
    }
};

最后生成的sql 如下

selectl * FROM department where tenantId="1" AND (
	FIND_IN_SET(7,department_id) or
	FIND_IN_SET(8,department_id) or
	FIND_IN_SET(9,department_id) or
	FIND_IN_SET(10,department_id)
)

完整案例

DepartmentEntity 实体

@Data
public class DepartmentEntity implements Serializable {
    private String id;
    /**
     * 租户id
     */
    private String tenantId;

    /**
     * 名称;
     */
    private String name;

    /**
     * 上级id;
     */
    private String parentId;

    private LocalDateTime createTime;
    
}

查询 代码

        //当前页,
        int page = ct.getPage();
        //如果是-1 ，则从 0 开始
        page = Math.max(page, 0);
        //页码 从 0 开始
        if (page > 0) {
            page = page - 1;
        }
        // 每页条数
        int pageSize = 20;
        log.info("page={}", page);
        log.info("pageSize={}", pageSize);
        //排序 createTime 倒序
        Sort sort = Sort.by(new Sort.Order(Sort.Direction.DESC, "createTime"));
        //Sort sort = Sort.by(new Sort.Order(Sort.Direction.DESC, "createTime"),new Sort.Order(Sort.Direction.DESC, "uid"));
        //分页
        Pageable pageable = PageRequest.of(page, pageSize, sort);
        log.info("pageable={}", pageable);
        //
        Specification<DepartmentEntity> specification = new Specification<DepartmentEntity>() {
            @Override
            public Predicate toPredicate(Root<DepartmentEntity> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder cb) {
                List<Predicate> list = Lists.newArrayList();
                list.add(cb.equal(root.get("tenantId"), user.getTenantId()));
                List<Predicate> or = Lists.newArrayList();
                for (String id : departmentList.get()) {
                    Expression<String> findInSetFun = cb.function("FIND_IN_SET", String.class, cb.literal(id), root.get("departmentId"));
                    //设置条件 只要返回值 >0 则说明该参数存在于目标字符串中
                    or.add(cb.notEqual(findInSetFun, "0"));
                }
                return cb.and(list.toArray(new Predicate[list.size()]));
            }
        };
        Page<DepartmentEntity> all = departmentDao.findAll(specification, pageable);
        log.info("all.getTotalElements={}", all.getTotalElements());
        log.info("all.getTotalPages={}", all.getTotalPages());
        log.info("all.getSize={}", all.getSize());