preface

Recently, when developing projects involving complex dynamic conditional queries, Mybaits does not support “if elseif” but we can use “choose when otherwise” indirectly, where choose is a whole, when is if and otherwise is else

Quick to use

In the past, we used the if tag to judge the conditions, and the conditions existed side by side

		    <if test="seat_no != null and seat_no != '' ">  
		        AND seat_no = #{seat_no}  
		    </if>   
Copy the code

Now use “chose when otherwise” if one of the conditions is true, the others will not be judged. If there are no valid conditions, the content in Otherwise is executed by default

		<choose>
		    <when test="...">...</when>
		    <otherwise>...</otherwise>
	    </choose>
Copy the code

The following is my own example of real use, and tested, for reference only:

Query user information based on dynamic filtering criteria

<select id="findUsersByUser" resultType="cn.soboys.kmall.sys.entity.User">
        select tu.USER_ID,tu.USERNAME,tu.SSEX,td.DEPT_NAME,tu.MOBILE,tu.EMAIL,tu.STATUS,tu.CREATE_TIME,
        td.DEPT_ID
        from t_user tu left join t_dept td on tu.DEPT_ID = td.DEPT_ID

        <where>
            <choose>
                <when test="userParams.adminType==4">And tu. ADMIN_TYPE_ID in (2, 3)</when>
                <otherwise>
                       <include refid="search"></include> 
                </otherwise>
            </choose>
            
        </where>

    </select>
