mybatis中的自定义TypeHandler处理PostgreSQL中的Jsonb类型
1.自定义typeHandler类型
mybatis默认是没有实现jsonb类型字段对应的TypeHandler，所以一般我们需要自定义mybatis的TypeHandler的一个简单实现：
package com.jiarui.operlog.util.typehandler;
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 JsonbTypeHandler extends BaseTypeHandler<Object> {
private static final PGobject jsonObject = new PGobject();
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType)
throws SQLException {
if (ps != null) {
jsonObject.setType("jsonb");
jsonObject.setValue(parameter.toString());
ps.setObject(i, jsonObject);
}
}
@Override
public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
return rs.getObject(columnName);
}
@Override
public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
return rs.getObject(columnIndex);
}
@Override
public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
return cs.getObject(columnIndex);
}
}
json类型字段对应的TypeHandler
package com.jiarui.operlog.util.typehandler;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedTypes;
import org.postgresql.util.PGobject;@MappedTypes({Object.class})
public class JsonTypeHandler extends BaseTypeHandler{@Override
public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
if (ps != null) {
PGobject ext = new PGobject();
ext.setType("json");
ext.setValue(parameter.toString());
ps.setObject(i, ext);
}
}@Override
public Object getNullableResult(ResultSet resultSet, String s) throws SQLException {
return resultSet.getObject(s);
}@Override
public Object getNullableResult(ResultSet resultSet, int i) throws SQLException {
return resultSet.getObject(i);
}@Override
public Object getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
return callableStatement.getObject(i);
}
}
2.将实体类里的对应的jsonb的字段设置为Object
/** 其他数据 */
private Object data;
3.修改相关set方法
public void setData(Object data) {
try {
//查询转换
this.data = JSON.parse(data.toString());
} catch (Exception e) {
//新增转换
this.data = JSONArray.toJSON(data);
}
}
4.修改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="com.jiarui.operlog.mapper.OperLogMapper">
<resultMap type="OperLog" id="OperLogResult">
<id property="Id" column="id" />
<result property="userId" column="user_id" />
<result property="operName" column="oper_name" />
<result property="operDescription" column="oper_description" />
<result property="createTime" column="create_time" />
<result property="startTime" column="start_time" />
<result property="endTime" column="end_time" />
<result property="operType" column="oper_type" />
<result property="project" column="project" />
<result property="pageName" column="page_name" />
<result property="queueName" column="queue_name" />
<result property="data" column="data"
jdbcType="OTHER" typeHandler="com.jiarui.operlog.util.typehandler.JsonbTypeHandler" />
</resultMap>
<insert id="insertOperLog" parameterType="OperLog">
insert into operlog (id, user_id, oper_name, oper_description, create_time, start_time, end_time, oper_type, project, page_name, queue_name, data)
values (#{Id}, #{userId}, #{operName}, #{operDescription}, now(), #{startTime}, #{endTime}, #{operType}, #{project}, #{pageName}, #{queueName}, #{data,jdbcType=OTHER,typeHandler=com.jiarui.operlog.util.typehandler.JsonbTypeHandler})
</insert>
<select id="selectOperLogList" parameterType="OperLogDTO" resultMap="OperLogResult">
select id, user_id, oper_name, oper_description, create_time, start_time, end_time, oper_type, project, page_name, queue_name, data from operlog
<where>
<if test="operName != null and operName != ''">
AND oper_name like concat('%', #{operName}, '%')
</if>
<if test="project != null and project != ''">
AND project like concat('%', #{project}, '%')
</if>
<if test="pageName != null and pageName != ''">
AND page_name like concat('%', #{pageName}, '%')
</if>
<if test="queueName != null and queueName != ''">
AND queue_name like concat('%', #{queueName}, '%')
</if>
<if test="beginTime != null and beginTime != ''"><!-- 开始时间检索 -->
and create_time >= #{beginTime}
</if>
<if test="endTime != null and endTime != ''"><!-- 结束时间检索 -->
and create_time <= #{endTime}
</if>
</where>
order by create_time desc
</select>
<delete id="deleteOperLogByIds" parameterType="String">
delete from operlog where id in
<foreach collection="array" item="infoId" open="(" separator="," close=")">
#{infoId}
</foreach>
</delete>
<update id="cleanOperLog">
truncate table operlog
</update>
</mapper>
5.接口方法
package com.jiarui.operlog.controller;
import com.jiarui.operlog.model.OperLog;
import com.jiarui.operlog.model.dto.OperLogDTO;
import com.jiarui.operlog.service.IOperLogService;
import com.jiarui.operlog.util.PageResult;
import com.jiarui.operlog.util.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;
import java.util.List;
import java.util.UUID;
@RestController
@RequestMapping("operlog")
public class OperlogController{
@Autowired
private IOperLogService operLogService;
@RequestMapping(value = "/save", method = {RequestMethod.POST})
@ResponseBody
public Result save(@RequestBody @Validated OperLog operLog)
{
operLog.setId(UUID.randomUUID().toString());
operLogService.insertOpeoLog(operLog);
return Result.ok();
}
@RequestMapping(value = "/list", method = {RequestMethod.GET})
@ResponseBody
public Result list(OperLogDTO operLogDTO,
final @RequestParam(value = "pageNum",required = false,defaultValue = "1") Integer pageNum,
final @RequestParam(value = "pageSize",required = false,defaultValue = "20") Integer pageSize)
{
PageResult<OperLog> result = new PageResult<>(pageNum,pageSize);
List<OperLog> list = operLogService.selectOperLogList(operLogDTO);
result.setTotal(list.size());
result.setData(list.subList((pageNum-1)*pageSize<0?0:(pageNum-1)*pageSize,pageSize<list.size()?pageSize:list.size()));
return Result.data(result);
}
}
6.模拟请求
新增请求：
查询请求：
注：JSON工具相关依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.70</version>
</dependency>