A preface

This article needs to have a certain mybatis entry basis to learn; If you are beginners please refer to the following links for systematic learning

Mybatis tutorial

  • Introduction to mybatis (1)
  • Mybatis Mapping (2)
  • Mybatis Configuration (3)
  • Mybatis dynamic SQL and batch insert (4)
  • Mybatis Custom typeHandler(5)
  • Mybatis plug-in Configuration (6)
  • To be continued

Of course, if you are a senior reader, and did not go through the system to learn, you can directly take a glance at the use of dynamic tags;

Dynamic SQL can be given different conditions, the execution of different SQL statements, the way to achieve dynamic SQL is to configure the tag syntax provided by Mybatis;

Dynamic SQL tags

2.1 if the label

  1. The if tag represents a conditional judgment
  2. If customer_name is not empty, the contents of the current if tag are executed. The SQL statement is select * fromcustomer where and customer_name = #{customer_name}
  3. The if statement is executed when gender is not empty; The SQL statement executed is select * fromcustomer where 1 = 1 and gender = #{gender}
  4. When neither the customer_NAME nor the gender tag is empty; The SQL statement executed is select * fromcustomer where and customer_name = #{customer_name} and gender = #{gender}
<select id="getCustomer" resultType="com.zszxz.dynamic.entity.Customer" > select * from `customer` where 1=1 <if test="customer_name! =null and customer_name! ='' "> and `customer_name` = #{customer_name} </if> <if test="gender! =null and gender! =''"> and `gender` = #{gender} </if> </select>Copy the code

2.2 the where TAB

In 2.1, we use the operation 1=1 after where, which means always true. It is not a standard database operation. We usually use the WHERE tag with the if tag in the condition section of the SELECT statement to eliminate the negative effects of the 1=1 operation

<select id="getCustomer" resultType="com.zszxz.dynamic.entity.Customer" > select * from `customer` <where> <if test="customer_name! =null and customer_name! ='' "> and `customer_name` = #{customer_name} </if> <if test="gender! =null and gender! =''"> and `gender` = #{gender} </if> </where> </select>Copy the code

2.3 Choose, When, and Otherwise labels

Sometimes one scenario is called if else, as in the following example

  1. If customer_name is not empty, the content of the when tag body is executed. In this case, SQL is selectgender from customer;
  2. The content of the otherwise tag body is executed when the input condition customer_name is empty. In this case, THE SQL is select * fromcustomer;
<select id="getCustomer" resultType="com.zszxz.dynamic.entity.Customer" > select <choose> <when test="customer_name=! null"> `gender` </when> <otherwise> * </otherwise> </choose> from `customer` </select>Copy the code

2.4 the trim tabs

Let’s start with what the trim attribute means

  1. Prefix indicates a prefix that is added to the trim label body.
  2. PrefixOverrides to remove the first matched string.
  3. Suffix indicates a suffix, and a suffix is added to the trim label body.
  4. SuffixOverrides indicates that the last string matched will be removed.

Select * from customer where customer_name = #{customer_name}

    <select id="getCustomer" resultType="com.zszxz.dynamic.entity.Customer" >
        select
            *
        from `customer`
        <trim prefix="where" prefixOverrides="and">
            and `customer_name` = #{customer_name}
        </trim> 

    </select>
Copy the code

2.5 the set label

The set tag can only be used in update operations; In the following example, the set tag defaults to removing the last extra comma.

<update id="updateCustomer" parameterType="com.zszxz.dynamic.entity.Customer"> update `customer` <set> <if test="customer_name! =null"> `customer_name` = #{customer_name}, </if> <if test="gender! =null"> `gender` = #{gender}, </if> <if test="telephone! =null"> `telephone` = #{telephone}, </if> <if test="register_time! =null"> `register_time` = #{register_time}, </if> </set> <where> id = #{id} </where> </update>Copy the code

If all parameters are not null the following statement is executed

update `customer` SET `customer_name` = ? , `gender` = ? , `telephone` = ? , `register_time` = ? WHERE id = ?Copy the code

