1. Dynamic SQL
1.1 what is dynamic SQL?
What is dynamic SQL?
- Dynamic SQL refers to generating different SQL statements according to different conditions.
Usually, we operate some different SQL statements, which are directly changed in the code, which is very troublesome. It is very convenient to use dynamic SQL of Mybatis.
Using the feature of dynamic SQL can completely get rid of this pain.
If you've used it before JSTL Or any class based XML Language text processor, you are on the dynamic SQL Elements may feel deja vu. stay MyBatis In previous releases, it took time to understand a large number of elements. With powerful OGNL Expression for, MyBatis 3 Most of the previous elements have been replaced, and the types of elements have been greatly simplified. Now there are fewer types of elements to learn than half of the original ones. The contents to be learned include the following four Tags: if choose (when, otherwise) trim (where, set) foreach
1.2 preparation
Blog entity class:
package com.itholmes.pojo;
import lombok.Data;
import java.util.Date;
@Data
public class Blog {
private String id;
private String title;
private String author;
/*
For this hump naming, we can configure the content of the corresponding database in the setting of the core configuration file of mybatis, and mapUnderscoreToCamelCase is true
*/
private Date createTime;
private int views;
}
Create an IDUtils class to get a string of UUId. Take this string as the id attribute of the Blog.
package com.itholmes.utils;
import org.junit.jupiter.api.Test;
import java.util.UUID;
public class IDUtils {
public static String getUUId(){
/*
UUID.randomUUID().toString()To generate a uuid string.
Note: UUID will not be repeated!
*/
return UUID.randomUUID().toString().replaceAll("-","");
}
//Test the effect of uuid.
@Test
public void test(){
String uuId = IDUtils.getUUId();
String uuId2 = IDUtils.getUUId();
String uuId3 = IDUtils.getUUId();
System.out.println(uuId);
System.out.println(uuId2);
System.out.println(uuId3);
}
}
1.3 if statement of dynamic SQL
<select id="findActiveBlogWithTitleLike"
resultType="Blog">
SELECT * FROM BLOG
WHERE state = 'ACTIVE'
<if test="title != null">
AND title like #{title}
</if>
</select>
The structure is: add a test attribute to the if tag, and the test attribute contains some restriction statements of the field name.
BlogMapper.xml file:
<?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.itholmes.dao.BlogMapper">
<select id="queryBlogIF" parameterType="map" resultType="com.itholmes.pojo.Blog">
select * from mybatis.blog where 1=1
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
</mapper>
BlogMapper interface class:
package com.itholmes.dao;
import com.itholmes.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
//Query blog
List<Blog> queryBlogIF(Map map);
}
Test class:
import com.itholmes.dao.BlogMapper;
import com.itholmes.pojo.Blog;
import com.itholmes.utils.IDUtils;
import com.itholmes.utils.MybatisSqlSession;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
public class test {
@Test
public void test2(){
SqlSession sqlSession = MybatisSqlSession.getMybatisSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title","data structure");
map.put("author","Zhang San");
List<Blog> list = mapper.queryBlogIF(map);
for (Blog blog : list) {
System.out.println(blog);
}
sqlSession.close();
}
}
1.4 trim (where, set) of dynamic SQL
where plays a key role. When we execute some dynamic SQL statements, SQL statement splicing errors may occur. As follows:
Function: the WHERE element inserts the "WHERE" clause only when the child element returns anything. Moreover, if clause starts with the "AND" OR ", WHERE element will also remove them.
Function of set: the set element will dynamically prefix the set keyword (set keyword of update set statement) and delete irrelevant commas.
The statement has the following effect:
<update id="updateBlog" parameterType="map">
update mybatis.blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author}
</if>
</set>
where id = #{id}
</update>
The trim element has four attributes:
- Prefix attribute: add the value of prefix to the beginning of the statement.
- prefixOverrides attribute: if the prefixOverrides attribute value is matched at the beginning of the statement, it will be removed.
- Suffix attribute: add the value of suffix to the end of the statement.
- suffixOverrides attribute: if the value of suffixOverrides attribute matches at the end of the statement, it will be removed.
As follows:
<update id="updateBlog" parameterType="map">
update mybatis.blog
<trim prefix="set" suffixOverrides="," suffix="where id = #{id}">
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author},
</if>
</trim>
</update>
1.5 choose(when, otherwise) statement of dynamic SQL
<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = 'ACTIVE'
<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>
</select>
choose，when，otherwise. It is similar to switch, case and default in Java code.
BlogMapper.xml file:
<?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.itholmes.dao.BlogMapper">
<select id="queryBlogChoose" parameterType="map" resultType="com.itholmes.pojo.Blog">
select * from mybatis.blog
<where>
<choose>
<when test="title != null">
-- Not here and，Because the first element is preceded by where.
title = #{title};
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</where>
</select>
</mapper>
BlogMapper interface class:
package com.itholmes.dao;
import com.itholmes.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
//choose test
List<Blog> queryBlogChoose(Map map);
}
Test class:
public void test3(){
SqlSession sqlSession = MybatisSqlSession.getMybatisSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
// map.put("title", "data structure");
// map.put("author", "Zhang San");
map.put("views",999);
List<Blog> list = mapper.queryBlogChoose(map);
for (Blog blog : list) {
System.out.println(blog);
}
sqlSession.close();
}
1.6 sql fragment of dynamic sql
Sometimes, we may extract some common parts for reuse.
Step 1: use the sql tag to define the common part.
<sql id="if-title-author">
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
Step 2: use the include tag reference where necessary.
<select id="queryBlogIF" parameterType="map" resultType="com.itholmes.pojo.Blog">
select * from mybatis.blog
<where>
<include refid="if-title-author"></include>
</where>
</select>
Precautions for sql fragments:
- It is best to define SQL fragments based on a single table.
- In the sql tag, do not have the where tag.
1.7 foreach statement of dynamic SQL
The function of foreach element: traverse the collection (especially when building IN conditional statements).
foreach is very simple. The corresponding attribute names can be understood as follows:
<!--
use foreach Make a query.
Demonstrate the following statement:
select * from mybatis.blog where 1=1 and (id = 1 or id = 2 or id = 3);
One accepted here map，this map You can save a collection in.
-->
<select id="queryBlogForeach" parameterType="map" resultType="com.itholmes.pojo.Blog">
select * from mybatis.blog
<where>
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>
2. What is cache? The role of caching in architecture?
When we query data, we first need to connect to the database and transfer the data to the background. In this way, each query result can be temporarily stored in a place that can be obtained directly! That's memory. This temporary data is called cache.
What is a cache?
- There is temporary data in memory.
- Put the data frequently queried by users in the cache (memory), and users can query the data directly from the cache instead of from the disk (relational database data file), so as to improve the query efficiency and solve the performance problem of high concurrency system.
Understanding of Architecture:
Mybatis 3. Cache
4. Mybatis L1 cache
4.1 preparation and first level cache cases
User entity class:
package com.itholmes.pojo;
import lombok.Data;
@Data
@AllArgsConstructor
public class User {
private int id;
private String name;
private String pwd;
}
UserMapper interface class:
package com.itholmes.dao;
import com.itholmes.pojo.User;
import org.apache.ibatis.annotations.Param;
public interface UserMapper {
//Query the user with the specified id
User queryUsers(@Param("id") int a);
}
UserMapper.xml file:
<?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.itholmes.dao.UserMapper">
<select id="queryUsers" parameterType="int" resultType="User">
select * from user where id = #{id};
</select>
</mapper>
Test class:
import com.itholmes.dao.UserMapper;
import com.itholmes.pojo.User;
import com.itholmes.utils.MybatisSqlSession;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;
public class test {
@Test
public void test(){
SqlSession sqlSession = MybatisSqlSession.getMybatisSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user1 = mapper.queryUsers(1);
System.out.println(user1);
System.out.println("===================");
User user2 = mapper.queryUsers(1);
System.out.println(user2);
System.out.println("user1 and user2 Equality:" + (user1==user2));
sqlSession.close();
}
}
From the execution results, we can see that for the same query twice, we only call sql once, and the objects obtained are the same (the object address is the same). This second query uses the effect of caching. Here, mybatis enables the L1 cache by default.
4.2 L1 cache invalidation (cleanup)
The cache must be invalidated (if it fails, the cache must be cleaned up). If the data in the database changes and the cache does not, there will be a big problem with the data obtained!!
Cache invalidation:
- 1. Insert different data.
- 2. Adding, deleting and modifying may change the original data, so the cache must be refreshed!! For example:
import com.itholmes.dao.UserMapper;
import com.itholmes.pojo.User;
import com.itholmes.utils.MybatisSqlSession;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;
public class test {
@Test
public void test(){
SqlSession sqlSession = MybatisSqlSession.getMybatisSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user1 = mapper.queryUsers(1);
System.out.println(user1);
System.out.println("Here we add the update operation, then the original cache will become invalid!!! To reload.");
mapper.updateUser(new User(2,"Wang Wu","1234"));
System.out.println("===============================================");
User user2 = mapper.queryUsers(1);
System.out.println(user2);
System.out.println("user1 and user2 Equality:" + (user1 == user2));
sqlSession.close();
}
}
- 3. Query different mapper XML file.
- 4. Manually clean up the cache and use sqlsession clearCache(); Manual cleaning. As follows:
import com.itholmes.dao.UserMapper;
import com.itholmes.pojo.User;
import com.itholmes.utils.MybatisSqlSession;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;
public class test {
@Test
public void test(){
SqlSession sqlSession = MybatisSqlSession.getMybatisSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user1 = mapper.queryUsers(1);
System.out.println(user1);
System.out.println("Manual cache cleanup");
sqlSession.clearCache();
System.out.println("===============================================");
User user2 = mapper.queryUsers(1);
System.out.println(user2);
System.out.println("user1 and user2 Equality:" + (user1 == user2));
sqlSession.close();
}
}
4.3 L1 cache summary
The L1 cache is enabled by default and is valid only once in a SqlSession, that is, to get the interval connected to the closed connection.
By default, only local session caching is enabled, which only caches the data in one session. Therefore, we also need to enable the global L2 cache.
5. Mybatis L2 cache
L2 cache is also called global cache. Since the scope of L1 cache is too low, L2 cache was born.
L2 cache is a cache based on namespace level. A namespace corresponds to a L2 cache.
Working mechanism of L2 cache:
To enable L2 caching, you only need to XML file, just declare a cache tag.
To use L2 cache:
- The first step is to turn on the cache. Go to the setting in the mybatis core configuration file to configure whether the global cache is enabled.
<settings>
<!--Here we use the standard log factory implementation-->
<setting name="logImpl" value="STDOUT_LOGGING"/>
<!--Whether to enable automatic hump naming mapping-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!--Globally turn on or off any cache configured in all mapper profiles.-->
<setting name="cacheEnabled" value="true"/>
</settings>
- 2. In mapper. To use L2 cache Open the L2 cache in the XML file and configure relevant properties.
Statements that do not want to load the cache can be defined with the useCache attribute
<!--
Enable L2 cache:
eviction Define a clear cache policy.
flushInterval Define the cache refresh time. Here, the cache is refreshed every 60.
size The maximum number of caches.
readOnly Read only.
-->
<cache eviction="FIFO"
flushInterval="60000"
size="512"
readOnly="true"/>
L2 cache test:
Test class:
import com.itholmes.dao.UserMapper;
import com.itholmes.pojo.User;
import com.itholmes.utils.MybatisSqlSession;
import org.apache.ibatis.session.SqlSession;
import org.junit.jupiter.api.Test;
public class test {
@Test
public void test(){
SqlSession sqlSession1 = MybatisSqlSession.getMybatisSqlSession();
UserMapper mapper = sqlSession1.getMapper(UserMapper.class);
User user1 = mapper.queryUsers(1);
System.out.println(user1);
sqlSession1.close();
/*
Here, the first level cache of sqlSession1 has ended, but the second level cache loads the contents of the first level cache into its own cache.
In this way, when we go to the next sqlSession2, we can get the data directly in the L2 cache.
* */
SqlSession sqlSession2 = MybatisSqlSession.getMybatisSqlSession();
UserMapper mapper2 = sqlSession2.getMapper(UserMapper.class);
User user2 = mapper2.queryUsers(1);
System.out.println(user2);
sqlSession2.close();
//Verify that the addresses of the two user s are the same.
System.out.println("user1 and user2 Are they equal: " + (user1 == user2));
}
}
Generally, we don't need to set any properties when using L2 cache. Just use the default.
<cache/>
But if you use the above, you may report an error, a Java io. Notserializableexception entity class has no serialization error. In this way, we must implement serialization to entity classes.
In the execution of the test, the effect is the same.
L2 cache considerations:
- As long as the L2 cache is enabled, in the same mapper XML file is valid (without refreshing).
- All data will be put in the first level cache first. Only when the sqlSession session is committed or closed, it will be committed to the L2 cache (indirectly indicating that the L2 cache is transaction level).
6. Mybatis cache principle
Process principle of executing cache:
- 1. Query the L2 cache first.
- 2. There is no L2 cache. Query the L1 cache.
- 3. If there is no L1 cache, query the database.
7. Custom cache
7.1 custom cache
Implement third-party caching or create your own caching scheme to create adapters to completely override caching behavior.
7.2 Ehcache cache (understand)
Ehcache is a widely used open source Java distributed cache. Mainly for general cache.
EhCache is one of the secondary cache technologies of Hibernate. Now, few people are using it.
First of all, there are many versions of ehcache. We need to import the ehcache of mybatis.
After that, if we use the cached mapper The type attribute of the configuration cache element in the XML file is specified as ehcache to overwrite the previous L2 cache.
<!--use Ehcache Cache.--> <cache type="org.mybatis.caches.ehcache.EhcacheCache"/>
Configure an ehcache XML to configure some configuration information of ehcache.
It's just the same as running a second level cache.
In the future, we will all use redis database for caching. Just know the caching at this level.