SpringBoot+MyBatis操作Postgresql中Json数据类型

SpringBoot+MyBatis操作Postgresql中Json数据类型
MyBatis中并没有原生json的jdbcType支持,只能通过自定义TypeHandler来实现转换

假设这是我要存入数据库的json串:

{"createtime":"2020-08-06T03:57:08.382Z","dataid":"727d2094-f6b3-4eee-a39b-7544c04105fc","schemaid":"ec9ebd79-d74d-4e0f-b026-9653006940f2"}
1
格式化一下:

{
"createtime": "2020-08-06T03:57:08.382Z",
"dataid": "727d2094-f6b3-4eee-a39b-7544c04105fc",
"schemaid": "ec9ebd79-d74d-4e0f-b026-9653006940f2"
}

我希望的效果:

首先新建实体类做映射,data字段设置为JSONObject 类型

private Object dataid;
private Object schemaid;
private JSONObject data;
private Date createtime;
//省略getter setter方法

第二部新建ObjectJsonHandler类做自定义TypeHandler

/**
* @author Oct.Ca
* @version 1.0
* @date 2020/8/6 11:34
* postgres中json格式的字段，进行转换的自定义转换器，转换为实体类的JSONObject属性
* MappedTypes注解中的类代表此转换器可以自动转换为的java对象
*/
@MappedTypes(JSONObject.class)
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;
}

如果这里报错的话

//引入PGSQL提供的工具类PGobject
private static final PGobject jsonObject = new PGobject();

需要在pom里添加

<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.14</version>
</dependency>

接下来在application.yml或者properties中设置mybatis handler 包的扫描地址

mybatis:
type-handlers-package: com.alibaba.common.handler
1
2
最后到mapper.xml里去设置自定义映射即可
resultmap里这么设置:

<resultMap id="BaseResultMap" type="com.shenlan.plains.domain.TbbattrdataDO">
<id column="dataid" jdbcType="OTHER" property="dataid"/>
<result column="schemaid" jdbcType="OTHER" property="schemaid"/>
<result column="data" property="data" typeHandler="com.alibaba.common.handler.ObjectJsonHandler"/>
<result column="createtime" jdbcType="TIMESTAMP" property="createtime"/>
</resultMap>

insert语句:

<insert id="insert" parameterType="com.shenlan.plains.domain.TbbattrdataDO">
insert into "tbbattrdata" (dataid,schemaid, "data", createtime)
values (#{dataid}::uuid,#{schemaid}::uuid, #{data,typeHandler=com.alibaba.common.handler.ObjectJsonHandler}, #{createtime,jdbcType=TIMESTAMP})
</insert>

查询语句:

<select id="selectByPrimaryKey" parameterType="java.lang.Object" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from "tbbattrdata"
where dataid = #{dataid}::uuid
</select>

新增结果:

查询结果:

————————————————
版权声明：本文为CSDN博主「Oct.ca」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/weixin_43616450/article/details/107844987