MyBatis基础笔记

一、mybatis配置

1、maven导入mybatis的包

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.2</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.46</version>
</dependency>

2、mybatis核心配置文件

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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/phpdemo?useSSL=true&amp;useUnicode=true&amp;charactEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value=""/>
            </dataSource>
        </environment>
    </environments>
	<!-- 注册mapper.xml文件 -->
    <mappers>
        <mapper resource="com/wei/dao/CollegeMapper.xml" />
    </mappers>
</configuration>

3、数据映射mapper.xml

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

<!-- namespace -->
<mapper namespace="com.wei.dao.CollegeMapper">
    <!-- id属性对应的是CollegeMapper接口的方法名 -->
    <!-- resultType 对应的是返回值的映射类-->
    <select id="getCollegeList" resultType="com.wei.pojo.College">
        select * from phpdemo.college
    </select>
</mapper>

4、将mybais的死代码提成工具类

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;

public class Database
{

  private static SqlSessionFactory sqlSessionFactory;
  static{
    //mybatis核心配置路径
    String resource = "mybatis-config.xml";
    try {
      InputStream in = Resources.getResourceAsStream(resource);
      sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
    } catch (IOException e) {
      e.printStackTrace();
    }
  }
  //获取Sqlsession
  public static SqlSession getSqlSession(){
    //return sqlSessionFactory.openSession(true);   如果设置为true则事务为不会自动开启
    return sqlSessionFactory.openSession();
  }
}

5、让maven读取src文件下的资源文件，在pom.xml加上

<build>
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.xml</include>
            </includes>
            <filtering>true</filtering>
        </resource>
    </resources>
</build>

二、mybatis的XML基本操作

1、mapper.java

import com.wei.pojo.College;

import java.util.List;
import java.util.Map;

public interface CollegeMapper {
  //获取所有的数据
  List<College> getCollegeList();
  //通过id获取数据
  College getCollege(int id);

  //插入数据
  int insertCollege(College college);
  //删除数据
  int deleteCollege(int id);

  //通过map数据类型插入数据
  int insertCollege2(Map map);

}

2、mapper.xml

resultType: 返回值类型

parameterType：参数类型，如果是类可以直接获取属性，如果是map可以直接获得键，默认是int类型

