MyBatis Dynamic SQL

MyBatis Dynamic sql:

Dynamic SQL is a programming method for generating and executing SQL at run time

Dynamic is relative to static. Static SQL refers to the well-defined fixed SQL statements that have been included in the code at the time of Code compilation

How is dynamic SQL set in MyBatis? How do I use it?

MyBatis Dynamic SQL is based on OGNL expression, which is judged by expression, and the SQL is flexibly spliced and assembled.

Common nodes are: if, choose when otherwise, where, set, trim, foreach, SQL fragment, etc.

　　1.1 If

    <select id= "Selectactiveblogbytitle" parametertype= "Blog" resultmap= "Blogresultmap" >                SELECT *  From blog        WHERE state = ' ACTIVE '        <if test= "title! = null and Title! = '" "> and            title like #{titl e}        </if>            </select>    

1.2 Choose when otherwise

    <!--several query conditions mutually exclusive--    <select id= "Selectactiveblogbytitleorstyle" parametertype= "Blog" resultmap= " Blogresultmap ">        select * from blog         WHERE state = ' ACTIVE '                <choose>            <when test=" Title! = NULL and Title! = "" > and                lower (title) like Lower (#{title})            </when>            <when test= " Style! = NULL and style! = "" > and                style = #{style}            </when>            <otherwise>  True </otherwise> </choose> </select>     

1.3 Where

<!--whereAutomatically patching query criteria, using the WHERE keyword in the query statement<where> Label Substitution Note: You cannot omit the and OR or keywords--<select id= "Selectblogbycondition" Parameterty Pe= "Blog" resultmap= "Blogresultmap" >select * FROM blog <where> <if test= "state! = null and state! =" "> state = #{state} </if> <if test=" title! = null and Title! = ' "" > and Lower (title) Li Ke Lower (#{title}) </if> <if test= "Featured! = NULL" > and featured = #{featured} </  if> </where> </select>            

1. 4 Set

<!--set:will automatically remove the extra comma---<update id= "upadteblogbycondition" parametertype= "Blog" after the statement >UPDATE ' blog ' <set> <if test= ' title! = null ' > ' title ' = #{title},< ;/if> <if test= "Authorid! = null" > ' author_id ' = #{authorid},</if> <if test= "sta Te! = null "> ' state ' = #{state},</if> <if test=" Featured! = null "> ' Featured ' = #{featured},</< C8>if> <if test= "style! = null" > ' style ' = #{style} </if> </set> WHERE ' id ' = #{id} </update>           

1. 4 Trim

<!--TrimTrim can implement set and where functions--<select id= "Selectblogbyconditiontrim" parametertype= "Blog" resultmap= "Blogresultmap ">select * from blog <trim prefix= "where" prefixoverrides= "and | or" > <if te St= "state! = null and state! =" "> state = #{state} </if> <if test=" title! = NULL and title! = "" > and lower (title) like Lower (#{title}) </if> <if test= "Featured! = NULL" > and featur ed = #{featured} </if> </trim> </select>        

1.5 foreach

    <delete id= "deletebloglist" parametertype= "list" > delete from the        blog where ID in        <foreach collection= "List" item= "Item" open= "(" close= ")" separator= "," >            #{item}        </foreach>    </ Delete> 

1.6 SQL Fragment

    <!-- SQL Clips--    <sql id= "Aliascolumn" >            </sql>                <!--references    --< Include refid= "Aliascolumn" >        </include> 

MyBatis Dynamic SQL