mybatis动态插⼊sql语句if标签
1. if标签就类似于jstl的if标签
where标签
1. 在执⾏多条件选择时，可能会多出⼀个and 关键字。譬如：
<select id="getEmpAndDep"  resultType="emp" databaseId="mysql">
select last_name,email,id,gender from employee
where
<if test="id!=null">
id=#{id}
</if>
<if test="lastName!=null and lastName!=''">
and last_name like#{lastName}
</if>
<if test="gender==0 or gender==1">
and gender=#{gender}
</if>
<if test="email!=null im()!=''">
and email=#{email}
</if>
</select>
1. 那么，当id=null时候，拼接的语句就会多出来⼀个and。
2. where标签可以删除多余的前缀and /or，然后加上where
3. 将动态语句嵌⼊where标签中即可
<where>
<if test="id!=null">
id=#{id}
</if>
<if test="lastName!=null and lastName!=''">
or last_name like#{lastName}
</if>
<if test="gender==0 or gender==1">
and gender=#{gender}
</if>
<if test="email!=null im()!=''">
and email=#{email}
</if>
</where>
set标签
批量更新sql语句1. 和where⽤法差不多，在执⾏更新语句时，可能会多余⼀个逗号。
2. ⽤于替代set关键字，⽤法如下
<set>
<if test="lastName!=null">
last_Name=#{lastName},
</if>
<if test="email!=null">
email=#{email},
</if>
<if test="gender!=null">
gender=#{gender}
</if>
</set>
trim标签
<trim prefix="set"suffixOverrides=","></trim>
1. 在语句前插⼊prefix指明的前缀，suffixOverrides 是对末尾指明的多余的符号清除。
2. 可以⽤来代替set标签和where标签
<trim prefix="WHERE"prefixOverrides="and"></trim>
choose 标签
<update id="updateEmp"  >
update employee
<set>
<choose>
<when test="lastName!=null">
last_Name=#{lastName},
</when>
<when test="email!=null">
email=#{email},
</when>
<when test="gender!=null">
gender=#{gender},
</when>
<otherwise>
gender=1
</otherwise>
</choose>
</set>
where id=#{id}
</update>
1. 类似于elseif 或者switch case语句
2. ⼀旦⼀个匹配上，其余的便不再执⾏。
foreach标签
1. collection 指定要遍历的集合，list类型的参数会特殊处理封装在map中，key就叫list
2. item 将当前遍历出的元素赋值给指定的变量
3. separator 每个元素之间的分隔符
4. open 在遍历出所有的结果之后拼接⼀个开始的字符串
5. close 遍历出所有的结果后拼接⼀个结束的字符
6. index 索引 遍历list 的时候是索引，遍历map的时候是map的key，item即为当前项的值。
⽤来批量查询
查询语句：select * from employee where id in (1,2,3);
<select id="getByIterator" resultType="emp">
select * from employee where id in
<foreach collection="map" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</select>
⽤来批量插⼊
1. 第⼀种，利⽤values
2. 第⼆种，同⼀⾏执⾏多条sql插⼊语句。需要在连接参数上进⾏配置。
jdbc.url=jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true&&useUnicode=true&&characterEncoding=utf8
第⼀种：<insert id="BatchInsert" >
insert into employee (last_name,email,gender)Values
<foreach collection="list" item="item" separator=",">
(#{item.lastName},#{ail},#{der})
</foreach>
</insert>
第⼆种
<insert id="BatchInsert2" >
<foreach collection="list" item="item" separator=";">
insert into employee (last_name,email,gender)Values
(#{item.lastName},#{ail},#{der})
</foreach>
</insert>
oracle环境下多条记录插⼊
1. 在begin 和 end 间写⼊多条插⼊语句
2. 利⽤中间表
sql语句
begin
insert into employee (last_name,email,gender)
Values ("hou","7989@qq",1);
insert into employee (last_name,email,gender)
Values ("hou","7989@qq",1);
end;
foreach代码
<insert id="BatchInsert2" >
<foreach collection="list" item="item" open="begin" close="end">
insert into employee (id,last_name,email,gender)Values
(val,#{item.lastName},#{ail},#{der})
</foreach>
</insert>
insert into employee (id,last_name,gender,email)
val,last_name,gender,email from(
select"jisdi@qq" email,"hou" last_name,0 gender from dual union select"jisdi@qq" email,"hou" last_name,0 gender from dual
)
foreach语句
<insert id="BatchInsert2" >
insert into employee (id,last_name,gender,email)
select val,last_name,gender,email from(
<foreach collection="list" item="item" separator="union">
select#{item.lastName} last_name,#{ail} email,#{der} gender  </foreach>
</insert>

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。