Mybatis学习笔记（二）

官方文档讲的真的很清楚了mybatis3官方文档
mybatis执行流程

复杂查询

环境搭建

• 导入相关依赖
• 核心配置文件mybatis-config.xml和db.properties

<?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"/>

    <settings>
        <!--日志工厂-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <!--开启驼峰命名-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--类型别名-->
    <typeAliases>
        <package name="com.diana.pojo"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--引入db.properties的值-->
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>

    </environments>

    <mappers>
        <!--mapper注册-->
        <mapper resource="com/diana/dao/EmployeeMapper.xml"/>
    </mappers>

</configuration>

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
username=root
password=root

• 数据库设计employee表和department表
  
  

• 实体类Employee和Department

public class Employee {
    private Integer id;
    private String lastName;
    private String email;
    private Integer gender;
    private Department department;
}

public class Department {
    private Integer id;
    private String departmentName;
}

多对一查询

子查询

public interface EmployeeMapper {
	// 通过id查找department
    Department getDeptById(Integer id);
    // 多对一查询
    List<Employee> getEmpList1();
}

<!--多对一查询，使用子查询-->
<select id="getEmpList1" resultMap="EmpList">
    select * from employee
</select>

<resultMap id="EmpList" type="employee">
	<!--可以省略写也可以查出-->
    <!--<result property="lastName" column="last_name"/>-->
    <!--复杂属性 对象使用association-->
    <association property="department" column="d_id" javaType="department" select="getDeptById"/>
</resultMap>

<select id="getDeptById" resultType="department">
    select * from department where id=#{d_id}
</select>

连表查询

public interface EmployeeMapper {
    // 多对一查询 连表查询
    List<Employee> getEmpList2();
}

<!--多对一查询，使用连表查询-->
<select id="getEmpList2" resultMap="EmpList2">
    select e.id eid, e.last_name ename, d.department_name dname from employee e, department d where e.d_id=d.id
</select>

<resultMap id="EmpList2" type="employee">
    <!--如果查询sql语句使用了别名则column的值为别名-->
    <!--查了哪些字段就写哪些字段-->
    <!--主键推荐使用id标签-->
    <result property="id" column="eid"/>
    <result property="lastName" column="ename"/>
    <association property="department" javaType="department">
        <result property="departmentName" column="dname"/>
    </association>
</resultMap>

一对多查询

子查询

public interface DepartmentMapper {
    // 一对多查询 子查询
    Department getDeptById1(Integer id);
    // 获取对应部门的所有员工
    List<Employee> getEmpList();
}

<!--一对多查询 子查询-->
<select id="getDeptById1" resultMap="deptList1">
    select * from department where id=#{did}
</select>

<resultMap id="deptList1" type="department">
    <id property="id" column="id"/>
    <!--复杂对象 集合使用collection javaType为对象属性的类型 ofType为集合中的泛型的类型 column为department的id-->
    <collection property="empList" javaType="ArrayList" ofType="employee" select="getEmpList" column="id"/>
</resultMap>

<select id="getEmpList" resultType="employee">
    select * from employee where d_id=#{did}
</select>

连表查询

public interface DepartmentMapper {
    // 一对多查询 连表查询
    Department getDeptById2(Integer id);
}

<!--一对多查询 连表查询-->
<select id="getDeptById2" resultMap="deptList2">
    select d.id, d.department_name, e.last_name from employee e, department d where e.d_id=d.id and d.id=1
</select>

<resultMap id="deptList2" type="department">
    <id property="id" column="id"/>
    <result property="departmentName" column="department_name"/>
    <!--由于empList是集合 所以使用ofType表示集合中泛型的类型-->
    <collection property="empList" ofType="employee">
        <result property="lastName" column="last_name"/>
    </collection>
</resultMap>

动态SQL

• if标签判断条件是否成立，成立则拼接到SQL中。使用where标签代替sql语句中的where可以防止SQL语法错误

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

• choose相当于switch，when中的条件如果满足只会按顺序选取第一个满足条件的语句，otherwise中的语句必定添加进SQL

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <where>
	<choose>
	    <when test="title != null">
	      AND title like #{title}
	    </when>
	    <when test="author != null and author.name != null">
	      AND author_name like #{author.name}
	    </when>
	    <otherwise>
	      AND featured = 1
	    </otherwise>
	</choose>
  </where>
</select>

• 更新SQL语句，set元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号

<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>

缓存

一级缓存

• 一级缓存默认开启，在SqlSession中，关闭了缓存就失效了

二级缓存

• 二级缓存现在核心配置文件中开启全局缓存，再在Mapper.xml文件中使用<cache/>标签开启，在Mapper中，一次会话关闭后二级缓存生效。注意实体类要序列化
  

其它

• 用户使用查询语句会进行缓存，再次执行查询语句会先从二级缓存中查找，再去一级缓存中查找，都没有找到才到数据库中查询
• 缓存会在增删改操作后会刷新缓存，SqlSession.clearCache()可以手动清除缓存
• 也可以使用自定义缓存

<cache type="com.domain.something.MyCustomCache"/>