Mybatis

Mybatis

一、创建项目及配置

Mapper模板

<?xml version="1.0" encoding="utf8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace=""> 

</mapper>

• 创建Maven项目

• 引入pom文件

  <dependencies>
          <dependency>
              <groupId>org.mybatis</groupId>
              <artifactId>mybatis</artifactId>
              <version>3.4.6</version>
          </dependency>
          <dependency>
              <groupId>mysql</groupId>
              <artifactId>mysql-connector-java</artifactId>
              <version>5.1.46</version>
          </dependency>
          <dependency>
              <groupId>log4j</groupId>
              <artifactId>log4j</artifactId>
              <version>1.2.12</version>
          </dependency>
      	<dependency>
              <groupId>junit</groupId>
              <artifactId>junit</artifactId>
              <version>4.11</version>
          </dependency>
  </dependencies>
  <build>
      <!--        告诉maven将xml文件也编译到target目录下-->
      <resources>
          <resource>
              <directory>src/main/java</directory>
              <includes>
                  <include>**/*.properties</include>
                  <include>**/*.xml</include>
              </includes>
          </resource>
          <resource>
              <directory>src/main/java</directory>
              <includes>
                  <include>**/*.properties</include>
                  <include>**/*.xml</include>
              </includes>
              <filtering>true</filtering>
          </resource>
      </resources>
  </build>
  

• 创建实体类和dao层

  • StudentDao

    public interface StudentDao {
        void insterStudent(Student student);
    }
    

  • StudentMapper.xml

    <?xml version="1.0" encoding="utf8"?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    
    <mapper namespace=""> 
    </mapper>
    

• 创建mybatis.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>
      <environments default="mysql">
          <!--MySQL数据库连接-->
          <environment id="mysql">
              <!--MyBatis中的事务管理，目前的事务管理和JDBC中的事务管理保持一致-->
              <transactionManager type="JDBC"/>
              <!--底层使用连接池连接数据库-->
              <dataSource type="POOLED">
                  <property name="driver" value="com.mysql.jdbc.Driver"/>
                  <property name="url" value="jdbc:mysql://localhost:3306/test?serverTimezone=UTC"/>
                  <property name="username" value="root"/>
                  <property name="password" value="root"/>
              </dataSource>
          </environment>
      </environments>
  
      <!--进行Mapper文件的扫描-->
      <mappers>
          <!--resource写的是xml所在的目录-->
          <mapper resource="com/ao/dao/StudentMapper.xml"/>
      </mappers>
  </configuration>
  

• 创建log4j.properties配置文件

  ### set log levels ###
  log4j.rootLogger =DEBUG,console,file
  
  ### 输出到控制台 ###
  log4j.appender.console = org.apache.log4j.ConsoleAppender
  log4j.appender.console.Target = System.out
  log4j.appender.console.Threshold=DEBUG
  log4j.appender.console.layout = org.apache.log4j.PatternLayout
  log4j.appender.console.layout.ConversionPattern =[%c]-%m%n
  
  ### 输出到日志文件 ###
  log4j.appender.file = org.apache.log4j.DailyRollingFileAppender
  log4j.appender.file.File = logs/log.log
  log4j.appender.file.MaxFileSize=10ms
  log4j.appender.file.Threshold = DEBUG ## 输出DEBUG级别以上的日志
  log4j.appender.file.layout = org.apache.log4j.PatternLayout
  log4j.appender.file.layout.ConversionPattern =[%p][%d{yy-mm-dd}][%c]%m%n
  
  ### 保存异常信息到单独文件 ###
  log4j.appender.org.mybatis =DEBUG
  log4j.appender.java.sql =DEBUG
  log4j.appender.java.sql.Statement =DEBUG
  log4j.appender.java.sql.ResultSet =DEBUG ## 只输出DEBUG级别以上的日志!!!
  log4j.appender.java.sql.PreparedStatement=DEBUG
  

• 实现类

  @Override
  public void insterStudent(Student student) {
  
      try {
          //读取mybatis配置文件
          InputStream resourceAsStream = Resources.getResourceAsStream("mybatis.xml");
  
          //创建SqlSessionFactory
          SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
  
          //创建SqlSession对象（线程不安全）
          SqlSession sqlSession = build.openSession();
  
          //操作sql
          sqlSession.insert("insertStudent",student);
          
          //提交事务
          sqlSession.commit();
      } catch (IOException e) {
          throw new RuntimeException(e);
      }
  }
  

二、Mybatis整合

• 创建MyBatisUtil工具类

  package com.ao.util;
  
  import org.apache.ibatis.io.Resources;
  import org.apache.ibatis.session.SqlSession;
  import org.apache.ibatis.session.SqlSessionFactory;
  import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  
  import java.io.IOException;
  import java.io.InputStream;
  
  /** 
   * 创建一个SqlSessionFactory
   * 获取多个SqlSession对象
   */
  public class MyBatisUtil {
      public static volatile SqlSessionFactory sqlSessionFactory;
  
      /*
      获取SqlSession
       */
  
      public static SqlSession getSqlSession(){
  
          InputStream resourceAsStream = null;
          try {
              if (sqlSessionFactory == null){
                  //读取mybatis配置文件
                  resourceAsStream = Resources.getResourceAsStream("mybatis.xml");
                  synchronized (MyBatisUtil.class){
                      //再次判断 DCL双重检验锁
                      if (sqlSessionFactory == null){
                          //创建SqlSessionFactory
                          sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
                      } 
                  } 
              }
          } catch (IOException e) {
              throw new RuntimeException(e);
          }
          //返回SqlSession对象(并自动提交)
          return sqlSessionFactory.openSession(true);
      } 
  }
  

