Mybatis batch operation

First, MySQL requires database connection configuration & allowmultiqueries = true

JDBC: mysql: // 127.0.0.1: 3306/mybank? Useunicode = true & characterencoding = utf8 & allowmultiqueries = true

Multiple statements can be executed in Oracle. The following three statements are the same.

<update id="batchUpdate" parameterType="java.util.List">        <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";" >             update T_EMP_1             <set>                       age = #{item.age}+1,name=#{item.name}            </set>            where id = #{item.id}        </foreach>    </update>

<update id="batchUpdate" parameterType="java.util.List">        <foreach collection="list" item="item" index="index" open="begin" close="end;" separator="" >             update T_EMP_1             <set>                       age = #{item.age}+1,name=#{item.name}            </set>            where id = #{item.id};        </foreach>    </update>

<update id="batchUpdate" parameterType="java.util.List">        begin        <foreach collection="list" item="item" index="index" separator="" >             update T_EMP_1             <set>                       age = #{item.age}+1,name=#{item.name}            </set>            where id = #{item.id};        </foreach>        end;    </update>

 

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. Item indicates the alias of each element in the set during iteration. index indicates the position of each iteration during the iteration. Open indicates the start of the statement, separator indicates the separator between each iteration, and close indicates the end of the iteration. When foreach is used, the most critical and error-prone is the collection attribute, this attribute must be specified, but in different cases, the value of this attribute is different, mainly in the following three cases:
1. If the input is a single parameter and the parameter type is a list, the collection property value is list
2. If the input is a single parameter and the parameter type is an array, the property value of the collection is array.
3. if the input parameters are multiple, We 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 parameters, in breast, It is encapsulated into a map, and the map key is the parameter name. Therefore, the collection property value is the input list or the key of the array object in the encapsulated map.

 

Batch Delete

<delete id="batchDeleteStudent" parameterType="List">      DELETE FROM STUDENT WHERE id IN      <foreach collection="list" index="index" item="item" open="(" separator="," close=")">           #{item}       </foreach>  </delete>

Batch Update note: in Oracle, statements such as "Update ** set ** where ** in (...)" and "in" contain a maximum of 1000 entries.

<update id="batchUpdateStudent" parameterType="List">      UPDATE STUDENT SET name = "5566" WHERE id IN      <foreach collection="list" item="item" index="index" open="(" separator="," close=")" >          #{item}      </foreach>  </update>

<update id="batchUpdateStudentWithMap" parameterType="Map" >      UPDATE STUDENT SET name = #{name} WHERE id IN       <foreach collection="idList" index="index" item="item" open="(" separator="," close=")">           #{item}       </foreach>  </update>

Batch insert of MySQL is different from that of Oracle

mysql：<insert id="batchInsertStudent" parameterType="java.util.List">      INSERT INTO STUDENT (id,name,sex,tel,address)      VALUES       <foreach collection="list" item="item" index="index" separator="," >          (#{item.id},#{item.name},#{item.sex},#{item.tel},#{item.address})      </foreach>  </insert> 

oracle：<insert id="insertBatch4Oracle" parameterType="List">        insert into aa(a,b)        <foreach collection="list" item="item" index="index" separator="union all" >         　　select  #{item.a},#{item.b} from dual        </foreach> </insert>