MyBatis自定义类型转换器

自定义类型转换器

添加数据时将List类型存储成以 ,分割的varchar类型
例如：Arrays.asList(“篮球”, “足球”) → 足球，篮球

实现步骤

1. 实现org.apache.ibatis.type.TypeHandler接口，指定入参类型
2. 通过@MappedJdbcTypes和@MappedTypes注解指定互相映射的数据类型
3. 实现接口方法（通过PreparedStatement和ResultSet进行参数类型转换）

代码实现

/**
 * List2VarcharHandler
 *
 * @author xgSama
 * @date 2021/3/7 13:38
 */
// 添加这两个注解指定互相映射的数据类型 
@MappedJdbcTypes(JdbcType.VARCHAR)
@MappedTypes(List.class)
public class List2VarcharHandler implements TypeHandler<List<String>> {

    // 将传入的List格式化后放入PreparedStatement中
    @Override
    public void setParameter(PreparedStatement preparedStatement, int i, List<String> strings, JdbcType jdbcType) throws SQLException {
        StringBuilder sb = new StringBuilder();
        if (strings.size() > 0) {
            for (String string : strings) {
                sb.append(string).append(",");
            }

            sb.deleteCharAt(sb.length() - 1);
        }

        preparedStatement.setString(i, sb.toString());
    }

    // 以下三个方法用于把查询结果中的varchar类型数据格式化成List类型
    @Override
    public List<String> getResult(ResultSet resultSet, String columnName) throws SQLException {
        String string = resultSet.getString(columnName);
        if (string != null) {
            return Arrays.asList(string.split(","));
        }
        return null;
    }

    @Override
    public List<String> getResult(ResultSet resultSet, int columnIndex) throws SQLException {
        String string = resultSet.getString(columnIndex);
        if (string != null) {
            return Arrays.asList(string.split(","));
        }
        return null;
    }

    @Override
    public List<String> getResult(CallableStatement callableStatement, int columnIndex) throws SQLException {
        String string = callableStatement.getString(columnIndex);
        if (string != null) {
            return Arrays.asList(string.split(","));
        }
        return null;
    }
}

使用示例

示例数据

+--+-------+---+------+---------+
|id|name   |age|gender|favorites|
+--+-------+---+------+---------+
|1 |xuange |18 |男     |NULL    |
|2 |sd     |20 |男     |篮球,足球|
|3 |yui    |34 |男     |篮球,足球|
+--+-------+---+------+---------+

定义查询和插入方法

Student getById(int id);
int addStudent(Student student);

实现Mapper

<?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.xgsama.mybatis.mapper.StudentMapper">
    <resultMap id="StuMap" type="com.xgsama.mybatis.model.Student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="gender" column="gender"/>
        <!-- 添加typeHandler属性指定使用自定义的类型转换器 -->
        <result property="favorites" column="favorites"
                typeHandler="com.xgsama.mybatis.typehandler.List2VarcharHandler"/>
    </resultMap>

    <!-- 插入时也需要通过typeHandler指定类型转换器 -->
    <insert id="addStudent">
        insert into student(id, name, age, gender, favorites)
        values (#{id}, #{name}, #{age}, #{gender},
                #{favorites, typeHandler=com.xgsama.mybatis.typehandler.List2VarcharHandler})
    </insert>

    <select id="getById" resultMap="StuMap">
        select *
        from student
        where id = #{id};
    </select>
</mapper>

测试结果

==>  Preparing: select * from student where id = ?; 
[DEBUG] 2021-03-07 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:143)
==> Parameters: 3(Integer)
[DEBUG] 2021-03-07 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:143)
<==      Total: 1
Student{id=3, name='yui', gender='男', favorites='[篮球, 足球]'}