spring jpa function(函数) JSON_EXTRACT 复杂查询，jpa josn查询

需求

查询数据库扩展字段内，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"));
        Expression<String> test2 = cb.function("JSON_ARRAY", String.class, cb.literal("3"));
        Expression<String> findInSetFun2 = cb.function("JSON_CONTAINS", String.class, findInSetFun, test2);
         //设置条件 只要返回值 !=0 则说明该参数存在于目标字符串中
        list.add(cb.notEqual(findInSetFun2, "0"));
        return cb.and(list.toArray(new Predicate[list.size()]));
    }
};

最后生成的sql 如下

selectl * FROM department where tenantId="1" AND JSON_CONTAINS(JSON_EXTRACT(extra,'$.type'),JSON_ARRAY("test"))<>0

完整案例

引入注解

https://search.maven.org/artifact/com.vladmihalcea/hibernate-types-52/2.16.2/jar

<dependency>
  <groupId>com.vladmihalcea</groupId>
  <artifactId>hibernate-types-52</artifactId>
  <version>2.16.2</version>
</dependency>

代码

DepartmentEntity 实体

@Data
@TypeDef(name = "json", typeClass = JsonStringType.class)
public class DepartmentEntity implements Serializable {
    private String id;
    /**
     * 租户id
     */
    private String tenantId;

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

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

    private LocalDateTime createTime;
    /**
    * 扩展字段
    **/
    @Type(type = "json")
    @Column( columnDefinition = "json" )
    private DepartmentExtraJson extra;
    
}

DepartmentExtraJson 实体

/**
* 扩展字段
**/
@Data
public class DepartmentExtraJson implements Serializable {
    /**
     * 类型
     */
    private List<String> type;

}

查询 代码

        //当前页,
        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"));
		        Expression<String> test2 = cb.function("JSON_ARRAY", String.class, cb.literal("3"));
		        Expression<String> findInSetFun2 = cb.function("JSON_CONTAINS", String.class, findInSetFun, test2);
		         //设置条件 只要返回值 !=0 则说明该参数存在于目标字符串中
		        list.add(cb.notEqual(findInSetFun2, "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());

方式二

        //当前页,
        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> findInSetFun2 = cb.function("JSON_EXTRACT", String.class, root.get("extra"), cb.literal("$.type"));
                list.add(cb.like(findInSetFun2, "%3%"));
                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());

最后生成的sql 如下

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