MyBatis最佳实践

动态SQL语句

        动态SQL是Mybatis的强大特性之一。如果使用过JDBC或其他类似的框架，就应该理解根据不同条件拼接SQL语句有多痛苦，例如拼接时要确保不能添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态SQL，可以彻底摆脱这种痛苦。

        使用动态SQL并非一件易事，但借助可用于任何SQL映射语句中的强大的动态SQL语言，Mybatis显著地提升了这一特性的易用性。

        如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相 识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式， MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要 少。

if 
choose (when, otherwise) 
trim (where, set) 
foreach

if

        需要判断的时候，条件写在test中

<select id="selectListIf" parameterType="user" resultMap="BaseResultMap" >
    select
        <include refid="baseSQL"></include>
    from t_user
    <where>
        <if test="id != null">
            and id = #{id}
        </if>
        <if test="userName != null">
            and user_name = #{userName}
        </if>
    </where>
</select>

choose

        需要选择一个条件的时候

<!-- choose 的使用 -->
<select id="selectListChoose" parameterType="user" resultMap="BaseResultMap">
    select
        <include refid="baseSQL"></include>
    from t_user
    <where>
        <choose>
            <when test="id != null">
                id = #{id}
            </when>
            <when test="userName != null and userName != ''">
                and user_name like CONCAT(CONCAT('%',#{userName,jdbcType=VARCHAR}),'%')
            </when>
            <otherwise>
            </otherwise>
        </choose>
    </where>
</select>

 trim

        需要去掉where、and、逗号之类的符号的时候

<!--
    trim 的使用
    替代where标签的使用
-->
<select id="selectListTrim" resultMap="BaseResultMap" parameterType="user">
    select <include refid="baseSQL"></include>
    <!-- <where>
            <if test="username!=null">
                and name = #{username}
            </if>
        </where> -->
    <trim prefix="where" prefixOverrides="AND |OR ">
        <if test="userName!=null">
            and user_name = #{userName}
        </if>
        <if test="age != 0">
            and age = #{age}
        </if>
    </trim>
</select>

<!-- 替代set标签的使用 -->
<update id="updateUser" parameterType="User">
    update t_user
    <trim prefix="set" suffixOverrides=",">
        <if test="userName!=null">
            user_name = #{userName},
        </if>
        <if test="age != 0">
            age = #{age}
        </if>
    </trim>
    where id=#{id}
</update>

 foreach

        需要遍历集合的时候

<delete id="deleteByList" parameterType="java.util.List">
    delete from t_user where id in
    <foreach collection="list" item="item" open="(" separator="," close=")">
        #{item.id,jdbcType=INTEGER}
    </foreach>
</delete>

总结

        动态SQL主要是用来解决SQL语句生成的问题。

批量操作

        我们在项目中会有一些批量操作的场景，比如导入文件批量处理数据的情况（批量新增商户、批量修 改商户信息），当数据量非常大，比如超过几万条的时候，在Java代码中循环发送SQL到数据库执行肯 定是不现实的，因为这个意味着要跟数据库创建几万次会话。即使在同一个连接中，也有重复编译和执 行SQL的开销。

        例如循环插入10000条（大约耗时3秒钟）：

public class Test03Batch {
    public SqlSession session;
    @Before
    public void init() throws IOException {
// 1.获取配置文件
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
// 2.加载解析配置文件并获取SqlSessionFactory对象
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
// 3.根据SqlSessionFactory对象获取SqlSession对象
        session = factory.openSession();
    }
    /**
     * 循环插入10000
     */
    @Test
    public void test1(){
        long start = System.currentTimeMillis();
        UserMapper mapper = session.getMapper(UserMapper.class);
        int count = 12000;
        for (int i=2000; i< count; i++) {
            User user = new User();
            user.setUserName("a"+i);
            mapper.insertUser(user);
        }
        session.commit();
        session.close();
        long end = System.currentTimeMillis();
        System.out.println("循环批量插入"+count+"条，耗时：" + (end -start )+"毫秒");
    }
}

        在MyBatis里面是支持批量的操作的，包括批量的插入、更新、删除。我们可以直接传入一个List、 Set、Map或者数组，配合动态SQL的标签，MyBatis会自动帮我们生成语法正确的SQL语句。

批量插入

        批量插入的语法是这样的，只要在values后面增加插入的值就可以了。

insert into tbl_emp (emp_id, emp_name, gender,email, d_id) values ( ?,?,?,?,? ),
( ?,?,?,?,? ),( ?,?,?,?,? )

在Mapper文件里面，我们使用foreach标签拼接 values部分的语句：

<!-- 批量插入 -->
    <insert id="insertUserList" parameterType="java.util.List" >
        insert into t_user(user_name,real_name)
    values
        <foreach collection="list" item="user" separator=",">
            (#{user.userName},#{user.realName})
        </foreach>
</insert>

Java代码里面，直接传入一个List类型的参数。

**
     * 批量插入
     */
    @Test
    public void test2(){
        long start=System.currentTimeMillis();
        UserMapper mapper=session.getMapper(UserMapper.class);
        int count=12000;
        List<User> list=new ArrayList<>();
        for(int i=2000;i<count; i++){
            User user=new User();
            user.setUserName("a"+i);
            list.add(user);
        }
        mapper.insertUserList(list);
        session.commit();
        session.close();
        long end=System.currentTimeMillis();
        System.out.println("循环批量插入"+count+"条，耗时："+(end-start)+"毫秒");
    }

插入一万条大约耗时1秒钟。

        可以看到，动态SQL批量插入效率要比循环发送SQL执行要高得多。最关键的地方就在于减少了跟 数据库交互的次数，并且避免了开启和结束事务的时间消耗。

批量更新

        批量更新的语法是这样的，通过case when，来匹配id相关的字段值

update t_user set
user_name =
case id
when ? then ?
when ? then ?
when ? then ? end ,
real_name =
case id
when ? then ?
when ? then ?
when ? then ? end
where id in ( ? , ? , ? )

        所以在Mapper文件里面最关键的就是case when和where的配置。 需要注意一下open属性和separator属性。

<update id="updateUserList">
    update t_user set user_name =
        <foreach collection="list" item="user" index="index" separator=" " open="case id" close="end">
            when #{user.id} then #{user.userName}
        </foreach>
        ,real_name =
        <foreach collection="list" item="user" index="index" separator=" " open="case id" close="end">
            when #{user.id} then #{user.realName}
        </foreach>
        where id in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item.id,jdbcType=INTEGER}
        </foreach>
</update>

java代码实现

        

/**
     * 批量更新
     */
    @Test
    public void test3(){
        long start = System.currentTimeMillis();
        UserMapper mapper = session.getMapper(UserMapper.class);
        int count = 12000;
        List<User> list = new ArrayList<>();
        for (int i=2000; i< count; i++) {
            User user = new User();
            user.setId(i);
            user.setUserName("a"+i);
            list.add(user);
        }
        mapper.updateUserList(list);
        session.commit();
        session.close();
        long end = System.currentTimeMillis();
        System.out.println("批量更新"+count+"条，耗时：" + (end -start )+"毫秒");
    }

批量删除

        批量删除也是类似的。

<delete id="deleteByList" parameterType="java.util.List">
    delete from t_user where id in
    <foreach collection="list" item="item" open="(" separator="," close=")">
        #{item.id,jdbcType=INTEGER}
    </foreach>
</delete>

BatchExecutor

        当然MyBatis的动态标签的批量操作也是存在一定的缺点的，比如数据量特别大的时候，拼接出来的 SQL语句过大。 MySQL的服务端对于接收的数据包有大小限制，max_allowed_packet 默认是 4M，需要修改默认配 置或者手动地控制条数，才可以解决这个问题。

Caused by: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (7188967 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.

         在我们的全局配置文件中，可以配置默认的Executor的类型（默认是SIMPLE）。其中有一种 BatchExecutor。

<setting name="defaultExecutorType" value="BATCH" />

也可以在创建会话的时候指定执行器类型

SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);

Executor

1. SimpleExecutor：每执行一次update或select，就开启一个Statement对象，用完立刻关闭Statement对象。
2. ReuseExecutor：执行update或select，以sql作为key查找Statement对象，存在就使用，不存在就创建，用完后，不关闭Statement对象，而是放置于Map内，供下一次使用。简言之，就是重复使用Statement对象。
3. BatchExecutor：执行update（没有select，JDBC批处理不支持select），将所有sql都添加到批处理中（addBatch()），等待统一执行（executeBatch()），它缓存了多个Statement对象，每个Statement对象都是addBatch()完毕后，等待逐一执行executeBatch()批处理。与JDBC批处理相同。executeUpdate()是一个语句访问一次数据库，executeBatch()是一批语句访问一次数据库（具体一批发送多少条SQL跟服务端的max_allowed_packet有关）。BatchExecutor底层是对JDBC ps.addBatch()和ps. executeBatch()的封装。

    @Test
    public void testJdbcBatch() throws IOException {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn =
                    DriverManager.getConnection("jdbc:mysql://localhost:3306/mybatisdb?
                            rewriteBatchedStatements=true", "root", "123456");
                            ps = conn.prepareStatement(
                                    "INSERT into blog values (?, ?, ?)");
            for (int i = 1000; i < 101000; i++) {
                Blog blog = new Blog();
                ps.setInt(1, i);
                ps.setString(2, String.valueOf(i)+"");
                ps.setInt(3, 1001);
                ps.addBatch();
            }
            ps.executeBatch();
            ps.close();
            conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (ps != null) ps.close();
            } catch (SQLException se2) {
            }
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }

关联查询

嵌套查询

        我们在查询业务数据的时候经常会遇到关联查询的情况，比如查询员工就会关联部门（一对一）， 查询学生成绩就会关联课程（一对一），查询订单就会关联商品（一对多），等等。 用户和部门的对应关系是1对1的关系

<!-- 嵌套查询 1对1 1个用户对应一个部门-->
<resultMap id="nestedMap1" type="user">
    <id property="id" column="id" jdbcType="INTEGER"/>
    <result property="userName" column="user_name" jdbcType="VARCHAR" />
    <result property="realName" column="real_name" jdbcType="VARCHAR" />
    <result property="password" column="password" jdbcType="VARCHAR"/>
    <result property="age" column="age" jdbcType="INTEGER"/>
    <result property="dId" column="d_id" jdbcType="INTEGER"/>
    <association property="dept" javaType="dept">
    <id column="did" property="dId"/>
    <result column="d_name" property="dName"/>
    <result column="d_desc" property="dDesc"/>
    </association>
</resultMap>
<select id="queryUserNested" resultMap="nestedMap1">
    SELECT
        t1.`id`
        ,t1.`user_name`
        ,t1.`real_name`
        ,t1.`password`
        ,t1.`age`
        ,t2.`did`
        ,t2.`d_name`
        ,t2.`d_desc`
    FROM t_user t1
    LEFT JOIN
        t_department t2
    ON t1.`d_id` = t2.`did`
</select>

还有就是1对多的关联关系，嵌套查询

<!-- 嵌套查询 1对多 1个部门有多个用户-->
<resultMap id="nestedMap2" type="dept">
    <id column="did" property="dId"/>
    <result column="d_name" property="dName"/>
    <result column="d_desc" property="dDesc"/>
    <collection property="users" ofType="user">
        <id property="id" column="id" jdbcType="INTEGER"/>
        <result property="userName" column="user_name" jdbcType="VARCHAR" />
        <result property="realName" column="real_name" jdbcType="VARCHAR" />
        <result property="password" column="password" jdbcType="VARCHAR"/>
        <result property="age" column="age" jdbcType="INTEGER"/>
        <result property="dId" column="d_id" jdbcType="INTEGER"/>
    </collection>
</resultMap>
<select id="queryDeptNested" resultMap="nestedMap2">
    SELECT
        t1.`id`
        ,t1.`user_name`
        ,t1.`real_name`
        ,t1.`password`
        ,t1.`age`
        ,t2.`did`
        ,t2.`d_name`
        ,t2.`d_desc`
    FROM t_user t1
    RIGHT JOIN
        t_department t2
    ON t1.`d_id` = t2.`did`
</select>

延迟加载

        在MyBatis里面可以通过开启延迟加载的开关来解决这个问题。

        在settings标签里面可以配置：

<!--延迟加载的全局开关。当开启时，所有关联对象都会延迟加载。默认false -->
<setting name="lazyLoadingEnabled" value="true"/>
<!--当开启时，任何方法的调用都会加载该对象的所有属性。默认false，可通过select标签的
fetchType来覆盖-->
<setting name="aggressiveLazyLoading" value="false"/>
<!-- MyBatis 创建具有延迟加载能力的对象所用到的代理工具，默认JAVASSIST -->
<setting name="proxyFactory" value="CGLIB" />

lazyLoadingEnabled决定了是否延迟加载（默认false）。

aggressiveLazyLoading决定了是不是对象的所有方法都会触发查询。

1对1的延迟加载配置

<!-- 延迟加载 1对1 -->
<resultMap id="nestedMap1Lazy" type="user">
    <id property="id" column="id" jdbcType="INTEGER"/>
    <result property="userName" column="user_name" jdbcType="VARCHAR" />
    <result property="realName" column="real_name" jdbcType="VARCHAR" />
    <result property="password" column="password" jdbcType="VARCHAR"/>
    <result property="age" column="age" jdbcType="INTEGER"/>
    <result property="dId" column="d_id" jdbcType="INTEGER"/>
    <association property="dept" javaType="dept" column="d_id" select="queryDeptByUserIdLazy">
    </association>
</resultMap>
<resultMap id="baseDept" type="dept">
    <id column="did" property="dId"/>
    <result column="d_name" property="dName"/>
    <result column="d_desc" property="dDesc"/>
</resultMap>
<select id="queryUserNestedLazy" resultMap="nestedMap1Lazy">
    SELECT
        t1.`id`
        ,t1.`user_name`
        ,t1.`real_name`
        ,t1.`password`
        ,t1.`age`
        ,t1.d_id
    FROM t_user t1
</select>
<select id="queryDeptByUserIdLazy" parameterType="int" resultMap="baseDept">
    select * from t_department where did = #{did}
</select>

        注意：开启了延迟加载的开关，调用user.getDept()以及默认的（equals,clone,hashCode,toString）时 才会发起第二次查询，其他方法并不会触发查询，比如blog.getName();

    /**
     * 1对1 关联查询 延迟加载
     * @throws Exception
     */
    @Test
    public void test03() throws Exception{
        init();
        UserMapper mapper = session.getMapper(UserMapper.class);
        List<User> users = mapper.queryUserNestedLazy();
        for (User user : users) {
            System.out.println(user.getUserName() );
//System.out.println(user.getUserName() + "---->"+user.getDept());
        }
    }

触发延迟加载的方法可以通过配置，默认 equals(),clone(),hashCode(),toString()。

1对多的延迟加载的配置

<!-- 1对多 延迟加载 -->
<resultMap id="nestedMap2Lazy" type="dept">
    <id column="did" property="dId"/>
    <result column="d_name" property="dName"/>
    <result column="d_desc" property="dDesc"/>
    <collection property="users" ofType="user" column="did" select="queryUserByDeptLazy">
    </collection>
</resultMap>
<select id="queryDeptNestedLazy" resultMap="nestedMap2">
    SELECT
        ,t2.`did`
        ,t2.`d_name`
        ,t2.`d_desc`
    FROM
        t_department t2
</select>
<select id="queryUserByDeptLazy" resultMap="BaseResultMap" >
    select * from t_user where d_id = #{did}
</select>