心路历程
目前单位对接的是生产类的系统,导致在业务上非常复杂。于是总有库存数量出错的时候,但是迟迟不找到原因,消耗了大量人力。于是想到添加额外的 log 记录,引出了一个未接触过的问题,如何在 postgreSQL 中添加 json 类型的数据,因为 java 的数据类型并不能映射。
postgreSQL 中 json 和 jsonb 的区别
postgresql 支持两种 json 数据类型:json 和 jsonb,而两者唯一的区别在于效率,json 是对输入的完整拷贝,使用时再去解析,所以它会保留输入的空格,重复键以及顺序等。而 jsonb 是解析输入后保存的二进制,它在解析时会删除不必要的空格和重复的键,顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别:json 类型存储快,使用慢,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 类型相比不是那么合适,但是也达到了解决问题的目的。