Mybatis [21] - mybatis L1 cache query

Note: the code has been hosted on GitHub at: https://github.com/Damaer/Mybatis-Learning , the project is mybatis-17-sqlsessionCache, which needs to be self fetched. maven environment and mysql environment need to be configured (the SQL statement is in test.sql under resource). If you find it useful, you can click a small star.

docsify document address: https://damaer.github.io/Mybatis-Learning/#/

The use of query cache is mainly to improve the query access speed. Instead of querying data from the database every time, it can improve the access speed, reduce the number of database queries and reduce the pressure on the database.

L1 query cache

  • 1.mybatis L1 cache is based on org apache. ibatis. cache. impl. The HashMap local cache of the perpetualcache class has the scope of sqlsession, that is, execute the same sql query statement twice in the same sqlsession. After the first execution, the query results will be written to the cache. The second time, data will be obtained directly from the cache, and there is no need to query the database.
  • 2. When a SqlSession ends, the and cache of the SqlSession will not exist. mybatis enables the L1 cache by default and cannot be closed
  • 3. The first level cache is stored according to the id of the sql statement, not the specific content of the sql.

Prove the existence of L1 cache

Instead of putting most of the code here, just the core code. The sql interface is as follows:

public interface IStudentDao {
    public Student selectStudentById(int id);
}

mapper.xml and its corresponding sql statement:

 <!-- adopt id To query students -->
 <select id="selectStudentById" resultType="Student">
  select * from student where id=#{xxx}
 
 </select>

Unit test:

 @Test
 public void testselectStudentById(){

  // First query
  Student student=dao.selectStudentById(17);
  System.out.println(student);
  // Second query
  Student student2=dao.selectStudentById(17);
  System.out.println(student2);
 }

As a result, we can see that we only execute the query once. In the second query, we directly go to the first level cache without querying the database. If we query the database, sql statements will be printed out:

[service] 2018-07-21 09:48:22,534 - dao.IStudentDao.selectStudentById -1349 [main] DEBUG dao.IStudentDao.selectStudentById  - ==>  Preparing: select * from student where id=? 
[service] 2018-07-21 09:48:22,635 - dao.IStudentDao.selectStudentById -1450 [main] DEBUG dao.IStudentDao.selectStudentById  - ==> Parameters: 17(Integer)
[service] 2018-07-21 09:48:22,677 - dao.IStudentDao.selectStudentById -1492 [main] DEBUG dao.IStudentDao.selectStudentById  - <==      Total: 1
Student [id=17, name=hello, age=14, score=94.6]
Student [id=17, name=hello, age=14, score=94.6]

Is the data read from the cache based on the sql id or the sql itself?

At this time, we have a question: what exactly is the cache based on? Then we need to do an experiment and write two sql with different IDS but the sql is completely the same to know the result. The following is the sql interface in the same namespace:

public interface IStudentDao {
    public Student selectStudentById(int id);
    // Test sql with different IDS
    public Student selectStudentById2(int id);
}

mapper.xml file:

<?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">
<!-- hold mapper of namespace Change to the full name of the class name, then it is possible to find the corresponding class name when directly calling the method of the interface mapper Corresponding method in -->
<mapper namespace="dao.IStudentDao">

 <!-- adopt id To query students -->
 <select id="selectStudentById" resultType="Student">
  select * from student where id=#{xxx}
 
 </select>

    <!-- Test different id of sql -->
    <select id="selectStudentById2" resultType="Student">
  select * from student where id=#{xxx}

 </select>
</mapper>

Test code:

    @Test
    public void testDiffereentId2(){

        // First query
        Student student=dao.selectStudentById(17);
        System.out.println(student);
        // The second query tests different IDs and the same namespace
        Student student2=dao.selectStudentById2(17);
        System.out.println(student2);
    }

The results are as follows. We can see that the sql is executed twice, which proves that the first level cache is not based on the sql itself, but based on the id of the sql:

