pgsql操作json类型

系统管理员 2023-10-15 21:17 58阅读 0赞

**目录**

一、表结构

二、实体类

三、json处理器

四、配置文件

五、josn数据

1、插入

2、查找

--------------------

## 一、表结构 ##

![c19bf1d6abbf4f5b8bebdeb6f21d444f.png][]

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}} )

![290fc4b356254ccca9225fce39a06fd9.png][]

### 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博客][PostgreSQL JSON_postgis geojson_-CSDN]

[c19bf1d6abbf4f5b8bebdeb6f21d444f.png]: https://img-blog.csdnimg.cn/c19bf1d6abbf4f5b8bebdeb6f21d444f.png
[290fc4b356254ccca9225fce39a06fd9.png]: https://img-blog.csdnimg.cn/290fc4b356254ccca9225fce39a06fd9.png
[PostgreSQL JSON_postgis geojson_-CSDN]: https://blog.csdn.net/neweastsun/article/details/93345799