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;@TableLogicprivate 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提供的工具类PGobjectprivate static final PGobject jsonObject = new PGobject();@Overridepublic void setNonNullParameter(PreparedStatement ps, int i, JSONObject param, JdbcType jdbcType) throws SQLException {//转换的操作在这里!!!jsonObject.setType("json");jsonObject.setValue(param.toString());ps.setObject(i, jsonObject);}@Overridepublic JSONObject getNullableResult(ResultSet rs, String columnName) throws SQLException {String sqlJson = rs.getString(columnName);if (null != sqlJson) {return JSONObject.parseObject(sqlJson);}return null;}//根据列索引，获取可以为空的结果@Overridepublic JSONObject getNullableResult(ResultSet rs, int columnIndex) throws SQLException {String sqlJson = rs.getString(columnIndex);if (null != sqlJson) {return JSONObject.parseObject(sqlJson);}return null;}@Overridepublic 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作为文本

selectliu_xi ->> 'dataid' as dataid
frompublic.pg_user

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

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

selectliu_xi -> 'items' ->> 'product' as product
frompublic.pg_user
order byproduct

where查询略

可参考：

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