[service] 2018-07-21 10:26:32,844 - dao.IStudentDao.selectStudentById -957  [main] DEBUG dao.IStudentDao.selectStudentById  - ==>  Preparing: select * from student where id=? 
[service] 2018-07-21 10:26:32,954 - dao.IStudentDao.selectStudentById -1067 [main] DEBUG dao.IStudentDao.selectStudentById  - ==> Parameters: 17(Integer)
[service] 2018-07-21 10:26:32,989 - dao.IStudentDao.selectStudentById -1102 [main] DEBUG dao.IStudentDao.selectStudentById  - <==      Total: 1
Student [id=17, name=hello, age=14, score=94.6]
[service] 2018-07-21 10:26:32,990 - dao.IStudentDao.selectStudentById2 -1103 [main] DEBUG dao.IStudentDao.selectStudentById2  - ==>  Preparing: select * from student where id=? 
[service] 2018-07-21 10:26:32,991 - dao.IStudentDao.selectStudentById2 -1104 [main] DEBUG dao.IStudentDao.selectStudentById2  - ==> Parameters: 17(Integer)
[service] 2018-07-21 10:26:32,996 - dao.IStudentDao.selectStudentById2 -1109 [main] DEBUG dao.IStudentDao.selectStudentById2  - <==      Total: 1
Student [id=17, name=hello, age=14, score=94.6]

What about the same id in different namespace s?

For different namespaces, even if they are the same id, the first level cache will not take effect, because the sql cache is distinguished according to different namespaces. The two sql interfaces are as follows:

public interface IStudentDao {
    public Student selectStudentById(int id);
}

public interface IStudentDao2 {
    //Test of the same id in different namespace s
    public Student selectStudentById(int id);
}

The two mapper files are as follows:

<?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">
        <!-- hold mapper of namespace Change to the full name of the class name, then it is possible to find the corresponding class name when directly calling the method of the interface mapper Corresponding method in -->
<mapper namespace="dao.IStudentDao">
<!-- adopt id To query students -->
<select id="selectStudentById" resultType="Student">
 select * from student where id=#{xxx}
</select>
</mapper>
<?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">
<!-- hold mapper of namespace Change to the full name of the class name, then it is possible to find the corresponding class name when directly calling the method of the interface mapper Corresponding method in -->
<mapper namespace="dao.IStudentDao2">
    <!-- adopt id To query students -->
    <select id="selectStudentById" resultType="Student">
  select * from student where id=#{xxx}
 </select>

</mapper>

Unit tests are as follows:

    @Test
    public void testDiffereentNamespaceSameId(){

        // First query
        Student student=dao.selectStudentById(17);
        System.out.println(student);
        // The second query tests the same id and different namespace s
        IStudentDao2 dao2= sqlSession.getMapper(IStudentDao2.class);
        Student student2=dao2.selectStudentById(17);
        System.out.println(student2);
    }

The results are as follows. We can see that sql was executed twice. This proves that the L1 cache is based on the same id in the same namespace. Even if it is the same id, the namespace will not take effect, because the L1 cache is stored according to the namespace

[service] 2018-07-21 10:37:36,916 - dao.IStudentDao.selectStudentById -1545 [main] DEBUG dao.IStudentDao.selectStudentById  - ==>  Preparing: select * from student where id=? 
[service] 2018-07-21 10:37:37,154 - dao.IStudentDao.selectStudentById -1783 [main] DEBUG dao.IStudentDao.selectStudentById  - ==> Parameters: 17(Integer)
[service] 2018-07-21 10:37:37,194 - dao.IStudentDao.selectStudentById -1823 [main] DEBUG dao.IStudentDao.selectStudentById  - <==      Total: 1
Student [id=17, name=hello, age=14, score=94.6]
[service] 2018-07-21 10:37:37,202 - dao.IStudentDao2.selectStudentById -1831 [main] DEBUG dao.IStudentDao2.selectStudentById  - ==>  Preparing: select * from student where id=? 
[service] 2018-07-21 10:37:37,204 - dao.IStudentDao2.selectStudentById -1833 [main] DEBUG dao.IStudentDao2.selectStudentById  - ==> Parameters: 17(Integer)
[service] 2018-07-21 10:37:37,210 - dao.IStudentDao2.selectStudentById -1839 [main] DEBUG dao.IStudentDao2.selectStudentById  - <==      Total: 1
Student [id=17, name=hello, age=14, score=94.6]

