MyBatis进阶应用

动态SQL语句

• if
• choose（when、otherwise）
• trim（where、set）
• foreach

if

    <select id="selectEmployeeIf" resultMap="BaseResultMap" parameterType="employee">
        select * from employee
        <where>
            <if test="id != null">
                and id = #{id}
            </if>
            <if test="name != null">
                and emp_name = #{name}
            </if>
        </where>
    </select>

choose

    <select id="selectEmployeeChoose" resultMap="BaseResultMap" parameterType="employee">
        select * from employee
        <where>
            <choose>
                <when test="id != null">
                    and emp_id = #{id}
                </when>
                <when test="name != null and name != ''">
                    and emp_name like CONCAT(CONCAT('%',#{name, jdbcType=VARCHAR}),'%')
                </when>
                <otherwise>
                    and emp_id = 2
                </otherwise>
            </choose>
        </where>
    </select>

trim

    <!-- 表示增加where前缀，并且将第一个and或者or抹除，并且在最后加上 order by emp_id desc-->
    <select id="selectEmployeeTrim1" resultMap="BaseResultMap" parameterType="employee">
        select * from employee
        <trim prefix="where" prefixOverrides="AND|OR" suffix="order by emp_id desc">
            <if test="id != null">
                and emp_id = #{id}
            </if>
            <if test="name != null">
                and emp_name = #{name}
            </if>
            <if test="sex != null">
                and emp_sex = #{sex}
            </if>
        </trim>
    </select>
    
    <!-- 表示增加set前缀，并且抹除最后一个逗号, -->
    <update id="updateEmployee" parameterType="employee">
        update employee
        <trim prefix="set" suffixOverrides=",">
            <if test="name != null">
                emp_name = #{name},
            </if>
            <if test="address != null">
                emp_address = #{address},
            </if>
        </trim>
        where emp_id = #{id}
    </update>

foreach

如果传入的是单参数且参数类型是一个 List 的时候，collection 属 性值为 list。
如果传入的是单参数且参数类型是一个 array 数组的时候， collection 的属性值为 array。

    <delete id="deleteByIds" parameterType="integer[]">
        delete from employee
        where emp_id in
        <foreach collection="array" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>

批量操作

批量插入

    <insert id="insertEmployeeList" parameterType="list">
        insert into employee(emp_name,emp_age,emp_sex,emp_address)
        values
        <foreach collection="list" item="emp" separator=",">
            (#{emp.name},#{emp.age},#{emp.sex},#{emp.address})
        </foreach>
    </insert>

java代码

    @Test
    public void test01() throws Exception{
        init();
        long start = System.currentTimeMillis();
        EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
        List<Employee> list = new ArrayList<>();
        for (int i = 0; i < 10000; i++){
            Employee employee = new Employee();
            employee.setName("Batch" + i);
            if(i % 2 == 0){
                employee.setSex("男");
            }else{
                employee.setSex("女");
            }
            employee.setAge(new Random().nextInt((50 - 18)) + 18);
            employee.setAddress("地址" + i);
            list.add(employee);
        }
        mapper.insertEmployeeList(list);
        session.commit();
        session.close();
        long end = System.currentTimeMillis();
        System.out.println("批量插入用时：" + (end - start) + "毫秒");
    }

批量更新

    <update id="updateEmployeeList" parameterType="list">
        update employee set emp_name =
        <foreach collection="list" item="emp" open="case emp_id" close="end" separator=" ">
            when #{emp.id} then #{emp.name}
        </foreach>
        ,emp_address =
        <foreach collection="list" item="emp" open="case emp_id" close="end" separator=" ">
            when #{emp.id} then #{emp.address}
        </foreach>
        where emp_id in
        <foreach collection="list" item="emp" open="(" separator="," close=")">
            #{emp.id}
        </foreach>
    </update>

java代码

    @Test
    public void test02() throws Exception{
        init();
        long start = System.currentTimeMillis();
        EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
        List<Employee> list = new ArrayList<>();
        for (int i = 100; i < 500; i++){
            Employee employee = new Employee();
            employee.setId(i);
            employee.setName("批量更新姓名" + i);
            employee.setAddress("批量更新地址" + i);
            list.add(employee);
        }
        Integer integer = mapper.updateEmployeeList(list);
        session.commit();
        session.close();
        long end = System.currentTimeMillis();
        System.out.println("批量更新"+integer+"条用时：" + (end - start) + "毫秒");
    }

关联查询

嵌套查询

    <resultMap id="EmployeeDeptMap1" type="employee">
        <id property="id" column="emp_id" jdbcType="INTEGER" />
        <result property="name" column="emp_name" jdbcType="VARCHAR" />
        <result property="age" column="emp_age" jdbcType="INTEGER" />
        <result property="sex" column="emp_sex" jdbcType="VARCHAR" />
        <result property="address" column="emp_address" jdbcType="VARCHAR" />
        <result property="deptId" column="dept_id" jdbcType="INTEGER" />
        <association property="department" javaType="department">
            <id property="id" column="dept_id" jdbcType="INTEGER" />
            <result property="name" column="dept_name" jdbcType="VARCHAR" />
            <result property="desc" column="dept_desc" jdbcType="VARCHAR" />
        </association>
    </resultMap>

    <select id="queryEmpAndDept" resultMap="EmployeeDeptMap1">
        SELECT
            e.emp_id,
            e.emp_name,
            e.emp_age,
            e.emp_sex,
            e.emp_address,
            d.dept_id,
            d.dept_name,
            d.dept_desc
        FROM
            employee e
        LEFT JOIN
            department d
            ON e.dept_id = d.dept_id
    </select>

实体类

/**
 * @program: mybatis-demo-01
 * @description: 员工类
 * @author: wxw
 * @create: 2022-03-22 09:53
 **/
@Data
public class Employee {
    private Integer id;

    private String name;

    private Integer age;

    private String sex;

    private String address;

    private Integer deptId;

    private Department department;
}

/**
 * @program: mybatis-demo-01
 * @description: 部门类
 * @author: wxw
 * @create: 2022-03-22 14:51
 **/
@Data
public class Department {

    private Integer id;

    private String name;

    private String desc;
}

延迟加载（一对一）

开启延迟加载开关

        <!-- 延迟加载， 默认 false -->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!-- 是否所有方法都会出发查询， 默认false -->
        <setting name="aggressiveLazyLoading" value="true"/>

映射文件

    <resultMap id="EmployeeDeptMap2" type="employee">
        <id property="id" column="emp_id" jdbcType="INTEGER" />
        <result property="name" column="emp_name" jdbcType="VARCHAR" />
        <result property="age" column="emp_age" jdbcType="INTEGER" />
        <result property="sex" column="emp_sex" jdbcType="VARCHAR" />
        <result property="address" column="emp_address" jdbcType="VARCHAR" />
        <result property="deptId" column="dept_id" jdbcType="INTEGER" />
        <association property="department" javaType="department" column="dept_id"
                     select="queryDeptByIdLazy">
        </association>
    </resultMap>
    <resultMap id="DeptResultMap" type="department">
        <id property="id" column="dept_id" jdbcType="INTEGER" />
        <result property="name" column="dept_name" jdbcType="VARCHAR" />
        <result property="desc" column="dept_desc" jdbcType="VARCHAR" />
    </resultMap>

    <select id="queryEmpAndDeptLazy" resultMap="EmployeeDeptMap2">
        SELECT * FROM employee
    </select>

    <select id="queryDeptByIdLazy" parameterType="integer" resultMap="DeptResultMap">
        select * from department where dept_id = #{deptId}
    </select>

注意：开启延迟加载后只有在调用关联对象的方法以及（equals、clone、hashCode、toString）方法时才会触发查询，也可以通过__配置触发的方法。

延迟加载（一对多）

    <resultMap id="DeptResultMap" type="department">
        <id property="id" column="dept_id" jdbcType="INTEGER" />
        <result property="name" column="dept_name" jdbcType="VARCHAR" />
        <result property="desc" column="dept_desc" jdbcType="VARCHAR" />
        <collection property="employees" ofType="employee" column="dept_id"
                    select="queryEmpsByDeptIdLazy">
        </collection>
    </resultMap>
    <resultMap id="BaseResultMap" type="employee">
        <id property="id" column="emp_id" jdbcType="INTEGER" />
        <result property="name" column="emp_name" jdbcType="VARCHAR" />
        <result property="age" column="emp_age" jdbcType="INTEGER" />
        <result property="sex" column="emp_sex" jdbcType="VARCHAR" />
        <result property="address" column="emp_address" jdbcType="VARCHAR" />
        <result property="deptId" column="dept_id" jdbcType="INTEGER" />
    </resultMap>

    <select id="queryDeptByIdLazy" parameterType="integer" resultMap="DeptResultMap">
        select * from department where dept_id = #{deptId}
    </select>

    <select id="queryEmpsByDeptIdLazy" resultMap="BaseResultMap">
        SELECT * FROM employee where dept_id = #{deptId}
    </select>

分页操作

逻辑分页

MyBatis自带逻辑分页对象RowBounds，通过设置offset和limit（从第几条开始，查询多少条），该方式本质上是将所有数据查询出来放在内存中进行筛选，数据量大的话效率会很低。

List<Employee> selectEmployeeListByPage(RowBounds rowBounds);

    @Test
    public void test01() throws Exception{
        init();
        EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
        RowBounds rowBounds = new RowBounds(1,5);
        List<Employee> list = mapper.selectEmployeeListByPage(rowBounds);
        for (Employee employee : list) {
            System.out.println(employee);
        }
        session.close();
    }

物理分页

第一种

自己封装分页对象，在SQL中使用limit翻页。

    <select id="selectEmployeeListByPage1" resultMap="BaseResultMap" parameterType="map" >
        select * from employee limit #{curIndex}, #{pageSize}
    </select>

    @Test
    public void test02() throws Exception{
        init();
        EmployeeMapper mapper = session.getMapper(EmployeeMapper.class);
        Map<String, Integer> map = new HashMap<>();
        map.put("curIndex", 1);
        map.put("pageSize", 8);
        List<Employee> list = mapper.selectEmployeeListByPage1(map);
        for (Employee employee : list) {
            System.out.println(employee);
        }
        session.close();
    }

第二种

使用分页插件，比如PageHelper。PageHelper通过MyBatis的插件拦截器实现，在SQL执行之前会拦截SQL语句进行增强从而实现分页。

特殊符号处理

在 mybatis 中的 xml 文件中，存在一些特殊的符号，比如：<、>、"、'、& 等，正常书写 mybatis 会报错，需要对这些符号进行转义。

特殊字符	转义字符
<	&lt;
>	&gt;
"	&quot;
’	&apos;
&	&amp;

在XML中可以使用<![CDATA[ ] ]>来包裹特殊字符

    <select id="selectEmpByAge" resultMap="BaseResultMap" parameterType="integer" >
        select * from employee where emp_age <![CDATA[<]]> #{age}
    </select>