内容简介
- 动态sql简介
- if
- where
- trim
- choose
- set和if结合动态更新
- foreach
- mysql下的批量保存
- oracle下的批量保存
- 内置参数
- 参数绑定
- sql抽取
动态sql的简介
Mybatis 的映射文件中,前面我们的 SQL 都是比较简单的,有些时候业务逻辑复杂时,我们的 SQL 是动态变化的,此时在前面的学习中我们的 SQL 就不能满足要求了。
需求:查询员工信息,要求传入的参数是Emp对象,如果empName不为空,需要根据empName查询,如果empMail不为空,需要根据empMail查询,如果empGender不为空,要求根据empGender查询,要求如果deptId不为空,要求根据deptId查询。
类似于以上的需求,我们必须使用MyBatis的动态,动态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:select * from emp where and emp_mail=#{empMail}…,不符合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分支选择
需求:查询员工信息,要求传入的参数为Emp对象,如果empName不为空,则根据empName查询,如果empMail不为空,则根据empMail查询,如果empGender不为空,则根据empGender查询,要求如果deptId不为空,则根据deptId查询。
这个需求的要求表示查询条件只有一个,我们使用if是完成不了了,只能使用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>
动态sql使用set 标签与if结合进行更新
Set 标签用于修改。根据emp_id 修改emp数据,入参为Emp对象,对象中如果empName不为空,empName则需要修改,如果empMail不为空,则empMail需要修改…
<!--
public int updateEmp(Emp emp);
set标签会自动帮我们去除多余的 ","
-->
<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>
<!-- 这个作用是:当empId为null,使修改失败,不然数据库中的数据都会被修改 -->
<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
实际上我们在接口的方法中使用@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
<!-- 注意union all 两边空格 -->
<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>
内置参数与databaseId
我们之前进行模糊查询,根据姓名模糊查询,在拼装sql的时候,如果参数没有传递%%,那么我们如何拼装sql呢?
隐式参数_parameter
<select id="selectEmpByEmpNameLike" resultType="com.wanbangee.entities.Emp">
<!--
在sql映射文件中,存在隐式参数
- 对于单个参数来说,这个隐式参数名称就是_parameter
- 对于多个参数来说,我们知道参数会封装成一个map,_parameter 就代表了这个map
参数绑定:bind标签完成
name : 指定新的参数名
value : 指定参数绑定规则
-->
<bind name="_empName" value="'%'+_parameter+'%'"/>
select * from emp_721 where emp_name like #{_empName}
</select>
我们在开发的时候,可以在insert,update,delete和select标签中,指定databaseid属性,表示不同的数据执行的不同的sql,实际上sql映射文件中还存在另外一个隐式参数,_databaseId,我们可以通过此隐式参数来执行不同的SQL(其实很少用)。
<insert id="insertEmpsOracle" >
<if test="_databaseId=='oracle'">
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>
</if>
<if test="_databaseId == 'mysql'">
<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>
</if>
</insert>
抽取可重用的sql片段
Sql 中可将重复的 sql 提取出来,使用时用 include 引用即可,最终达到 sql 重用的目的。
<!-- 抽取重用的sql片段 -->
<sql id="emp_column">
emp_id,emp_name,emp_mail,emp_gender,dept_id
</sql>
<!-- 根据Id查询员工信息 -->
<select id="selectEmpByEmpId" resultType="com.nhkj.entity.Emp">
select emp_id,emp_name,emp_mail,emp_gender,dept_id from emp where empId = #{empId}
</select>
<!-- 根据Id查询员工信息 -->
<select id="selectEmpByEmpId" resultType="com.nhkj.entity.Emp">
select <include refid="emp_column></include> from emp where empId = #{empId}
</select>