mybatis笔记

动态SQL

MyBatis 的动态 SQL XML 中支持的几种标签

• if
• choose (when oterwise)
• trim (where set)
• foreach
• bind

- <select id=” selectByUser” result Type=” tk.mybatis . simple .model . SysUser” >
select id, 
user name userName , 
user password userPassword, 
user email userEma
use fo userinfo , 
head img headimg , 
create me createTime 
from sys user 
where 1 = 1 
<if test=” u serName != null and userName != ””>
and user name like coηcat （’ ’，＃ userName ｝，
</if> 
<if test=” userEmail != null and userEmail !=”” >
and user email = #{userEmail} 
〈／工 f>
</select>

MyBatis 中选择性更新的方法名会以 Selective 作为后缀。

<update updateByidSele ctive
update sys user 
set 
〈工 test=” userName != null and userName !=””>
user name= #{userName}, 
</if> 
<if test=”userPassword != null and userPassword !=””>
user password= #{userPassword} , 
〈／工 f>
<if test=”userEmail != null and userEmail ! = ””>
user email = #{userEmail}, 
</if> 
test=” u serlnfo != null and userlnfo ! = ””>
user info= #{userlnfo}, 
</if> 
<if test=” headlmg != nul l ”>
head img = #{headlmg, jdbcType=BLOB}, 
</if> 
<if test createT me != nul l ”>
create time = #{createTime, jdbcType=TIMESTAMP}, 
〈／工 f>
id = #{id} 
where id = #{id} 
</update> 

<select id=”selectByidOrUserName” resultType=” tk.mybatis.simple.m0del. SysUser” >
select id, 
user name userName , 
us e r password userPassword, 
user email userEmail , 
user info userinfo , 
head_img headimg , 
create time createTime 
from sys_ user 
where 1 = 1 
<choose> 
<when test=” id != nul l ” >
and id= #{id} 
</when> 
<when test=” u serName != null and userName !=””>
and user name = #{userName} 
</when> 
<otherwise> 
and 1 = 2 
</otherwise> 
</ choose> 
</select>

<select id=” selectByUser” result Type=” tk . mybatis . simple . model . SysUser” >
select id, 
user name u serName, 
u ser password userPassword, 
user ema 工 l userEmail , 
u ser info userinfo , 
head img headimg, 
create time createTime 
from sys user 
76 I MyBatis 从入门到
<where> 
<if test=”userName != null and userName !=””>
and user name ke concat ’，＃｛ serName ’）
</ if> 
<if test=” userEmail != ” and userEmail != nul l ”>
and user email = #{userEmail} 
</ if> 
</where> 
</select >

<update id=”updateByidSelective ” >
update sys_user 
<set> 
<if test=” userName != null and userName !=””>
user name= #{userName} , 
</ if> 
<if test=” userPassword != null and userPassword ! = ””>
user password= #{userPassword} , 
</if> 
<if test=”userEmail != null and userEmail != ””>
user email = #{userEmail} , 
</ if> 
<if test=” userinfo != null and userinfo !=””>
user info = #{userinfo}, 
</if> 
<if test=”headimg != null” >
head_img = #{headimg, jdbcType=BLOB}, 
</if > 
MyBatis SQL I 77 
<if test=” createTime != nul l ”>
create_time = #{createTime, jdbcType=TIMESTAMP}, 
</if> 
id = # {id} , 
</set> 
where id = #{id} 
</ update>

foreach 包含以下属性。

• collection 必填，值为要选代循环的属性名。这个属性值的情况有很多。
• • item ：变量名，值为从法代对象中取出的每一个值。
• index ：索引的属性名，在集合数组情况下值为当前索引值 当选代循环的对象是 Map
  类型时，这个值为 Map key （键值）。
• open：整个循环内容开头的字符串
• close 整个循环内容结尾的字符串。
• separator ：每次循环的分隔符

collection 的属性要如何设置呢？来看一下 MyBatis 是如何处理这种类型的参数的。

<select id=” selectByidList” resultType=” tk .mybatis . simple .model . SysUser” >
select id, 
user name userName , 
user password userPassword, 
user email userEmail, 
use fo userinfo , 
head img headimg, 
create time createTime 
from sys_user 
where in 
<foreach collection=” lis t ” open=” (” close=” )” separator=” , ” 
item=” id” index=” i ” >
#{id} 
</foreach> 
</ select>

foreach 实现批量插入

nt insertList(List<SysUser> userList); 
UserMapper xml 添加如下 SQL
〈工 nsert id=”insertList”>
insert into sys user( 
user ame user password, user email , 
user_info , head_img , create_time) 
values 
<foreach collection=” lis t ” i tern=” user