MyBatis Learning Summary (vi)--Dynamic SQL

MyBatis's dynamic SQL is based on the OGNL expression, which helps us to implement some logic in SQL statements conveniently.

The elements used to implement dynamic SQL in MyBatis are:

• If
• Choose (when,otherwise)
• Foreach
• where
• Set
• Trim

below we mainly say where set trim these three tags

1,where Label

<!--inquiry student list,like Name, = gender--     <select id= "Getstudentlistwhere" parametertype= "studententity" resultmap= "Studentresultmap" >         select * from Student_tbl ST              WHERE              <if test= "Studentname!=null and studentname! = "" >                 ST. Student_name like CONCAT (CONCAT ('% ', #{studentname}), '% ')              </if>             <if test= "studentsex!= null and Studentsex!= ' "> and                 ST. Student_sex = #{studentsex}              </if>     

If the above example, the parameter studentname is null or ' ', or causes this SQL group to synthesize "WHERE and" keywords redundant error SQL.
At this point we can use the where dynamic statement to resolve. The "where" tab will know if it contains a return value in the tag, it will insert a ' where '. In addition, if the label returns content that begins with and or or, it is removed.

Using the Where tab

<!--inquiry student list,like Name, = gender--     <select id= "Getstudentlistwhere" parametertype= "studententity" resultmap= "Studentresultmap" >         select * from Student_tbl ST          <where>             <if test= "Studentname!=null and Studentname!= ' ">                 ST. Student_name like CONCAT (CONCAT ('% ', #{studentname}), '% ')              </if>             <if test= "studentsex!= null and Studentsex!= ' "> and                 ST. Student_sex = #{studentsex}              </if>         </where>     </select>   

2,set label

If the IF tag is used in an UPDATE statement, or if the preceding if is not executed, it causes a comma superfluous error. The set label allows you to configure the dynamic configuration of the Set keyword, and to reject any unrelated commas appended to the end of the condition.
If you do not use the IF label, an error will result if one of the parameters is null, as shown in the following example:

<!--update student information-     <update id= "updatestudent" parametertype= "studententity" >         update student_tbl             SET student_tbl. Student_name = #{studentname},                 student_tbl. Student_sex = #{studentsex},                 student_tbl. Student_birthday = #{studentbirthday},                 student_tbl. class_id = #{classentity.classid}           WHERE student_tbl. student_id = #{studentid};      </update>  

when modified with the set+if tag, if an item is NULL, it is not updated, but the database's original value is maintained. The following example:

<!--update student information-     <update id= "updatestudent" parametertype= "studententity" >         update student_tbl          <set>             <if test= "Studentname!=null and studentname!=" ">                 student_tbl. Student_name = #{studentname},              </if>             <if test= "Studentsex!=null and studentsex!=" ">                 Student_tbl. Student_sex = #{studentsex},              </if>             <if test= "Studentbirthday!=null" >                 student_tbl. Student_birthday = #{studentbirthday},              </if>             <if test= "Classentity!=null and Classentity.classid !=null and classentity.classid!= ' ">                 student_tbl. class_id = #{classentity.classid}              </if>         </set>         WHERE student_tbl. student_id = #{studentid};      </update>     

3,trim Label

Trim is a more flexible place for the label of superfluous keywords, and he can practice the effects of where and set.

The equivalent trim statement for the where example:

<!--inquiry student list,like Name, = gender--     <select id= "Getstudentlistwhere" parametertype= "studententity" resultmap= "Studentresultmap" >         select * from Student_tbl ST          <trim prefix= "WHERE" prefixoverrides= "And|or" >             <if test= "Studentname!=null and studentname!=" >                 St. Student_name like CONCAT (CONCAT ('% ', #{studentname}), '% ')              </if>             <if test= "studentsex!= null and Studentsex!= ' "> and                 ST. Student_sex = #{studentsex}              </if>         </trim>     </select>   

the equivalent trim statement for the set example:

<!--update student information-     <update id= "updatestudent" parametertype= "studententity" >         update student_tbl          <trim prefix= "SET" suffixoverrides= "," >             <if test= "Studentname!=null and studentname!=" >                 Student_tbl. Student_name = #{studentname},              </if>             <if test= "Studentsex!=null and studentsex!=" ">                 Student_tbl. Student_sex = #{studentsex},              </if>             <if test= "Studentbirthday!=null" >                 student_tbl. Student_birthday = #{studentbirthday},              </if>             <if test= "Classentity!=null and Classentity.classid !=null and classentity.classid!= ' ">                 student_tbl. class_id = #{classentity.classid}              </if>         </trim>         WHERE student_tbl. student_id = #{studentid};      </update>     

Summary:

In fact, in the real application, we are more using the query, the above describes the dynamic SQL, below we also to compare

--Way one <select id= "selectresouceinfobynotnullattributes" resultmap= "Expandresultmap" parametertype= "Bean class name" >    SELECT * FROM table_name where 1=1      <if test= "ResourceId! = null" > and        resource_id = #{resourceid,jdbct Ype=integer}      </if>      <if test= "AppID! = null" > and  appid = #{appid,jdbctype=tinyint}      </if>      <if test= "ResourceUrl! = null" > and  resource_url = #{resourceurl,jdbctype=varchar}      </if>      <if test= "Resourcedesc! = null" > and  resource_desc = #{resourcedesc,jdbctype =varchar}      </if>  </select>

The main point of this approach is in the 1=1 behind the where, plus this avoids the need to add and the selection dilemma after the first if condition.

--Way two <select id= "selectresouceinfobynotnullattributes" resultmap= "Expandresultmap" parametertype= "Bean class name" >    select * FROM table name    <where>      <if test= "ResourceId! = null" >        and resource_id = #{resourceid, Jdbctype=integer}      </if>      <if test= "AppID! = null" > and  appid = #{appid,jdbctype=tinyint}      </if>      <if test= "ResourceUrl! = null" > and  resource_url = #{resourceurl,jdbctype= VARCHAR}      </if>      <if test= "Resourcedesc! = null"       >  and Resource_desc = #{resourcedesc, Jdbctype=varchar}      </if>    </where>  </select>

This is a more where label than the first, and does not require a 1=1 field to be displayed after the where.

--way three <select id= "Selectresouceinfobynotnullattributes" resultmap= "      Expandresultmap "parametertype=" Bean class name > select * from table name <trim prefix = "where" prefixoverrides= "And|or" > <if test= "ResourceId! = null" > and resource_id = #{resourceid,jdbctype=integer} </if> &L T;if test= "AppID! = null" > and AppID = #{appid,jdbctype=tinyint} </if> <if test= "ResourceUrl ! = null "> and Resource_url = #{resourceurl,jdbctype=varchar} </if> <if test=" Resourcedesc! = Null "> Resource_desc = #{resourcedesc,jdbctype=varchar} </if> </trim> </select>
   

The Third Way is the most recommended way, where the label in the trim tag is prefixed with where, that is, the word of the Where condition. The prefixoverrides= "And|or" in the back says that if the where tag contains content beginning with and or or, then long ignores and or. There is another: Suffixoverrides= "And|or", which means that the and or or end of the where is ignored and or OR.

MyBatis Learning Summary (vi)--Dynamic SQL