I. Introduction to dynamic SQL
  dynamic SQL is the most powerful function of MyBatis, in essence, splicing SQL statements according to conditions.
  I always thought that dynamic SQL was only supported by the configuration of XxxMapper.xml, but I didn't expect that MyBatis in the annotation version also supports (I don't know if it's new in the later version): write dynamic SQL through < script > script

public interface StudentMapper {

	@Select("<script>SELECT * FROM students <if test=\"id !=null \">WHERE id = #{id}</if></script>")
	public List<Student> getByEntity(Student stu);

	public List<Student> getByName(@Param(value = "name") String stu);
}

II. Use of dynamic SQL
  only the configuration version of dynamic SQL is described below.
  1. If: if it meets the conditions, it will be spliced, otherwise it will not be spliced.

<select id="getByAge" parameterType="integer" resultType="person">
    select * from persons where 1 = 1 
    <if test="age > 0">
        and age = #{age}
    </if>
</select>

  2. where: usually used in combination with if

<select id="getStudentByCondition" parameterType="student" resultType="student">
    select * from students 
    <where>
	    <if test="age > 0">
	        and age = #{age}
	    </if>
	    <if test="name != null and name != ''">
	        and name like concat('%',#{age},'%')
	    </if>
    </where>
</select>

   Tip: when the first word of the first qualified < if > block in the < where > tag is and or, it will be automatically eliminated.
  3. choose/when/otherwise: similar to switch in java

<select id="getStudent_choosewhenotherwise" parameterType="hashmap" resultType="Student">
	select * from tbl_student
	<choose>
		<when test=" id != null and id !='' ">
			where id >= #{id}
		</when>
		<when test=" birth != null and birth !='' ">
			where birth > #{birth}
		</when>
		<otherwise>
			where 1 = 1
		</otherwise>
	</choose>
</select>

  4. foreach: commonly used when using in query, traversing the list stored in the object
Method declaration in   interface:

public List<Student> getStudentByForeach(Map<String,Object> map);

Configuration in     XxxMapper.xml:

<select id="getStudentByForEach" parameterType="Map" resultType="Student">
	select * from tbl_student
	<if test="ages != null">
		<where>
			age in
			<foreach item="age" collection="ages" open="(" separator=","
			close=")">
				#{age}
			</foreach>
		</where>
	</if>
</select>

   test:

Map<String,Object> map = new HashMap<String,Object>(); 
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Integer> ages = new ArrayList<Integer>();
ages.add(33);
ages.add(64);
ages.add(0);
map.put("ages",ages);
List<Student> list = studentMapper.getStudentByForEach(map);
for (Student element : list){
  System.out.println(element.toString());
}

  5. trim: here is the meaning of substitution. prefix replaces the content of the first prefixOverrides, and suffix replaces the content of the last prefixOverrides.
Example ①: indicates that the first and or will be replaced by where when splicing sql statements

<select id="getStudentByTrim" parameterType="Student" resultType="Student">
	select * from tbl_student
	<trim prefix="where" prefixOverrides="and|or">
		<if test="age > 0">
			and age = #{age}
		</if>
		<if test="name !=null and name !='' ">
			and name like #{name}
		</if>
	</trim>
</select>

Example ②:

<insert id="insertByTrim" parameterType="map">
	insert into tbl_student
	<trim prefix="(" suffix=")" suffixOverrides=",">
		<if test="name != null">
			name,
		</if>
		<if test="score != null">
			score,
		</if>
		<if test="birth != null">
			birth,
		</if>
		<if test="age != null">
			age,
		</if>
	</trim>
	<trim prefix=" values (" suffix=")" suffixOverrides=",">
		<if test="name != null">
			#{name},
		</if>
		<if test="score != null">
			#{score},
		</if>
		<if test="birth != null">
			#{birth},
		</if>
		<if test="age != null">
			#{age},
		</if>
	</trim>
</insert>  

  6. During the set: update operation, you can modify the attribute according to the condition, and the last one will be automatically removed.

<update id="updateStudent" parameterType="Student">
	update tbl_student
	<set>
		<if test="name !=null and name !=''">name=#{name},</if>
		<if test="score !=null and score !=''">score=#{score},</if>
		<if test="birth !=null and birth !=''">birth=#{birth},</if>
		<if test="age !=null and age !=''">age=#{age},</if>
	</set>
	where id=#{id}
</update>