🏇 wow Click Click : \textcolor{blue} {wow, Kaka:} Wow, Kaka: ancient Na PULL black dark of god ， please Bestow Give I power amount ， Give Way I rise individual Bed \textcolor{green} {gunara, God of darkness, please give me strength to get out of bed} Gunara, God of darkness, please give me strength to get out of bed 😈
💥MyBatis learning source code in gitee💥The code used can be found here🐳

12, Dynamic SQL

sql in mybatis

What is dynamic SQL: dynamic SQL is to generate different SQL statements according to different conditions

Dynamic SQL is one of the powerful features of MyBatis. If you have used JDBC or other similar frameworks, you should be able to understand how painful it is to splice SQL statements according to different conditions. For example, when splicing, make sure you can't forget to add the necessary spaces and remove the comma of the last column name in the list.

1. Build environment

CREATE TABLE `blog`(
`id` VARCHAR(50) NOT NULL COMMENT 'Blog id',
`title` VARCHAR(100) NOT NULL COMMENT 'Blog title',
`author` VARCHAR(30) NOT NULL COMMENT 'Blogger',
`create_time` DATETIME NOT NULL COMMENT 'Creation time',
`views` INT(30) NOT NULL COMMENT 'Views'
)ENGINE=INNODB DEFAULT CHARSET=utf8

2. Create a basic project

2.1 Guide Package

<dependencies>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.20</version>
    </dependency>
</dependencies>

2.2 preparation of configuration files

mybaits-config.xml and db.properties

2.3 writing entity classes

import lombok.Data;

import java.util.Date;

@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;//The property name and field name are inconsistent
    private int views;
}

2.4 write Mapper interface and Mapper.xml file corresponding to entity class

BlogMapper and BlogMapper.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">
<!--Core profile-->
<configuration>
    <!--Import external profile-->
    <properties resource="db.properties"/>

    <settings>
        <!--Standard log factory implementation-->
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    
    <!--You can alias an entity class-->
    <typeAliases>
        <package name="com.hxl.pojo"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <!--transaction management-->
            <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="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper class="com.hxl.dao.BlogMapper"/>

    </mappers>


</configuration>

2.5 tool class for generating UUID

import java.util.UUID;

@SuppressWarnings("all")//Suppress warning
public class IDutils {
    public static String getId(){
        return UUID.randomUUID().toString().replaceAll("-","");
    }
}

2.6 test data

public interface BlogMapper {
    //insert data
    int addBlog(Blog blog);
}

