For some complex queries, we may specify multiple query conditions, but these conditions may or may not exist, such as finding a house on 58 Tongcheng, we may specify area, floor and location to find a house source, or we may specify area, price, household type and location to find a house source, at which point the user-specified bar is required.Component generates SQL statements dynamically.If you don't use a persistence framework, you may need to assemble your own SQL statements, but MyBatis provides dynamic SQL capabilities to solve this problem.The main elements used to implement dynamic SQL in MyBatis are:
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
Due to the complexity of each project, you can refer to it Section 1: Getting started with mybatis To build a simple project, and then test the contents of this section.
1. Dynamic sql parameter delivery
When we focus on dynamic sql, there is actually more than one parameter to query. At this time, we usually have the following choices:
- Using multiple parameters in a method with Param annotations
- Use POJO or TO
- Using map
In the following tests, we will describe each of them, starting with a scenario where we query by age and address.So the mapper interface method is:
public interface PersonMapper { List<Person> getPersonByParam(@Param("age") Integer age, @Param("address") String address); List<Person> getPersonByPOJO(Person person); List<Person> getPersonByMap(Map<String,Object> map); }
After you have written the mapper interface, write the mapper mapping file.The above three methods have different parameter forms, but the select s below are the same except for the id.
<mapper namespace="com.yefengyu.mybatis.mapper.PersonMapper"> <select id="getPersonByParam" resultType="com.yefengyu.mybatis.entity.Person"> select id, first_name firstName, last_name lastName, age, email, address from person where age > #{age} and address = #{address} </select> <select id="getPersonByPOJO" resultType="com.yefengyu.mybatis.entity.Person"> select id, first_name firstName, last_name lastName, age, email, address from person where age > #{age} and address = #{address} </select> <select id="getPersonByMap" resultType="com.yefengyu.mybatis.entity.Person"> select id, first_name firstName, last_name lastName, age, email, address from person where age > #{age} and address = #{address} </select> </mapper>
The following tests are performed:
package com.yefengyu.mybatis; import com.yefengyu.mybatis.mapper.PersonMapper; import com.yefengyu.mybatis.entity.Person; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import java.io.IOException; import java.io.InputStream; import java.util.HashMap; import java.util.List; import java.util.Map; public class Main { public static void main(String[] args) throws IOException { InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml"); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream); SqlSession sqlSession = sqlSessionFactory.openSession(); PersonMapper mapper = sqlSession.getMapper(PersonMapper.class); //Annotation Form Parameter List<Person> personByParam = mapper.getPersonByParam(20, "beijing"); System.out.println(personByParam.size()); //POJO Formal parameters Person person = new Person(); person.setAge(20); person.setAddress("beijing"); List<Person> personByPOJO = mapper.getPersonByPOJO(person); System.out.println(personByPOJO.size()); //map Formal parameters Map<String, Object> map = new HashMap<>(); map.put("age",20); map.put("address","beijing"); List<Person> personByMap = mapper.getPersonByMap(map); System.out.println(personByMap.size()); sqlSession.close(); } }
The above code uses age and address as filtering criteria in all three parameter passes, so the query is fine.But now let's modify the above code so that we don't pass the value of address, that is, we don't use address as the filter condition, we just use age as the filter condition.
1,mapper.getPersonByParam(20, null);
2. Comment on this sentence: //person.setAddress("beijing");
3. Comment on this sentence: //map.put("address","beijing");
//Annotation Form Parameter List<Person> personByParam = mapper.getPersonByParam(20, null); System.out.println(personByParam.size()); //POJO Formal parameters Person person = new Person(); person.setAge(20); //person.setAddress("beijing"); List<Person> personByPOJO = mapper.getPersonByPOJO(person); System.out.println(personByPOJO.size()); //map Formal parameters Map<String, Object> map = new HashMap<>(); map.put("age",20); //map.put("address","beijing"); List<Person> personByMap = mapper.getPersonByMap(map); System.out.println(personByMap.size());
* We expect that address will no longer be used as a filter condition if the value of address is not passed, but the result of the query is 0 pieces of data because address = null in sql is also used as a query condition and natural queries do not achieve the desired result.So how do we meet our needs?
2,if
We can use the if tag to solve the above problem.
<select id="getPersonByPOJO" resultType="com.yefengyu.mybatis.entity.Person"> select id, first_name firstName, last_name lastName, age, email, address from person where <if test="age!=null"> age > #{age} </if> <if test="address!=null and address!=''"> and address = #{address} </if> </select>
Use the if tag above, and if age is not null, stitch age > #{age} to where. Similarly, if the address condition is met, it will stitch to the back.
There are four cases (means pass this property, _means don't pass this property):
| Sequence Number | age | address | sql |
| 1 | ✔ | ✔ | select id, first_name firstName, last_name lastName, age, email, address from person where age > ？ and address = ？ |
| 2 | ✔ | ❌ | select id, first_name firstName, last_name lastName, age, email, address from person where age > ？ |
| 3 | ❌ | ✔ | select id, first_name firstName, last_name lastName, age, email, address from person where and address = ？ |
| 4 | ❌ | ❌ | select id, first_name firstName, last_name lastName, age, email, address from person where |
You can see that the latter two obvious problems can be solved simply by adding 1=1 after where and and and before the first if condition, as follows:
<select id="getPersonByPOJO" resultType="com.yefengyu.mybatis.entity.Person"> select id, first_name firstName, last_name lastName, age, email, address from person where 1=1 <if test="age!=null"> and age > #{age} </if> <if test="address!=null and address!=''"> and address = #{address} </if> </select>
At this time, the sql corresponding to the four situations is, which can query the data normally.
| Sequence Number | age | address | sql |
| 1 | ✔ | ✔ | select id, first_name firstName, last_name lastName, age, email, address from person where 1=1 and age > ？ and address = ？ |
| 2 | ✔ | ❌ | select id, first_name firstName, last_name lastName, age, email, address from person where 1=1 and age > ？ |
| 3 | ❌ | ✔ | select id, first_name firstName, last_name lastName, age, email, address from person where 1=1 and address = ？ |
| 4 | ❌ | ❌ | select id, first_name firstName, last_name lastName, age, email, address from person where 1=1 |
With 1=1, you can solve a series of sql splicing problems, but mybatis itself provides stronger tags to solve these problems.
3,where
The where tag can be used to solve a series of problems arising from the above sql splicing. When using the where tag, you cannot write the where keyword in sql at the same time.When using it, just include the if judgment in the where tag.
<select id="getPersonByPOJO" resultType="com.yefengyu.mybatis.entity.Person"> select id, first_name firstName, last_name lastName, age, email, address from person <where> <if test="age!=null"> age > #{age} </if> <if test="address!=null and address!=''"> and address = #{address} </if> </where> </select>
The where tag inserts the WHERE clause only if the condition for at least one subelement returns an SQL clause.(Resolve Article 4)
Also, if the statement begins with AND or OR, the where tag will remove them.(Resolve Article 3)
| Sequence Number | age | address | sql |
| 1 | ✔ | ✔ | select id, first_name firstName, last_name lastName, age, email, address from person where and age > ？ and address = ？ |
| 2 | ✔ | ❌ | select id, first_name firstName, last_name lastName, age, email, address from person where age > ？ |
| 3 | ❌ | ✔ | select id, first_name firstName, last_name lastName, age, email, address from person where address = ？ |
| 4 | ❌ | ❌ | select id, first_name firstName, last_name lastName, age, email, address from person |
4,set
This tag is mainly used in the update operation. If an attribute is passed, the corresponding field is updated.Here, if you want to update an age or address based on an id and update which attribute as it is passed in, then using dynamic sql is the following.
<update id="updatePerson"> update person <set> <if test="age!=null"> age = #{age}, </if> <if test="address!=null and address!=''"> address = #{address} </if> </set> where id = #{id} </update>
Here's why you need to use the set tag:
If you remove the set tag, add a set keyword in front of the first if tag, as follows, the same sql splicing problem can occur as above:
- Only age will be passed in, where will be preceded by an additional comma.
- If neither is passed, where will be preceded by a set keyword.
- Splice sql normally if all pass
- Pass-only address also splice sql properly
<update id="updatePerson"> update person set <if test="age!=null"> age = #{age}, </if> <if test="address!=null and address!=''"> address = #{address} </if> where id = #{id} </update>
Here, the set tag dynamically precedes the SET keyword and also deletes extraneous commas, which are likely to be left after the generated SQL statement when conditional statements are used.Because the "if" tag is used, if the last "if" does not match and the previous match, a comma is left at the end of the SQL statement.
5,trim
trim tags are powerful and can be used in the following ways:
<trim prefix="" prefixOverrides="" suffix="" suffixOverrides=""> ...... </trim>
The trim tag has four attributes that can be used in random combinations to represent:
- Prefix: prefix surrounded statements
- PrefixOverrides: Removes all content specified in the prefixOverrides property at the front of the enclosed statement
- Suffix: append a suffix to the surrounding statement
- SuffixOverrides: Removes all content specified in the suffixOverrides property at the end of the enclosed statement
The where tag is equivalent to noting that the space in this example is also necessary (AND |OR).
<trim prefix="WHERE" prefixOverrides="AND |OR "> ... </trim>
The set tag is equivalent to:
<trim prefix="SET" suffixOverrides=","> ... </trim>
The trim and where and set tags are the same kind of tag.
6,choose, when, otherwise
Sometimes we don't want to apply to all conditional statements, we just want to choose one of them.In this case, MyBatis provides the choose tag, which is a bit like the switch statement in Java.Now we want to filter by age criteria if age is provided, and not focus on other criteria.If age is not provided, if address is provided, it is filtered according to the address condition, and the others are not filtered even if ten conditions are met.
<select id="getPersonByPOJO" resultType="com.yefengyu.mybatis.entity.Person"> select id, first_name firstName, last_name lastName, age, email, address from person where <choose> <when test="age!=null"> age = #{age} </when> <when test="address!=null and address!=''"> address = #{address} </when> <otherwise> 1=1 </otherwise> </choose> </select>
Be careful:
1. where keywords are handwritten.
2. choose encapsulates different query criteria. when is similar to if function to determine whether the criteria are met.
3. You do not need to add and before each filter condition, because only one filter condition will be executed.
4. Finally, you can add else, where the filter condition is executed when all conditions are not satisfied.