if标签
- if标签就类似于jstl的if标签
where标签
- 在执行多条件选择时,可能会多出一个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 and email.trim()!=''">
and email=#{email}
</if>
</select>
- 那么,当id=null时候,拼接的语句就会多出来一个and。
- where标签可以删除多余的前缀and /or,然后加上where
- 将动态语句嵌入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 and email.trim()!=''">
and email=#{email}
</if>
</where>
set标签
- 和where用法差不多,在执行更新语句时,可能会多余一个逗号。
- 用于替代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>
- 在语句前插入prefix指明的前缀,suffixOverrides 是对末尾指明的多余的符号清除。
- 可以用来代替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>
- 类似于elseif 或者switch case语句
- 一旦一个匹配上,其余的便不再执行。
foreach标签
- collection 指定要遍历的集合,list类型的参数会特殊处理封装在map中,key就叫list
- item 将当前遍历出的元素赋值给指定的变量
- separator 每个元素之间的分隔符
- open 在遍历出所有的结果之后拼接一个开始的字符串
- close 遍历出所有的结果后拼接一个结束的字符
- 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>
用来批量插入
- 第一种,利用values
- 第二种,同一行执行多条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},#{item.email},#{item.gender})
</foreach>
</insert>
第二种
<insert id="BatchInsert2" >
<foreach collection="list" item="item" separator=";">
insert into employee (last_name,email,gender)Values
(#{item.lastName},#{item.email},#{item.gender})
</foreach>
</insert>
oracle环境下多条记录插入
- 在begin 和 end 间写入多条插入语句
- 利用中间表
sql语句
begin
insert into employee (last_name,email,gender)
Values ("hou","7989@qq.com",1);
insert into employee (last_name,email,gender)
Values ("hou","7989@qq.com",1);
end;
foreach代码
<insert id="BatchInsert2" >
<foreach collection="list" item="item" open="begin" close="end">
insert into employee (id,last_name,email,gender)Values
(employee_seq.nextval,#{item.lastName},#{item.email},#{item.gender})
</foreach>
</insert>
insert into employee (id,last_name,gender,email)
select employeeseq.nextval,last_name,gender,email from(
select "jisdi@qq.com" email,"hou" last_name,0 gender from dual union
select "jisdi@qq.com" email,"hou" last_name,0 gender from dual
)
foreach语句
<insert id="BatchInsert2" >
insert into employee (id,last_name,gender,email)
select employee_seq.nextval,last_name,gender,email from(
<foreach collection="list" item="item" separator="union">
select #{item.lastName} last_name,#{item.email} email,#{item.gender} gender
</foreach>
</insert>