一、前言
MyBatis的update元素的用法與insert元素基本相同，因此本篇不打算重復(fù)了。本篇僅記錄批量update操作的
sql語(yǔ)句，懂得SQL語(yǔ)句，那么MyBatis部分的操作就簡(jiǎn)單了。
注意：下列批量更新語(yǔ)句都是作為一個(gè)事務(wù)整體執(zhí)行，要不全部成功，要不全部回滾。
二、MSSQL的SQL語(yǔ)句
WITH R AS（
SELECT 'John' as name, 18 as age, 42 as id
UNION ALL
SELECT 'Mary' as name, 20 as age, 43 as id
UNION ALL
SELECT 'Kite' as name, 21 as age, 44 as id
）
UPDATE TStudent SET name = R.name, age = R.age
FROM R WHERE R.id = TStudent.Id
三、MSSQL、ORACLE和MySQL的SQL語(yǔ)句 UPDATE TStudent SET Name = R.name, Age = R.age
from (
SELECT 'Mary' as name, 12 as age, 42 as id
union all
select 'John' as name , 16 as age, 43 as id
) as r
where ID = R.id
四、SQLITE的SQL語(yǔ)句
當(dāng)條更新：
REPLACE INTO TStudent(Name, Age, ID)
VALUES('Mary', 12, 42)
批量更新：
REPLACE INTO TStudent(Name, Age, ID)
SELECT * FROM (
select 'Mary' as a, 12 as b, 42 as c
union all
select 'John' as a, 14 as b, 43 as b
) AS R
說(shuō)明：REPLACE INTO會(huì)根據(jù)主鍵值，決定執(zhí)行INSERT操作還是UPDATE操作。
五、總結(jié)
本篇突出MyBatis作為半自動(dòng)ORM框架的好處了，全手動(dòng)操控SQL語(yǔ)句怎一個(gè)爽字了得。但對(duì)碼農(nóng)的SQL知識(shí)要求也相對(duì)增加了不少，倘若針對(duì)項(xiàng)目要求再將這些進(jìn)行二次封裝那會(huì)輕松比少。
English » | | | | | | | | |
Text-to-speech function is limited to 100 characters