• dao层

  package com.ao.dao;
  
  import com.ao.entity.Student;
  import com.ao.util.MyBatisUtil;
  import org.apache.ibatis.session.SqlSession;
  
  
  public class StudentMapper implements StudentDao{
  
      @Override
      public void insterStudent(Student student) {
          //通过工具类获取SqlSession对象，并自动关闭
          try(
                  SqlSession sqlSession = MyBatisUtil.getSqlSession()
                  ) { 
  
              //操作sql
              sqlSession.insert("insertStudent",student);
              //提交事务
              //sqlSession.commit();
          } catch (Exception e) {
          }
      }
  }
  

• db.properties

  jdbc.driver=com.mysql.jdbc.Driver
  jdbc.url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC
  jdbc.username=root
  jdbc.password=root
  

• mybatis.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"/>
  <!--    为实体类创建别名 mapper中语句parameterType可直接使用student-->
      <typeAliases>
      	
          <typeAlias type="com.ao.entity.Student" alias="student"/>
  
          <!--   扫描实体类上条语句可废弃-->
          <package name="com.ao.entity"/>
      </typeAliases>
      <environments default="mysql">
          <!--MySQL数据库连接-->
          <environment id="mysql">
              <!--MyBatis中的事务管理，目前的事务管理和JDBC中的事务管理保持一致-->
              <transactionManager type="JDBC"/>
              <!--底层使用连接池连接数据库-->
              <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>
  
  <!--进行Mapper文件的扫描-->
      <mappers>
          <!--resource写的是xml所在的目录-->
          <mapper resource="com/ao/dao/StudentMapper.xml"/>
      </mappers>
  </configuration>
  

三、配置文件说明

• 扫描实体类

  方法1：

  <!--    为实体类创建别名-->
      <typeAliases>
          <typeAlias type="com.ao.entity.Student" alias="student"/>
          <!--   扫描实体类-->
          <package name="com.ao.entity"/>
      </typeAliases>
  

  方法2：实体类前添加@Alias注解（少用）

  @Alias("student")
  

• 设置多个连接池

  <environments default="mysql">
          <!--MySQL数据库连接-->
          <environment id="mysql">
              <!--MyBatis中的事务管理，目前的事务管理和JDBC中的事务管理保持一致-->
              <transactionManager type="JDBC"/>
              <!--底层使用连接池连接数据库-->
              <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>
  <!--        测试环境-->
          <environment id="test">
              <!--MyBatis中的事务管理，目前的事务管理和JDBC中的事务管理保持一致-->
              <transactionManager type="JDBC"/>
              <!--底层使用连接池连接数据库-->
              <dataSource type="POOLED">
                  <property name="driver" value="${test.driver}"/>
                  <property name="url" value="${test.url}"/>
                  <property name="username" value="${test.username}"/>
                  <property name="password" value="${test.password}"/>
              </dataSource>
          </environment>
      </environments>
  

• 事务管理器（JDBC、MANAGED）

  <transactionManager type="JDBC"/>
  

• 数据资源类型（UNPOOLED、POOLED、JNDI）

  <dataSource type="POOLED">
  

• mapper文件的扫描

  <!--进行Mapper文件的扫描-->
  <mappers>
      <!--resource写的是xml所在的目录-->
      <mapper resource="com/ao/dao/StudentMapper.xml"/>
      <!--   dao和mapper名字需相同、并在同一个包下面  namespace需要写接口的全类名 -->
      <mapper class="com.ao.dao.StudentDao"/>
      <!--  扫描包,-->
      <package name="com.ao.dao"/>
  </mappers>
  

四、mybatis的操作

1、增删改查操作

插入操作

插入并返回主键

