mybatis--Dynamic SQL

Dynamic SQL reprint: Http://loveshisong.cn/mybatis/2015/01/24/MyBatis (%e5%9b%9b)%e5%8a%a8%e6%80%81sql.html The structure of this article

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

If

If is generally a part of where, such as

<select id="findBlog" resultType="Blog"> SELECT * FROM BLOG WHERE 1 = 1 <if test="title != null"> AND title LIKE CONCAT(CONCAT(‘%‘, #{title}), ‘%‘) <!-- mysql也可以这样写：AND title LIKE "%"#{title}"%" --> </if> <if test="author != null and author.name != null"> AND author_name like #{author.name} </if></select>

where

Like the example above, in order to splice SQL, specifically wrote a WHERE 1 = 1, the where label can avoid such problems, it will automatically remove the wrong AND orOR

<select id="findBlog" resultType="Blog"> SELECT * FROM blog <where> <if test="state != null"> state = #{state} </if> <if test="title != null"> AND title like #{title} </if> <if test="author != null and author.name != null"> AND author_name like #{author.name} </if> </where></select>

Set

The set element is primarily used when updating operations, and it intelligently removes the last comma. If one of the conditions in set is not met, an error is provided.

<update id="updateBlog" parameterType="Blog"> UPDATE blog <set> <if test="title != null"> title = #{title}, </if> <if test="content != null"> content = #{content}, </if> <if test="owner != null"> owner = #{owner} </if> </set> where id = #{id} </update> 

Foreach

foreach is primarily used in the in statement, and it can traverse a collection in an SQL statement.

<select id="foreachTest" parameterType="java.util.List" resultType="Blog"> SELECT * FROM blog WHERE id in <foreach collection="list" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach> </select>

• itemDeclares a collection item that can be used in the body of an element, equivalent to an alias when iterating over each element of the collection
• indexDeclares an indexed variable that can be used in the body of an element, that is, the position of the element.
  • In the example above, if the element inside the list is "[3,6,9]", the value of ' item ' will be "3,6,9", respectively " index 0,1,2"
• open separator closeUsed to specify a string for opening and closing matches and to place delimiters between iterations, such as "(1,2,3,4)"
• collectionProperty comparisons are error-prone
  • When the incoming parameter (parametertype) is a single parameter and the parameter type is a list, the collection property value is list, like above, passed in parameterparameterType="java.util.List"
  • When an incoming parameter (parametertype) is a single parameter and the parameter type is an array, the collection property value is an array, such asparameterType="[Ljava.lang.Integer;"
  • When the parameter passed in is multiple, it must be encapsulated into a map, at this time the collection property value is the key value of the variable of the list or array type in the incoming map, for example parameterType="java.util.Map" , a key in the incoming map is "idlist" List object, the collection property value should be idlist at this time.

Choose (when, otherwise)

The Choose Element acts as a switch statement in Java

 <select id=  "choosetest" parametertype= "Blog" resulttype=< Span class= "s" > "Blog" > select * from Blog WHERE 1 = 1 <choose> <when test= "title! = NULL" > and title = #{title} </when> <when test=" content! = NULL " > and content = #{content} </when> <otherwise> and Author = "Michael" </otherwise> </choose> </ select>               

 

mybatis--Dynamic SQL