#1, Basic use

1.mybatis-config.xml template

<?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.jianan.mapper.xxx">


</mapper>

2. Top level elements

The real power of MyBatis lies in its statement mapping, which is its magic. Because of its extraordinary power, the XML file of the mapper is relatively simple.

The SQL mapping file has only a few top-level elements

• Cache – the cache configuration for this namespace.
• Cache ref – refers to the cache configuration of other namespaces.
• resultMap – describes how to load objects from the database result set. It is the most complex and powerful element.
• sql – a repeatable sentence block that can be referenced by other statements.
• Insert – map insert statements.
• Update – map update statements.
• Delete – map delete statements.
• select – map query statements.

3.select

The select element allows you to configure many properties to configure the behavior details of each statement.

<select
  id="selectPerson"
  parameterType="int"
  resultType="hashmap"
  resultMap="personResultMap"
  flushCache="false"
  useCache="true"
  timeout="10"
  fetchSize="256"
  statementType="PREPARED">
</select>

attribute	describe
id	Unique identifier
parameterType	The fully qualified name or alias of the class for the parameter
resultType	The fully qualified name or alias of the resulting class
resultMap	Named reference to external resultMap
flushCache	When it is set to true, as long as the statement is called, the local cache and L2 cache will be emptied. The default value is false.
useCache	Setting it to true will cause the results of this statement to be cached by the secondary cache. The default value is true for the select element.
timeout	This setting is the number of seconds the driver waits for the database to return the requested result before throwing an exception. The default value is unset (database driven).
fetchSize	This is a recommended value for the driver. Try to make the number of result lines returned by the driver in batch each time equal to this setting value. The default value is unset (drive dependent).
statementType	Optional status, PREPARED or CALLABLE. This will make MyBatis use statement, PreparedStatement or CallableStatement respectively. The default value is PREPARED.
resultSetType	FORWARD_ONLY，SCROLL_ SENSITIVE, SCROLL_ Either intrinsic or DEFAULT (equivalent to unset). The DEFAULT value is unset (dependent on database driver).
databaseId	If the database vendor ID (databaseIdProvider) is configured, MyBatis will load all statements without databaseId or matching the current databaseId; If there are statements with and without, the statements without are ignored.

4.insert, update and delete

1. Foundation use

<insert
  id="insertAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  keyProperty=""
  keyColumn=""
  useGeneratedKeys=""
  timeout="20">

<update
  id="updateAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  timeout="20">

<delete
  id="deleteAuthor"
  parameterType="domain.blog.Author"
  flushCache="true"
  statementType="PREPARED"
  timeout="20">

attribute	describe
id	Unique identifier.
parameterType	The fully qualified name or alias of the class for the parameter
flushCache	When it is set to true, as long as the statement is called, the local cache and L2 cache will be emptied. The default value is (for insert, update and delete statements) true.
timeout	This setting is the number of seconds the driver waits for the database to return the requested result before throwing an exception. The default value is unset (database driven).
statementType	Optional status, PREPARED or CALLABLE. This will make MyBatis use statement, PreparedStatement or CallableStatement respectively. The default value is PREPARED.
useGeneratedKeys	(only applicable to insert and update) whether to return the automatically generated primary key. The default value is false.
keyProperty	(only applicable to insert and update) specify the attribute that can uniquely identify the object. MyBatis will use the return value of getGeneratedKeys or the selectKey sub element of the insert statement to set its value. The default value is unset.
keyColumn	(only applicable to insert and update) set the column name of the generated key value in the table. In some databases (such as PostgreSQL), it must be set when the primary key column is not the first column in the table. If more than one column is generated, multiple attribute names can be separated by commas.
databaseId	If the database vendor ID (databaseIdProvider) is configured, MyBatis will load all statements without databaseId or matching the current databaseId; If there are statements with and without, the statements without are ignored.

2. Return the generated primary key

Note: when the generated primary key is returned in this way, the @ Param annotation cannot be used for passing parameters