<insert id="insertStudent" parameterType="student">
    INSERT INTO t_student(name, age, score) VALUES (#{name}, #{age}, #{score})
    <selectKey resultType="int" keyProperty="id" order="AFTER">
        SELECT @@identity
    </selectKey>
</insert>

• 插入中文时乱码

  jdbc.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=false
  

• 实现类

  @Override
  public void insterStudent(Student student) {
      //通过工具类获取SqlSession对象，并自动关闭
      try (SqlSession sqlSession = MyBatisUtil.getSqlSession()) { 
          //操作sql
          sqlSession.insert("insertStudent",student); 
          
          System.out.println(student.getId());
          //提交事务
          //sqlSession.commit();
      }
  }
  

删除操作

<delete id="deleteStudent">
        DELETE FROM t_student WHERE id = #{id}
</delete>

• 实现类

  @Override
  public void deleteStudent(int id) {
      try (SqlSession sqlSession = MyBatisUtil.getSqlSession()){
          sqlSession.delete("deleteStudent",id);
      }
  }
  

修改操作

<update id="updateStudent">
    UPDATE t_student SET name=#{name},age=#{age},score=#{score} WHERE id=#{id}
</update>

• 实现类

  @Override
  public void updateStudent(Student student) {
      try (SqlSession sqlSession = MyBatisUtil.getSqlSession()){
          sqlSession.update("updateStudent",student);
      }
  }
  

查询操作

• 多条数据查询

  <select id="selectAllStudents" resultType="student">
      SELECT * FROM t_student
  </select>
  

  • 实现类

    @Override
    public List<Student> selectAllStudents() {
        List<Student> studentList = null;
        try (SqlSession sqlSession = MyBatisUtil.getSqlSession()){
            studentList = sqlSession.selectList("selectAllStudents");
        }
        return studentList;
    }
    

  • test类遍历

    @Test
    public void selectAllStudents(){
        List<Student> students = studentDao.selectAllStudents();
        //遍历
        students.forEach(student -> System.out.println(student.toString()));
    
    }
    

• 单条数据查询

  <select id="selectStudentById" parameterType="int" resultType="student">
      SELECT * FROM t_student WHERE id=#{id}
  </select>
  

  • 实现类

    @Override
    public Student selectStudentById(int id) {
        Student student = null;
        try (SqlSession sqlSession = MyBatisUtil.getSqlSession()){
            student = sqlSession.selectOne("selectStudentById",id);
        }
        return student;
    }
    

• 模糊查询

  <!--方式1  #{} 为占位符 以 ? 进行占位，建议使用-->
  <select id="selectStudentByName" parameterType="string" resultType="student">
      SELECT * FROM t_student WHERE name LIKE '%' #{name} '%'
  </select>
  <!--方式2  ${} 为字符拼接符-->
  <select id="selectStudentByName2" parameterType="string" resultType="student">
      SELECT * FROM t_student WHERE name LIKE '%${value}%'
  </select>
  

  • 实现类

    @Override
    public List<Student> selectStudentByName(String name) {
        List<Student> students = null;
        try (SqlSession sqlSession = MyBatisUtil.getSqlSession()){
        students = sqlSession.selectList("selectStudentByName",name);
        }
        return students;
        }
    

  • test类

    @Test
    public void selectStudentByName(){
        List<Student> students = null;
        students = studentDao.selectStudentByName("a");
        students.forEach(student -> System.out.println(student.toString()));
    }
    

2、字段名与属性名不一致的情况

• 方式1：使用别名

  <select id="selectAllStudents" resultType="student">
      SELECT id,name userName,age,score FROM t_student
  </select>
  

• 方式2：结果映射（写复杂SQL语句使用）

  <!--    结果映射-- column为字段，property为属性 >
      <resultMap id="studentMap" type="student">
          <!--主键-->
          <id column="id" property="id"/>
          <!--普通字段-->
          <result column="userName" property="name"/>
      </resultMap>
  
  	<select id="selectAllStudents2" resultMap="studentMap">
          SELECT id,name userName,age,score FROM t_student
      </select>
  
  

3、mybatis动态代理

• 更改mapper文件的namespace与接口进行映射（删掉StudentDao接口的实现类）

  <mapper namespace="com.ao.dao.StudentDao">
  

• test类中使用动态代理创建对象

  private StudentDao studentDao;
  
  private SqlSession sqlSession;
  
  @Before //在执行test方法之前会先调用该方法
  public void initStudentDao(){
      
      sqlSession = MyBatisUtil.getSqlSession();
      //通过mybatis动态代理创建对象
      studentDao = sqlSession.getMapper(StudentDao.class);
  }
  
  /**
   * 关闭sqlSession
   */
  @After
  public void closeSession(){
      if (sqlSession != null){
          sqlSession.close();
      }
  }
  

• 乱码在mapper顶部加上

  <?xml version="1.0" encoding="utf8"?>
  

五、动态SQL

mapper和接口分离

• 配置文件里注册映射文件

  <mappers>  
  	<!--  扫描包 dao接口文件需要与mapper文件名字相同-->
  	<package name="com.ao.dao"/>
  </mappers>
  

• 在resources里创建与包名相同的文件夹（com.ao.dao）把mapper移到文件夹里

1、if标签

<select id="selectIf" resultType="student">
    SELECT id,name,age,score
    FROM t_student
    WHERE 1=1--解决and问题
    <if test="name != null and name != ''">
        AND name LIKE '%' #{name} '%'
    </if>
    <if test="age >= 0">
        AND age > #{age}
    </if>
</select>

2、WHERE和trim标签

<select id="selectWhere" resultType="student">
    SELECT id,name,age,score
    FROM t_student
    <where>
        <if test="name != null and name != ''">
            name LIKE '%' #{name} '%'
        </if>
        <if test="age >= 0">
            AND age > #{age}
        </if>
    </where>
</select>

<select id="selectTrim" resultType="student">
    SELECT id,name,age,score
    FROM t_student
    <trim prefix="WHERE" prefixOverrides="AND | OR">
        <if test="name != null and name != ''">
            name LIKE '%' #{name} '%'
        </if>
        <if test="age >= 0">
            AND age > #{age}
        </if>
    </trim>
</select>

3、choose标签

相当于if-elseif-else

<select id="selectChoose" resultType="student">
    SELECT id,name,age,score
    FROM t_student
    <where>
        <choose>
            <when test="name != null and name != ''">
                name LIKE '%' #{name} '%'
            </when>
            <when test="age >= 0">
                age > #{age}
            </when>
            <otherwise>
                1 != 1
            </otherwise>
        </choose>
    </where>
</select>

4、set标签

<update id="updateSet">
    UPDATE t_student 
    <set>
        <if test="name != null">name=#{name},</if>
        <if test="age > 0">age=#{age},</if>
        <if test="score > 0">score=#{score}</if> 
    </set>
    WHERE id=#{id}
</update>

5、foreach标签

• 遍历数组

  <select id="selectForeachArray" resultType="student">
      SELECT id,name,age,score 
      FROM t_student
      <if test="array!=null and array.length>0">
          WHERE id IN 
          <foreach collection="array" open="(" close=")" item="id" separator=",">
              #{id}
          </foreach>
      </if>
  </select>
  

• 遍历自定义类型数组

  <select id="selectForeachListStudent" resultType="student">
      SELECT id,name,age,score
      FROM t_student
      <if test="array!=null and array.length>0">
          WHERE id IN
          <foreach collection="array" open="(" close=")" item="stu" separator=",">
              #{stu.id}
          </foreach>
      </if>
  </select>
  

6、sql标签

<select id="selectSql" resultType="student">
    --         引入sql片段
    <include refid="selectSQL"/> 
</select>

<sql id="selectSQL">
    SELECT id,name,age,score
    FROM t_student
</sql>

7、注意事项

• 符号问题

  • 方法1：

    < 	&lt;
    <=	&lt;=
    > 	&gt;
    >=	&gt;=
    & 	&amp;
    ’ 	&apos;
    "	&quot;
    

  • 方法2：

    <![CDATA[]]>
    
    <![CDATA[ < ]]>
    

六、多表查询

1、一对多关联查询

一张表的一条数据对应另一张表的多条数据（一个团队对应多个运动员）

• 实体类

  • Player

    public class Player {
        private int id;
        private String name;
    }
    

  • Team

    public class Team {
        private int id;
        private String name;
    
        //将一对多的维护关系放到一的一方
        private List<Player> playerList;
        
        //省略getter、setter和toString
    }
    

• TeamMapper.xml

  <?xml version="1.0" encoding="utf8"?>
  <!DOCTYPE mapper
          PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
          "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  <mapper namespace="com.ao.dao.TeamMapper">
      <resultMap id="teamMap" type="team">
  <!--        team中的属性映射-->
          <id column="tid" property="id"/>
          <result column="tName" property="name"/>
  
  <!--        关系属性的映射关系-->
          <collection property="playerList" ofType="Player">
              <id column="pid" property="id"/>
              <result column="pName" property="name"/>
          </collection>
      </resultMap> 
      
      <select id="selectTeamById" resultMap="teamMap">
          SELECT
              t.id tId,
              t.NAME tName,
              p.id pId,
              p.name pName
          FROM
              t_team t,
              t_player p
          WHERE
              t.id = p.tid
            AND t.id = 1
      </select>
  </mapper>
  

• TeamMapper.java

  public interface TeamMapper {
      Team selectTeamById(int id);
  }
  

• test类

  public class StudentTest {
  
      private StudentMapper studentMapper;
      private TeamMapper teamMapper;
  
      private SqlSession sqlSession;
  
      @Before //在执行test方法之前会先调用该方法
      public void initDao(){
          sqlSession = MyBatisUtil.getSqlSession();
          //通过mybatis动态代理创建对象
          teamMapper = sqlSession.getMapper(TeamMapper.class);
      } 
      /**
       * 关闭sqlSession
       */
      @After
      public void closeSession(){
          if (sqlSession != null){
              sqlSession.close();
          }
      }
  
      @Test
      public void selectTeamById(){
          Team team = teamMapper.selectTeamById(1);
          System.out.println(team.toString());
      } 
  }
  

• 输出日志

  [com.ao.dao.TeamMapper.selectTeamById]-==>  Preparing: SELECT t.id tId, t.NAME tName, p.id pId, p.name pName FROM t_team t, t_player p WHERE t.id = p.tid AND t.id = 1 
  [com.ao.dao.TeamMapper.selectTeamById]-==> Parameters: 
  [com.ao.dao.TeamMapper.selectTeamById]-<==    Columns: tId, tName, pId, pName
  [com.ao.dao.TeamMapper.selectTeamById]-<==        Row: 1, 湖人, 1, 詹姆斯
  [com.ao.dao.TeamMapper.selectTeamById]-<==        Row: 1, 湖人, 2, 戴维斯
  [com.ao.dao.TeamMapper.selectTeamById]-<==      Total: 2
  Team{id=1, name='湖人', playerList=[Player{id=1, name='詹姆斯'}, Player{id=2, name='戴维斯'}]}
  

2、多对一关系查询

相当于一对一（一个运动员对应一个团队）

• 实体类

  • Player

    public class Player {
        private int id;
        private String name;
    
        //多对一，将维护关系放到多的一方
        private Team team;
        //省略getter、setter和toString
    }
    

  • Team

    public class Team {
        private int id;
        private String name;
        //省略getter、setter和toString
    }
    

• PlayerMapper.xml

  <?xml version="1.0" encoding="utf8"?>
  <!DOCTYPE mapper
          PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
          "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  <mapper namespace="com.ao.dao.PlayerMapper">
      <resultMap id="playerMap" type="player">
          <!--        team中的属性映射-->
          <id column="pid" property="id"/>
          <result column="pName" property="name"/>
  
          <!--        关系属性的映射关系-->
          <association property="team" javaType="Team">
              <id column="tid" property="id"/>
              <result column="tName" property="name"/>
          </association>
      </resultMap> 
  
      <select id="selectPlayerById" resultMap="playerMap">
          SELECT
              t.id tId,
              t.NAME tName,
              p.id pId,
              p.name pName
          FROM
              t_team t,
              t_player p
          WHERE
              t.id = p.tid
            AND p.id = #{id}
      </select>
  
  
      <select id="selectPlayers" resultMap="playerMap">
          SELECT
              t.id tId,
              t.NAME tName,
              p.id pId,
              p.name pName
          FROM
              t_team t,
              t_player p
          WHERE
              t.id = p.tid
      </select>
  </mapper>
  

• selectPlayerById输出日志

  [com.ao.dao.PlayerMapper.selectPlayerById]-==>  Preparing: SELECT t.id tId, t.NAME tName, p.id pId, p.name pName FROM t_team t, t_player p WHERE t.id = p.tid AND p.id = ? 
  [com.ao.dao.PlayerMapper.selectPlayerById]-==> Parameters: 2(Integer)
  [com.ao.dao.PlayerMapper.selectPlayerById]-<==    Columns: tId, tName, pId, pName
  [com.ao.dao.PlayerMapper.selectPlayerById]-<==        Row: 1, 湖人, 2, 戴维斯
  [com.ao.dao.PlayerMapper.selectPlayerById]-<==      Total: 1
  Player{id=2, name='戴维斯', team=Team{id=1, name='湖人'}}
  

• selectPlayers输出日志

  [com.ao.dao.PlayerMapper.selectPlayers]-==>  Preparing: SELECT t.id tId, t.NAME tName, p.id pId, p.name pName FROM t_team t, t_player p WHERE t.id = p.tid 
  [com.ao.dao.PlayerMapper.selectPlayers]-==> Parameters: 
  [com.ao.dao.PlayerMapper.selectPlayers]-<==    Columns: tId, tName, pId, pName
  [com.ao.dao.PlayerMapper.selectPlayers]-<==        Row: 1, 湖人, 1, 詹姆斯
  [com.ao.dao.PlayerMapper.selectPlayers]-<==        Row: 1, 湖人, 2, 戴维斯
  [com.ao.dao.PlayerMapper.selectPlayers]-<==        Row: 2, 篮网, 3, 欧文
  [com.ao.dao.PlayerMapper.selectPlayers]-<==        Row: 2, 篮网, 4, 杜兰特
  [com.ao.dao.PlayerMapper.selectPlayers]-<==        Row: 3, 雷霆, 5, 保罗
  [com.ao.dao.PlayerMapper.selectPlayers]-<==      Total: 5
  Player{id=1, name='詹姆斯', team=Team{id=1, name='湖人'}}
  Player{id=2, name='戴维斯', team=Team{id=1, name='湖人'}}
  Player{id=3, name='欧文', team=Team{id=2, name='篮网'}}
  Player{id=4, name='杜兰特', team=Team{id=2, name='篮网'}}
  Player{id=5, name='保罗', team=Team{id=3, name='雷霆'}}
  

3、自关联一对多查询

自关联一对多，查询一个人的全部下属

• 实体类

  • Employee

    public class Employee {
    
        private int id;
    
        private String name;
    
        private String job;
    
        //表示多的一方，即当前员工的所有下属
        private List<Employee> children;
    
        //省略getter、setter和toString
    }
    

• Mapper接口

  public interface EmployeeDao {
  
      List<Employee> selectChildrenByPid(int mgr);
  }
  

• EmployeeMapper.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.monkey1024.dao.EmployeeDao">
  
      <!--形成递归查询-->
      <resultMap id="childrenMap" type="Employee">
          <id column="id" property="id"/>
          <result column="name" property="name"/>
          <collection property="children" ofType="Employee" select="selectChildrenByPid" column="id"/>
      </resultMap>
  
      <select id="selectChildrenByPid" resultMap="childrenMap">
          SELECT id, name ,job
          FROM t_employee
          WHERE mgr=#{pid}
      </select>
  </mapper>
  

• 日志

  [com.ao.dao.EmployeeMapper.selectChildrenById]-==>  Preparing: SELECT id,name,job FROM t_employee WHERE mgr=? 
  [com.ao.dao.EmployeeMapper.selectChildrenById]-==> Parameters: 1002(Integer)
  [com.ao.dao.EmployeeMapper.selectChildrenById]-<==    Columns: id, name, job
  [com.ao.dao.EmployeeMapper.selectChildrenById]-<==        Row: 1005, tomas, 技术一部经理
  [com.ao.dao.EmployeeMapper.selectChildrenById]-====>  Preparing: SELECT id,name,job FROM t_employee WHERE mgr=? 
  [com.ao.dao.EmployeeMapper.selectChildrenById]-====> Parameters: 1005(Integer)
  [com.ao.dao.EmployeeMapper.selectChildrenById]-<====    Columns: id, name, job
  [com.ao.dao.EmployeeMapper.selectChildrenById]-<====        Row: 1011, may, 软件工程师
  [com.ao.dao.EmployeeMapper.selectChildrenById]-======>  Preparing: SELECT id,name,job FROM t_employee WHERE mgr=? 
  [com.ao.dao.EmployeeMapper.selectChildrenById]-======> Parameters: 1011(Integer)
  [com.ao.dao.EmployeeMapper.selectChildrenById]-<======      Total: 0
  [com.ao.dao.EmployeeMapper.selectChildrenById]-<====        Row: 1012, bella, 软件工程师
  [com.ao.dao.EmployeeMapper.selectChildrenById]-======>  Preparing: SELECT id,name,job FROM t_employee WHERE mgr=? 
  [com.ao.dao.EmployeeMapper.selectChildrenById]-======> Parameters: 1012(Integer)
  [com.ao.dao.EmployeeMapper.selectChildrenById]-<======      Total: 0
  [com.ao.dao.EmployeeMapper.selectChildrenById]-<====      Total: 2
  [com.ao.dao.EmployeeMapper.selectChildrenById]-<==        Row: 1006, linda, 技术二部经理
  [com.ao.dao.EmployeeMapper.selectChildrenById]-====>  Preparing: SELECT id,name,job FROM t_employee WHERE mgr=? 
  [com.ao.dao.EmployeeMapper.selectChildrenById]-====> Parameters: 1006(Integer)
  [com.ao.dao.EmployeeMapper.selectChildrenById]-<====    Columns: id, name, job
  [com.ao.dao.EmployeeMapper.selectChildrenById]-<====        Row: 1013, kelly, 软件工程师
  [com.ao.dao.EmployeeMapper.selectChildrenById]-======>  Preparing: SELECT id,name,job FROM t_employee WHERE mgr=? 
  [com.ao.dao.EmployeeMapper.selectChildrenById]-======> Parameters: 1013(Integer)
  [com.ao.dao.EmployeeMapper.selectChildrenById]-<======      Total: 0
  [com.ao.dao.EmployeeMapper.selectChildrenById]-<====      Total: 1
  [com.ao.dao.EmployeeMapper.selectChildrenById]-<==      Total: 2
  Employee{id=1005, name='tomas', job='技术一部经理', children=[Employee{id=1011, name='may', job='软件工程师', children=[]}, Employee{id=1012, name='bella', job='软件工程师', children=[]}]}
  Employee{id=1006, name='linda', job='技术二部经理', children=[Employee{id=1013, name='kelly', job='软件工程师', children=[]}]}
  

自关联一对多，将自己的数据也查询出来

• EmployeeMapper.xml

  <resultMap id="childrenMap" type="employee">
      <id column="id" property="id"/>
      <result column="name" property="name"/>
  <!--        形成递归查询-->
      <collection property="children" ofType="employee" select="selectChildrenById" column="id"/>
  </resultMap>
  
  
  <select id="selectChildrenById" resultMap="childrenMap">
      SELECT id,name,job FROM t_employee WHERE mgr=#{pid}
  </select>
  
  <select id="selectEmployeeByPid" resultMap="childrenMap">
      SELECT id, name ,job
      FROM t_employee
      WHERE id=#{id}
  </select>
  

• EmployeeMapper

  List<Employee> selectEmployeeByPid(int mgr);
  

4、自关联多对一查询

自关联一对多，查询一个人的全部领导

• 实体类

  • Employee

    public class Employee {
    
        private int id;
    
        private String name;
    
        private String job;
    
        //该员工的直接领导
        private Employee leader;
    }
    

• Mapper接口

  public interface EmployeeMapper { 
      Employee selectLeaderById(int id);
  }
  

• EmployeeMapper.xml

  <resultMap id="leaderMap" type="employee">
      <id column="id" property="id"/>
      <result column="name" property="name"/>
      <!--        形成递归查询-->
      <association property="leader" javaType="employee" select="selectLeaderById" column="mgr"/>
  </resultMap>
  
  <select id="selectLeaderById" resultMap="leaderMap">
      SELECT id,name,job,mgr FROM t_employee WHERE id=#{mgr}
  </select>
  

5、多对多关联查询

一个课程对应多个学生

• 实体类

  • Course

    public class Course {
    
        private int id;
    
        private String name;
    
        private List<Student> students;
    
        //省略getter、setter和toString
    }
    

• CourseMapper.xml

  <resultMap id="courseMapper" type="Course">
      <id column="cid" property="id"/>
      <result column="cname" property="name"/>
  
      <collection property="studentList" ofType="student">
          <id column="sid" property="id"/>
          <result column="sname" property="name"/>
      </collection>
   </resultMap>
  
  <select id="selectCourseStudent" resultMap="courseMapper">
      SELECT
          c.id cid,
          c.NAME cname,
          s.id sid,
          s.NAME sname
      FROM
          t_student s,
          t_course c,
          t_student_course sc
      WHERE
          c.id = #{id}
          AND s.id = sc.sid
          AND c.id = sc.cid
  </select>
  

• CourseMapper

  public interface CourseMapper {
      Course selectCourseStudent(int id);
  }
  

• 日志

  [com.ao.dao.CourseMapper.selectCourseStudent]-==>  Preparing: SELECT c.id cid, c.NAME cname, s.id sid, s.NAME sname FROM t_student s, t_course c, t_student_course sc WHERE c.id = ? AND s.id = sc.sid AND c.id = sc.cid 
  [com.ao.dao.CourseMapper.selectCourseStudent]-==> Parameters: 1001(Integer)
  [com.ao.dao.CourseMapper.selectCourseStudent]-<==    Columns: cid, cname, sid, sname
  [com.ao.dao.CourseMapper.selectCourseStudent]-<==        Row: 1001, SpringMVC, 1, ao
  [com.ao.dao.CourseMapper.selectCourseStudent]-<==        Row: 1001, SpringMVC, 3, paul
  [com.ao.dao.CourseMapper.selectCourseStudent]-<==        Row: 1001, SpringMVC, 4, gh
  [com.ao.dao.CourseMapper.selectCourseStudent]-<==      Total: 3
  Course{id=1001, name='SpringMVC', studentList=[Student{id=1, name='ao', age=0, score=0.0, courseList=null}, Student{id=3, name='paul', age=0, score=0.0, courseList=null}, Student{id=4, name='gh', age=0, score=0.0, courseList=null}]}
  

七、延迟加载

• 直接加载：执行完对主加载对象的 select 语句，马上执行对关联对象的 select 查询。
• 侵入式延迟： 执行对主加载对象的查询时，不会执行对关联对象的查询。但当要访问主加载对象的详情属性时，就会马上执行关联对象的select查询。
• 深度延迟： 执行对主加载对象的查询时，不会执行对关联对象的查询。访问主加载对象的详情时也不会执行关联对象的select查询。只有当真正访问关联对象的详情时，才会执行对关联对象的 select 查询。

<resultMap id="teamMapAlone" type="team">
    <!--        team中的属性映射-->
    <id column="id" property="id"/>
    <result column="name" property="name"/>

    <!--        关系属性的映射关系  fetchType值为lazy时开启深度延迟，eager不开启深度延迟-->
    <collection property="playerList" ofType="Player" fetchType="lazy" select="selectPlayerByTeamId" column="id"/>
</resultMap>
<select id="selectTeamByIdAlone" resultMap="teamMapAlone">
    SELECT id,name FROM t_team WHERE id=#{id}
</select>
<select id="selectPlayerByTeamId" resultType="player">
    SELECT id,name FROM t_player WHERE tid=#{id}
</select>

1、直接加载

selectTeamByIdAlone和selectPlayerByTeamId都加载

2、侵入式加载

mybatis.xml

<configuration>

<!--    注册配置文件-->
    <properties resource="db.properties"/>

    <!--全局参数设置-->
    <settings>
        <!--延迟加载总开关-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!--侵入式延迟加载开关-->
        <!--3.4.1版本之前默认是true，之后默认是false-->
        <setting name="aggressiveLazyLoading" value="true"/>
    </settings>

• 测试1（只加载selectTeamByIdAlone）

  @Test
  public void selectTeamByIdAlone() {
      Team team = teamDao.selectTeamByIdAlone(1);
  }
  

• 测试2（selectTeamByIdAlone和selectPlayerByTeamId都加载）

  @Test
  public void selectTeamByIdAlone() {
      Team team = teamDao.selectTeamByIdAlone(1);
      System.out.println(team.getName());
  }
  

3、深度加载

mybatis.xml

<configuration>

<!--    注册配置文件-->
    <properties resource="db.properties"/>

    <!--全局参数设置-->
    <settings>
        <!--延迟加载总开关-->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!--侵入式延迟加载开关-->
        <!--3.4.1版本之前默认是true，之后默认是false-->
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>

• 测试1（只加载selectTeamByIdAlone）

  @Test
  public void selectTeamByIdAlone() {
      Team team = teamDao.selectTeamByIdAlone(1);
      System.out.println(team.getName());
  }
  

• 测试2（当调用player时才加载selectPlayerByTeamId）

  @Test
  public void selectTeamByIdAlone(){
      Team team = teamMapper.selectTeamByIdAlone(1);
      System.out.println(team.getName());
      System.out.println(team.getPlayerList());
  }
  

八、缓存

1、一级缓存

MyBatis的一级查询缓存（也叫作本地缓存）是基于org.apache.ibatis.cache.impl.PerpetualCache 类的 HashMap本地缓存，其作用域是SqlSession，即在同一个SqlSession中两次执行相同的 sql 查询语句，第一次执行完毕后，会将查询结果写入到缓存中，第二次会从缓存中直接获取数据，而不再到数据库中进行查询，这样就减少了数据库的访问，从而提高查询效率。
当一个 SqlSession 结束后，该 SqlSession 中的一级查询缓存也就不存在了。 myBatis 默认一级查询缓存是开启状态，且不能关闭。

• 增删改对一级缓存的影响

  mybatis会为selectStudentById建立缓存，那在下一次访问该数据的时候会直接从缓存中获取，倘若在这期间，建立缓存后，下次访问前，对数据进行了增删改的操作，此时无论是否commit，都会清空一级缓存。

2、内置二级缓存

myBatis 内置的二级缓存为 org.apache.ibatis.cache.impl.PerpetualCache。与一级缓存不同的是二级缓存的生命周期会与整个应用同步，与sqlSession是否关闭没有关系。二级缓存的使用比较简单，只需对之前的程序稍作修改即可。

• 设置方法

  • 将javabean实现Serializable接口，如果该javabean有显示的父类的话，让父类也实现Serializable接口。

  • 在mapper配置文件中的mapper标签下添加下面标签：

    <cache/>
    

• 验证内置二级缓存

  二级缓存的声明周期会与整个应用同步，而一级缓存只在sqlSession域中有效，此时需要将测试代码修改如下，将sqlSession关闭，让一级缓存失效，从而验证二级缓存的存在。

• 二级缓存的关闭

  根据关闭的范围大小，可以分为全局关闭与局部关闭。

  • 全局关闭
    全局关闭是将整个应用的二级缓存全部关闭，所有查询均不使用二级缓存。全局开关设置在mybatis.xml配置文件的全局设置中，将属性cacheEnabled设置为 false，则关闭；设置为 true，则开启，默认值为 true。即二级缓存默认是开启的。

    <setting name="cacheEnabled" value="false"/>
    

  • 局部关闭
    局部关闭是只关闭某个select查询的二级缓存，在select标签中将属性useCache设置为false，那么就会关闭该select查询的二级缓存。

    <select id="selectStudentById" useCache="false" resultMap="studentMapper">
        SELECT id,name,age,score,password FROM t_student where id=#{id}
    </select>
    

• 二级缓存的使用注意事项

  • 在一个命名空间下使用二级缓存
    二级缓存对于不同的命名空间namespace的数据是互不干扰的，倘若多个namespace中对一个表进行操作的话，就会导致这不同的namespace中的数据不一致的情况。
  • 在单表上使用二级缓存
    在做关联关系查询时，就会发生多表的操作，此时有可能这些表存在于多个namespace中，这就会出现上一条内容出现的问题了。
  • 查询多于修改时使用二级缓存
    在查询操作远远多于增删改操作的情况下可以使用二级缓存。因为任何增删改操作都将刷新二级缓存，对二级缓存的频繁刷新将降低系统性能。

3、外置二级缓存

mybatis除了自带的二级缓存，还支持一些第三方的缓存，并且由于mybatis只擅长sql，所以这些第三方缓存的性能要比mybatis的好一些，下面以ehCache为例看下第三方的二级缓存使用方式。
ehCache是一款知名的缓存框架，hibernate框架的默认缓存策略使用的就是ehCache。
使用ehCache二级缓存，实体类无需实现Serializable接口。

• 添加ehCache依赖jar包

  需要添加ehCache和mybatis-ehCache整合两个jar包，除此之外ehcache使用了slf4j记录日志，所以需要导入slf4j相关的jar包，需要注意的是目前mybatis只支持ehcache的2.x的版本

      <dependency>
          <groupId>net.sf.ehcache</groupId>
          <artifactId>ehcache</artifactId>
          <version>2.10.0</version>
      </dependency>
      <dependency>
          <groupId>org.mybatis.caches</groupId>
          <artifactId>mybatis-ehcache</artifactId>
          <version>1.1.0</version>
      </dependency>
  
      <dependency>
          <groupId>org.slf4j</groupId>
          <artifactId>slf4j-api</artifactId>
          <version>1.7.25</version>
      </dependency>
      <dependency>
          <groupId>org.slf4j</groupId>
          <artifactId>slf4j-log4j12</artifactId>
          <version>1.7.25</version>
      </dependency>
  

• 添加ehcache.xml配置文件

  在resources目录下创建ehcache.xml文件，里面填写下面内容，配置项是ehcache2.x的，在ehcache3.x中会不同，所以下面内容简单了解即可：

  <ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="../config/ehcache.xsd">
  
      <diskStore path="java.io.tmpdir"/>
  
      <defaultCache
              maxElementsInMemory="10000"
              eternal="false"
              timeToIdleSeconds="120"
              timeToLiveSeconds="120"
              maxElementsOnDisk="10000000"
              diskExpiryThreadIntervalSeconds="120"
              memoryStoreEvictionPolicy="LRU">
          <persistence strategy="localTempSwap"/>
      </defaultCache>
  </ehcache>
  

• 启用 ehcache 缓存

  • 在映射文件的 mapper 中cache中通过 type 指定缓存机制为 Ehcache 缓存

    <cache type="org.mybatis.caches.ehcache.EhcacheCache"/>
    

  • 除此之外还可以在不同的mapper中进行个性化的设置：

      <cache type="org.mybatis.caches.ehcache.EhcacheCache"/>
        <property name="timeToIdleSeconds" value="3600"/>
        <property name="timeToLiveSeconds" value="3600"/>
        <property name="maxEntriesLocalHeap" value="1000"/>
        <property name="maxEntriesLocalDisk" value="10000000"/>
        <property name="memoryStoreEvictionPolicy" value="LRU"/>
      </cache>