MyBatis Magic Hall: Batch update operations for each database

First, preface

The MyBatis update element is used in the same way as the insert element, so this article is not intended to be repeated. This article only records the SQL statement of the bulk update operation, and understands the SQL statements, then the operation of the MyBatis section is simple.

Note: The following bulk update statements are performed as a whole, either all successful or all rolled back.

Ii. SQL statements for MSSQL

With R as (SELECT'John'  asName -  asAge the  asID UNION all SELECT'Mary'  asName -  asAge +  asID UNION all SELECT'Kite'  asName +  asAge -  asID) UPDATE tstudent SET name= R.name, age =r.agefrom R WHERE r.id= Tstudent.id

Iii. SQL statements for MSSQL, Oracle, and MySQL

UPDATE tstudent SET Name = r.name, age =R.age from(SELECT'Mary'  asName A  asAge the  asidunion AllSelect 'John'  asName, -  asAge +  asID) asRwhereID = R.id

Iv. SQL statements for SQLite

When the article is updated:

REPLACE into Tstudent (Name, age, ID) VALUES ('Mary'42 )

Batch update:

 replace into Tstudent (Name, age, ID) SELECT  *  from ( select    mary    as  A,  B, 42  as   C UNION ALL  select   " john   " as  A, 14  as  B, 43  as   b) as R  

Description: REPLACE into determines whether an insert operation or an update operation is performed based on the primary key value.

V. Summary

This article highlights the benefits of MyBatis as a semi-automatic ORM framework, full manual manipulation of SQL statements how a cool word. However, the code farmers of the SQL knowledge requirements have also increased a lot, if the project requirements for these two times the package that will be less easily.

Respect the original, reprint please indicate from: http://www.cnblogs.com/fsjohnhuang/p/4078999.html ^_^ Fat Boy John

Vi. references

http://feng10251225.blog.163.com/blog/static/6312279820114302391320/

Http://www.cnblogs.com/liping13599168/archive/2011/05/24/2054908.html

MyBatis Magic Hall: Batch update operations for each database