Copy the code

 <sql id="search">
         <if test="userParams.adminType==null or userParams.adminType==''">And tu. ADMIN_TYPE_ID in (0, 1)</if>

         <if test="userParams.adminType ! = null and userParams.adminType ! = "">
             and tu.ADMIN_TYPE_ID=#{userParams.adminType}
         </if>


         <if test="userParams.roleId ! = null and userParams.roleId ! = "">
             and (select group_concat(ur.ROLE_ID)
             from t_user u
             right join t_user_role ur on ur.USER_ID = u.USER_ID,
             t_role r
             where r.ROLE_ID = ur.ROLE_ID
             and u.USER_ID = tu.USER_ID and r.ROLE_ID=#{userParams.roleId})
         </if>


         <if test="userParams.mobile ! = null and userParams.mobile ! = "">
             AND tu.MOBILE =#{userParams.mobile}
         </if>
         <if test="userParams.username ! = null and userParams.username ! = "">
             AND tu.USERNAME   like CONCAT('%',#{userParams.username},'%')
         </if>
         <if test="userParams.ssex ! = null and userParams.ssex ! = "">
             AND tu.SSEX  =#{userParams.ssex}
         </if>
         <if test="userParams.status ! = null and userParams.status ! = "">
             AND tu.STATUS =#{userParams.status}
         </if>
         <if test="userParams.deptId ! = null and userParams.deptId ! = "">
             AND td.DEPT_ID =#{userParams.deptId}
         </if>
         <if test="userParams.createTime ! = null and userParams.createTime ! = "">
             AND DATE_FORMAT(tu.CREATE_TIME,'%Y%m%d') BETWEEN substring_index(#{userParams.createTime},'#',1) and substring_index(#{userParams.createTime},'#',-1)
         </if>
     </sql>
Copy the code

So this is the if else if judgment. As soon as the WHEN condition in the Choose tag fails, the condition in the Otherwise tag, the judgment statement, is executed, which is the SQL fragment condition I include below

For more details on the condition TAB use refer to my article click here

SQL fragment stitching

When you’re writing an SQL statement, you’re going to have some requirement, some fragment of SQL statement that repeats, and you don’t want to write it repeatedly, so you can use SQL fragment to pull out the common SQL statement and then reference it where you need to

The < SQL > element in MyBatis is used to define an SQL fragment that separates common SQL statements, such as the part between the SELECT keyword and the WHERE keyword. Among them:

Id: unique identifier used elsewhere to reference using

tags;

Lang: Set character encoding;

DatabaseId: specifies the ID of the database where the SQL statement is executed. The databaseId is configured in mybatis-cfg.xml.

At the same time, you can also see that complex SQL fragments can be defined with

,
, < WHERE >,

,

,

,

,

tags in < SQL > tags

Simply define SQL fragment as follows:

<sql id="user_columns">
    `user_id`, `name`, `sex`, `age`
</sql>
Copy the code

Use the

tag in the < SQL > tag to introduce the defined SQL fragment as follows:

<! Define base column -->
<sql id="user_base_columns">
    `user_id`, `name`
</sql>
 
<! Define a SQL fragment -->
<sql id="user_columns">
    <include refid="user_base_columns"/>, `sex`, `age`
</sql>
Copy the code

Example Query user information


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"
   "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hxstrive.mybatis.sql.demo1.UserMapper">
   <! -- Mapping result -->
   <resultMap id="RESULT_MAP" type="com.hxstrive.mybatis.sql.demo1.UserBean">
      <id column="user_id" jdbcType="INTEGER" property="userId" />
      <result column="name" jdbcType="VARCHAR" property="name" />
      <result column="sex" jdbcType="VARCHAR" property="sex" />
      <result column="age" jdbcType="INTEGER" property="age" />
   </resultMap>
 
   <! Define a SQL fragment -->
   <sql id="user_columns">
      `user_id`, `name`, `sex`, `age`
   </sql>
 
   <! -- Query all user information -->
   <select id="findAll" resultMap="RESULT_MAP">
      select <include refid="user_columns" /> from `user`
   </select>
 
</mapper>

Copy the code

SQL parameter value and OGNL expression

See that #{xx} is the OGNL expression used in the #{params}.

In the module of “XML Mapping file” in the official document of Mybatis, it is parsed as follows:

When we use the #{} parameter symbol, we are telling Mybatis to create a preprocessed statement parameter. Through JDBC, such a parameter is represented by a “? To be passed to a new preprocessed statement.

That is, when we use the #{XX} OGNL expression, it first generates a SQL statement with placeholders, and then sets this parameter for us at the bottom: ps.setint (1, id);

OGNL is an object-graph Navigation Language with the syntax #{}.

Don’t you have any idea what this is? OGNL function is to do data interaction between the object and the view, you can access the attributes of the object and call the method of the object, through the expression can iterate out the structure of the whole object

MyBatis OGNL

The above content is only suitable for use in MyBatis OGNL expressions, the full expression click here.

There are two places in MyBatis where OGNL can be used:

dynamicSQLIn an expression
${param}In the parameters

MySql like query

<select id="xxx" .>
    select id,name,... from country
    <where>
        <if test="name ! = null and name ! = "">
            name like concat('%', #{name}, '%')
        </if>
    </where>
</select>
Copy the code

The value of test in the above code is computed using OGNL.

Example 2, general like query:

<select id="xxx" .>
    select id,name,... from country
    <bind name="nameLike" value="'%' + name + '%'"/>
    <where>
        <if test="name ! = null and name ! = "">
            name like #{nameLike}
        </if>
    </where>
</select>
Copy the code

The value value here will be computed using OGNL.

Note: Calls to the <bind parameter can be obtained by #{} or ${}. #{} prevents injection.

Mapper supports a UUID primary key, which is implemented in Mapper using a tag that calls a static method like this:

<bind name="username_bind" 
      value='@java.util.UUID@randomUUID().toString().replace("-", "")' />
Copy the code

This method automatically calls static methods, but does not write back the corresponding property values, so use caution.

${params}The parameters in the

In the like example above, it is easiest to use the following method

<select id="xxx" .>
    select id,name,... from country
    <where>
        <if test="name ! = null and name ! = "">
            name like '${'%' + name + '%'}'
        </if>
    </where>
</select>
Copy the code

${‘%’ + name + ‘%’}, not %${name}%.

${XXX} = ${XXX} = ${XXX} = ${XXX} = ${XXX} = ${XXX} = ${XXX} = ${XXX}

Expressions can be all the expressions OGNL supports and can be written in complex ways, calling static method return values or static attribute values.

For example, a condition to determine whether an input attribute value contains a substring can be used directly using the contains test

<foreach collection="list" item="item" index="index" separator="AND" open="(" close=")">

    <choose>
        <when test='item.cname.contains("select") or item.cname.contains("checkbox") or item.cname.contains("date")'>
            <if test='item.cname.contains("select") or item.cname.contains("checkbox")'>
                find_in_set(#{item.value},base.${item.cname})
            </if>

            <if test='item.cname.contains("date")'>
                DATE_FORMAT(base.${item.cname},'%Y-%m-%d') = DATE_FORMAT(#{item.value},'%Y-%m-%d')
            </if>
        </when>
        <otherwise>
            base.${item.cname} = #{item.value}
        </otherwise>
    </choose>


</foreach>
Copy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Mybatis if else if condition determines the value and concatenation of SQL fragment expressions

preface

Quick to use

SQL fragment stitching

SQL parameter value and OGNL expression

Mybatis if else if condition determines the value and concatenation of SQL fragment expressions

preface

Quick to use

SQL fragment stitching

SQL parameter value and OGNL expression

Related Posts

You may not know about Docker?

🚄 [Redis Principle Exploration] In-depth understanding of persistence principle (Basic)

Spring Boot series (5) Integrated Druid data source