PostgreSQL特殊类型字段在Mybatis中映射

心路历程

目前单位对接的是生产类的系统，导致在业务上非常复杂。于是总有库存数量出错的时候，但是迟迟不找到原因，消耗了大量人力。于是想到添加额外的 log 记录，引出了一个未接触过的问题，如何在 postgreSQL 中添加 json 类型的数据，因为 java 的数据类型并不能映射。

postgreSQL 中 json 和 jsonb 的区别

postgresql 支持两种 json 数据类型：json 和 jsonb，而两者唯一的区别在于效率，json 是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。而 jsonb 是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json 类型存储快，使用慢，jsonb 类型存储稍慢，使用较快。

找了很多文章，最后找到了解决办法。

参考文章：PgSQL数组及json类型在Mybatis项目中的类型转换_ymzhao-CSDN博客_mybatis pgsql 数组

postgresql----JSON和JSONB类型_思考、总结、专注-CSDN博客_jsonb

我需要存储的 json 结果，如图是比较特殊的，需要 Map<String, List<dto>> 的形式。

首先，尝试了在字段定义上添加 Mybatis Plus 提供的原生 JacksonTypeHandler 类，但是并没有效果。

/**
 * 操作参数
 */
@TableField(typeHandler = JacksonTypeHandler.class)
private Map<String, List<PdProduceMaterialDetailLogDetailDto>> params;

后来终于在各种尝试后，找到了解决办法！！！

解决办法

JSON

1、需要针对自己的类型创建定制一个 Handler 类

        主要是用于读写特殊类型字段时的处理。

• 继承 BaseTypeHandler<> ，在尖括号中声明自己定义的字段类型；
• @MappedTypes 注解也需要对应到自己定义的dto；
• @MappedJdbcTypes 注解中 JdbcType 需要设为 OTHER；
• 声明的方法只需要按照自动生成的参数名修改即可。

package com.xxx.xxx.xxx.dto.handler;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.TypeReference;
import com.xxx.xxx.xxx.dto.PdProduceMaterialDetailLogDetailDto;
import com.xxx.xxx.xxx.dto.PdProduceMaterialDetailLogDto;
import org.apache.ibatis.type.BaseTypeHandler;
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;
import java.util.List;
import java.util.Map;

/**
 * @author admin
 */
@MappedTypes(PdProduceMaterialDetailLogDto.class)
@MappedJdbcTypes(JdbcType.OTHER)
public class JSONTypeHandler extends BaseTypeHandler<Map<String, List<PdProduceMaterialDetailLogDetailDto>>> {

    private static final PGobject jsonObject = new PGobject();

    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Map<String, List<PdProduceMaterialDetailLogDetailDto>> stringListMap, JdbcType jdbcType) throws SQLException {
        jsonObject.setType("json");
        jsonObject.setValue(JSON.toJSONString(stringListMap));
        preparedStatement.setObject(i, jsonObject);
    }

    @Override
    public Map<String, List<PdProduceMaterialDetailLogDetailDto>> getNullableResult(ResultSet resultSet, String s) throws SQLException {
        Map<String, List<PdProduceMaterialDetailLogDetailDto>> object = JSON.parseObject(resultSet.getString(s), new TypeReference<Map<String, List<PdProduceMaterialDetailLogDetailDto>>>() {});
        return object;
    }

    @Override
    public Map<String, List<PdProduceMaterialDetailLogDetailDto>> getNullableResult(ResultSet resultSet, int i) throws SQLException {
        Map<String, List<PdProduceMaterialDetailLogDetailDto>> object = JSON.parseObject(resultSet.getString(i), new TypeReference<Map<String, List<PdProduceMaterialDetailLogDetailDto>>>() {});
        return object;
    }

    @Override
    public Map<String, List<PdProduceMaterialDetailLogDetailDto>> getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        Map<String, List<PdProduceMaterialDetailLogDetailDto>> object = JSON.parseObject(callableStatement.getString(i), new TypeReference<Map<String, List<PdProduceMaterialDetailLogDetailDto>>>() {});
        return object;
    }
}

 2、在字段声明时添加注解 @TableField(typeHandler = 你刚才创建的 Handler 类的路径.class)

/**
 * 操作参数
 */
@TableField(typeHandler = com.xxx.xxx.xxx.dto.handler.JSONTypeHandler.class)
private Map<String, List<PdProduceMaterialDetailLogDetailDto>> params;

3、在 xml 文件中，使用该字段时需要添加 typeHandler = 你创建的 Handler 类的路径

        之前忽略了这一点，导致一直失败。

<insert id="insertPdProduceMaterialDetailLog">
    INSERT INTO pd_produce_material_detail_log(id, params)
    VALUES (#{pdProduceMaterialDetailLogDto.id},
            #{pdProduceMaterialDetailLogDto.params, 
            typeHandler=com.xxx.xxx.xxx.dto.handler.JSONTypeHandler}
    )
</insert>

Array

1、创建 Handler 类

        和上面的 json 差不多，这里指出不同之处。

• @MappedJdbcTypes(JdbcType.ARRAY)
• extends BaseTypeHandler<Object[]>
• 定义了多种类型的常量，在处理时添加了数组类型的判断

package com.xxxxx.xxxxx.config;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.TypeException;

import java.sql.*;

@MappedJdbcTypes(JdbcType.ARRAY)
public class ArrayTypeHandler extends BaseTypeHandler<Object[]> {
    private static final String TYPE_NAME_VARCHAR = "varchar";
    private static final String TYPE_NAME_INTEGER = "integer";
    private static final String TYPE_NAME_BOOLEAN = "boolean";
    private static final String TYPE_NAME_NUMERIC = "numeric";

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Object[] parameter, JdbcType jdbcType) throws SQLException {
        String typeName = null;
        if (parameter instanceof Integer[]) {
            typeName = TYPE_NAME_INTEGER;
        } else if (parameter instanceof String[]) {
            typeName = TYPE_NAME_VARCHAR;
        } else if (parameter instanceof Boolean[]) {
            typeName = TYPE_NAME_BOOLEAN;
        } else if (parameter instanceof Double[]) {
            typeName = TYPE_NAME_NUMERIC;
        }

        if (typeName == null) {
            throw new TypeException("ArrayType2Handler parameter typeName error, your type is " + parameter.getClass().getName());
        }

        // 这3行是关键的代码，创建Array，然后ps.setArray(i, array)就可以了
        Connection conn = ps.getConnection();
        Array array = conn.createArrayOf(typeName, parameter);
        ps.setArray(i, array);

    }

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

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

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

    private Object[] getArray(Array array) {
        if (array == null) {
            return null;
        }
        try {
            return (Object[]) array.getArray();
        } catch (Exception e) {
        }
        return null;
    }
}

2、在定义字段的类中给字段添加注解

@TableField(typeHandler = com.xxx.xxx.xxx.ArrayTypeHandler.class)
private String[] attributes;

3、在 mapper 或 xml 文件自定义的 sql 中包含该字段时，需要指明该项读写时所需的类型转换

<update id="XXXXXXXXXXX">
   UPDATE pd.product SET 
        update_time=now(), 
        attributes = #{attrs, typeHandler=com.xxxxx.xxxxx.config.ArrayTypeHandler}
   WHERE id=#{id}
</update>

额外的办法

一开始想到的解决办法是将字段设置成 text 类型，然后通过 JSONObject 和 JSONArray 在代码中将数据拼接成想要的 json 格式，存储时通过 JSONObject.toJSONString() 的方式把 json 类型变成字符串类型存储。可能和存储成 json 类型相比不是那么合适，但是也达到了解决问题的目的。