Dynamic sql

Dynamic SQL is one of the powerful features of MyBatis. Using dynamic SQL, we can flexibly splice SQL statements according to different conditions. Common dynamic SQL tags are as follows:

• if
• choose(when,otherwise)
• trim(where,set)
• foreach

if

When < if > sql < / if > is used, if the conditions are met, the SQL will be spliced with the previous SQL fragments. An example is as follows:

<select id="selectStudentIf" resultType="com.example.entity.Student">
    select * from student
    where 1 = 1
    <if test="name != null and name != ''">
        and name = #{name}
    </if>
    <if test="age > 0">
        and age > #{age}
    </if>
</select>

If the value of name is null or the value is an empty string, the and name = #{name}sql fragment will not be spliced with the previous sql. If both conditions of the above example are met, the resulting sql statement is:

select * from student where 1 = 1 and name = #{name} and age > #{age}

Note: in the above example, when we look at it for the first time, we can't help wondering, isn't the condition of 1 = 1 redundant? In fact, after careful consideration, we will find that if this condition is removed, when the first condition is not satisfied (that is, the name value is null or empty string), the sql statement formed is:

select * from student where and age > #{age}

Obviously, there is an error in the sql statement, and should be removed. If we add a constant condition before and, we can solve this problem. In addition, we can also use < where > to solve this problem (see below).

choose(when,otherwise)

< choose > is a bit similar to switch case. Using this tag, you can select one of multiple conditions to use. An example is as follows:

<select id="selectStudentIf" resultType="com.example.entity.Student">
	select * from student
    where 1 = 1
    <choose>
    	<!-- Condition 1 -->
		<when test="name != null and name != ''">
            and name = #{name}
        </when>
        <!-- Condition 2 -->
        <when test="age > 0">
            and age > #{age}
        </when>
        <otherwise>
            and id < #{id}
        </otherwise>
    </choose>
</select>

As shown above, there are three cases:

1. If conditions 1 and 2 are met, select the sql statement in condition 1. The final result is:

select * from student where 1 = 1 and name = #{name}

1. When only one of the conditions 1 and 2 is satisfied, select the sql statement that meets the conditions. The final result is:

<!-- Condition 1 met -->
select * from student where 1 = 1 and name = #{name}

<!-- Condition 2 met -->
select * from student where 1 = 1 and age > #{age}

1. If conditions 1 and 2 are not satisfied, select the sql statement in < otherwise > (if this label is used), and the final result is:

select * from student where 1 = 1 and id < #{id}

trim(where,set)

• where
  Using < where >, we can solve the problems mentioned in the if tag example above. The examples are as follows:

<select id="selectStudentWhere" resultType="com.example.entity.Student">
	select * from student
	<where>
		<!-- Condition 1 -->
		<if test="name != null and name != ''">
			name = #{name}
		</if>
		<!-- Condition 2 -->
		<if test="age > 0">
			or age > #{age}
		</if>
	</where>
</select>

When condition 1 is not satisfied and condition 2 is satisfied, the final result is:

select * from student where age > #{age}

At this time, there will be no redundant and | or. The where tag automatically removes the redundant and or for us to get the correct sql statement.

• set

<update id="updateSet">
	update student
	<set>
		<if test="name != null">name = #{name},</if>
		<if test="age > 0">age = #{age},</if>
		<!-- Condition 3 -->
		<if test="email != null">email = #{email}</if>
	</set>
	where id = #{id}
</update>

As in the above example, if condition 3 is not satisfied and at least one of the first two conditions is satisfied, the final sql statement will appear redundant. Using < set > can remove the redundant commas and obtain the correct sql statement.

• trim
  If the results of the above where and set tags are different from our expectations, you can customize the functions of the where and set tags by customizing the trim tags. For example, the custom trim tags equivalent to the where and set tags are as follows:

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

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

foreach

The foreach tag is used in the scenario of traversing the collection (for example, in conditional statement). An example is as follows:

<select id="selectForeachOne" resultType="com.example.entity.Student">
	select * from student where id in
	<foreach collection="list" item="myid" open="(" close=")" separator=",">
		#{myid}
	</foreach>
</select>

If the elements in the traversed collection are user-defined classes, such as list < student >, item = "stu", when the value of id attribute is used, #{myid} can be changed to #{stu.id}.
Description of each attribute value in foreach tag:

collection: Represents the type of method parameter in the interface. If it is an array, use array，If it is list Then use list
item: Custom variables that represent array and collection members
open: Character at the beginning of the loop
close: Character at the end of the loop
separator: Separator between collection members

sql fragment

Steps:

1. First define < SQL id = "user defined name (unique)" > sql statement, table name, field, etc. < / SQL >
2. Reuse < include refid = "value of id" / >
   Examples are as follows:

<sql id="studentSql">
	select * from student
</sql>

<!-- use -->
<select id="selectStudentIf" resultType="com.example.entity.Student">
    <include refid="studentSql"/>
    ...
</select>