The set label is equivalent to the trim label

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>
Copy the code

2.6 The foreach tag implements in

The following table describes the attributes of the foreach tag

  1. The collection parameter
  2. Open the prefix
  3. The separator separator
  4. The item iteration gets the value when the Entry argument is map. Entry or Map
  5. The key in the index iteration, which is the key when the Entry argument is map. Entry or Map; If the input parameter is the number of groups, it is the index.
<select id="getCustomerById" resultType="com.zszxz.dynamic.entity.Customer" > select * from `customer` <where> <if test="ids! =null"> and id in <foreach collection="ids" open="(" separator="," item="id" index="i" close=")"> #{id} </foreach> </if> </where> </select>Copy the code

The final SQL statement execution form is as follows

select * from `customer` WHERE id in ( ? ,?)Copy the code

2.7 Batch Insertion of Foreach Labels

Add the following statement to the XML

 <insert id="addCustomer" >
         insert into `customer`(
            `customer_name`,
            `gender`,
            `telephone`,
            `register_time`
         )values 
         <foreach collection="customers"  separator="," item="customer" index="i">
             (
                 #{customer.customer_name},
                 #{customer.gender},
                 #{customer.telephone},
                 #{customer.register_time}
             )
         </foreach>
    </insert>
Copy the code

Part of the test class is as follows: create three customer objects in the for loop into the List, and then execute the batch insert method

@test public void testInsert(){// Obtain the form of mapper CustomerMapper mapper = sqlsession.getMapper (customermapper.class); ArrayList<Customer> list = new ArrayList<>(); for (int i = 0; i < 3; i++) { Customer customer = new Customer(); Customer.setcustomer_name (" Knowledge seeker "); Customer. SetGender (" male "); customer.setTelephone("999"+i); list.add(customer); } // add mapper.addCustomer(list); sqlSession.commit(); sqlSession.close(); }Copy the code

The format of the SQL statement is as follows

insert into `customer`( `customer_name`, `gender`, `telephone`, `register_time` )values ( ? ,? ,? ,?) . (? ,? ,? ,?) . (? ,? ,? ,?)Copy the code

2.8 the script tags

The script tag is used to annotate versions of dynamic SQL, as shown in the official example below

@Update({"<script>", "update Author", " <set>", " <if test='username ! = null'>username=#{username},</if>", " <if test='password ! = null'>password=#{password},</if>", " <if test='email ! = null'>email=#{email},</if>", " <if test='bio ! = null'>bio=#{bio}</if>", " </set>", "where id=#{id}", "</script>"}) void updateAuthorValues(Author author);Copy the code

2.9 SQL, include Tags

An SQL tag is an SQL fragment. In the following example, the query criteria are extracted into an SQL fragment and referenced using the include tag. This is useful when we want to reuse SQL fragments, reduce the amount of code;

<sql id="condition"> <where> <if test="customer_name! =null and customer_name! ='' "> and `customer_name` = #{customer_name} </if> <if test="gender! =null and gender! =''"> and `gender` = #{gender} </if> </where> </sql> <select id="getCustomer" resultType="com.zszxz.dynamic.entity.Customer" > select * from `customer` <include refid="condition"> </include> </select>Copy the code

2.10 the bind tag

The bind element can create a variable from the OGNL expression and bind it to the context; The advantage of using the BIND tag is that you can eliminate the influence of different database syntax during database replacement; Mysql, Oracle, postgresQL, etc. The concat function used in mysql is no longer available in Oracle.

<sql id="condition"> <where> <if test="customer_name! =null and customer_name! ='' "> and customer_name like #{customer_name} </if> <if test="gender! =null and gender! =''"> and gender = #{gender} </if> </where> </sql> <select id="getCustomer" resultType="com.zszxz.dynamic.entity.Customer" > <bind name="customer_name" value="'%'+customer_name+'%'"/> select * from customer <include refid="condition"> </include> </select>Copy the code

Three source

If it is a beginner, some places do not understand can refer to the author of mybatis column notes have source address, source code each example has complete code to provide personal learning;