Mybatis performs batch update and mybatis performs update.

Mybatis performs batch update and mybatis performs update.

For batch insert of Mybatis, visit http://ljhzzyx.blog.163.com/blog/static/384243122013536375 /. At present, if you want to update data in batches, if the update values are the same, it is very easy to organize

Batch update

The mysql update statement is simple. to update a field of a piece of data, write as follows:

Copy codeThe Code is as follows:
UPDATE mytable SET myfield = 'value' WHERE other_field = 'other _ value ';

If the same field is updated to the same value, mysql is also very simple. modify where:

Copy codeThe Code is as follows:
UPDATE mytable SET myfield = 'value' WHERE other_field in ('other _ values ');
 

Note that 'other _ values' is a comma (,)-separated string, for example, 1, 2, 3.

If multiple data entries are updated with different values, many may write as follows:

Copy codeThe Code is as follows:
Foreach ($ display_order as $ id => $ ordinal ){
$ SQL = "UPDATE categories SET display_order = $ ordinal WHERE id = $ id ";
Mysql_query ($ SQL );
}

It is an update record that repeats one by one. Update a record once, which has poor performance and can easily cause blocking.

Can I update an SQL statement in batches? Mysql does not provide a direct method for batch update, but it can be implemented with tips.

Copy codeThe Code is as follows:
UPDATE mytable
SET myfield = CASE id
WHEN 1 THEN 'value'
WHEN 2 THEN 'value'
WHEN 3 THEN 'value'
END
WHERE id IN (1, 2, 3)

Here we use the case when tip to implement batch update.
For example:

Copy codeThe Code is as follows:
UPDATE categories
SET display_order = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END
WHERE id IN (1, 2, 3)

This SQL statement indicates that the display_order field is updated. If id = 1, the value of display_order is 3. If id = 2, the value of display_order is 4, if id = 3, the value of display_order is 5.
The condition statements are written together.
The where part does not affect code execution, but it increases the SQL Execution efficiency. Make sure that the SQL statement only executes the number of rows to be modified. Here, only three rows of data are updated, and the where clause ensures that only three rows of data are executed.

If you want to update multiple values, you only need to slightly modify them:

Copy codeThe Code is as follows:
UPDATE categories
SET display_order = CASE id
WHEN 1 THEN 3
WHEN 2 THEN 4
WHEN 3 THEN 5
END,
Title = CASE id
WHEN 1 THEN 'new Title 1'
WHEN 2 THEN 'new Title 2'
WHEN 3 THEN 'new Title 3'
END
WHERE id IN (1, 2, 3)
At this point, a mysql statement has been completed to update multiple records.

Update table set column = '...' where id in (, 3) l SQL. <Update id = "batchUpdateStudentWithMap" parameterType = "java. util. map "> update student set name = # {name} WHERE id IN <foreach collection =" idList "index =" index "item =" item "open =" ("separator =", "close =") ">#{ item} </foreach> </update> but there are few such requirements. Generally, there is a set with different values in each element, then you need to update it all at once. The general processing method is to use a for loop. This is less efficient. When the data volume is large, we expect a one-time insert operation. If mysql is used, there are insert into table (aa, bb, cc) values (xx, xx, xx), (oo, oo, oo) on duplicate key update and replace into table (aa, bb, cc) values (xxx, xxx, xxx), (ooo, ooo, ooo), (ccc, ccc, ccc) two methods are available. The current database is oracle, you can use case when to construct a long string of SQL statements to process UPDATE mytable SET myfield = CASE id WHEN 1 THEN 'value' WHEN 2 THEN 'value' WHEN 3 THEN 'value' ENDWHERE id IN (1, 2, 3) In fact, this method is also effective for mysql. At the beginning, you can write a series of parallel update statements. <update id = "updateBatch" parameterType = "java. util. list "> <foreach collection =" list "item =" item "index =" index "separator = "; "open =" "close =" "> update REGION_CODE set CODE = # {item. code, jdbcType = VARCHAR}, NAME = # {item. name, jdbcType = VARCHAR} where ID = # {item. id, jdbcType = DECIMAL} </foreach> </update>. An error is reported because the SQL statement in the Mybatis ing file is not allowed. According to the feasible case when processing method, the Mybatis ing file is written as follows: <update id = "updateBatch" parameterType = "java. util. list "> update REGION_CODE set CODE = <foreach collection =" list "item =" item "index =" index "separator =" "open =" case ID "close =" end"> when # {item. id, jdbcType = DECIMAL} then # {item. code, jdbcType = VARCHAR} </foreach>, NAME = <foreach collection = "list" item = "item" index = "index" separator = "" open = "case ID" close = "end"> wh En # {item. id, jdbcType = DECIMAL} then # {item. name, jdbcType = VARCHAR} </foreach> where ID in <foreach collection = "list" index = "index" item = "item" separator = ", "open =" ("close =") ">#{ item. id, jdbcType = DECIMAL} </foreach> </update> now, the batch update function is complete. Slave-method for the original mybatis to execute batch update (oracle, mysql)

Oracle Database:

1234567891011

<code class="hljs tcl"><<span class="hljs-keyword">updateid=<span class="hljs-string">"batchUpdate" parameterType=<span class="hljs-string">"java.util.List">             <<span class="hljs-keyword">foreach collection=<span class="hljs-string">"list"item=<span class="hljs-string">"item"index=<span class="hljs-string">"index"<span class="hljs-keyword">open=<span class="hljs-string">"begin"<span class="hljs-keyword">close=<span class="hljs-string">"end;"separator=<span class="hljs-string">";">                <span class="hljs-keyword">updatetest                <<span class="hljs-keyword">set>                  test=${item.test}+<span class="hljs-number">1                </<span class="hljs-keyword">set>                whereid = ${item.id}       </<span class="hljs-keyword">foreach>              </<span class="hljs-keyword">update><br><br><br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

Mysql database:

The mysql database can be executed in the following way, but the database connection must be configured with: & allowMultiQueries = true

Example: jdbc: mysql: // 192.168.1.236: 3306/test? UseUnicode = true & amp; characterEncoding = UTF-8 & allowMultiQueries = true

1234567891011

<code class="hljs tcl"><<span class="hljs-keyword">updateid=<span class="hljs-string">"batchUpdate" parameterType=<span class="hljs-string">"java.util.List">                <<span class="hljs-keyword">foreach collection=<span class="hljs-string">"list"item=<span class="hljs-string">"item"index=<span class="hljs-string">"index"<span class="hljs-keyword">open=<span class="hljs-string">""<span class="hljs-keyword">close=<span class="hljs-string">""separator=<span class="hljs-string">";">                <span class="hljs-keyword">updatetest                <<span class="hljs-keyword">set>                  test=${item.test}+<span class="hljs-number">1                </<span class="hljs-keyword">set>                whereid = ${item.id}         </<span class="hljs-keyword">foreach>              </<span class="hljs-keyword">update><br><br><br>------------------------------------------------------------------------------------------------------------------------------<br><br>https://my.oschina.net/zouqun/blog/405424<br><br>--------------------------------------------------------------------------------------------------------------------------------<br><br></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></code>

Article Reprinted from: http://www.jb51.net/article/41852.htm
Http://www.cnblogs.com/hyq0002013/p/6077847.html