java---jsonb

悦悦妍妍
646 阅读2分钟

pgsql中使用jsonb类型，参考链接

1.数据库的连接池设置

?stringtype=unspecified

2.表实体的设置

@TableName(value = "xxx", autoResultMap = true)

@TableField(typeHandler = JacksonTypeHandler.class)
private List<String> roleIds;

 // 自定义类型处理器，便于入库和出库时自动转换
@TableField(typeHandler = UserDTOTypeHandler.class)
private List<UserDTO> users;
  • 2.1 表定义
create table user_info
(
   role_ids jsonb default '[]'::jsonb   not null
);
  • 2.2 自定义类型处理器
package com.my.demo.common.handler;

import com.baomidou.mybatisplus.extension.handlers.AbstractJsonTypeHandler;
import com.my.demo.model.dto.UserDTO;
import com.my.demo.utils.JacksonUtil;

import java.util.List;

public class AttachmentListTypeHandler extends AbstractJsonTypeHandler<List<UserDTO>> {

    /**
     * 出库时解析成json对象
     * @param json 表中存储的json字符串
     * @return 业务代码里面的对象
     */
    @Override
    protected List<UserDTO> parse(String json) {
        return JacksonUtil.decodeList(json, UserDTO.class);
    }

    /**
     * 入库时转成json字符串
     * @param obj 业务代码里面的对象
     * @return 表中即将要存入的jsno字符串
     */
    @Override
    protected String toJson(List<UserDTO> obj) {
        return JacksonUtil.encode(obj);
    }
}

3.插入或更新时sql设置

// 需要转成对应的json字符串，方可插入或更新
JSON.toJSONString(userDTO.getRoleIds())

4.查询时sql

//jsonb_exists_any  表中含有查找中的任意1个就会返回
//jsonb_exists_all  表中含有查找中的全部就会返回

//如下，使用 jsonb_exists_all 函数，role_ids列含有['88','99']的会返回，['77','88','99']也会在结果集中，而['88']不会在结果集中。
select * from "user_info" where jsonb_exists_all(role_ids, array ['88','99']) ;

//结合mybatis-plus
String idsString = roleIds.stream().map(item -> String.format("'%s'", item)).collect(Collectors.joining(",", "array[", "]"));
List<User> list = lambdaQuery().apply("jsonb_exists_all(role_ids, " + idsString + ")").list();

5.应用场景

//1. 当存在一对多时，经常把关联表的重要字段以jsonb类型，存入主表中。方便查找。 譬如1个用户可以拥有多个角色，那为了方便查找可以把该用户所有的角色id，以jsonb类型存入用户表中。
//2. 这种上下级的字段也可以用jsonb类型存入。譬如1个机构它的上级机构有多个，表中则可以存入该机构有的上级机构codes。

6.常见举例：想看一个机构本身以及该机构所属下级的数据 或 这个机构的上级链数据，如机构树

image.png

需求： 2级机构02创建的数据，自身能查询到，顶部机构00能看到，还有下属的3级机构 02-1 02-2 02-3都能访问到

  • 6.1 相关表设计

create table user_info
(
    upperDeptCodes jsonb   default '[]'::jsonb   not null
);
  • 6.2查询数据
// 该用户机构的上级机构链
List<String> upperDeptCodes = user.getUpperDeptCodes();
QueryWrapper<Course> queryWrapper = new QueryWrapper<>();
queryWrapper.lamda().and(wrapper -> wrapper
// 这个机构的上级链创建的数据
.in(Course::getDeptCode, upperDeptCodes)
.or()
// 一个机构本身以及该机构所属下级的创建的数据
.apply("jsonb_exists_any(upper_dept_codes, array[{0}]", deptCode)
.list()

mysql支持json

select * from user_info where JSON_SEARCH('upper_dept_codes', 'all', '08')

oracle等不支持json | jsonb的数据库

1. 建表和实体

create table user_info
(
    upper_dept_codes varchar2(256) default '[]' not null
);


@TableName(value = "xxx", autoResultMap = true)

@TableField(typeHandler = JacksonTypeHandler.class)
private List<String> upperDeptCodes;

2.查询

// xml
where upper_dept_codes like '%"${deptCode}"%' ande id = #{id}
// plus
wrapper.lambda().like(UserDo::getUpperDeptCodes, String.format("\"%s\"", deptCode))
avatar
文章
阅读
粉丝