Learn mybatis dynamic sql usage

One of the powerful features of MyBatis is its dynamic SQL.

If you have experience using JDBC or other similar frameworks, you will understand how painful it is to splice SQL statements based on different conditions. When splicing, make sure not to forget the necessary spaces, and be careful to omit the comma at the end of the column list. Take advantage of the dynamic SQL feature to get rid of this pain completely.

Usually using dynamic SQL cannot be an independent part. MyBatis certainly uses a powerful dynamic SQL language to improve this situation. This language can be used in any SQL mapping statement.

Dynamic SQL elements are similar to using JSTL or other similar XML-based text processors. In previous versions of MyBatis, there were many elements to understand. MyBatis 3 has greatly improved them, and now you can use less than half of the original elements. MyBatis uses powerful OGNL-based expressions to eliminate other elements.

The dynamic sql statement of mybatis is based on OGNL expression. It is easy to implement certain logic in sql statements. Generally speaking, mybatis dynamic SQL statements mainly include the following categories:

1. if statement (simple conditional judgment)
2. choose (when, otherwize), which is equivalent to switch in the java language, and is very similar to choose in jstl.
3. trim (add prefix, or suffix, etc., prefix, suffix, etc. to the included content)
4. where (mainly used to simplify the judgment of where conditions in sql statements, and can intelligently handle and or, without worrying about redundant syntax errors)
5. set (mainly used for updates)
6. foreach (especially useful when implementing mybatis in statement query)
The following processing methods are introduced respectively:

1. mybaits if statement processing

<select id="dynamicIfTest" parameterType="Blog" resultType="Blog">select * from t_blog where 1 = 1
        <if test="title != null">and title = #{title}</if>
        <if test="content != null">and content = #{content}</if>
        <if test="owner != null">and owner = #{owner}</if>
    </select>

The meaning of this statement is very simple. If you provide the title parameter, then it must satisfy title=#{title }, similarly if you provide Content and Owner, they also need to meet the corresponding conditions, and then return all Blogs that meet these conditions. This is a very useful function. In the past, we used other types of frameworks or directly used JDBC. Sometimes, if we want to achieve the same selection effect, we need to spell out SQL statements, which is extremely troublesome. In comparison, the above dynamic SQL is much simpler.

2. choose (when,otherwize), which is equivalent to switch in java language, and is very similar to choose in jstl Similar:

<select id="dynamicChooseTest" parameterType="Blog" resultType="Blog">select * from t_blog where 1 = 1 
        <choose>
            <when test="title != null">and title = #{title}</when>
            <when test="content != null">and content = #{content}</when>
            <otherwise>and owner = "owner1"
            </otherwise>
        </choose>
    </select>

when element indicates when the conditions in when are met Just output the content, which is similar to the switch effect in JAVA. It is in the order of conditions. When a condition in when is met, choose will jump out. That is, among all the when and otherwise conditions, only one will be output. When all the conditions are met, choose will be output. When the conditions are not met, the content in otherwise will be output. So the meaning of the above statement is very simple. When title!=null, output and titlte = #{title}, and no longer judge the conditions. When title is empty and content!=null, output and content = #{ content}, when all conditions are not met, the content in otherwise is output.

3.trim (Add prefix, or suffix, etc. to the included content, prefix, suffix)

    <select id="dynamicTrimTest" parameterType="Blog" resultType="Blog">select * from t_blog <trim prefix="where" prefixOverrides="and |or">
            <if test="title != null">title = #{title}</if>
            <if test="content != null">and content = #{content}</if>
            <if test="owner != null">or owner = #{owner}</if>
        </trim>
    </select>

The main function of the trim element is to add certain prefixes before the content it contains, and you can also add certain suffixes after it. The corresponding attributes are prefix and suffix; You can overwrite, that is, ignore, some content in the header of the included content, or you can overwrite some content in the tail. The corresponding attributes are prefixOverrides and suffixOverrides; because trim has such a function, we can also use trim very simply. Replaces the function of where element.

4. where (mainly used to simplify the judgment of where conditions in sql statements, and can intelligently handle and or conditions

<select id="dynamicWhereTest" parameterType="Blog"   resultType="Blog">select * from t_blog <where>
            <if test="title != null">title = #{title}</if>
            <if test="content != null">and content = #{content}</if>
            <if test="owner != null">and owner = #{owner}</if>
        </where>
    </select>

where 元素知道只有在一个以上的if条件有值的情况下才去插入“WHERE”子句。而且，若最后的内容是“AND”或“OR”开头的，where 元素也知道如何将他们去除。

如果 where 元素没有按正常套路出牌，我们还是可以通过自定义 trim 元素来定制我们想要的功能。比如，和 where 元素等价的自定义 trim 元素为：

 ...

prefixOverrides 属性会忽略通过管道分隔的文本序列（注意此例中的空格也是必要的）。它带来的结果就是所有在 prefixOverrides 属性中指定的内容将被移除，并且插入 prefix 属性中指定的内容。

 

where元素的作用是会在写入where元素的地方输出一个where，另外一个好处是你不需要考虑where元素里面的条件输出是什么样子的，MyBatis会智能的帮你处理，如果所有的条件都不满足那么MyBatis就会查出所有的记录，如果输出后是and 开头的，MyBatis会把第一个and忽略，当然如果是or开头的，MyBatis也会把它忽略；此外，在where元素中你不需要考虑空格的问题，MyBatis会智能的帮你加上。像上述例子中，如果title=null， 而content != null，那么输出的整个语句会是select * from t_blog where content = #{content}，而不是select * from t_blog where and content = #{content}，因为MyBatis会智能的把首个and 或 or 给忽略。

5.set (主要用于更新时)

   <update id="dynamicSetTest" parameterType="Blog">update t_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>

set元素主要是用在更新操作的时候，它的主要功能和where元素其实是差不多的，主要是在包含的语句前输出一个set，然后如果包含的语句是以逗号结束的话将会把该逗号忽略，如果set包含的内容为空的话则会出错。有了set元素我们就可以动态的更新那些修改了的字段。

6. foreach (在实现 mybatis in 语句查询时特别有用) 
foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。foreach元素的属性主要有item，index，collection，open，separator，close。item表示集合中每一个元素进行迭代时的别名，index指定一个名字，用于表示在迭代过程中，每次迭代到的位置，open表示该语句以什么开始，separator表示在每次进行迭代之间以什么符号作为分隔符，close表示以什么结束，在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的，主要有一下3种情况：
如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，实际上如果你在传入参数的时候，在MyBatis里面也是会把它封装成一个Map的，map的key就是参数名，所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key

　　1.1.单参数List的类型

<select id="dynamicForeachTest" resultType="Blog">select * from t_blog where id in<foreach collection="list" index="index" item="item" open="(" separator="," close=")">#{item}</foreach>
    </select>

 

　　上述collection的值为list，对应的Mapper是这样的：　　public List dynamicForeachTest(List ids); 

　　2.数组类型的参数

　　

<select id="dynamicForeach2Test" resultType="Blog">select * from t_blog where id in<foreach collection="array" index="index" item="item" open="(" separator="," close=")">#{item}</foreach>
    </select>

　　对应的Mapper： 　　public List dynamicForeach2Test(int[] ids); 

　　3.Map 类型的参数

<select id="dynamicForeach3Test" resultType="Blog">select * from t_blog where title like "%"#{title}"%" and id in<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">#{item}</foreach>
    </select>

　　对应的Mapper：　　　　public List dynamicForeach3Test(Map params);

 

The above is the detailed content of Learn mybatis dynamic sql usage. For more information, please follow other related articles on the PHP Chinese website!