mabatis typeHandler 的使用案例

案例如下:(实现jdbc-String类型转Java的List集合)
一个商户，在登记的时候需要注册它的经营范围。比如1手机，2电脑，3相机，4平板，在界面上是一个复选框(checkbox)。
在数据库保存的是用逗号分隔的字符串，例如“1,3,4”，而返回给程序的时候是整形数组List {1,3,4}。
实现一个TypeHandler，可以把List转换成数据库的varchar。把数据库的vachar转换成List。
需要创建一张表、创建POJO、Mapper、xml映射器。

1.创建表

CREATE TABLE `t_merchant` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(255) NOT NULL COMMENT '商户名称',
  `scope` varchar(255) NOT NULL COMMENT '经营范围 ex: 1,3,4',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.创建POJO

@Data
public class Merchant implements Serializable {
    Integer id;
    String name;
    List<Integer> scope;
}

3.创建mapper

public interface MerchantMapper {
    Merchant selectById(Integer id);
    int insert(Merchant merchant);
}

4.创建TypeHandler

@MappedJdbcTypes(JdbcType.VARCHAR)
public class ListTypeHandler extends BaseTypeHandler<List<Integer>> {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, List<Integer> parameter, JdbcType jdbcType) throws SQLException {
        String value = String.join(",", parameter.stream().map(String::valueOf).collect(Collectors.toList()));
        ps.setString(i,value);
    }

    @Override
    public List<Integer> getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return handler(rs.getString(columnName));
    }
    @Override
    public List<Integer> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
         return handler(rs.getString(columnIndex));
    }

    @Override
    public List<Integer> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
         return handler(cs.getString(columnIndex));
    }
    private List<Integer> handler(String listString) {
        List<Integer> list = new ArrayList<>();
        if (StringUtils.isEmpty(listString)) {
            return list;
        }
        for (String s : listString.split(",")) {
            list.add(Integer.parseInt(s));
        }
        return list;
    }
}

5.创建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.gupaoedu.mapper.MerchantMapper">
    
    <resultMap id="BaseResultMap" type="com.gupaoedu.domain.Merchant">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="scope" property="scope" jdbcType="VARCHAR" typeHandler="com.gupaoedu.type.ListTypeHandler"/>
    </resultMap>
    
    <select id="selectById" resultMap="BaseResultMap" >
        select * from t_merchant where id = #{id}
    </select>

    <insert id="insert">
        insert into t_merchant (name,scope)
        values (#{name},#{scope,jdbcType=VARCHAR,typeHandler=com.gupaoedu.type.ListTypeHandler})

    </insert>
</mapper>

6.创建测试类

public class MyBatisTest {

    private SqlSessionFactory sqlSessionFactory;

    @Test
    public void testMerchantSelect() {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            MerchantMapper mapper = session.getMapper(MerchantMapper.class);
            Merchant merchant = mapper.selectById(1);
            System.out.println(merchant);
        } finally {
            session.close();
        }
    }

    @Test
    public void testMerchantInsert() {
        SqlSession session = sqlSessionFactory.openSession();
        try {
            MerchantMapper mapper = session.getMapper(MerchantMapper.class);
            Merchant merchant = new Merchant();
            merchant.setName("一点点");
            List<Integer> list=new ArrayList<>();
            list.add(1);
            list.add(3);
            merchant.setScope(list);
            mapper.insert(merchant);
            session.commit();
        } finally {
            session.close();
        }
    }

    @Before
    public void prepare() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }

}