Mybatis dynamic SQL, mybatissql

Mybatis dynamic SQL, mybatissql

Glossary

OGNL expression

OGNL, called Object-Graph Navigation Language, is a powerful expression Language used to obtain and set attributes of Java objects, it aims to provide a higher abstraction level to navigate Java object graphs.

The basic unit of an OGNL expression is "navigation chain". Generally, a navigation chain consists of the following parts:

All OGNL expressions perform value calculation based on the context of the current object. The results of the first part of the chain are used as the context for subsequent value calculation. For example, names [0]. length ().

MybatisDynamic SQL statementYesOGNL-based expressions. Some logic can be easily implemented in SQL statements. In general, mybatis dynamic SQL statements mainly include the following types:
1.IfStatement (simple condition judgment)
2.Choose(When, otherwize), equivalent to a switch in java, similar to choose in jstl.
3.Trim(Prefix, suffix, and so on are added to the contained content)
4.Where(It is mainly used to simplify where condition judgment in SQL statements, intelligently process and or, without worrying about redundant errors)
5.Set(Mainly used for updating)
6.Foreach(This is especially useful when querying mybatis in statements)
The following describes the processing methods respectively.

1. mybaitsIfStatement 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>

 

Analysis:

If you provide the title parameter, it must satisfy the title =#{ title}. If you provide the Content and Owner, they must also meet the corresponding conditions, then, all the blogs that meet these conditions are returned. This is a very useful function.

In the past, we used other types of frameworks or directlyWhen using JDBCIf we want to achieve the same effectSQL statements to be spelled out, This is extremely troublesome. Compared with the above dynamic SQL statements, it is much easier.

 

2.Choose(When, otherwize), equivalent to a switch in java, similar to choose in jstl

  <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>

The when element indicates that content is output when the when condition is met,Similar to the switch in JAVAIn the order of conditions,When conditions are met, the chooseThat is, only one of the when and otherwise conditions will output the content in otherwise if all the conditions are not met. Therefore, the preceding statement is very simple! If it is null, and titlte =#{ title} is output. The condition is no longer judged. When the title is empty and the content is displayed! If it is null, and content = # {content} is output. If none of the conditions are met, the content in otherwise is output.

 

3.Trim(Prefix, suffix, and so on are added to the contained content)

 　　<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>

 

Trim ElementsFunctionYesAdd some prefixes before your own content,You can also add some suffixes to it.And the corresponding attributes are prefix and suffix. You can overwrite, ignore, or overwrite, some content at the end of the content, the corresponding attributes are prefixOverrides and suffixOverrides. Because trim has such a function, we can simply use trim to replace the where element function.

 

4.Where(It is mainly used to simplify where condition judgment in SQL statements and intelligently process 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 ElementFunctionYesOutput A where statement at the place where the where element is written., AnotherBenefitsYes youYou do not need to consider what the conditional output in the where element looks like.MyBatis will help you intelligently. If all the conditions are not met, MyBatis will find all records. If the output starts with and, MyBatis will ignore the first and, of course, if it starts with or, MyBatis will also ignore it. In addition, you do not need to consider the space issue in the where element. MyBatis will help you intelligently add it. In the preceding example, if title = null and content! = Null, the entire output statement will be select * from t_blog where content =#{ content}, instead of select * from t_blog where and content =#{ content }, because MyBatis intelligently ignores the first and or.

 

5.Set(MainUsed for updateTime)

    <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>

 

The set element is mainly used in update operations.The function is similar to the where element.Is mainly to output a set before the included statement. If the included statement ends with a comma, the comma will be ignored. If the set contains null content, an error will occur. With the set element, we can dynamically update the modified fields.

 

6.Foreach(In the implementation of mybatisIn statement query is particularly useful)
Foreach is mainly used to construct in conditions. It can iterate a set in SQL statements. Attributes of the foreach element include item, index, collection, open, separator, and close.

• ItemIndicates eachElementTheAlias,
• IndexSpecifies a name for each iterationIterationOfLocation,
• OpenIndicates the statementStart,
• SeparatorIndicates the symbol used between each iteration.Delimiter,
• CloseIndicatesEnd,

When using foreach, the most critical and error-prone isCollection Properties, This attribute isRequiredBut in different cases, the value of this attribute is different. There are three main cases:

• If the input isSingle ParameterAndThe parameter type is a ListWhen the collection property value isList
• If the input isSingle ParameterParametersType is an arrayWhen the collection property value isArray
• IfMultiple ParametersWe need to encapsulate them into a Map. Of course, a single parameter can also be encapsulated into a map. In fact, if you pass in a parameter, in MyBatis, It is encapsulated into a Map, and the map key is the parameter name. Therefore, the collection property value is the key of the input List or array object in the encapsulated map.

6.1.Single Parameter ListType

　　<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>

 

The value of the preceding collection is list, correspondingMapperYes.

public List<Blog> dynamicForeachTest(List<Integer> ids); 

 

Test code

　　@Test    public void dynamicForeachTest() {        SqlSession session = Util.getSqlSessionFactory().openSession();        BlogMapper blogMapper = session.getMapper(BlogMapper.class);        List<Integer> ids = new ArrayList<Integer>();        ids.add(1);        ids.add(3);        ids.add(6);        List<Blog> blogs = blogMapper.dynamicForeachTest(ids);        for (Blog blog : blogs)            System.out.println(blog);        session.close();    }

 

　　

6.2.Array typeParameters

<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<Blog> dynamicForeach2Test(int[] ids);  

 

 

6.3.Map typeParameters

 <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 should be like this interface:

public List<Blog> dynamicForeach3Test(Map<String, Object> params); 

 

Through the above method, you can complete the general dynamic SQL statement of mybatis.Most commonIf where foreach is the key.

 

Thank you! Thank you for your patience!