1.问题描述
因为需要存入list数据类型所以mybatis端需要TypeHandler进行类型转换
2.代码
1.转换工具类
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.springframework.stereotype.Component;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
/**
* @ClassName ListToStringHandler
* @Description mybatis list转换为String 相互转换工具类
* @Author ygt
* @Date 2021/3/3 14:49
* @Version V1.0
*/
@Component
public class ListToStringHandler extends BaseTypeHandler<List> {
@Override
public void setNonNullParameter(PreparedStatement preparedStatement, int i, List list, JdbcType jdbcType) throws SQLException {
preparedStatement.setString(i, JSON.toJSONString(list));
}
@Override
public List getNullableResult(ResultSet resultSet, String s) throws SQLException {
JSONArray jsonArray = JSONArray.parseArray( resultSet.getString(s));
return jsonArray;
}
@Override
public List getNullableResult(ResultSet resultSet, int i) throws SQLException {
JSONArray jsonArray = JSONArray.parseArray( resultSet.getString(i));
return jsonArray;
}
@Override
public List getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
JSONArray jsonArray = JSONArray.parseArray( callableStatement.getString(i));
return jsonArray;
}
}
2.sql xml 部分代码
需要注意 有的地方需要加双引号有的地方不需要加双引号
否则有可能会出现类似如下报错 其实是双引号多了
mybatis: No enum constant org.apache.ibatis.type.JdbcType.“VARCHAR”
<resultMap id="BaseResultMap" type="xxxx.GameMatch">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="game_id" jdbcType="INTEGER" property="gameId" />
<result column="positions" jdbcType="VARCHAR" property="positions" typeHandler="xxx.util.ListToStringHandler" />
<result column="sub_channels" jdbcType="VARCHAR" property="subChannels" typeHandler="xxx.util.ListToStringHandler" />
</resultMap>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from game_match
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="xxx.model.GameMatch">
insert into game_match (id, game_id, positions,
sub_channels)
values (#{id,jdbcType=INTEGER}, #{gameId,jdbcType=INTEGER}, #{positions, jdbcType=VARCHAR ,typeHandler=xxx.util.ListToStringHandler},
#{subChannels,jdbcType=VARCHAR})
</insert>
<update id="updateByPrimaryKey" parameterType="xxx.model.GameMatch">
update game_match
set game_id = #{gameId,jdbcType=INTEGER},
positions = #{positions, jdbcType=VARCHAR ,typeHandler=xxx.util.ListToStringHandler},
sub_channels = #{subChannels, jdbcType=VARCHAR ,typeHandler=xxx.util.ListToStringHandler}
where id = #{id,jdbcType=INTEGER}
</update>
3.参考链接
mybatis 处理数组类型及使用Json格式保存数据 JsonTypeHandler and ArrayTypeHandler
深入浅出Mybatis系列(五)—TypeHandler简介及配置(mybatis源码篇)
玩转Mybatis中的类型转换器TypeHandler
mybatis plus将对象list转换为string存储&mybatisplus存储list字符串
MyBatis中的JdbcType映射介绍