springboot+mybatis+pgsql存储jsonb格式数据

maven

将runtime注释掉，不然会报错找不到我们要用的PGObject类

        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
<!--            <scope>runtime</scope>-->
        </dependency>

sql

DROP TABLE IF EXISTS sensor CASCADE;

CREATE TABLE sensor (
	id serial PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    type VARCHAR NOT NULL,
    experiment_id INT NOT NULL references experiment(id),
    position JSONB DEFAULT NULL,
    description TEXT DEFAULT NULL,
    addition JSONB DEFAULT NULL,
    data_unit VARCHAR(6) DEFAULT NULL,
    state BOOLEAN DEFAULT TRUE,
    created_date TIMESTAMP DEFAULT NOW()
);
CREATE INDEX index_experiment_id ON sensor(experiment_id);

bean

pg数据库中字段为json/jsonb，对应java实体类的类型是Object，以上数据库对应的bean

package com.dbhd.gvs.bean;

import com.dbhd.gvs.enumeration.EquipmentType;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import org.springframework.stereotype.Repository;

import java.io.Serializable;
import java.util.Date;

@Data
@Repository
public class Sensor implements Serializable {
    private static final long serialVersionUID=1L;
    private Integer id;
    private String name;
    private EquipmentType type;
    private Integer experimentId;
    private Object position;
    private String description;
    private Object addition;
    private String dataUnit;
    private Boolean state;
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8") // 返回的时间格式
    private Date createdDate;
}

JSONTypeHandlerPg

在mybatis的xml中,常规无法直接进行映射，需要自己写一个TypeHandler，自定义一个JSONTypeHandlerPg类

具体代码：

package com.dbhd.gvs.common;

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;

@MappedTypes({Object.class})
public class JsonTypeHandler extends BaseTypeHandler<Object> {

    private static final PGobject jsonObject = new PGobject();

    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Object o, JdbcType jdbcType) throws SQLException {
        jsonObject.setType("jsonb");
        jsonObject.setValue(JSON.toJSONString(o));
        preparedStatement.setObject(i, jsonObject);
    }

    @Override
    public Object getNullableResult(ResultSet resultSet, String s) throws SQLException {
        return resultSet.getString(s);
    }

    @Override
    public Object getNullableResult(ResultSet resultSet, int i) throws SQLException {
        return resultSet.getString(i);
    }

    @Override
    public Object getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        return callableStatement.getString(i);
    }
}

mapper

使用typeHandler=com.dbhd.gvs.common.JsonTypeHandler，这个typeHandler是上面JSONTypeHandlerPg处理数据类的全限定名。

    <resultMap id="resultMap_sensor" type="com.dbhd.gvs.bean.Sensor">
        <!-- column：主键在数据库中的列名 property：主键在pojo中的属性名 -->
        <id property="id" column="id"/>
        <result column="username" property="username"/>
        <result column="type" property="type"/>
        <result column="experiment_id" property="experimentId"/>
        <result column="position" property="position" typeHandler="com.dbhd.gvs.common.JsonTypeHandler"
                javaType="Object"/>
        <result column="description" property="description"/>
        <result column="addition" property="addition" typeHandler="com.dbhd.gvs.common.JsonTypeHandler"
                javaType="Object"/>
        <result column="data_unit" property="dataUnit"/>
        <result column="state" property="state"/>
        <result column="created_date" property="createdDate"/>
    </resultMap>

    <insert id="save" parameterType="com.dbhd.gvs.bean.Sensor" useGeneratedKeys="true" keyProperty="id">
    insert into sensor(name, type, experiment_id, position, description, addition, data_unit)
    values (
        #{name}, #{type}, #{experimentId},
        #{position, typeHandler=com.dbhd.gvs.common.JsonTypeHandler},
        #{description},
        #{addition, typeHandler=com.dbhd.gvs.common.JsonTypeHandler},
        #{dataUnit}
    )
    </insert>