<?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">
<!--Core profile-->
<mapper namespace="com.hxl.dao.BlogMapper">

    <insert id="addBlog" parameterType="blog">
        insert into blog (id,title,author,create_time,views)
        values (#{id},#{title},#{author},#{createTime},#{views});
    </insert>

</mapper>

import com.hxl.dao.BlogMapper;
import com.hxl.pojo.Blog;
import com.hxl.utils.IDutils;
import com.hxl.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.Date;

public class MyTest {
    @Test
    public void addInitBlog(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = new Blog();
        blog.setId(IDutils.getId());
        blog.setTitle("Mybatis");
        blog.setAuthor("Wang Mumu");
        blog.setCreateTime(new Date());
        blog.setViews(9999);
        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("Java");
        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("Spring");
        mapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("Microservices");
        mapper.addBlog(blog);

        sqlSession.close();
    }
}

• Something went wrong

  

  He said it was an invalid binding.

  Binding is required here, but I bound it, so I did the following operations

  

  • Add the following words in the pom file to solve the problem. Remember to refresh maven after adding

  • <build>
        <resources>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <filtering>true</filtering>
            </resource>
        </resources>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-resources-plugin</artifactId>
                <configuration>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
        </plugins>
    </build>
    

  

• I think the above problem occurs because my BlogMapper.xml is placed under src/main/java/com/hxl/dao. Instead of the one above under resources. So that's the problem.

3. if

List<Blog> queryBlogIF(Map map);

<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from blog where 1=1
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</select>

@Test
public void queryBlogIF(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
    map.put("title","Java");
    List<Blog> blogs = mapper.queryBlogIF(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

4. trim(where,set)

4.1 where

<select id="queryBlogIF" parameterType="map" resultType="blog">
  select * from blog
  <where>
      <if test="title != null">
          title = #{title}
      </if>
      <if test="author != null">
          and author = #{author}
      </if>
  </where>
</select>

• The where element will only insert the "where" self clause if the condition of at least one child element returns the SQL clause. Moreover, if the statement starts with "AND" OR ", the where element will also remove them.

  • It's easy to see that the previously mentioned if uses a where 1=1. In fact, this is inappropriate. But now a label is added, which can automatically identify whether it is the first where, and then judge whether to add and.
  • The existence of where will determine whether and is needed. And should be added before and after the second.

If the where element is different from what you expect, you can also customize the functions of the where element by customizing the trim element. For example, the equivalent custom trim element to the where element is:

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

The prefixOverrides property ignores text sequences separated by pipe characters (note that spaces are necessary in this example). The above example removes all the contents specified in the prefixOverrides property and inserts the contents specified in the prefix property.

4.2 set

• The set element will dynamically prefix the set keyword and delete irrelevant commas, because these commas are likely to be left behind the generated SQL statement after the conditional statement is used

<!--Basic type not required resultType-->
<update id="updateBlog" parameterType="map">
  update blog
  <set>
      <if test="title != null">
          title = #{title},
      </if>
      <if test="author != null">
          author = #{author},
      </if>
  </set>
  where id = #{id}
</update>

@Test
public void updateBlog(){
  SqlSession sqlSession = MybatisUtils.getSqlSession();
  BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
  HashMap map = new HashMap();
  map.put("id","ac040b29e3b047a09a4fe78797193597");
  map.put("author","Wang Mumu");
  map.put("title","Java1");
  mapper.updateBlog(map);
  sqlSession.close();
}

Take a look at the custom trim element equivalent to the set element:

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

Note that we have overridden the suffix value setting and customized the prefix value.

5. choose(when,otherwise)

<select id="queryBlogChoose" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="author != null">
                author = #{author}
            </when>
            <otherwise>
                and views = #{views}
            </otherwise>
        </choose>
    </where>
</select>

This choose is a bit like switch and case. It can only run one. For example, if the previous ones do not match, he must have a view to match, otherwise an error will be reported. If one of the previous ones matches in order, he will jump out.

6. foreach

select * from user where 1=1 and
<foreach item="id" collection="ids"
	open="(" separator="or" close=")">
</foreach>

(id=1 or id=2 or id=3)

use

//Query the blog of record No. 1-2-3
List<Blog> queryBlogForeach(Map map);

<!--Deliver a universal map，this map A collection can exist-->
<select id="queryBlogForeach" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <foreach item="id" collection="ids" open="and (" separator="or" close=")">
            id = #{id}
        </foreach>
    </where>
</select>

@Test
public void queryBlogForeach(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
    //Here, because uuid is String, if it is 1 or 2, Integer is used
    ArrayList<String> ids = new ArrayList<String>();
    map.put("ids",ids);
    List<Blog> blogs = mapper.queryBlogForeach(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

We can also carry out the following tests

@Test
public void queryBlogForeach(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
    ArrayList<String> ids = new ArrayList<String>();
    ids.add("ac040b29e3b047a09a4fe78797193597");
    map.put("ids",ids);
    List<Blog> blogs = mapper.queryBlogForeach(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

@Test
public void queryBlogForeach(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
    ArrayList<String> ids = new ArrayList<String>();
    ids.add("ac040b29e3b047a09a4fe78797193597");
    ids.add("a46a7874d69f4d69b9572816d8f30ab7");
    map.put("ids",ids);
    List<Blog> blogs = mapper.queryBlogForeach(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

7. SQL fragment

Sometimes we will extract some functional parts for reuse

1. Extract common parts using SQL tags.

   For example: [the external chain picture transfer fails, and the source station may have an anti-theft chain mechanism. It is recommended to save the picture and upload it directly (img-z0t21UFq-1636723718299)(... / Tool/Typora/image/image-20211006095656927.png)]

2. Use the include tag reference where necessary

   We can add an sql tag, and the id can be taken at will. Then, the following include refid is the reference id, and the above id is one. In this way, reuse can be realized.

   <sql id="if-title-author">
       <if test="title != null">
           title = #{title}
       </if>
       <if test="author != null">
           and author = #{author}
       </if>
   </sql>
   
   <select id="queryBlogIF" parameterType="map" resultType="blog">
       select * from blog
       <where>
           <include refid="if-title-author"/>
       </where>
   </select>
   

3. matters needing attention:

   • It is best to define SQL fragments based on a single table
   • Do not have a where tag

8. Summary

The so-called dynamic SQL is still an SQL statement in essence, but we can execute a logical code at the SQL level.

if，where，set，choose，when

Dynamic SQL is splicing SQL statements. We just need to ensure the correctness of SQL and arrange and combine them according to the SQL format

Recommendations:

• First write a complete SQL in Mysql, and then modify it accordingly to become our dynamic SQL. Realize universal.

13, Cache

1. Introduction

query-->It needs to connect to the database, which consumes resources
	The results of a query are temporarily stored in a place that can be directly accessed-->Memory: caching
 When we query the same data again, we directly go to the cache instead of the database 

1. What is Cache?
   • Temporary data in memory
   • Put the data frequently queried by users in the cache (memory), and users do not need to query from the disk (relational database data file) but from the cache to query the data, so as to improve the query efficiency and solve the performance problem of high concurrency system.
2. Why cache?
   • Reduce the number of interactions with the database, reduce system overhead and improve system efficiency
3. What kind of data can be cached?
   • Frequently queried and infrequently changed data

2. Mybatis cache

1. Mybatis includes a very powerful query caching feature, which can easily customize and configure the cache. Caching can greatly improve query efficiency.
2. Two levels of cache are defined by default in the Mybatis system: L1 cache and L2 cache
   • By default, only L1 cache is enabled. (Sqlsession level cache, also known as local cache)
   • The L2 cache needs to be manually enabled and configured. It is based on the namespace level cache
   • In order to improve scalability, mybatis defines a Cache interface. We can customize the L2 Cache by implementing the Cache interface.

3. L1 cache

• The L1 cache is also called the local cache: SqlSession
  • The data queried during the same session with the database will be placed in the local cache
  • In the future, if you need to obtain the same data, you can get it directly from it. There is no need to query the database

3.1 test steps:

1. Open log

   In the core configuration file

   <settings>
       <!--Standard log factory implementation-->
       <setting name="logImpl" value="STDOUT_LOGGING"/>
   </settings>
   

2. Test to query the same record twice in a Session

   @Test
   public void queryUserByIdTest(){
       SqlSession sqlSession = MybatisUtils.getSqlSession();
       UserMapper mapper = sqlSession.getMapper(UserMapper.class);
       User user = mapper.queryUserById(1);
       System.out.println(user);
       System.out.println("========");
       User user1 = mapper.queryUserById(1);
       System.out.println(user1);
       sqlSession.close();
   }
   

3. View log output

   

3.2 cache failure

• Query different things

• The addition, deletion and modification operation may change the original data, so the cache must be refreshed

  • For example, when checking two identical users, insert an operation to modify the user in the middle

• Query different Mapper.xml

• Manual cache cleanup

  • sqlSession.clearCache();//Manual cache cleanup
    

3.3 summary

The L1 cache is enabled by default and is only valid in one sqlSession, that is, the interval from getting the connection to closing the connection

The first level cache is equivalent to a map

4. L2 cache

4.1 introduction

4.2 opening steps

1. Turn on global cache

   Although the default is true, you still need to turn it on to enhance readability

   

   In the core configuration file

   <settings>
       <!--Show global cache enabled-->
       <setting name="cacheEnabled" value="true"/>
   </settings>
   

2. To enable L2 caching, you need to add a line of code to the sql mapping file

   <!--At present Mapper.xml Enable L2 cache in-->
   <cache/>
   

   You can also customize some parameters

   <!--This is on the official website. In addition, there will be some settings, such as first in first out, 60 seconds, 512 references, and read-only true-->
   <cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"/>
   

   You can also turn off the cache where you want, just add the following code under the sql statement tab

   

4.3 testing

1. If the L2 cache is not added, querying two sqlsessions will enter the database twice

   @Test
   public void queryUserByIdTest(){
       SqlSession sqlSession = MybatisUtils.getSqlSession();
       SqlSession sqlSession2 = MybatisUtils.getSqlSession();
       UserMapper mapper = sqlSession.getMapper(UserMapper.class);
       UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class);
       User user = mapper.queryUserById(1);
       System.out.println(user);
       User user1 = mapper2.queryUserById(1);
       System.out.println(user1);
   
       sqlSession.close();
       sqlSession2.close();
   }
   

   

2. If L2 cache is enabled

   @Test
   public void queryUserByIdTest(){
       SqlSession sqlSession = MybatisUtils.getSqlSession();
       SqlSession sqlSession2 = MybatisUtils.getSqlSession();
   
       UserMapper mapper = sqlSession.getMapper(UserMapper.class);
       User user = mapper.queryUserById(1);
       System.out.println(user);
       sqlSession.close();
   
       UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class);
       User user1 = mapper2.queryUserById(1);
       System.out.println(user1);
       System.out.println(user == user1);
       sqlSession2.close();
   }
   

   

3. Question:

   We need to serialize the entity class, otherwise an error will be reported

   Caused by:java.io.NotSerializableException:com.hxl.pojo.User
   

   We need to add implements Serializable after the entity class

   @Data
   public class User implements Serializable {
       private int id;
       private String name;
       private String pwd;
   }
   

4.4 summary

1. As long as the L2 cache is enabled, it is valid under the same Mapper
2. All data will be put in the first level cache first
3. Only when the session is committed or closed will it be committed to the L2 cache
4. Entity class to serialize

5. Cache principle

6. User defined cache Ehcache

Ehcache is a widely used open source Java distributed cache, mainly for general cache

To use Ehcache in your program, you must first import the package

<dependency>
    <groupId>org.mybatis.caches</groupId>
    <artifactId>mybatis-ehcache</artifactId>
    <version>1.2.1</version>
</dependency>

Create an ehcache.xml file under resources

<?xml version="1.0" encoding="UTF-8" ?>
<ehcache xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:noNamespaceSchemaLocation="http://ehcache.org/ehcache.xsd"
         updateCheck="false">

    <!--

    -->
    <diskStore path="./tmpdir/Tmp_EhCache"/>
    <defaultCache
            eternal="false"
            maxElementsInMemory="10000"
            overflowToDisk="false"
            diskPersistent="false"
            timeToIdleSeconds="1800"
            timeToLiveSeconds="259200"
            memoryStoreEvictionPolicy="LRU"/>
    <cache
            name="cloud_user"
            eternal="false"
            maxElementsInMemory="5000"
            overflowToDisk="false"
            diskPersistent="false"
            timeToIdleSeconds="1800"
            timeToLiveSeconds="1800"
            memoryStoreEvictionPolicy="LRU"/>
</ehcache>

Under the required Mapper.xml

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

test