mybatis plush : 解决pgSql数据库 jsonb存取的问题

以下是必须加的

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

 

package com.hlta.map.utils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.baomidou.mybatisplus.extension.handlers.AbstractJsonTypeHandler;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
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;
/**
 * @Author WangChangDian
 * @Description //TODO pgSql jsonb 数据处理器
 * @Date 16:17 2021/5/24
 **/
@MappedTypes({Object.class})
@MappedJdbcTypes({JdbcType.VARCHAR})
public class JsonbTypeHandler extends AbstractJsonTypeHandler<Object> {
    private static final PGobject jsonObject = new PGobject();
    private final Class<?> type;

    public JsonbTypeHandler(Class<?> type) {
        this.type = type;
    }

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
        if (ps != null) {
            jsonObject.setType("jsonb");
            jsonObject.setValue(JSON.toJSONString(parameter));
            ps.setObject(i, jsonObject);
        }
    }

    @Override
    public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
        Object v = rs.getObject(columnName);
        return toFill(v);
    }

    @Override
    public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        Object v = rs.getObject(columnIndex);
        return toFill(v);
    }

    @Override
    public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        Object v = cs.getObject(columnIndex);
        return toFill(v);
    }

    @Override
    protected Object parse(String json) {
        return JSON.parseObject(json, this.type);
    }

    //必须将v转成 PGObject处理
    private Object toFill(Object v) {
        if (v != null && v instanceof PGobject) {
            PGobject p = (PGobject) v;
            String pv = p.getValue();
            if (StringUtils.isNotEmpty(pv) && ("jsonb".equals(p.getType()) || "json".equals(p.getType()))) {
                return parse(p.getValue());
            }
        }
        return v;
    }

    @Override
    protected String toJson(Object obj) {
        return JSON.toJSONString(obj, SerializerFeature.WriteMapNullValue, SerializerFeature.WriteNullListAsEmpty, SerializerFeature.WriteNullStringAsEmpty);
    }
}

实体类 

package com.hlta.map.entity;

import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.databind.ser.std.ToStringSerializer;
import com.hlta.map.utils.JsonbTypeHandler;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.io.Serializable;
import java.time.LocalDateTime;

/**
 * <p>
 *
 * </p>
 *
 * @author ${author}
 * @since 2021-04-20
 */
@Data
@EqualsAndHashCode(callSuper = false)
@TableName(value = "dwd_school", autoResultMap = true)
public class DwdSchool implements Serializable {

    private static final long serialVersionUID = 1L;

    @JsonSerialize(using = ToStringSerializer.class)
    private Long id;


    /**
     * 学校范围信息
     */

    @TableField(typeHandler = JsonbTypeHandler.class)
    private Object mapData;




}

 

数据类型

 

实体类字段的@TableField 配置等，同如下的

***Mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="net.loyin.ucenter.mapper.UserMapper">
    <resultMap id="userResultMap" type="net.loyin.ucenter.entity.User">
       <id property="id" column="id"/>
        <result column="profile" property="profile" jdbcType="OTHER" typeHandler="net.loyin.common.db.JsonbTypeHandler"/>
    </resultMap>
    <select id="selectUser" parameterType="java.lang.Long" resultMap="userResultMap">
        select id,profile from solar_user where id = #{id}
    </select>
</mapper>

 

 

 还有一个处理类可以用

package com.hlta.map.utils;

import com.alibaba.fastjson.JSON;
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 JsonbTypeHandlerTwo extends BaseTypeHandler<Object> {
    private static final PGobject jsonObject = new PGobject();

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

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

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

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