pgsql操作json类型

目录

一、表结构

二、实体类

三、json处理器

四、配置文件

五、josn数据

1、插入

2、查找

---

一、表结构

CREATE TABLE "public"."pg_user" (
  "id" int8 NOT NULL GENERATED ALWAYS AS IDENTITY (
INCREMENT 1
MINVALUE  1
MAXVALUE 9223372036854775807
START 1
CACHE 1
),
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "age" int4,
  "sex" varchar(255) COLLATE "pg_catalog"."default",
  "create_time" timestamp(0),
  "create_name" varchar(255) COLLATE "pg_catalog"."default",
  "update_time" timestamp(0),
  "update_name" varchar(255) COLLATE "pg_catalog"."default",
  "is_del" varchar(255) COLLATE "pg_catalog"."default" DEFAULT '0'::character varying,
  "liu_xi" json,
  CONSTRAINT "pg_user_pkey" PRIMARY KEY ("id")
)
;

ALTER TABLE "public"."pg_user" 
  OWNER TO "postgres";

二、实体类

Entity

package com.chensir.system.domain.entity;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.annotation.*;
import com.chensir.domain.BaseEntity;
import com.chensir.handler.ObjectJsonHandler;
import lombok.Data;

/**
 * @author ChenSir
 * @Date 2023/9/11
 **/
@Data
@TableName(value = "public.pg_user")
public class PgUser extends BaseEntity {

    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    private String name;

    private Integer age;

    private String sex;

    private JSONObject liuXi;

    @TableLogic
    private String isDel;

}

Bo

package com.chensir.system.domain.bo;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.chensir.domain.BaseEntity;
import com.chensir.handler.ObjectJsonHandler;
import lombok.Data;

/**
 * @author ChenSir
 * @Date 2023/9/11
 **/
@Data
public class PgUserBo extends BaseEntity {

    private Long id;

    private String name;

    private Integer age;

    private String sex;

    private JSONObject liuXi;

}

Vo

package com.chensir.system.domain.vo;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.chensir.handler.ObjectJsonHandler;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import org.springframework.format.annotation.DateTimeFormat;

import java.time.LocalDateTime;

/**
 * @author ChenSir
 * @Date 2023/9/11
 **/
@Data
public class PgUserVo {

    private Long id;

    private String name;

    private Integer age;

    private String sex;

    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime createTime;

    private String createName;

    @JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    @DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime updateTime;

    private String updateName;

    private JSONObject liuXi;


}

三、json处理器

package com.chensir.handler;

import com.alibaba.fastjson.JSONObject;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.postgresql.util.PGobject;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * pgsql-json处理器
 * @author ChenSir
 * @Date 2023/9/11
 * postgres中json格式的字段，进行转换的自定义转换器，转换为实体类的JSONObject属性
 * MappedTypes注解中的类代表此转换器可以自动转换为的java对象
 */
@MappedTypes(JSONObject.class) // 会对使用JSONObject类型的字段进行json映射
public class ObjectJsonHandler extends BaseTypeHandler<JSONObject> {
    //引入PGSQL提供的工具类PGobject
    private static final PGobject jsonObject = new PGobject();


    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, JSONObject param, JdbcType jdbcType) throws SQLException {
        //转换的操作在这里!!!
        jsonObject.setType("json");
        jsonObject.setValue(param.toString());
        ps.setObject(i, jsonObject);
    }

    @Override
    public JSONObject getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String sqlJson = rs.getString(columnName);
        if (null != sqlJson) {
            return JSONObject.parseObject(sqlJson);
        }
        return null;
    }

    //根据列索引，获取可以为空的结果
    @Override
    public JSONObject getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String sqlJson = rs.getString(columnIndex);
        if (null != sqlJson) {
            return JSONObject.parseObject(sqlJson);
        }
        return null;
    }

    @Override
    public JSONObject getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String sqlJson = cs.getString(columnIndex);
        if (null != sqlJson) {
            return JSONObject.parseObject(sqlJson);
        }
        return null;
    }
}

        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.14</version>
        </dependency>

四、配置文件

使用jsonObject需要在Mybatis-plus添加json处理器包路径

  type-handlers-package: com.chensir.handler

五、josn数据

{
	"createtime": "2020-08-06T03:57:08.382Z",
	"dataid": "727d2094-f6b3-4eee-a39b-7544c04105fc",
	"schemaid": "ec9ebd79-d74d-4e0f-b026-9653006940f2",
    "items": {"product": "Toy Train","qty": 2}
}

1、插入

INSERT INTO public.pg_user ( liu_xi ) VALUES ( {"createtime":"2020-08-06T03:57:08.382Z","dataid":"727d2094-f6b3-4eee-a39b-7544c04105fc","schemaid":"ec9ebd79-d74d-4e0f-b026-9653006940f2","items": {"product": "Toy Train","qty": 2}} )

2、查找

1、使用select语句查找json数据,与其他基本类型类似

select * from public.pg_user

2、查询json中所有dataid作为键

select 
    liu_xi -> 'dataid' as dataid 
from 
    public.pg_user

3、查询json中所有dataid作为文本

select
    liu_xi ->> 'dataid' as dataid
from
    public.pg_user

4、->操作返回json对象，可以链式调用 ->>返回特定节点。

先使用liu_xi-> 'items' 返回对象。然后使用liu_xi -> 'items' ->> 'product' 返回所有产品文本值。

select
  liu_xi -> 'items' ->> 'product' as product
from
    public.pg_user
order by
    product

where查询略

可参考：

PostgreSQL JSON数据类型_postgis geojson字段类型_梦想画家的博客-CSDN博客