id: 该属性值跟mapper接口方法名绑定，因为id唯一，所以接口的方法不能够重载

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wei.dao.CollegeMapper">
    <select id="getCollegeList" resultType="com.wei.pojo.College">
        select * from college
    </select>

    <select id="getCollege" parameterType="int" resultType="com.wei.pojo.College">
        select * from phpdemo.college where id=#{id}
    </select>
    <insert id="insertCollege" parameterType="com.wei.pojo.College">
        insert into phpdemo.college (id,collegeName) values (#{id},#{collegeName});
    </insert>

    <insert id="insertCollege2" parameterType="map">
        insert into phpdemo.college (id,collegeName) values (#{CollegeId},#{CollegeName})
    </insert>

    <delete id="deleteCollege" parameterType="int">
        delete from phpdemo.college where id=#{id}
    </delete>
</mapper> 

3、mybatis-config.xml引用db.properties文件

在开头写入

<properties resource="db.properties"/>

然后可以直接用${var}调用

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/phpdemo?useSSL=true&useUnicode=true&charactEncoding=UTF-8
username=root
password=

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"/>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value=""/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!-- <mapper resource="com/wei/dao/*Mapper.xml"/>  这样可以引用所有Mapper.xml文件-->
        <mapper resource="com/wei/dao/CurriculumMapper.xml"/>
    </mappers>
</configuration>

4、别名（Alias）

只能给映射类起别名， mapper 的namespace属性无法识别别名

<typeAliases>
    <!-- 直接给类起别名 -->
    <typeAlias type="com.wei.pojo.Curriculum" alias="Curr"/>
    <!-- 自动扫描包 -->
    <package name="com.wei.pojo"/>
</typeAliases>

给类直接起别名，在mapper可以直接用

<mapper namespace="com.wei.dao.CurriculumMapper">
    <select id="getCurriculumList" resultType="Curriculum">
        select * from curriculum
    </select>
</mapper>

自动扫描包可以不用写包名，可以直接识别包下的所有类，默认使用原类名、同时可以忽略大小写。

也可以在类上加@Alias注解修改名字，这样自动扫描包就使用@Alias的名字，也不分大小写。

import org.apache.ibatis.type.Alias;

@Alias("curriculum")
public class Curriculum {

}

默认的别名：

别名	类型
_byte	byte
_long	long
_int	int
_long	long
_short	short
_integer	int
_double	double
_float	float
_boolean	boolean
string	String
byte	Byte
long	Long
int	Integer
integer	Integer
double	Double
map	Map
float	Float
boolean	Boolean
date	Date
decimal	BigDecimal
bigdecimal	BigDecimal
object	Object
hashmap	Hashmap
list	List
arraylist	ArrayList
collection	Collection
iterator	Iterator

5、生命周期

1.SqlSessionFactoryBuilder

该类用来创造工厂类SqlSessionFactory的通常是创建以后就不需要了，所以可以用局部变量

2.SqlSessionFactory

该类创建一直存活到程序结束，可以不断的创建SqlSession类，当SqlSession使用完后可以回收。

3.SqlSession

该类可以获取映射表的创建mapper的子类（应该是通过反射创建），来执行执行方法，用完后

Close被SqlSessionFactory回收。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-edCodGq5-1637553355531)(C:\Users\PC-WEI\AppData\Roaming\Typora\typora-user-images\image-20211118200212290.png)]

6、映射类属性名字跟跟数据库名字不一致时的解决方案

1.起别名

<select id="getCurriculumList" resultType="curriculum">
    select id,week_num,teacher_id,class_section as ClassSection from curriculum
</select>

该方法使用sql的层面来解决

2.使用resultMap

<resultMap id="Curr" type="curriculum">
    <!-- coulumn是数据库属性  property是映射表属性 -->
    <result column="class_section" property="ClassSection"/>
</resultMap>

<select id="getCurriculumList" resultMap="Curr">
    select * from curriculum
</select>

该方法是利用mybatis框架层面来解决

7、日志工厂

让我们可以更清楚看到mybatis内部的执行过程，并且找到到我们编写的错误

在mybatis-config.xml配置

<settings>
    <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>

STDOUT_LOGGING是其中一种。

8、利用map参数实现分页

mapper.xml

<select id="getCurriculumLimit" resultMap="Curr" parameterType="map">
    select * from curriculum limit ${startIndex},${pageSize}
</select>

map.put("startIndex",0);
map.put("pageSize",4);
List<Curriculum> curriculumLimit = mapper.getCurriculumLimit(map);

本质通过数据库层面limit实现分页

三、使用注解开发

public interface CurriculumMapper {


  @Select("select * from curriculum where id=#{id}")
  List<Curriculum> getCurriculumList(int id);
}

1.核心文件不需要引用mapper.xml文件了，只需要引用class就可以实现

<mappers>
    <mapper class="com.wei.dao.CurriculumMapper"/>
</mappers>

但是注解开发一般只能在简单的查询下使用，一旦出现多表查询将不适合注解了

@Select("select * from curriculum where id=#{id} and name=#{name}")
List<Curriculum> getCurriculumList(@Param("id") int id,@Param("id") String name);

当使用多个参数是必须加上@Param注解，mybatis层面不然无法识别

四、使用resultMap进行连表查询

1.多对一处理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gE8OzxNV-1637553355533)(C:\Users\PC-WEI\AppData\Roaming\Typora\typora-user-images\image-20211119164127328.png)]

（1）查询嵌套处理（子查询）

Student.java

package com.wei.pojo;

import lombok.Data;

@Data
public class Student {


  private int id;
  private String name;
  private Teacher teacher;
}

Teacher.java

package com.wei.pojo;

import lombok.Data;

@Data
public class Teacher {

  private int id;

  private String name;
}

Student.xml

<mapper namespace="com.wei.dao.StudentMapper">
    <resultMap id="StudentAndTeacher" type="Student">
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacherList"/>
    </resultMap>

    <select id="getStudentList" resultMap="StudentAndTeacher">
        select * from student
    </select>

    <select id="getTeacherList" resultType="Teacher">
        select * from teacher
    </select>
</mapper>

association是处理复杂对象的

因为Student映射表关联的是老师的类，但数据库表只是记录teacher表的id属性，所以在association需要一个子查询

<association property="teacher" column="tid" javaType="Teacher" select="getTeacherList"/>

property:映射类的属性名

colum:记录的是父表的键

javaType:记录的是映射类的属性类型

select:通过另一个select查询

<select id="getTeacher" resultType="Teacher">
    select * from teacher where id=#{id}
</select>

colum会当成传进参数传进去子查询，如果不需要，这个查询可以不需要在mapper.java接口文件写声明方法

（2）结果集嵌套处理（连表查询）

<select id="getStudentList2" resultMap="StudentAndTeacher2">
    select s.id sid, s.name sname,t.name tname,t.id tid from student s,teacher t where s.tid=t.id
</select>

<resultMap id="StudentAndTeacher2" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher" javaType="Teacher">
        <result property="name" column="tname"/>
        <result property="id" column="tid"/>
    </association>
</resultMap>

从这里可以看出

association也有resultMap的功能，映射表和bean的属性

property的属性值是属于父标签的，

但是colum是属于resultmap的，是查询的resultmap内怎样使用都是查询到的数据库属性

2.一对多处理

teacher.java

package com.wei.pojo;
import lombok.Data;
import java.util.List;
@Data
public class Teacher {
  private int id;
  private String name;
  private List<Student> students;
}

需求：通过查找teacher的id获取该老师所有信息和老师的学生所有的信息

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cvAXdFKS-1637553355534)(C:\Users\PC-WEI\AppData\Roaming\Typora\typora-user-images\image-20211119192414064.png)]

（1）查询嵌套处理（子查询）

<select id="getTeacher2" resultMap="getTeacher2">
    select id,name from teacher where id=#{tid}
</select>

<resultMap id="getTeacher2" type="Teacher">
    <collection property="students"  ofType="Student" column="id" select="getStudentByTeacherId"/>
</resultMap>

<select id="getStudentByTeacherId" resultType="Student">
    select * from student where tid=#{tid}
</select>

ofType:是表示集合内的泛型类型

（2）结果集嵌套处理（连表查询）

<select id="getTeacher" resultMap="getTeacher">
    select s.id sid,s.name sname,
    t.id tid,t.name tname
    from teacher t,student s
    where s.tid=t.id and t.id=#{tid}
</select>

<resultMap id="getTeacher" type="Teacher">
    <result column="tid" property="id"/>
    <result property="name" column="tname"/>
    <collection property="students" ofType="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="tid"/>
    </collection>
</resultMap>

3.总结

结果集嵌套映射与查询嵌套映射，结果集嵌套映射的可读性更高更推荐使用结果集嵌套映射。

一对多 : association ,javaType

多对一： collection ,ofType

colum是属于数据库属性的，是查询的resultmap内怎样使用colum都是查询到的数据库属性

在查询嵌套也可以使用colum来作为参数传进子查询

五、动态Sql

所谓动态Sql加上通过动态的改变sql语句使java内的逻辑更加简单。

例如：

if

mapper.xml

<select id="getTeacher" resultType="Teacher" parameterType="map">
    select * from teacher_1 where 1=1
    <if test="id !=null">
        and id=#{id}
    </if>
    <if test="name !=null">
        and name=#{name}
    </if>
</select>

实例

@Test
public void test(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();

    TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);

    HashMap<String, String> map = new HashMap<>();

    map.put("name","王五");
    
    List<Teacher> teacher = mapper.getTeacher(map);

    for (Teacher teacher1 : teacher) {
        System.out.println(teacher);
    }

    sqlSession.close();
}

我可以通过java的map集合put的值来动态的改变sql，需要查什么就put什么。

where

但是为了让sql拼接的完整，我们要加 where 1=1，为了让程序更优雅，就出现了标签

<select id="getTeacher2" resultType="Teacher" parameterType="map">
    select * from teacher_1
    <where>
        <if test="id !=null">
            id=#{id}
        </if>
        <if test="name !=null">
            and name=#{name}
        </if>
    </where>
</select>

这个标签还可以智能的识别where前面是否有and或or，可以帮我们直接去掉.

shoose、when、otherwise

既然有if那肯定少不了开关语句。

shoose、when、otherwise就跟java学的switch、case、default三兄弟一样，但是shoose、when、otherwise不需要写beack，不会有贯穿特性。

<select id="getTeacher3" resultType="Teacher" parameterType="map">
    select * from teacher_1
    <where>
        <choose>
            <when test="id!=null">
                id=#{id}
            </when>
            <when test="name!=null">
                and name=#{name}
            </when>
            <otherwise>
                and name="王五"
            </otherwise>
        </choose>
    </where>
</select>

set

set跟where有点相似

会自动帮我们去掉不规范的逗号

<update id="setTeacher" parameterType="map">
    update teacher_1
    <set>
        <if test="name!=null">
            name=#{name},
        </if>
        <if test="age!=null">
            age=#{age},
        </if>
    </set>
    where id=#{id}
</update>

Trim

Trim可以说是Where和set的父类

<update id="setTeacher2" parameterType="map">
    update teacher_1
    <trim prefix="set" suffixOverrides=",">
        <if test="name!=null">
            name=#{name},
        </if>
    </trim>
    where id=#{id}
</update>

一般我们不需要使用，有set和where就够用了

include

为了提高sql代码的复用性官方提供了标签和标签

<sql id="getTeacherByNameOrId">
    <if test="id!=null">
        id=#{id}
    </if>
    <if test="name!=null">
        and name=#{name}
    </if>
</sql>

<select id="getTeacher" parameterType="map" resultType="Teacher">
    select * from teacher_1
    <where>
        <include refid="getTeacherByNameOrId"/>
    </where>
</select>

sql标签跟select标签同级，

sql以id作为标识，在内引用。

但是建议不要把写在sql标签内，这样会导致where的智能去除and或or的功能失去作用，一般在sql内写if标签就好了。

foreach

这个标签可以遍历的拼接字符，前提是给它的参数是一个集合

<select id="getTeacher2" parameterType="map" resultType="Teacher">
    select * from teacher_1
    <where>
        1=2 or
        <if test="ids!=null">
            id in
            <foreach collection="ids" index="index" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </if>
    </where>
</select>

如果map内的ids集合有2和1

那么拼接的sql语句是：

select * from teacher_1 WHERE 1=2 or id in ( 1 , 2 ) 

参数

collection：要传的参数（集合）

index：（下标）

item：集合的内容

open：在开头拼接的字符

close：在最后拼接的字符

separator：在item之间拼接的字符

总结

动态sql本质是拼接sql语句，我们只要保证sql的正确性，按照sql的格式去排列组合就可以了。

6、缓存

所谓缓存也就是将数据库内的数据暂时存储在程序内，当多次查询相同数据时，可以减少向数据库读取信息的次数。

而缓存又分为一级缓存和二集缓存。

一级缓存

public void test(){

    SqlSession sqlSession = MybatisUtils.getSqlSession();

    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    User userByid = mapper.getUserByid(1);

    System.out.println(userByid);


    User userByid2 = mapper.getUserByid(1);

    System.out.println(userByid);

    System.out.println(userByid==userByid2);
    sqlSession.close();
}

在获取SqlSession到Close的期间是一级缓存的生命周期，上面我在周期内查询两次相同的数据。在控制台显示：

Reader entry: <?xml version="1.0" encoding="UTF-8" ?>
Checking to see if class com.wei.dao.UserMapper matches criteria [is assignable to Object]
Opening JDBC Connection
Created connection 510063093.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e66f1f5]
==>  Preparing: select id,name,password from `user` where id=?; 
==> Parameters: 1(Integer)
<==    Columns: id, name, password
<==        Row: 1, adsfdas, 123456
<==      Total: 1
User(id=1, name=adsfdas, pwd=123456)
User(id=1, name=adsfdas, pwd=123456)
true
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e66f1f5]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e66f1f5]

可以看出只做了一次查询而且获取的映射对象是同一个。

二集缓存

二级缓存是在sqlsessionClose是将一级缓存的内容保存在mapper中，周期可以自己设置

public void test(){

    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User userByid = mapper.getUserByid(1);
    System.out.println(userByid);
    sqlSession.close();

    SqlSession sqlSession2 = MybatisUtils.getSqlSession();
    UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class);
    User userByid2 = mapper2.getUserByid(1);
    System.out.println(userByid2);
    sqlSession2.close();


    System.out.println(userByid==userByid2);
}

Opening JDBC Connection
Created connection 510063093.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e66f1f5]
==>  Preparing: select id,name,password from `user` where id=?; 
==> Parameters: 1(Integer)
<==    Columns: id, name, password
<==        Row: 1, adsfdas, 123456
<==      Total: 1
User(id=1, name=adsfdas, pwd=123456)
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e66f1f5]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e66f1f5]
Returned connection 510063093 to pool.
Opening JDBC Connection
Checked out connection 510063093 from pool.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e66f1f5]
==>  Preparing: select id,name,password from `user` where id=?; 
==> Parameters: 1(Integer)
<==    Columns: id, name, password
<==        Row: 1, adsfdas, 123456
<==      Total: 1
User(id=1, name=adsfdas, pwd=123456)
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e66f1f5]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@1e66f1f5]

一级缓存是默认开启的，无法关闭，因为一级缓存太短了，如果在一个网站内有多个客户端访问同一个数据。一级缓存则不起作用，

需要开启二级缓存。

开启二级缓存只需要在mapper.xml文件加上一个cache标签

<cache/>

还有几个参数

<cache
  eviction="FIFO"
  flushInterval="60000"
  size="512"
  readOnly="true"/>

eviction:缓存策略，默认是FIFO，先进先出

flushInterval:缓存周期时间，毫秒为单位，默认是60秒

size:缓存引用的大小，默认是512个引用

readOnly：只读，如果每日设置则要在映射类继承序列化接口Serializable，否则会报错

当开启二集缓存后，再运行上面的代码

Cache Hit Ratio [com.wei.dao.UserMapper]: 0.0
Opening JDBC Connection
Created connection 1955920234.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7494f96a]
==>  Preparing: select id,name,password from `user` where id=?; 
==> Parameters: 1(Integer)
<==    Columns: id, name, password
<==        Row: 1, adsfdas, 123456
<==      Total: 1
User(id=1, name=adsfdas, pwd=123456)
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7494f96a]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7494f96a]
Returned connection 1955920234 to pool.
Cache Hit Ratio [com.wei.dao.UserMapper]: 0.5
User(id=1, name=adsfdas, pwd=123456)
true

发现这次还是查询了一次，

但是如果在期间内进行了增删改并且提交了事务时，缓存会自动清空

public void test(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User userByid = mapper.getUserByid(1);
    System.out.println(userByid);
    sqlSession.close();


    SqlSession sqlSession1 = MybatisUtils.getSqlSession();
    UserMapper mapper1 = sqlSession1.getMapper(UserMapper.class);
    int num = mapper1.addUser(new User(5, "test", "123456"));
    System.out.println(num);
    sqlSession1.commit();
    sqlSession1.close();

    SqlSession sqlSession2 = MybatisUtils.getSqlSession();
    UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class);
    User userByid2 = mapper2.getUserByid(1);
    System.out.println(userByid2);
    sqlSession2.close();

    System.out.println(userByid==userByid2);
}

结果是

Opening JDBC Connection
Created connection 1955920234.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7494f96a]
==>  Preparing: select id,name,password from `user` where id=?; 
==> Parameters: 1(Integer)
<==    Columns: id, name, password
<==        Row: 1, adsfdas, 123456
<==      Total: 1
User(id=1, name=adsfdas, pwd=123456)
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7494f96a]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7494f96a]
Returned connection 1955920234 to pool.
Opening JDBC Connection
Checked out connection 1955920234 from pool.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7494f96a]
==>  Preparing: insert into user(id,name,password) values ( ?, ?, ? ) 
==> Parameters: 5(Integer), test(String), 123456(String)
<==    Updates: 1
1
Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7494f96a]
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7494f96a]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7494f96a]
Returned connection 1955920234 to pool.
Cache Hit Ratio [com.wei.dao.UserMapper]: 0.0
Opening JDBC Connection
Checked out connection 1955920234 from pool.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7494f96a]
==>  Preparing: select id,name,password from `user` where id=?; 
==> Parameters: 1(Integer)
<==    Columns: id, name, password
<==        Row: 1, adsfdas, 123456
<==      Total: 1
User(id=1, name=adsfdas, pwd=123456)
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@7494f96a]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7494f96a]
Returned connection 1955920234 to pool.
false

可以发现查询了两次，且获得的映射对象不相等。

还有在不同mapper接口内查询相同数据则不生效。

如果有两个mapper，两mapper关联的是同一张表，在mapper1进行查询，然后mapper2进行增删改，然后再到mapper1查询，到底会不会清空一二级缓存呢？

先看看一级缓存

public void test(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User userByid = mapper.getUserByid(1);
    System.out.println(userByid);

    //使用另一个mapper
    SqlSession sqlSession3 = MybatisUtils.getSqlSession();
    UserMapper2 mapper3 = sqlSession3.getMapper(UserMapper2.class);
    int num = mapper3.deleteUserById(1);
    System.out.println(num);
    sqlSession3.commit();
    sqlSession3.close();

    //删除后再查询一下
    User userByid2 = mapper.getUserByid(1);
    System.out.println(userByid==userByid2);
    sqlSession.close();
}

结果：

Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@757277dc]
==>  Preparing: select id,name,password from `user` where id=?; 
==> Parameters: 1(Integer)
<==    Columns: id, name, password
<==        Row: 1, adsfdas, 123456
<==      Total: 1
User(id=1, name=adsfdas, pwd=123456)
Opening JDBC Connection
Created connection 263885523.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@fba92d3]
==>  Preparing: delete from `user` where id=? 
==> Parameters: 1(Integer)
<==    Updates: 1
1
Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@fba92d3]
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@fba92d3]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@fba92d3]
Returned connection 263885523 to pool.
Cache Hit Ratio [com.wei.dao.UserMapper]: 0.0
true

查询结果可以看出，缓存没有被清理，

再看看二级缓存

public void test2(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User userByid = mapper.getUserByid(2);
    System.out.println(userByid);
    sqlSession.close();

    SqlSession sqlSession3 = MybatisUtils.getSqlSession();
    UserMapper2 mapper3 = sqlSession3.getMapper(UserMapper2.class);
    int num = mapper3.deleteUserById(2);
    System.out.println(num);
    sqlSession3.commit();
    sqlSession3.close();

    SqlSession sqlSession2 = MybatisUtils.getSqlSession();
    UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class);
    User userByid2 = mapper2.getUserByid(2);
    System.out.println(userByid==userByid2);
    sqlSession2.close();
}

输出结果为:

Opening JDBC Connection
Created connection 1970436060.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@757277dc]
==>  Preparing: select id,name,password from `user` where id=?; 
==> Parameters: 3(Integer)
<==    Columns: id, name, password
<==        Row: 3, hgewt, 123456
<==      Total: 1
User(id=3, name=hgewt, pwd=123456)
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@757277dc]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@757277dc]
Returned connection 1970436060 to pool.
Opening JDBC Connection
Checked out connection 1970436060 from pool.
Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@757277dc]
==>  Preparing: delete from `user` where id=? 
==> Parameters: 3(Integer)
<==    Updates: 1
1
Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@757277dc]
Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@757277dc]
Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@757277dc]
Returned connection 1970436060 to pool.
Cache Hit Ratio [com.wei.dao.UserMapper]: 0.5
true

同样可以看出二级缓存没有进行清理。

总结

通过上面的实验可以看出每一个mapper都是独立存在独立的，无法交流的缓存。

如果在mapper1中修改了数据，mapper2不知道，所以会导致数据错乱。

尽量一个表只关联一个mapper。