需求

查询数据库扩展字段内，json字符串中指定类型的数据

环境说明

数据库扩展字段: extra 此字段是是json格式数据
内容如下：

{"type":["1","2","3","4"]}

需要查询的内容：3

spring jpa. json JSON_EXTRACT 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()));
        Expression<String> findInSetFun = cb.function("JSON_EXTRACT", String.class, root.get("extra"), cb.literal("$.type"));
         //设置条件 只要返回值 type相同 则说明该参数存在于目标字符串中
        list.add(cb.equal(findInSetFun, type));
        return cb.and(list.toArray(new Predicate[list.size()]));
    }
};

最后生成的sql 如下

selectl * FROM department where tenantId="1" AND JSON_EXTRACT(extra, '$.type') = ?

完整案例

DepartmentEntity 实体

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

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

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

    private LocalDateTime createTime;
    /**
    * 扩展字段
    **/
    private String extra;
    
}

查询代码

        //当前页,
        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()));
                Expression<String> findInSetFun = cb.function("JSON_EXTRACT", String.class, root.get("extra"), cb.literal("$.type"));
                //设置条件 只要返回值 type相同 则说明该参数存在于目标字符串中
                list.add(cb.equal(findInSetFun, type));
                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());