Dynamic SQL in MyBatis

Dynamic SQL in MyBatis

There are two ways to configure SQL in MyBatis, one is to use XML to configure, and one to configure with annotations.

MyBatis uses annotations to configure SQL, but is rarely used because of limited configuration functionality and poor readability for complex SQL.

MyBatis Common XML configuration, using a few simple elements of XML, you can complete the function of dynamic SQL, a large number of judgments can be configured in the Mybaties mapping XML, to achieve many need a lot of code to achieve the function, greatly reduce the amount of code, It embodies the flexibility, highly configurable and maintainability of MyBatis.

Elements of Dynamic SQL

Elements	Role	Note
If	Judgment statement	Single conditional branch judgment
Choose (when,otherwise)	Equivalent to switch and case statements in Java	Multi-Conditional branch judgment
Trim	Auxiliary elements for handling specific SQL assembly problems	Issues for working with SQL Assembly
Foreach	Looping statements	In statements such as list conditions are commonly used

If element

The IF element is the most commonly used judgment statement, equivalent to the Java China if statement, which is often used in conjunction with the Test property.

<select id= "FindRole1" parametertype= "string" resultmap= "Roleresultmap" >        1=1        <if test= "RoleName! = null and RoleName! =" "> and            role_name like concat ('% ', #{rolename}, '% ') 
    </if>    </select>

When the parameter roleName is passed into the mapper, if the argument is not empty, then construct a fuzzy query to roleName, otherwise do not construct this condition. Through the mybaties if element saves a lot of splicing SQL work, centralized in the XML maintenance.

Choose, when, otherwise elements

If you have more choices when judging, it's not just two options, like Switch...case...default ... The statement of the function. In a mapped SQL statement, use the Choose, when, and otherwise elements to assume this function.

<select id= "FindRole2" parametertype= "role" resultmap= "Roleresultmap" >        Select Role_no, role_name, note From T_role        1=1        <choose>            <when test= "Roleno! = null and Roleno! =" "                > =  #{roleno}            </when>            <when test= "RoleName! = null and RoleName! =" "> and                role_name Like Concat ('% ', #{rolename}, '% ')            </when>            <otherwise>                  NULL            </otherwise>        </choose>    </select>

The above scenario is:

First, if the role number is not empty, only the role number is used as the criteria query.

When the role number is empty and the role name is not empty, the role name is used as the criteria for the fuzzy query.

When both the role number and the role number are blank, the role memo is required to be not empty.

Trim, where, set element

In the preceding SQL statement, "1=1" was added to enable its functionality, but a better implementation would be to use where. When the condition inside the where element is set, the where SQL keyword is added to the assembled SQL, otherwise it will not be added.

<select id= "FindRole3" parametertype= "role" resultmap= "Roleresultmap" >        Select Role_no, role_name, note From T_role        <where>            <if test= "RoleName! = null and RoleName! =" "> and                role_ Name like Concat ('% ', #{rolename}, '% ')            </if>            <if test= "Note! = NULL and note! = "" "> and                note like concat ('% ', #{note}, '%'            )</if>        </where>    </select>

　　

Sometimes it is necessary to remove some special SQL syntax, such as common and, or, and so on. The trim element can also be used to achieve the desired effect. Where prefix represents the prefix of the statement, Prefixoverrides represents the kind of string that needs to be removed. is equivalent to the preceding where statement.

<select id= "FindRole4" parametertype= "string" resultmap= "Roleresultmap" >        Select Role_no, Role_name, Note from T_role        <trim prefix= "where" prefixoverrides= "and" >            <if test= "RoleName! = NULL and RoleName! = "" "> and                role_name like concat ('% ', #{rolename}, '%'            )</if >        </trim>    </select>

In Hibernate, if you have to send all the fields to a persisted object because you are updating a field, this affects the efficiency of the SQL statement execution. The best way is to pass the values of the primary key and the Update field to SQL to update. The set element can implement this function. The set element encounters a comma, and it automatically removes the corresponding comma.

<update id= "Updaterole" parametertype= "role" >        update t_role        <set>            <if test= "RoleName! = null and RoleName! =" "                > = #{rolename}            ,</if>            <if test= "Note! = null and note! =" "                > = #{note            }</if< /c16>>        </set>        = #{roleno}    </update>

foreach element

The foreach element is a looping statement that iterates through the collection, which supports the collection of arrays and lists, set interfaces, and provides traversal functionality, which is often used in keywords in SQL.

<select id= "Findrolebynums" resultmap= "Roleresultmap" >        Select Role_no, role_name, note from T_role where Role_no        in <foreach item= "Roleno" index= "index" collection= "rolenolist"            Open= "(" Separator = "," close= ")" >            #{roleno}        </foreach>    </select>

Collection configured Rolenolist is a parameter name passed in, which can be an array, List, set, and so on.

Item is configured with the current element in the loop.

Index is configured with the current element in the position of the collection subscript.

Open and close are configured with what symbols to wrap these collection elements together.

Separator is the delimiter for individual elements.

To judge a string with the properties of test

Test is used for conditional judgment statements, which are equivalent to judging true and false, and in most scenarios, are mainly used to judge both empty and non-empty.

<select id= "Getroletest" parametertype= "string" resultmap= "Roleresultmap" >        Select Role_no, Role_name, Note from T_role        <if test= "type = = ' Y '. toString ()"            > 1=1        </if >    </select>

If you pass type= ' Y ' to SQL, you can implement MyBatis to add the conditional where 1=1, so for the judgment of the string, you can add the ToString () method to compare.

Bind element

The role of the bind element is to define a context variable through the OGNL expression, which is more convenient to use.

For example, in a fuzzy query, if it is a MySQL database, you often use a concat, which is connected with% and parameters. However, there is no Oracle database, the Oracle database with the connection symbol is "| |", so that SQL needs two forms to implement, with the bind element, you do not use the database language.

<select id= "FindRole5" parametertype= "string" resultmap= "Roleresultmap" >        <bind name= "pattern" value= "' % ' + _parameter + '% ' "/>        Select Role_no, role_name, note from T_role        where role_name like #{pattern}    /c1></select>

The above is my learning process for mybatis in the dynamic SQL statement of the common knowledge points, I hope you can learn to progress together!

Dynamic SQL in MyBatis