Preface
One of the powerful features of MyBatis is its dynamic SQL. So today, the editor will introduce to you a powerful feature of Mybatis - Dynamic SQL
Dynamic SQL is a powerful feature of Mybatis. When using JDBC to manipulate data, if there are many query conditions, concatenating conditions into SQL strings is a very painful thing. The usual solution is to write a lot of if-else conditional statements to judge and splice, and ensure that you cannot forget spaces or omit commas at the end of the field. Mybatis uses a powerful dynamic SQL language to improve this situation
Dynamic SQL is based on OGNL expressions, which allows us to easily implement certain logic in SQL statements. So what elements are there to support this function? This is what I'm going to say below
- if: use if to achieve simple conditional selection
- Choose (when, otherwise): equivalent to switch statement in java, usually paired with when and otherwise
- where: simplify the conditional judgment of where in SQL statement
- set: Solve dynamic update statement
- trim: Flexible removal of unnecessary keywords
- foreach: Iterates over a set, usually used for in-conditions
The elements mentioned above are all used in files, and they are also a tag, double tag. In fact, the usage is similar to that of our ordinary Java writing. Next, the editor will explain one by one how to talk about individual elements, and let’s talk about combination tags.
Implement multi-condition query using if+where
<select resultType="User">
select * from user
<where>
<if test="username!=null and userName!=''">
and userName like CONCAT('%',#{userName},'%')
</if>
</where>
</select>
The above code is the simplest if+where SQL mapping statement. The where element tag will automatically identify whether there is a return value in its tag. If so, insert a where keyword. In addition, if the content returned by the tag starts with and or or, the where element will automatically remove it. The main attribute in the if element tag is the test attribute. The test is followed by an expression, which returns true or false to make judgments.
Use if+trim to implement querying condition
<select resultType="User">
select * from user
<trim prefix="where" prefixOverride="and|or">
<if test="username!=null and userName!=''">
and userName like CONCAT('%',#{userName},'%')
</if>
</trim>
</select>
From the above code, we can see that the usage of trim and where element tags are similar. There are a few more elements in the trim tag, so what elements are there?
- prefix: prefix, which is used to prefix the content contained in trim.
- suffix: The suffix is used to add a suffix to the content contained in trim.
- prefixOverride: Ignore the specified content (as shown as "and|or") for the header of the content contained in the trim.
- suffixOverride: ignores the specified content on the beginning and end of the content contained in trim.
Next, let’s take a look at using dynamic SQL to implement update operations
Use if+set to implement update operation
<update parameterType="AppInfo">
update app_info
<set>
<if test="logoPicPath != null">logoPicPath=#{logoPicPath},</if>
<if test="logoLocPath != null">logoLocPath=#{logoLocPath},</if>
<if test="modifyBy != null">modifyBy=#{modifyBy},</if>
<if test="modifyDate != null">modifyDate=#{modifyDate},</if>
</set>
where id=#{id}
</update>
The above code is the simplest if+set dynamic SQL. From the above code, we can see that the update operation it does is dynamic, which means that your value is not empty, and if it is not empty, it will be updated. If it is empty, it doesn’t matter. Emmmm, it seems that its design is still very user-friendly.
The above operation can also be implemented using trim
if+trim implements update operation
<update parameterType="AppInfo">
update app_info
<trim prefix="set" suffixOverride="," suffix="where id=#{id}">
<if test="logoPicPath != null">logoPicPath=#{logoPicPath},</if>
<if test="logoLocPath != null">logoLocPath=#{logoLocPath},</if>
<if test="modifyBy != null">modifyBy=#{modifyBy},</if>
<if test="modifyDate != null">modifyDate=#{modifyDate},</if>
</trim>
</update>
The editor has introduced the usage of trim before, so I won’t go into details here. I should understand all serious children’s shoes.
The next step is to use foreach to complete complex queries. Let’s talk about the foreach iteration of Mybatis parameter into an array.
The editor will first take you to understand the basic usage and properties of foreach. Foreach is mainly used in the construction in condition, which can iterate a collection in SQL statements. Its main properties are: item, index, collection, separator, close, open. Let's look at the code below.
<select resultMap="userMapByRole">
select * from user_info where userRole in
<foreach collection="array" item="roleIds" open="("separator","close=")">
#{roleIds}
</foreach>
</select>
<resultMap tye="User" >
<id property="id" clumn="id"/>
<result property="userCode" column="userCode/>
<result property="userName" column="userName/>
</resultMap>
For SQL conditional loops (in statements), you need to use the foreach tag. Then the editor will use the above code to introduce the basic properties of foreach to you.
Item: Alias for each element in the set to iterate (roleIds here).
index: Specify a name to represent the position to which each iteration is reached during the iteration process.
open: indicates what the statement starts with (since it is an in-condition statement, it must start with "(")
separator: indicates which symbol is used as the separator between each iteration (since it is an in conditional statement, ", must be used as the separator).
close: indicates what ends with the statement (since it is an in-condition statement, it must end with ")".
collection: The most critical and error-prone attribute, you need to pay special attention to it. This attribute must be specified. In different cases, the value of this attribute is different. There are three main situations
- If the parameter is a single parameter and the parameter type is a list, the collection attribute value is list.
- If the parameter is a single parameter and the parameter type is an array, the collection attribute value is array (the parameter Integer[] roleIds is the array type, so the collection attribute value is set to "array").
- If the passed parameters are multi-parameters, they need to be encapsulated into a map for processing.
The above are the basic syntax and elements of dynamic SQL. I hope it will be helpful to everyone's learning and I hope everyone will support me more.