Effect of addition, deletion and modification on L1 cache

Add, delete and modify operations, whether or not to submit sqlsession Commit() will empty the query cache and make the query cache query from the DB again.

The interfaces of sql requests are as follows:

public interface IStudentDao {
    public Student selectStudentById(int id);
    // Test sql with different IDS
    public Student selectStudentById2(int id);


    // Increase students
    public void insertStudent(Student student);
    // Delete student by id
    public void deleteStudentById(int id);
    // Update student information
    public void updateStudent(Student student);
}

mapper.xml file:

<?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">
<!-- hold mapper of namespace Change to the full name of the class name, then it is possible to find the corresponding class name when directly calling the method of the interface mapper Corresponding method in -->
<mapper namespace="dao.IStudentDao">

    <!-- adopt id To query students -->
    <select id="selectStudentById" resultType="Student">
  select * from student where id=#{xxx}

 </select>

    <!-- Test different id of sql -->
    <select id="selectStudentById2" resultType="Student">
  select * from student where id=#{xxx}
</select>

    <insert id="insertStudent" parameterType="Student">
  insert into student(name,age,score) values(#{name},#{age},#{score})
 </insert>
    <!-- delete -->
    <delete id="deleteStudentById">
        delete from student where id=#{id}
        <!-- there id Anything can be put. It's just a placeholder. It doesn't mean anything -->
    </delete>

    <update id="updateStudent">
  update student set name=#{name},age=#{age},score=#{score} where id=#{id}
 </update>
</mapper>

Unit test:

    public void test03(){

        // First query
        Student student=dao.selectStudentById(17);
        System.out.println(student);
        //Insert student
        Student student1 = new Student("12112",12,21.6);
        //dao.insertStudent(student1);
        dao.updateStudent(student1);
        student=dao.selectStudentById(17);
        System.out.println(student);
    }

When we perform an insert operation after the first query, we find that the L1 cache has been updated:

[service] 2018-07-21 13:07:27,136 - dao.IStudentDao.selectStudentById -1059 [main] DEBUG dao.IStudentDao.selectStudentById  - ==>  Preparing: select * from student where id=? 
[service] 2018-07-21 13:07:27,247 - dao.IStudentDao.selectStudentById -1170 [main] DEBUG dao.IStudentDao.selectStudentById  - ==> Parameters: 17(Integer)
[service] 2018-07-21 13:07:27,288 - dao.IStudentDao.selectStudentById -1211 [main] DEBUG dao.IStudentDao.selectStudentById  - <==      Total: 1
Student [id=17, name=hello, age=14, score=94.6]
[service] 2018-07-21 13:07:27,289 - dao.IStudentDao.insertStudent -1212 [main] DEBUG dao.IStudentDao.insertStudent  - ==>  Preparing: insert into student(name,age,score) values(?,?,?) 
[service] 2018-07-21 13:07:27,291 - dao.IStudentDao.insertStudent -1214 [main] DEBUG dao.IStudentDao.insertStudent  - ==> Parameters: 12112(String), 12(Integer), 21.6(Double)
[service] 2018-07-21 13:07:27,295 - dao.IStudentDao.insertStudent -1218 [main] DEBUG dao.IStudentDao.insertStudent  - <==    Updates: 1
[service] 2018-07-21 13:07:27,295 - dao.IStudentDao.selectStudentById -1218 [main] DEBUG dao.IStudentDao.selectStudentById  - ==>  Preparing: select * from student where id=? 
[service] 2018-07-21 13:07:27,295 - dao.IStudentDao.selectStudentById -1218 [main] DEBUG dao.IStudentDao.selectStudentById  - ==> Parameters: 17(Integer)
[service] 2018-07-21 13:07:27,302 - dao.IStudentDao.selectStudentById -1225 [main] DEBUG dao.IStudentDao.selectStudentById  - <==      Total: 1
Student [id=17, name=hello, age=14, score=94.6]

After the first query, we perform another update operation. Even if the updated data is not the query data, but belongs to the same table, the L1 cache is also updated:

[service] 2018-07-21 23:43:28,073 - dao.IStudentDao.selectStudentById -1081 [main] DEBUG dao.IStudentDao.selectStudentById  - ==>  Preparing: select * from student where id=? 
[service] 2018-07-21 23:43:28,202 - dao.IStudentDao.selectStudentById -1210 [main] DEBUG dao.IStudentDao.selectStudentById  - ==> Parameters: 17(Integer)
[service] 2018-07-21 23:43:28,236 - dao.IStudentDao.selectStudentById -1244 [main] DEBUG dao.IStudentDao.selectStudentById  - <==      Total: 1
Student [id=17, name=hello, age=14, score=94.6]
[service] 2018-07-21 23:43:28,239 - dao.IStudentDao.updateStudent -1247 [main] DEBUG dao.IStudentDao.updateStudent  - ==>  Preparing: update student set name=?,age=?,score=? where id=? 
[service] 2018-07-21 23:43:28,241 - dao.IStudentDao.updateStudent -1249 [main] DEBUG dao.IStudentDao.updateStudent  - ==> Parameters: 12112(String), 12(Integer), 21.6(Double), 18(Integer)
[service] 2018-07-21 23:43:28,246 - dao.IStudentDao.updateStudent -1254 [main] DEBUG dao.IStudentDao.updateStudent  - <==    Updates: 1
[service] 2018-07-21 23:43:28,246 - dao.IStudentDao.selectStudentById -1254 [main] DEBUG dao.IStudentDao.selectStudentById  - ==>  Preparing: select * from student where id=? 
[service] 2018-07-21 23:43:28,246 - dao.IStudentDao.selectStudentById -1254 [main] DEBUG dao.IStudentDao.selectStudentById  - ==> Parameters: 17(Integer)
[service] 2018-07-21 23:43:28,251 - dao.IStudentDao.selectStudentById -1259 [main] DEBUG dao.IStudentDao.selectStudentById  - <==      Total: 1
Student [id=17, name=hello, age=14, score=94.6]

When we perform a query operation and a delete operation, the L1 cache will also be updated:

[service] 2018-07-21 23:44:49,296 - dao.IStudentDao.selectStudentById -1172 [main] DEBUG dao.IStudentDao.selectStudentById  - ==>  Preparing: select * from student where id=? 
[service] 2018-07-21 23:44:49,457 - dao.IStudentDao.selectStudentById -1333 [main] DEBUG dao.IStudentDao.selectStudentById  - ==> Parameters: 17(Integer)
[service] 2018-07-21 23:44:49,504 - dao.IStudentDao.selectStudentById -1380 [main] DEBUG dao.IStudentDao.selectStudentById  - <==      Total: 1
Student [id=17, name=hello, age=14, score=94.6]
[service] 2018-07-21 23:44:49,505 - dao.IStudentDao.deleteStudentById -1381 [main] DEBUG dao.IStudentDao.deleteStudentById  - ==>  Preparing: delete from student where id=? 
[service] 2018-07-21 23:44:49,505 - dao.IStudentDao.deleteStudentById -1381 [main] DEBUG dao.IStudentDao.deleteStudentById  - ==> Parameters: 18(Integer)
[service] 2018-07-21 23:44:49,508 - dao.IStudentDao.deleteStudentById -1384 [main] DEBUG dao.IStudentDao.deleteStudentById  - <==    Updates: 1
[service] 2018-07-21 23:44:49,509 - dao.IStudentDao.selectStudentById -1385 [main] DEBUG dao.IStudentDao.selectStudentById  - ==>  Preparing: select * from student where id=? 
[service] 2018-07-21 23:44:49,509 - dao.IStudentDao.selectStudentById -1385 [main] DEBUG dao.IStudentDao.selectStudentById  - ==> Parameters: 17(Integer)
[service] 2018-07-21 23:44:49,517 - dao.IStudentDao.selectStudentById -1393 [main] DEBUG dao.IStudentDao.selectStudentById  - <==      Total: 1
Student [id=17, name=hello, age=14, score=94.6]

Added by Dr John on Tue, 15 Feb 2022 08:56:00 +0200