一.动态sql的介绍
需求：查询员工信息，要求传入的参数为Emp对象，如果empName不为空，需要根据empName查询，如果empMail不为空，需要根据empMail查询，如果empGender不为空，要求根据empGender查询，要求如果deptId不为空，要求根据deptId查询。
类似于以上的需求，必须使用MyBatis的动态sql，动态sql是MyBatis强大的特性之一，能够极大的简化我们sql拼装的过程，动态sql使用标签完成动态sql，类似于我们用过的jstl，获取和其他的xml类似的语法。
二. 动态sql之if判断
<!--
public List<Emp> selectEmpByEmp(Emp emp);
-->
<select id="selectEmpByEmp" resultType="com.wanbangee.entities.Emp">
select * from emp where
<if test="empName != null and empName != ''"> <!-- test="empName != null && empName != ''" -->
emp_name = #{empName}
</if>
<if test="empMail != null and empMail != ''">
and emp_mail = #{empMail}
</if>
<if test="empGender != null">
and emp_gender = #{empGender}
</if>
<if test="deptId != null">
and dept_id = #{deptId}
</if>
</select>
现在在满足每一个if条件的情况下，正常运行，但是如果将参数中的empName设置为空，那么拼装的sql语句肯定不能够正常执行。
三. 动态sql之where查询条件
以上的程序，在指定的情况下，sql不能正常执行，要么前面多一个and，要么后面多一个and，我们有两种解决方案：
① where 1=1 …
select * from emp where 1=1
<if test="empName != null and empName != ''"> <!-- test="empName != null && empName != ''" -->
and emp_name = #{empName}
</if>
<if test="empMail != null and empMail != ''">
and emp_mail = #{empMail}
</if>
<if test="empGender != null">
and emp_gender = #{empGender}
</if>
<if test="deptId != null">
and dept_id = #{deptId}
</if>
②：MyBatis提倡的方式，就是使用where标签，将查询的判断放在where标签中
<!--
public List<Emp> selectEmpByEmp(Emp emp);
-->
<!--
<select id="selectEmpByEmp" resultType="com.wanbangee.entities.Emp">
-->
select * from emp
<where>
<if test="empName != null and empName != ''"> <!-- test="empName != null && empName != ''" -->
emp_name = #{empName} and
</if>
<if test="empMail != null and empMail != ''">
emp_mail = #{empMail} and
</if>
<if test="empGender != null">
emp_gender = #{empGender} and
</if>
<if test="deptId != null">
dept_id = #{deptId}
</if>
</where>
</select>
以上的程序依然存在问题，在deptId为null情况下，会多出一个and，导致sql报错。
四. 动态sql之trim（自定义字符串截取）
Trim标签可以很好的解决where解决不了的问题。
<select id="selectEmpByEmp" resultType="com.wanbangee.entities.Emp">
<!--
-->
select * from emp
<!--
prefix : 添加前缀
suffix ： 添加后缀
prefixOverrides ： 前缀覆盖
prefixOverrides="abc" 表示如果trim标签中拼凑的sql语句 以 abc 结尾，则用空串将abc覆盖
suffixOverrides ： 后缀覆盖
suffixOverrides="and" 表示如果trim标签中拼凑的sql语句 最后以 and 结尾，则用空串将and覆盖
-->
<trim prefix=" where " suffix="" prefixOverrides="abc" suffixOverrides="and" >
<if test="empName != null and empName != ''"> <!-- test="empName != null && empName != ''" -->
emp_name = #{empName} and
</if>
<if test="empMail != null and empMail != ''">
emp_mail = #{empMail} and
</if>
<if test="empGender != null">
emp_gender = #{empGender} and
</if>
<if test="deptId != null">
dept_id = #{deptId}
</if>
</trim>
</select>
五. 动态sql之choose（分支选择）
<!--
public List<Emp> selectEmpByEmp2(Emp emp);
-->
<select id="selectEmpByEmp2" resultType="com.wanbangee.entities.Emp">
select * from emp
<where>
<choose>
<when test="empName != null and empName != ''">
emp_name = #{empName}
</when>
<when test="empMail != null and empMail != ''">
emp_mail = #{empMail}
</when>
<when test="empGender != null">
emp_gender = #{empGender}
</when>
<when test="deptId != null">
dept_id = #{deptId}
</when>
<!-- <otherwise>
1=1
</otherwise> -->
</choose>
</where>
</select>
需求：查询员工信息，要求传入的参数为Emp对象，如果empName不为空，则根据empName查询，如果empMail不为空，则根据empMail查询，如果empGender不为空，则根据empGender查询，要求如果deptId不为空，则根据deptId查询。这个需求的要求表示查询条件只有一个，我们使用if是完成不了了，只能使用choose分支标签。
六. 动态sql之set标签与if结合进行更新
Set 标签用于修改。根据emp_id 修改emp数据，入参为Emp对象，对象中如果empName不为空，empName则需要修改，如果empMail不为空，则empMail需要修改…
<!--
public int updateEmp(Emp emp);
-->
<update id="updateEmp">
update emp set
<set>
<if test="empName != null and empName != ''">
emp_name = #{empName} ,
</if>
<if test="empMail != null and empMail != ''">
emp_mail = #{empMail} ,
</if>
<if test="empGender != null">
emp_gender = #{empGender} ,
</if>
<if test="deptId != null">
dept_id = #{deptId}
</if>
</set>
<where>
<if test="empId == null">
1 = 2
</if>
<if test="empId != null">
emp_id = #{empId}
</if>
</where>
</update>
当然我们也可以使用trim标签完成
<!--
public int updateEmp(Emp emp);
-->
<update id="updateEmp">
update emp
<trim prefix=" set " suffixOverrides=",">
<if test="empName != null and empName != ''">
emp_name = #{empName} ,
</if>
<if test="empMail != null and empMail != ''">
emp_mail = #{empMail} ,
</if>
<if test="empGender != null">
emp_gender = #{empGender} ,
</if>
<if test="deptId != null">
dept_id = #{deptId}
</if>
</trim>
<where>
<if test="empId == null">
1 = 2
</if>
<if test="empId != null">
emp_id = #{empId}
</if>
</where>
</update>
七. 动态sql之foreach标签遍历集合
Foreach标签用来遍历集合的，如何使用呢？范例：传入一个Integer类型的List集合，要求emp_id in(集合中的数据)，这个时候我们可以使用foreach标签去循环遍历list集合。
<!--
public List<Emp> selectEmpByEmpIds(List<Integer> ids);
-->
<select id="selectEmpByEmpIds" resultType="com.wanbangee.entities.Emp">
select * from emp where emp_id in
<!--
collection : 表示要遍历的集合,
如果传入的参数是List类型，那么可以使用list或者collection，如果传入是Set集合，只能使用collection
如果是数组，则使用array
实际上我们在接口的方法中使用@Param指定collection属性使用的值 ,指定之后，能使用指定的内容和param1....
open : 前缀添加
close：后缀添加
separator ： 每次遍历的分割符
index:遍历次数
item : 每次遍历的数据赋值的变量
-->
<foreach collection="ids" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</select>
八. MySQL环境下使用foreach进行批量插入
Mysql进行批量新增数据的两种sql语法：
# 第一种
INSERT INTO emp(emp_name,emp_mail,emp_gender,dept_id)
VALUES('AAA','aaa@163.com',1,1),
('BBB','aaa@163.com',1,1),
('CCC','aaa@163.com',1,1),
('DDD','aaa@163.com',1,1)
# 第二种
INSERT INTO emp(emp_name,emp_mail,emp_gender,dept_id)VALUES('aaa','aaa@163.com',1,1);
INSERT INTO emp(emp_name,emp_mail,emp_gender,dept_id)VALUES('bbb','aaa@163.com',1,1);
INSERT INTO emp(emp_name,emp_mail,emp_gender,dept_id)VALUES('ccc','aaa@163.com',1,1);
INSERT INTO emp(emp_name,emp_mail,emp_gender,dept_id)VALUES('ddd','aaa@163.com',1,1);
使用foreach进行批量新增：
<!--
public int insertEmps(List<Emp> emps);
-->
<insert id="insertEmps">
<!-- 第一种sql
INSERT INTO emp(emp_name,emp_mail,emp_gender,dept_id) VALUES
<foreach collection="list" separator="," item="emp">
(#{emp.empName},#{emp.empMail},#{emp.empGender},#{emp.deptId})
</foreach> -->
<!-- 第二种sql ：
出现了问题，因为默认情况下，JDBC允许一次执行多条sql，而且使用；隔开就是多条sql，为了能让数据库一次性执行多条SQL，我们需要修改连接的参数allowMultiQueries=true
jdbc:mysql://127.0.0.1:3306/ssm?characterEncoding=utf-8&useUnicode=true&allowMultiQueries=true
-->
<foreach collection="list" separator=";" item="emp">
INSERT INTO emp(emp_name,emp_mail,emp_gender,dept_id) VALUES (#{emp.empName},#{emp.empMail},#{emp.empGender},#{emp.deptId})
</foreach>
</insert>
九. Oracle环境下使用foreach进行批量插入
Oracle数据库和mysql数据库的批量新增的语法，完全不同
-- 语法1
begin
INSERT INTO emp_721(emp_id,emp_name,emp_mail,emp_gender,dept_id)VALUES(seq_2020721.nextval,'aaa','aaa@163.com',1,1);
INSERT INTO emp_721(emp_id,emp_name,emp_mail,emp_gender,dept_id)VALUES(seq_2020721.nextval,'bbb','aaa@163.com',1,1);
INSERT INTO emp_721(emp_id,emp_name,emp_mail,emp_gender,dept_id)VALUES(seq_2020721.nextval,'ccc','aaa@163.com',1,1);
INSERT INTO emp_721(emp_id,emp_name,emp_mail,emp_gender,dept_id)VALUES(seq_2020721.nextval,'ddd','aaa@163.com',1,1);
end ;
-- 语法2
INSERT INTO emp_721(emp_id,emp_name,emp_mail,emp_gender,dept_id) select seq_2020721.nextval,emp_name,emp_mail,emp_gender,dept_id from(
select 'AAA' emp_name,'aaa@qq.com' emp_mail,1 emp_gender,1 dept_id from dual
union all
select 'BBB' emp_name,'aaa@qq.com' emp_mail,1 emp_gender,1 dept_id from dual
union all
select 'CCC' emp_name,'aaa@qq.com' emp_mail,1 emp_gender,1 dept_id from dual
union all
select 'DDD' emp_name,'aaa@qq.com' emp_mail,1 emp_gender,1 dept_id from dual
)
foreach完成批量新增
<!--
public int insertEmpsOracle(List<Emp> emps);
-->
<insert id="insertEmpsOracle">
<!-- 第一种语法 -->
<!-- begin
<foreach collection="list" item="emp">
INSERT INTO emp_721(emp_id,emp_name,emp_mail,emp_gender,dept_id)VALUES(seq_2020721.nextval,#{emp.empName},#{emp.empMail},#{emp.empGender},#{emp.deptId});
</foreach>
end; -->
<!-- 第二种语法 -->
INSERT INTO emp_721(emp_id,emp_name,emp_mail,emp_gender,dept_id) select seq_2020721.nextval,emp_name,emp_mail,emp_gender,dept_id from
<foreach collection="list" close=")" open="(" separator=" union all " item="emp">
select #{emp.empName} emp_name,#{emp.empMail} emp_mail,#{emp.empGender} emp_gender,#{emp.deptId} dept_id from dual
</foreach>
</insert>
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