<insert id="add" parameterType="com.jianan.entity.User" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO `user`(`name`) VALUES(#{name})
</insert>

For databases that do not support automatic generation of primary key columns and JDBC drivers that may not support automatic generation of primary keys, MyBatis has another method to generate primary keys.

<insert id="insertAuthor">
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    select CAST(RANDOM()*1000000 as INTEGER) a from SYSIBM.SYSDUMMY1
  </selectKey>
  insert into Author
    (id, username, password, email,bio, favourite_section)
  values
    (#{id}, #{username}, #{password}, #{email}, #{bio}, #{favouriteSection,jdbcType=VARCHAR})
</insert>

In the above example, the statement in the selectKey element will be run first, and the id of the Author will be set, and then the insert statement will be called.

attribute	describe
keyProperty	The target property to which the result of the selectKey statement should be set. If more than one column is generated, multiple attribute names can be separated by commas.
keyColumn	Returns the column name of the generated column property in the result set. If more than one column is generated, multiple attribute names can be separated by commas.
resultType	Type of result. Usually MyBatis can be inferred, but for more accuracy, there will be no problem writing it. MyBatis allows any simple type to be used as the type of primary key, including string. If you generate more than one column, you can use an Object or Map that contains the desired attributes.
order	Can be set to BEFORE or AFTER. If it is set to BEFORE, it will first generate the primary key, set the keyProperty, and then execute the insert statement. If it is set to AFTER, the insert statement is executed first, and then the statement in the selectKey - this is similar to the behavior of Oracle database. There may be embedded index calls inside the insert statement.
statementType	As before, MyBatis supports mapping statements of state, PREPARED and CALLABLE types, representing Statement, PreparedStatement and CallableStatement types respectively.

5. Parameter transfer

1. Single parameter

<insert id="insert" parameterType="com.jianan.entity.User">
    INSERT INTO `user`(`name`) VALUES(#{name})
</insert>

2. Multiple parameters

It will be automatically encapsulated into a Map. The key of the Map is param1, param2

<insert id="add2" parameterType="com.jianan.entity.User">
    INSERT INTO `user`(`name`) VALUES(#{param1},#{param2})
</insert>

3. Naming parameters

Annotate with @ Param and set the parameter name

// user.xxx is required to pass POJO s for use inside
// Pass common types for direct use
int add(@Param("user") User user,@Param("age") Integer age);

4.POJO

// We pass the POJO directly and use the fields directly in the mapper file
int add(User user);

<insert id="add" parameterType="com.jianan.entity.User">
    INSERT INTO `user`(`name`) VALUES(#{name})
</insert>

5.Map

Multiple parameters can be encapsulated as map s and passed directly

int insert2(@Param("map")HashMap<String,String> map);

6. Parameter processing

Parameter can also specify a special data type

#{age,javaType=string,jdbcType=int}

To further customize type handling, you can specify a special type processor class

#{age,javaType=int,jdbcType=NUMERIC,typeHandler=MyTypeHandler}

For numeric types, you can also set numericScale to specify the number of digits to be retained after the decimal point

#{height,javaType=double,jdbcType=NUMERIC,numericScale=2}

Although the above options are powerful, most of the time, you just need to specify the attribute name, specify the JDBC type for the column that may be empty, and leave the rest to MyBatis to infer.

6. String substitution #{} and ${}

By default, when using #{} parameter syntax, MyBatis will create a placeholder for the PreparedStatement parameter and set the parameters safely through the placeholder. This is safer and faster. It is usually the preferred method. However, sometimes you just want to insert a non escaped string directly into the SQL statement. For example, in the ORDER BY clause, you can:

ORDER BY ${columnname}

#The difference between {} and ${}

#{} get the value of the parameter, precompile it into SQL, safe.

${} gets the value of the parameter and splices it into SQL. There is a SQL injection problem

2, resultMap

1. Use examples

<!-- Very complex result mapping -->
<resultMap id="detailedBlogResultMap" type="Blog">
  <constructor>
    <idArg column="blog_id" javaType="int"/>
  </constructor>
  <result property="title" column="blog_title"/>
  <association property="author" javaType="Author">
    <id property="id" column="author_id"/>
    <result property="username" column="author_username"/>
    <result property="password" column="author_password"/>
    <result property="email" column="author_email"/>
    <result property="bio" column="author_bio"/>
    <result property="favouriteSection" column="author_favourite_section"/>
  </association>
  <collection property="posts" ofType="Post">
    <id property="id" column="post_id"/>
    <result property="subject" column="post_subject"/>
    <association property="author" javaType="Author"/>
    <collection property="comments" ofType="Comment">
      <id property="id" column="comment_id"/>
    </collection>
    <collection property="tags" ofType="Tag" >
      <id property="id" column="tag_id"/>
    </collection>
    <discriminator javaType="int" column="draft">
      <case value="1" resultType="DraftPost"/>
    </discriminator>
  </collection>
</resultMap>

2. Concept attempt (resultMap)

• Constructor - used to inject results into the constructor when instantiating a class
  • idArg - ID parameter; Marking the results as IDS can help improve overall performance
  • arg - will be injected into a normal result of the constructor
• ID – an ID result; Marking the results as IDS can help improve overall performance
• Result – the normal result injected into a field or JavaBean property
• association – a complex type of association; Many results will be packaged into this type
• Nested result mapping – associations can be resultMap elements or references to other result mappings
• Collection – a collection of complex types
  • Nested result mappings – collections can be resultMap elements or references to other result mappings
• discriminator – uses the result value to determine which resultMap to use
  • case – result mapping based on certain values
    • Nested result mapping – case is also a result mapping, so it has the same structure and elements; Or reference other result mappings

3. Attribute list of resultmap

attribute	describe
id	Unique identification
type	The fully qualified name or alias of the class

4. Attributes of ID and Result

attribute	describe
property	Fields or properties mapped to column results. If the JavaBean has a property with this name, it will be used first.
column	The column name in the database, or the alias of the column.
javaType	The fully qualified name or alias of a Java class
jdbcType	JDBC type
typeHandler	Type processor
select	The ID of the mapping statement used to load the complex type attribute. It retrieves data from the column specified in the column attribute and passes it to the select statement as a parameter.

5. Construction method

When we get the results, we will call the parameterless construction of the class to create objects by default. Construction method injection allows you to set the value of the property for the class during initialization without exposing the public method.

public class User {
   // Parametric construction method
   public User(Integer id, String username, int age) {
     //...
  }
}

Sets the construction method used

<constructor>
   <idArg column="id" javaType="int" name="id" />
   <arg column="age" javaType="_int" name="age" />
   <arg column="username" javaType="String" name="username" />
</constructor>

attribute	describe
column	The column name in the database, or the alias of the column.
javaType	The fully qualified name of a Java class, or a type alias
jdbcType	JDBC type
typeHandler	Type processor
select	The ID of the mapping statement used to load the complex type attribute. It retrieves data from the column specified in the column attribute and passes it to the select statement as a parameter.
resultMap	ID of the result map
name	The name of the constructor parameter

6. One to one association

	// Method 1: directly configure the mapping results
	<resultMap id="parent" type="com.jianan.entity.Parent">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <association property="son" javaType="com.jianan.entity.Son">
            <id column="id" property="id" />
            <result column="name" property="name" />
        </association>
    </resultMap>

// Method 2: call separate query mapping results through select
<resultMap id="blogResult" type="Blog">
  // Query objects through separate select
  // column can be passed in as a parameter. If there are multiple columns, they can be encapsulated by map
  <association property="author" column="author_id" javaType="Author" select="selectAuthor"/>
</resultMap>

<select id="selectBlog" resultMap="blogResult">
  SELECT * FROM BLOG WHERE ID = #{id}
</select>

<select id="selectAuthor" resultType="Author">
    SELECT * FROM AUTHOR WHERE ID = #{id}
</select>

// Method 3: configure a resultMap separately to obtain results
<resultMap id="blogResult" type="Blog">
  <id property="id" column="blog_id" />
  <result property="title" column="blog_title"/>
  // The result map can be configured separately through resultMap
  <association property="author" column="blog_author_id" javaType="Author" resultMap="authorResult"/>
</resultMap>

<resultMap id="authorResult" type="Author">
  <id property="id" column="author_id"/>
  <result property="username" column="author_username"/>
  <result property="password" column="author_password"/>
  <result property="email" column="author_email"/>
  <result property="bio" column="author_bio"/>
</resultMap>

Attribute list

attribute	describe
property	Fields or properties mapped to column results.
column	The ID of the mapping statement used to load the complex type attribute. It will retrieve data from the column specified in the column attribute and pass it to the select statement as a parameter. It can be multiple
javaType	The fully qualified name of a Java class, or a type alias
jdbcType	JDBC type
typeHandler	Type processor
select	ID of the mapping statement used to load complex type properties
resultMap	Calling a separate mapping set

7. One to many association collection

    // Method 1: directly map one to many results
    <resultMap id="parent" type="com.jianan.entity.Parent">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <collection property="son" ofType="com.jianan.entity.Son" >
            <id column="id" property="id" />
            <result column="name" property="name" />
        </collection>
    </resultMap> 

// Method 2: call a separate query set
<resultMap id="blogResult" type="Blog">
  <collection property="posts" javaType="ArrayList" column="id" ofType="Post" select="selectPostsForBlog"/>
</resultMap>

<select id="selectBlog" resultMap="blogResult">
  SELECT * FROM BLOG WHERE ID = #{id}
</select>

<select id="selectPostsForBlog" resultType="Post">
  SELECT * FROM POST WHERE BLOG_ID = #{id}
</select>

// Method 3: map the results separately through resultMap
<resultMap id="blogResult" type="Blog">
  <id property="id" column="blog_id" />
  <result property="title" column="blog_title"/>
  <collection property="posts" ofType="Post" resultMap="blogPostResult" columnPrefix="post_"/>
</resultMap>

<resultMap id="blogPostResult" type="Post">
  <id property="id" column="id"/>
  <result property="subject" column="subject"/>
  <result property="body" column="body"/>
</resultMap>

8. Discriminator

A database query may return multiple different result sets. The discriminator element is designed to deal with this situation, which is much like the switch statement in the Java language.

<resultMap id="vehicleResult" type="Vehicle">
  <id property="id" column="id" />
  <result property="vin" column="vin"/>
  <result property="year" column="year"/>
  <result property="make" column="make"/>
  <result property="model" column="model"/>
  <result property="color" column="color"/>
  <discriminator javaType="int" column="vehicle_type">
    <case value="1" resultMap="carResult"/>
    <case value="2" resultMap="truckResult"/>
    <case value="3" resultMap="vanResult"/>
    <case value="4" resultMap="suvResult"/>
  </discriminator>
</resultMap>

In the above section, we need to define multiple resultmaps. At the same time, we can also use a concise mapping style, which all represent the same meaning

<resultMap id="vehicleResult" type="Vehicle">
  <id property="id" column="id" />
  <result property="vin" column="vin"/>
  <result property="year" column="year"/>
  <result property="make" column="make"/>
  <result property="model" column="model"/>
  <result property="color" column="color"/>
  <discriminator javaType="int" column="vehicle_type">
    <case value="1" resultType="carResult">
      <result property="doorCount" column="door_count" />
    </case>
    <case value="2" resultType="truckResult">
      <result property="boxSize" column="box_size" />
      <result property="extendedCab" column="extended_cab" />
    </case>
    <case value="3" resultType="vanResult">
      <result property="powerSlidingDoor" column="power_sliding_door" />
    </case>
    <case value="4" resultType="suvResult">
      <result property="allWheelDrive" column="all_wheel_drive" />
    </case>
  </discriminator>
</resultMap>

3, Dynamic SQL

Dynamic SQL is one of the powerful features of MyBatis.

1.if

<if test="author != null and author.name != null and author.name != ''">
    AND author_name like #{author.name}
</if>

2.choose,when,otherwise

 <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>

3.where

<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>

4.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>

5.trim

attribute	describe
prefix	prefix
suffix	suffix
prefixoverride	Delete prefix content
suffixoverride	Delete suffix content

Customize the where function through trim

// When the content is returned in the tag, add the where field and delete the set prefix
SELECT id,name FROM `user`
<trim prefix="WHERE" prefixOverrides="AND |OR">
   <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
</trim>

Customize set function through trim

// When the content is returned in the tag, add the set field and delete the set suffix
UPDATE `user` 
<trim prefix="SET" suffixOverrides=",">
  	<if test="username != null">username=#{username},</if>
     <if test="password != null">password=#{password},</if>
</trim>

6.foreach

 <foreach item="item" index="index" collection="list"  open="(" separator="," close=")">
        #{item}
  </foreach>

7.sql

<sql id="sqlid">
    SELECT * FROM ....
</sql>

<include refid="sqlid"/>

8.bind

The bind element allows you to create a variable outside the OGNL expression and bind it to the current context. For example:

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

9.OGNL object graph Navigation Language

Properties of the calling object	person.name
Call method	person.getName()
Calling static properties / methods	@java.util.UUID@randomUUID()
Call constructor	new com.jianan.entity.User().getName()
operator	+ - * / %
Logical operator	in, not in, > >= < <= == !=

4, Cache

1. General

MyBatis has built-in a powerful transactional query caching mechanism, which can be easily configured and customized. Caching can greatly improve query efficiency. When caching is enabled, the query process of the database is level 2 cache -- > Level 1 cache -- > database

Two level cache is defined by default in Mybatis system

1. L1 cache: by default, only L1 cache is enabled
2. L2 cache: L2 cache needs to be manually enabled and configured. It is based on namespace level cache
3. In order to improve scalability, Mybatis defines the cache interface cache. We can customize the L2 cache by implementing the cache interface

2. L1 cache

1) Foundation

The L1 Cache is a local Cache, and the scope is sqlSession by default. When the Session is flushed or close d, all caches in the Session will be cleared

The local cache cannot be closed, but clearCache() can be called to empty the local cache or change the scope of the cache

2) Cache test

	// Create session
    public SqlSession getSqlSession() {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
            return factory.openSession();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Test
    public void m1() {
        // Create sqlsession
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        // First query
        List<User> list = userMapper.listAll("Jialin");
        // Second query
        List<User> list2 = userMapper.listAll("Jialin");
        System.out.println(list);
        System.out.println(list2);
        MybatisTest.sqlSession.commit();
        MybatisTest.sqlSession.close();
    }

Query the log through the console. I found that I queried the log twice, but did not query the log the second time, which means that the L1 cache is used

17:29:41.115 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6986852]
17:29:41.117 [main] DEBUG com.jianan.mapper.UserMapper.listAll - ==>  Preparing: SELECT id,name FROM `user` WHERE name LIKE CONCAT('%',?,'%') 
17:29:41.152 [main] DEBUG com.jianan.mapper.UserMapper.listAll - ==> Parameters: Jialin(String)
17:29:41.193 [main] DEBUG com.jianan.mapper.UserMapper.listAll - <==      Total: 2
[User{id=5, name='null'}, User{id=6, name='null'}]
[User{id=5, name='null'}, User{id=6, name='null'}]

3) Cache invalidation

1. Different sqlsessions correspond to different L1 caches
2. The same SqlSession but different query conditions
3. Any addition, deletion and modification operations are performed during two queries of the same SqlSession
4. The cache was manually emptied during two queries of the same SqlSession

4) Cache invalidation test

    // Different sqlsessions will invalidate the cache
	@Test
    public void m2() {
        // Create the first SqlSession
        SqlSession sqlSession1 = getSqlSession();
        // Create a second SqlSession
        SqlSession sqlSession2 = getSqlSession();
        UserMapper userMapper1 = sqlSession1.getMapper(UserMapper.class);
        UserMapper userMapper2 = sqlSession2.getMapper(UserMapper.class);
        List<User> list1 = userMapper1.listAll("Jialin");
        List<User> list2 = userMapper2.listAll("Jialin");
        System.out.println(list1);
        System.out.println(list2);
    }

	// Different query conditions
	@Test
    public void m3() {
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> list = userMapper.listAll("Jialin");
        List<User> list2 = userMapper.listAll("test");
        System.out.println(list);
        System.out.println(list2);
        MybatisTest.sqlSession.commit();
        MybatisTest.sqlSession.close();
    }	

	// Add, delete and modify operations or manually empty the cache are performed in the middle
	// From the log, you can see that the query was performed twice
    @Test
    public void m4() {
        SqlSession sqlSession = getSqlSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> list = userMapper.listAll("Jialin");
        // Add data
        userMapper.add("Add data");
        // wipe cache 
        // sqlSession.clearCache();
        List<User> list2 = userMapper.listAll("test");
        System.out.println(list);
        System.out.println(list2);
        MybatisTest.sqlSession.commit();
        MybatisTest.sqlSession.close();
    }

3. L2 cache

1) Foundation

