Mybatis Study Notes (4): dynamic SQL statements and mybatis Study Notes

Mybatis Study Notes (4): dynamic SQL statements and mybatis Study Notes
Dynamic SQL

The dynamic SQL statement of mybatis is based on the OGNL expression. Some logic can be easily implemented in SQL statements. In general, mybatis dynamic SQL statements mainly include the following types:

1. if statement (simple condition judgment)
2. choose (when, otherwize) is equivalent to a switch in java, similar to choose in jstl.
3. trim (prefix, suffix, etc., prefix, and suffix are added to the contained content)
4. where (it is mainly used to simplify the where condition judgment in SQL statements, and can intelligently process and or, without worrying about redundant causes of syntax errors)
5. set (mainly used for updating)
6. foreach (this is especially useful when querying mybatis in statements)

1. if Processing

The common statement with a where query is as follows,

<! -- Comprehensive query --> <select id = "findUserList" parameterType = "User" resultType = "User"> select * from 'user' where id = # {id} and userName like' % $ {userName} % '</select>

In this case, if the id or userName is null, the query result of this statement may report an error. We can use the logical if judgment for the where statement: if the value is null or equal to a null string, we will not judge this condition to increase flexibility.

After the judgment is added, it is as follows:

<! -- Pass pojo to query user information comprehensively --> <select id = "findUserList" parameterType = "user" resultType = "user"> select * from user where 1 = 1 <if test =" id! = Null and id! = ''"> And id =#{ id} </if> <if test = "username! = Null and username! = ''"> And username like '% $ {username} %' </if> </select>

This statement is used to query the user table. If the id or usrname is not null or empty and the userName and id query conditions are passed in, the query statement is: select * from 'user' where id = # {id} and userName like '% $ {userName} %'; otherwise, query all records of select * from user where 1 = 1.

Note: The purpose of adding 1 = 1 in the where statement is to prevent the condition after the where statement from being null if it is not true. An error is returned. If none of the if conditions are true, the query is equivalent. select * from user where is an incorrect SQL statement.

<! -- Pass pojo to query user information comprehensively --> <select id = "findUserList" parameterType = "user" resultType = "user"> select * from user where <if test = "id! = Null and id! = ''"> And id =#{ id} </if> <if test = "username! = Null and username! = ''"> And username like '% $ {username} %' </if> </select>

2. choose (when, otherwise) Processing

It is equivalent to the switch in java. Sometimes we don't want to apply all the conditions, but just want to select one from multiple options. When the if tag is used, if the expression in test is true, the conditions in the if tag are executed. MyBatis provides the choose element. The if tag is related to (and), while the choose tag is or (or.

The choose tag is used to determine in sequence whether the test condition in its internal when tag is true. If one is true, the choose ends and jumps out of the choose, when all the when conditions in the choose are not satisfied, the SQL statement in otherwise is executed. Similar to the switch statement in Java, choose is switch, when is case, and otherwise is default.

<select id="queryByName" parameterType="User" resultType="User">      SELECT <include refid="columns"></include>      FROM sys_user      WHERE user_type_id ＝ 1      <choose>          <when test="userName != null">user_name like '%' #{userName} '%'</when>          <when test="nickName != null">nick_name like '%' #{nickName} '%'</when>          <otherwise>is_valid ＝ 1</otherwise>      </choose>  </select>  

3. trim Processing

Trim (prefix, suffix, etc., prefix, suffix)

Trim attributes

Prefix: overwrite the prefix and add its content.

Suffix: overwrite the suffix and add its content

PrefixOverrides: prefix judgment Condition

SuffixOverrides: Condition for determining the suffix

    <select id="findUserList"  parameterType="User" resultType="User">        select b.* from sys_menu b where where 1=1    <trim suffix="WHERE" suffixOverrides="AND | OR">      <if test="id != null and id !='' ">  AND b.id =#{id}  </if>      <if test="name != null">  AND b.menu_name like #{name}  </if>          </trim>      </select>

The final SQL print is: select B. * from sys_menu B where 1 = 1 AND B. menu_name like ''where

4. foreach Processing

Pass list Implementation

When you want to input multiple IDs for query, such as: SELECT * from users where username LIKE '% sheets %' and id IN (, 16), when using IN query.

In this case, you can use foreach to input parameters. If it is true, the query statement is the preceding SQL statement.

<Select id = "selectUserByList" parameterType = "java. util. List" resultType = "user"> select * from user <where> <! -- Pass the List. The List contains pojo --> <if test = "list! = Null "> <foreach collection =" list "item =" item "open =" and id in ("separator =", "close =") ">#{ item. id} </foreach> </if> </where> </select>

Transmit a single data (string type in the array)

<! -- Pass an array to query user information comprehensively --> <select id = "selectUserByArray" parameterType = "Object []" resultType = "user"> select * from user <where> <! -- Pass the array --> <if test = "array! = Null "> <foreach collection =" array "index =" index "item =" item "open =" and id in ("separator =", "close = ") ">#{ item} </foreach> </if> </where> </select>

We know that only one input parameter can be used for SQL ing by Mybatis. If you want to input multiple parameters, you can only use Java List or Array for encapsulation before passing in. The preceding statement puts the Id values of multiple records to be deleted in the List object.

The foreach element is very powerful. It allows you to specify a set and declare the set items and index variables that can be used in the element body. It also allows you to specify the string (the open and close attributes in the preceding example) that matches the open and close operations and to place the separator (separator attribute) in the middle of the iteration ). This element is intelligent, so it does not accidentally add additional delimiters.

We can pass a List instance or array as a parameter object to MyBatis. When we do this, MyBatis automatically wraps it in a Map and uses the name as the key. A List instance uses "list" as the key, while an array instance uses "array" as the key ".

Reference

1. mybatis series: http://blog.csdn.net/chris_mao/article/details/48827961

2. dynamic SQL statement: http://limingnihao.iteye.com/blog/782190