01 dynamic SQL

MyBatis One of its powerful features is its dynamics SQL. If you use JDBC Or other similar framework experience , And you can see that you can stitch together according to different conditions SQL The pain of words .

dynamic SQL According to different query conditions , Generate different SQL sentence

MyBatis 3 Greatly reduced the types of elements , Now you just need to learn half of the original elements .MyBatis Use powerful based on OGNL To eliminate most of the other elements .

if： Provides an optional text search function .

Examples of official documents ：

<select id="findActiveBlogLike"
resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name like #{author.name}
</if>
</select>

choose, when, otherwise： It’s kind of like Java Medium switch sentence .

Examples of official documents ：

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

trim, where, set： Solve the first few examples where When the conditions don’t match ,SQL The problem of wrong statements

where Elements will only be in At least Yes One Conditional return of a child element SQL Clause to insert “WHERE” Clause . and , If the beginning of a sentence is “AND” or “OR”,where The elements also remove them .

Examples of official documents ：

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

If where The element doesn’t play the normal way , We can customize trim Elements to customize where The function of elements . such as , and where Element equivalent customization trim Element is ：

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

set Element can be used to dynamically include columns that need to be updated , And leave out the rest .

Examples of official documents :

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

foreach You are allowed to specify one aggregate , Declare the collection items that can be used inside the element （item） And index （index） Variable . It also allows you to specify the start and end strings and place separators between iteration results . This element is very intelligent , So it doesn’t accidentally append extra delimiters .

Examples of official documents ：

<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>

test ： Fuzzy query

UserMapper Interface

// Fuzzy query 
public abstract List<User> getUsers(Map map);

userMapper.xml file

<select id="getUsers" parameterType="Map" resultType="user" >
select * from user
<where>
<if test="name != null">
name like concat("%",#{name},"%")
</if>
<if test="id!=null">
and id = #{id}
</if>
</where>
</select>

Juint Test code 1

@Test
public void getUsers(){

SqlSession session = MybatisUtil.getSqlSession();
UserMapper mapper = session.getMapper(UserMapper.class);
Map map = new HashMap();
List<User> users = mapper.getUsers(map);
for (User user : users) {

System.out.println(user);
}
}

test result 1：

Juint Test code 2

@Test
public void getUsers(){

SqlSession session = MybatisUtil.getSqlSession();
UserMapper mapper = session.getMapper(UserMapper.class);
Map<String,Object> map = new HashMap<String,Object>();
map.put("name","H");
List<User> users = mapper.getUsers(map);
for (User user : users) {

System.out.println(user);
}
}

test result 2：

Juint Test code 3

@Test
public void getUsers(){

SqlSession session = MybatisUtil.getSqlSession();
UserMapper mapper = session.getMapper(UserMapper.class);
Map<String,Object> map = new HashMap<String,Object>();
map.put("name","H");
map.put("id",1);
List<User> users = mapper.getUsers(map);
for (User user : users) {

System.out.println(user);
}
}

test result 3:

02 cache

MyBatis Built in a powerful Transactional query caching mechanism , It can be easily configured and customized .

By default , Only local session caching is enabled , It just caches the data in a session . To enable global L2 caching , Only need SQL Add a line to the mapping file of ：

<cache/>

The effect of this simple statement is as follows :

• Map all in statement file select The result of the statement will be cached .
• Map all in statement file insert、update and delete Statement flushes the cache .
• The cache will use the least recently used algorithm （LRU, Least Recently Used） Algorithm to clear unwanted cache .
• The cache does not refresh regularly （ in other words , No refresh interval ）.
• Caching saves lists or objects （ No matter which query method returns ） Of 1024 A reference .
• The cache will be treated as read / Write cache , This means that the acquired objects are not shared , Can be modified safely by the caller , Without interfering with the potential changes made by other callers or threads .

matters needing attention ： Caching only works on cache The statement in the mapping file where the tag is located .

cache Attribute of element

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

These attribute configurations represent the creation of a FIFO cache , every other 60 seconds , At most… That can store result objects or lists 512 A reference , And the returned object is considered read-only , So making changes to them can cause conflicts among callers in different threads .

Generally in Execute the query When , Will Use the cache , The way to do it is ： take useCache Property set to true

<select id="getUser" resultType="User" useCache="true">
select * from mybatis.user
</select>

Cache can be used to query the results of the For the time being the , If short time Query the same statement more , You can use the cache Improve query speed , But using caching can Consume memory resources .
Caching only works on cache The statement in the mapping file where the tag is located .

In a real development environment , Very few Use SQL Layer cache to improve query speed .