• L2 cache, global scope cache
• L2 cache is not enabled by default and needs to be configured manually
• Mybatis provides the interface and implementation of L2 cache. The cache implementation requires POJO to implement the Serializable interface
• L2 cache will not take effect until SqlSession is closed or committed
• The L2 cache is transactional

2) Use steps

1. Enable L2 cache in global configuration file

   // On by default
   <setting name="cacheEnabled" value="true" />
   

2. Note: POJO needs to implement the Serializable interface

3. Mapping file configuration L2 cache

   <cache />
   

   Through such a simple configuration, the effect of this simple statement is as follows:

• The results of all select statements in the mapping statement file will be cached
• All insert, update, and delete statements in the mapping statement file flush the cache
• The cache uses the least recently used (LRU) algorithm to identify unwanted caches
• The cache is not refreshed regularly
• The cache holds 1024 references to a list or object
• The cache will be read / write, which means that the obtained objects are not shared and can be safely modified by the caller without interfering with the potential modifications made by other callers or threads

3) Cache test

	public SqlSessionFactory getFactory(){
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
            return factory;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Test
    public void m4(){
        // Get the factory through the method. Note that you need to get the same factory here
        // If the above method returns SqlSession, different factories will return different sessions
        SqlSessionFactory factory = getFactory();
        // Create two sessions
        SqlSession sqlSession1 = factory.openSession(true);
        SqlSession sqlSession2 = factory.openSession(true);
        UserMapper userMapper1 = sqlSession1.getMapper(UserMapper.class);
        UserMapper userMapper2 = sqlSession2.getMapper(UserMapper.class);
	   // First query
        System.out.println(userMapper1.listAll("Jialin"));
        // SqlSession is closed and stored in L2 cache
        sqlSession1.close();
        // The second query gets from the cache
        System.out.println(userMapper2.listAll("Jialin"));
    }

// journal
09:50:24.274 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - Created connection 115433442.
09:50:24.276 [main] DEBUG com.jianan.mapper.UserMapper.listAll - ==>  Preparing: SELECT id,name FROM `user` WHERE name LIKE CONCAT('%',?,'%') 
09:50:24.325 [main] DEBUG com.jianan.mapper.UserMapper.listAll - ==> Parameters: Jialin(String)
09:50:24.347 [main] DEBUG com.jianan.mapper.UserMapper.listAll - <==      Total: 2
[User{id=5, name='null'}, User{id=6, name='null'}]
09:50:24.355 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@6e15fe2]
09:50:24.355 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - Returned connection 115433442 to pool.
09:50:24.359 [main] DEBUG com.jianan.mapper.UserMapper - Cache Hit Ratio [com.jianan.mapper.UserMapper]: 0.5
[User{id=5, name='null'}, User{id=6, name='null'}]

4)cache tag

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

The attributes that can be set for the cache tag are:

attribute	describe
eviction	Cache recycling policy, default LRU, the least recently used is cleared
flushInterval	(refresh interval), which is not set by default, that is, there is no refresh interval. The cache is only refreshed when calling statements
size	(number of references) represents the maximum number of objects that can be stored in the cache. Too large can easily lead to memory overflow. The default value is 1024.
readOnly	(read only) true: read only cache; The same instance of the cache object is returned to all callers. False: read / write cache; A copy of the cached object is returned. This will be slower, but safe, so the default is false.

5) Setup summary

1. The cacheEnable of global setting configures the switch of L2 cache. The L1 cache is always on

2.select tag useCache tag to configure whether this select uses L2 cache. L1 cache is always used

<select id="listAll" resultType="com.jianan.springtest.entity.Car" useCache="true">
    SELECT * FROM `car`
</select>

3. For the flushCache attribute of sql tag, add, delete or change the default flushCache=true. After the sql is executed, the L1 and L2 caches will be emptied at the same time. Query default flushCache=false

<insert id="add" flushCache="true">

</insert>

4.sqlSession.clearCache() is used to clear the L1 cache

5. After a C/U/D operation is performed in a scope, all the caches in the select under the scope will be clear ed by default