MyBatis的优点、自定义TypeHandler

一、为什么要用MyBatis？MyBatis解决了什么问题？

• MyBatis是一个半自动化的ORM框架，可以简化我们的开发工作。
• 他解决了我们使用传统JDBC时遇到的问题：

1. 代码重复问题（加载驱动、获取数据库连接、获取语句集、关闭语句集、关闭数据库连接等）
2. 资源管理问题（无法动态配置数据源，硬编码）
3. pojo映射问题（需要自己new对象，并且需要把数据库字段类型与pojo对象字段类型手动做一一映射）
4. sql耦合问题（传统jdbc的sql需要硬编码，不易修改）

• MyBatis的核心功能有：

1. 使用连接池对连接管理（可自定义连接池）
2. SQL和代码进行分离，便于管理（可配置在resource中，不参与编译）
3. 结果集映射（可直接返回pojo或泛型list，可自定义结果集）
4. 参数映射（可自定义参数映射策略）
5. 动态sql（可根据条件进行动态sql拼接）
6. 重复sql提取（减少代码重复率，更加简洁，便于管理）
7. 缓存机制（还没说）
8. 插件机制（对已有功能增强）

二、通过自定义TypeHandler实现List 转字符串

1. ShopDto.java

import java.io.Serializable;
import java.util.List;

/**
 * @author m
 * @date 2020-05-07
 */
@Data
public class ShopDto implements Serializable {
    private static final long serialVersionUID = 340408430335128350L;
    private Integer id;
    private String name;
    /** 定为数组或list */
    private List<Integer> range;
}

2. ShopMapper.java

import com.lyz.mybatis.entity.ShopDto;

/**
 * (Shop)表数据库访问层
 *
 * @author linyizhi
 * @since 2020-05-07 00:36:43
 */
public interface ShopMapper {

    ShopDto queryById(Integer id);

    int insert(ShopDto shop);

}

3. MyTypeHadler.java

public class MyTypeHandler extends BaseTypeHandler<List<Integer>> {
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, List<Integer> integers, JdbcType jdbcType) throws SQLException {
        String collect = integers.stream().map(Object::toString).collect(Collectors.joining(","));
        preparedStatement.setString(i,collect);
    }
    @Override
    public List<Integer> getNullableResult(ResultSet resultSet, String s) throws SQLException {
        String resultString = resultSet.getString(s);
        List<Integer> stream = Arrays.stream(resultString.split(",")).map(Integer::parseInt).collect(Collectors.toList());
        return stream;
    }
    @Override
    public List<Integer> getNullableResult(ResultSet resultSet, int i) throws SQLException {
        String resultString = resultSet.getString(i);
        List<Integer> stream = Arrays.stream(resultString.split(",")).map(Integer::parseInt).collect(Collectors.toList());
        return stream;
    }
    @Override
    public List<Integer> getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        String resultString = callableStatement.getString(i);
        List<Integer> stream = Arrays.stream(resultString.split(",")).map(Integer::parseInt).collect(Collectors.toList());
        return stream;
    }
}

4. ShopMapper.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.lyz.mybatis.dao.ShopMapper">
    <resultMap type="com.lyz.mybatis.entity.ShopDto" id="ShopMap">
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <result property="range" column="range" jdbcType="VARCHAR" typeHandler="com.lyz.mybatis.type.MyTypeHandler"/>
    </resultMap>
    <!--查询单个-->
    <select id="queryById" resultMap="ShopMap">
        select
          id, name, `range`
        from shop
        where id = #{id}
    </select>
    <!--新增所有列-->
    <insert id="insert" keyProperty="id" useGeneratedKeys="true">
        insert into mybatis.shop(id, name, `range`)
        values (#{id}, #{name}, #{range,jdbcType=VARCHAR,typeHandler=com.lyz.mybatis.type.MyTypeHandler})
    </insert>
</mapper>

5. mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="db.properties"/>
    <typeAliases>
        <typeAlias alias="shop" type="com.lyz.mybatis.entity.Shop" />
    </typeAliases>
    <typeHandlers>
        <typeHandler handler="com.lyz.mybatis.type.MyTypeHandler"/>
    </typeHandlers>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/><!-- 单独使用时配置成MANAGED没有事务 -->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/ShopMapper.xml"/>
    </mappers>
</configuration>

6. Test.java

public class MyBatisTest {

    private SqlSessionFactory sqlSessionFactory;

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

    @Test
    public void testSelect() throws IOException {
        SqlSession session = sqlSessionFactory.openSession(); // ExecutorType.BATCH
        try {
            ShopMapper mapper = session.getMapper(ShopMapper.class);
            ShopDto blog = mapper.queryById(1);
            System.out.println(blog);
        } finally {
            session.close();
        }
    }
    @Test
    public void testInsert() throws IOException {
        SqlSession session = sqlSessionFactory.openSession(); // ExecutorType.BATCH
        try {
            ShopMapper mapper = session.getMapper(ShopMapper.class);
            ShopDto shopDto = new ShopDto();
            shopDto.setId(2);
            shopDto.setName("测试1");
            List<Integer> integers = new ArrayList<Integer>();
            integers.add(2);
            integers.add(3);
            integers.add(4);
            shopDto.setRange(integers);
            mapper.insert(shopDto);
            System.out.println(shopDto);
        } finally {
            session.close();
        }
    }
}