1, Important tags in xml

1.1. where label

Query criteria entity class

public class QueryTeamVO {

    private String name;

    private Date beginTime;

    private Date endTime;

    private String location;

    public QueryTeamVO() {
    }

    public QueryTeamVO(String name, Date beginTime, Date endTime, String location) {
        this.name = name;
        this.beginTime = beginTime;
        this.endTime = endTime;
        this.location = location;
    }

    @Override
    public String toString() {
        return "QueryTeamVO{" +
                "name='" + name + '\'' +
                ", beginTime=" + beginTime +
                ", endTime=" + endTime +
                ", location='" + location + '\'' +
                '}';
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Date getBeginTime() {
        return beginTime;
    }

    public void setBeginTime(Date beginTime) {
        this.beginTime = beginTime;
    }

    public Date getEndTime() {
        return endTime;
    }

    public void setEndTime(Date endTime) {
        this.endTime = endTime;
    }

    public String getLocation() {
        return location;
    }

    public void setLocation(String location) {
        this.location = location;
    }
} 

TeamMapper. Adding interface methods to Java

    List<Team> queryByVO(QueryTeamVO vo);

TeamMapper.xml add mapping method

<select id="queryByVO" parameterType="QueryTeamVO" resultMap="baseResultMap">
        select * from team
        <where>
            <!--In the first judgment condition and Can write but not write-->
            <if test="name!=null">
                and teamName like CONCAT('%',#{name},'%')
            </if>
            <if test="beginTime!=null">
                and createTime>= #{beginTime}
            </if>
            <if test="endTime!=null">
                and createTime &lt;= #{endTime}
            </if>
            <if test="location!=null">
                and location = #{location}
            </if>
        </where>
    </select>

Add test class:

public class DynamicSqlTest {

    private TeamMapper mapper = MybatisUtil.getSqlSession().getMapper(TeamMapper.class);

    @Test
    public void test1() {
        QueryTeamVO vo=new QueryTeamVO();
        vo.setName("Scholar");
        vo.setEndTime(new Date());
        List<Team> teams = mapper.queryByVO(vo);
        teams.forEach(team -> {
            System.out.println(team);
        });
    } 
}

test result

The query conditions of sql correspond to the passed in parameters

1.2. set label

Add Mapper interface method:

    Integer update1(Team team);

Add xml Mapping:

<update id="update1" parameterType="team">
        update team
        <set>
            <if test="teamName!=null">
                teamName=#{teamName},
            </if>
            <if test="location!=null">
                location=#{location},
            </if>
            <if test="createTime!=null">
                createTime=#{createTime}
            </if>
        </set>
        where teamId=#{teamId}
    </update>

Add test method:

 @Test
    public void test2(){
        Team team = mapper.queryById(1117);
        team.setTeamName("hotFire");
        team.setCreateTime(new Date());
        team.setLocation(null);
        Integer num = mapper.update1(team);
        System.out.println("Number of affected results:"+num);
    }

1.3 forEach label

1.3.1 batch addition

Interface addition method:

    void addList(List<Team> teamList);

mapper add mapping method

    <insert id="addList" parameterType="arrayList">
        insert into team(teamName,location) values
        <foreach collection="list" item="t" separator=",">
            (#{t.teamName},#{t.location})
        </foreach>
    </insert>

Add test method

    @Test
    public void test3(){
        List<Team> list=new ArrayList<>();
        for (int i=0;i<3;i++){
            Team team=new Team();
            team.setTeamName("jk"+i);
            team.setLocation("address"+i);
            team.setCreateTime(new Date());
            list.add(team);
        }
        mapper.addList(list);
        MybatisUtil.getSqlSession().commit();
    }

1.3.2. Batch deletion

Interface addition method:

    void delList(List<Integer> list);

mapper mapping method:

    <delete id="delList" parameterType="arrayList">
        delete from team where teamId in
        <!--collection=The parameter of the collection to be traversed is directly written to the collection type
            item=Traverse each element in the set
            separator=Traverse each element in the set and divide it with
            open=Start wrapping elements, close=End wrap element-->
        <foreach collection="list" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

Add test method:

 @Test
    public void test4(){
        List<Integer> list=new ArrayList<>();
        list.add(1116);
        list.add(1117);
        mapper.delList(list);
        MybatisUtil.getSqlSession().commit();
    }

2, Paging plug-in

2.1. Add jar package

    <!--Paging plug-in-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.3.0</version>
        </dependency>

2.2. Add global configuration

  <!-- introduce pageHelper plug-in unit --> <!--Note that it should be written here PageInterceptor, 5.0 Previous versions were written PageHelper, 5.0 Then replace it with PageInterceptor-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!--reasonable: Paging rationalization parameter. The default value is false,Query directly according to parameters. When this parameter is set to true When, pageNum<=0 The first page will be queried,
            pageNum>pages(When the total number is exceeded, the last page will be queried. Dialects can be omitted and will be based on the parameters of the connection data url Automatic inference-->
            <!--<property name="reasonable" value="true"/>-->
        </plugin>
    </plugins>

2.3. Add test method

 @Test
    public void test5(){
        // PageHelper.startPage must be immediately adjacent to the query statement and will only take effect for the first query statement 
        PageHelper.startPage(2,5);
        // The end of the query statement cannot have;
        List<Team> teams = mapper.queryAll();
        teams.forEach(team -> {
            System.out.println(team);
        });
        PageInfo<Team> pageInfo=new PageInfo<>(teams);
        System.out.println("Paging information is as follows:");
        System.out.println("Current pages:"+pageInfo.getPageNum());
        System.out.println("Total pages:"+pageInfo.getPages());
        System.out.println("Previous page:"+pageInfo.getPrePage());
        System.out.println("Next page:"+pageInfo.getNextPage());
    }

3, mybatis cache

3.1 function of cache

Caching is a function provided by general ORM framework, which aims to improve query efficiency and reduce database pressure. The frequently queried data is stored in the cache. When querying the data, users do not need to read from the disk, but directly read from the cache, so as to improve the query efficiency and solve the problem of high concurrency.

3.2. L1 cache

Sqlsession cache, automatically enabled. When operating the database, you need to construct an sqlsession object, which has a data structure HashMap user cache data. Hashmaps between different sqlsessions do not affect each other.

The scope of the first level cache is the same sqlSession. Execute sql statements twice in the same sqlSession. After the first execution, the query results will be written to the cache. For the second time, read from the cache instead of query from the database, so as to improve the query efficiency.

When an sqlsession ends, the first level cache in the sqlsession does not exist.

Mybatis enables the L1 cache by default. It exists in memory and cannot be closed. You can call clearCache() to empty the local cache or change the scope of the cache.

3.2.1 L1 cache analysis

working principle

Test class:

public class TestCache {

    private SqlSession sqlSession= MybatisUtil.getSqlSession();

    @Test
    public void test1(){
        TeamMapper teamMapper = sqlSession.getMapper(TeamMapper.class);
        Team team = teamMapper.queryById(1001);
        System.out.println(team);
        Team team1 = teamMapper.queryById(1001);
        System.out.println(team1);
        MybatisUtil.closeSqlSession();//Close the connection and empty the cache

        sqlSession= MybatisUtil.getSqlSession();
        teamMapper=sqlSession.getMapper(TeamMapper.class);//Get the connection again, and the cache is empty
        Team team3 = teamMapper.queryById(1001);
        System.out.println(team3);
        teamMapper.delete(1131);
        MybatisUtil.getSqlSession().commit();//Cache empty after commit

        Team team4 = teamMapper.queryById(1001);
        System.out.println(team4);
    } 
}

Empty cache mode:

1. session.clearCache();
2. execute update();
3. session.close();
4. xml configuration flushCache=true
5. rollback;
6. commit;

3.2. L2 cache

Mapper level cache. Multiple sqlsessions operate the sql statement of a mapper together, and multiple sqlsessions can share the L2 cache.

The L2 cache is shared by multiple sqlsessions, and its scope is the same namespace of mapper.

Different sqlsessions execute the same sql statement in the same namespace twice with the same parameters, that is, execute the same sql statement. The first execution will write the execution result to the cache, and the second execution will directly obtain the result from memory to improve the query efficiency.

Mybatis does not enable L2 cache by default. You need to configure enabling L2 cache in setting global settings.

Schematic diagram of L2 cache:

3.2.1. Steps to enable L2 cache

L2 cache is mapper level and is not enabled by default.

1. Enable L2 cache in global configuration file of Mybatis

    <settings> 
        <!-- Whether to enable L2 cache   -->
        <setting name="cacheEnabled" value="true"/>
    </settings>

2. Add a cache flag to the mapper that needs L2 cache

<mapper namespace="com.jsonliu.test.mapper.TeamMapper">
    <cache></cache>
    ...
</mapper>

3. Entity classes must implement the Serializable interface

public class Team implements Serializable {
    ...
}

4. Test L2 cache

If two sessions are not obtained from the same Factory, the L2 cache will not work.

 @Test
    public void test2(){
        SqlSession sqlSession1 = MybatisUtil.getSqlSession();
        TeamMapper mapper1 = sqlSession1.getMapper(TeamMapper.class);
        Team team1 = mapper1.queryById(1001);
        System.out.println(team1);
        MybatisUtil.closeSqlSession();

        SqlSession sqlSession2 = MybatisUtil.getSqlSession();
        TeamMapper mapper2 = sqlSession2.getMapper(TeamMapper.class);
        Team team2 = mapper2.queryById(1001);
        System.out.println(team2);
        MybatisUtil.closeSqlSession();

        SqlSession sqlSession3= MybatisUtil.getSqlSession();
        TeamMapper mapper3 = sqlSession3.getMapper(TeamMapper.class);
        Integer delete = mapper3.delete(1031);
        System.out.println(delete);
        MybatisUtil.closeSqlSession();

        SqlSession sqlSession4 = MybatisUtil.getSqlSession();
        TeamMapper mapper4 = sqlSession4.getMapper(TeamMapper.class);
        Team team4 = mapper4.queryById(1001);
        System.out.println(team4);
        MybatisUtil.closeSqlSession(); 
    }

3.2.2 disable L2 cache

For sql that changes frequently, you can disable the L2 cache.
Set useCache=false in the corresponding statement in the XML that has started the secondary cache to disable the secondary cache of the current Select statement, which means that the SQL statement only needs to query the database every time and will not query the cache.
The default value of useCache is true. For some very important data, do not put it in the L2 cache.

3.2.3. Attribute configuration of cache

<cache> 
    <property name="eviction" value="LRU"/><!--The recycling strategy is LRU--> 
    <property name="flushInterval" value="60000"/><!--The automatic refresh interval is 60 S--> 
    <property name="size" value="1024"/><!--Cache up to 1024 reference objects-->
    <property name="readOnly" value="true"/><!--read-only--> 
</cache>