一、动态sql概念
动态SQL,即通过MyBatis 提供的各种标签对条件作出判断已实现动态拼接SQL语句。条件判断使用的表达式为OGNL表达式。
二、动态sql标签的分类
大概分为一下四种常用的动态标签
元素 | 作用 | 使用场景 |
---|---|---|
if | 判断语句 | 用于单条件分支判断 |
where、set、trim | 辅助元素 | 用于处理一些查询条件拼接问题 |
choose、when、otherwise | 条件的选择、相当java中的 switch case default 语句 | 用于多条件分支判断 |
foreach | 循环语句 | 用于批量添加和查询 |
注意事项:
在mapper的动态SQL中如出现大于号,小于号,大于等于号,小于等于号,最好将其转换为实体符号,否则,XML可能会出现解析出错问题
特别是小于号 (<),在XML中绝对不能出现 ,否则一定出错
原符号 | 实体符号 |
---|---|
< | &It; |
<= | &It;= |
> | >; |
>= | >;= |
& | &; |
’ | &apos; |
" | "; |
三、动态标签的用法
员工实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class EmpEntity {
private Integer empno;
private String ename;
private String job;
private Integer mgr;
private Date hiredate;
private Float sal;
private Float comm;
private Integer deptno;
}
数据库员工表
3.1 if标签——单条件的判断
当参数满足条件才会执行某个条件
dao层:
List<EmpEntity> ifTest(EmpEntity empEntity);
mapper层: 根据实体中 ename 和 empno 是否非空进行拼接查询
<select id="ifTest" resultType="com.aaa.entity.EmpEntity">
select * from emp where 1=1
<if test="ename!=null and ename !=''">
and ename = #{ename}
</if>
<if test="empno!=null and empno !=''">
and empno = #{empno}
</if>
</select>
测试类
public class EmpDaoTest {
SqlSession sqlSession;
EmpInfoMybatisDao empDao;
@Before
public void setUp() throws Exception {
System.out.println("开始测试");
sqlSession = MySqlsessionFactory.getSQLSession();
empDao = sqlSession.getMapper(EmpInfoMybatisDao.class);
}
@After
public void tearDown() throws Exception {
System.out.println("结束测试");
if (sqlSession != null) {
// 事物提交
sqlSession.commit();
} else {
// 关闭sqlSession
sqlSession.close();
}
}
@Test
public void ifTest() {
EmpEntity entity = new EmpEntity();
entity.setEname("张三");
entity.setEmpno(1);
List<EmpEntity> entities = empDao.ifTest(entity);
for (EmpEntity empEntity : entities) {
System.out.println(empEntity);
}
}
}
结果显示:
3.2 where标签
如果不使用where标签,就要在where其他判断语句前加入1=1 。如 select * from emp where 1=1加其他的 if 判断语句
如果不想在sql语句后加入这个1=1,可以直接使用where标签和下面的choose标签和if标签搭配使用。使用where 语句 可以自动消除第一个条件中的 and 且加上where 例子如下面两个标签中即可
dao层
List<EmpEntity> whereTest(@Param("salMin") Integer salMin,@Param("salMax") Integer salMax);
mapper层
<select id="whereTest" resultType="com.aaa.entity.EmpEntity">
select * from emp
<where>
<if test="salMin!=null and salMin!=''">
and sal > #{salMin}
</if>
<if test="salMax!=null and salMax!=''">
and sal < #{salMax}
</if>
</where>
</select>
测试类
@Test
public void whereTestDemo() {
List<EmpEntity> sals = empDao.whereTest(15000, 20000);
for (EmpEntity sal : sals) {
System.out.println(sal.toString());
}
}
测试结果
3.3 choose、when、otherwise 标签 多条件分支判断
choose、when、otherwise : choose标签是按顺序判断其内部when标签中的test条件是否成立,如果有一个成立,则choose结束;如果所有的when条件都不满足时,则执行otherwise中的SQL。类似于java的switch语句。
dao层
List<EmpEntity> chooseTest(@Param("enameX")String ename,@Param("empnoX")Integer empno);
mapper层:跟据判断 ename 和 empno 是否非空进行拼接 如果都为空则拼接 ename='王五'
<select id="chooseTest" resultType="com.aaa.entity.EmpEntity">
select * from emp
<where>
<choose>
<when test="enameX!=null and enameX!=''">
ename = #{enameX}
</when>
<when test="empnoX!=null and empnoX!=''">
empno = #{empnoX}
</when>
<otherwise>
ename = '王五'
</otherwise>
</choose>
</where>
</select>
测试层
@Test
public void chooseTest() {
List<EmpEntity> test = empDao.chooseTest("张三", null);
// List<EmpEntity> test = empDao.chooseTest(null, 1);
// List<EmpEntity> test = empDao.chooseTest("张三", 1);
// List<EmpEntity> test = empDao.chooseTest(null, null);
for (EmpEntity entity : test) {
System.out.println(entity);
}
}
上面四种测试结果分别为
3.4 set标签
set标签用于解决动态更新语句存在的符号问题,一般配合if标签用于修改语句,set标签会动态前置SET关键字,同时也会消除无关的逗号,因为用了条件语句后,可能就会在生成的赋值语句的后面留下逗号。
dao层:
int setTest(EmpEntity empEntity);
mapper:
<update id="setTest">
update emp
<set>
<if test="ename!=null and ename !=''">
ename=#{ename},
</if>
<if test="job!=null and job !=''">
job=#{job},
</if>
<if test="deptno!=null and deptno !=''">
deptno=#{deptno},
</if>
</set>
<where>
empno = 1
</where>
</update>
测试类
@Test
public void setTestDemo() {
EmpEntity entity = new EmpEntity();
entity.setJob("会计");
entity.setDeptno(5);
int test = empDao.setTest(entity);
System.out.println("影响条数:"+test);
}
测试结果
3.5 foreach标签
循环标签 适用于批量添加、删除 和查询记录
正常sql语句为 select * from emp where id in(1,3,5);
下面的为使用foreach遍历 循环查询
解释:
<foreach collection="集合类型" open="开始的字符" close="结束的字符"
item="集合中的成员" separator="集合成员之间的分割符">
#{item的值}
</foreach>
标签属性:
collection:表示循环的对象是数组还是list集合。
如果dao方法的形参是数组,collection="array";
如果dao方法形参是list,collection="list";
open:循环开始的字符。sql.append("(");
close:循环结束的字符。sql.append(")");
item:集合成员,自定义的变量。Integer item = idList.get(i);
separator:集合成员之间的分隔符。sql.append(",");
#{item的值}:获取集合成员的值;
最重要的是 collection 属性 该属性是必须指定的
- 如果传入的是单参数且参数类型是一个List的时候,collection属性值为list
- 如果传入的单参数且参数类型是一个array数组的时候,collection的属性值为array
- 如果传入的参数是多个的啥时候,我们就需要把他们封装成一个Map,单参数也可以封装成Map,实际上如果我们在传入参数的时候,在Mybatis里面也是会把它封装成一个map,map的key就是参数名,所以这个时候collection1属性值就是传入的List或array对象在自己封装的Map里面的key
1、使用foreach进行批量新增
dao层
int addEmpBatch(@Param("addInfo") List list);
mapper层
<!-- 批量添加-->
<insert id="addEmpBatch">
insert into emp (ename,job,sal) values
<foreach collection="addInfo" item="aaa" separator=",">
(#{aaa.ename},#{aaa.job},#{aaa.sal})
</foreach>
</insert>
测试类
// 批量添加
@Test
public void addEmpBatchDemo() {
StopWatch sw = new StopWatch();
sw.start();
List<EmpEntity> empList = new ArrayList<>();
for (int i = 0; i < 100; i++) {
EmpEntity entity = new EmpEntity();
entity.setEname("张三"+(i+1));
entity.setJob("工程师"+(i+1));
entity.setSal((i+1)*1f);
empList.add(entity);
}
int aa = empDao.addEmpBatch(empList);
sw.stop();
System.out.println("成功添加"+aa+"条");
System.out.println("花费时间:"+sw.getTotalTimeMillis());
}
测试结果
2、使用foreach进行批量删除
dao层
int forEachTest(@Param("myList")List list);
mapper层
<delete id="forEachTest">
delete from emp where empno in
<foreach collection="myList" item="empno" open="(" close=")" separator=",">
#{empno}
</foreach>
</delete>
测试类
@Test
public void forEachTstDemo() {
List<Integer> empnos = new ArrayList();
for (int i = 8949; i < 9049; i++) {
empnos.add(i);
}
int aa = empDao.forEachTest1(empnos);
System.out.println("删除条数为:" + aa);
}
测试结果
3.6 新增返回主键 (前提数据库是自增的)
mybatis提供了不少操作sql语句的标签,其中包括了新增的标签,其返回与主键相关属性也是该标签提供的,与返回主键相关的属性如下:
属性 | 描述 |
---|---|
keyProperty | (仅对insert和update有用)此属性的作用是将插入或更新操作时的返回值赋值给PO类的某个属性,通常会设置为主键对应的属性。 如果需要设置联主键,可以在多个值之间用逗号隔开 |
keyColumn | (仅对insert和update有用)此属性用于设置第几列是主键,当主键列不是表中的第一列时需要设置。在需要主键联合时,值可以用逗号隔开 |
useGeneratedKeys | (仅对insert叶和update有用)此属性会使MyBatis使用JDBC的getGeneratedKeys()方法来获取由数据库内部生产的主键,如MySQL相SQLServer等自动递增的字段,其默认值为false |
dao层
// 新增返回主键
int autoKey(EmpEntity entity);
mapper层
<insert id="autoKey" keyProperty="empno" useGeneratedKeys="true">
insert into emp (ename,job,sal) values (#{ename},#{job},#{sal})
</insert>
测试类
// 新增返回主键
@Test
public void autoKry() {
EmpEntity entity = new EmpEntity();
entity.setEname("黄天霸");
entity.setJob("销售员");
entity.setSal(8000f);
int aa = empDao.autoKey(entity);
// 新增完 通过实体.getId()来获取主键
System.out.println("新增返回主键ID:" + entity.getEmpno());
}
测试结果
普通新增
四、模糊查询
当我们从数据库中查询数据时,大批量的数据会存在相同的数据。比如重名的人,当我们使用姓名查询该姓名的所有数据时,我们需要在Mybatis中使用到模糊查询的概念。
4.1 模糊查询 like 语句常用的有一下三种
- 方式一 '%${xxx}%' 可能引起SQL注入,不推荐
- 方式二 '%#{xxx}%' 或者 "%"#{xxx}"%" 因为 #{xxx}解析成sql语句时候,会在变量外侧自动加单引号,所以这里 % 需要使用双引号,不能使用单引号,不然会查不到任何结果。
- 方式三 concat('%', #{xxx} , '%') 使用CONCAT()函数 (推荐)
4.2 #{} 和 ${}区别
- #{}是占位符,预编译处理;${}是拼接符,字符串替换,没有预编译处理。
- Mybatis在处理#{}时,#{}传入参数是以字符串传入,会将SQL中的#{}替换为?号,调用PreparedStatement的set方法来赋值。
- #{} 可以有效的防止SQL注入,提高系统安全性;
- ${}不能防止SQL注入 #{} 的变量替换是在DBMS中;${} 的变量替换是在DBMS外
4.3 代码演示
dao层
List<EmpEntity> mhQuery(Map map);
mapper层
<select id="mhQuery" resultType="com.aaa.entity.EmpEntity">
select * from emp
<where>
<if test="ename !=null and ename != ''">
and ename like concat('%',#{ename},'%')
</if>
<if test="job !=null and job != ''">
and job like "%" #{job} "%"
</if>
<if test="sal !=null and sal != ''">
and sal like '%${sal}%'
</if>
</where>
</select>
测试类
// 模糊查询
@Test
public void moHuQueryTest(){
Map map = new HashMap();
map.put("ename","张");
map.put("job","员");
map.put("sal","20");
List<EmpEntity> entities = empDao.mhQuery(map);
for (EmpEntity entity : entities) {
System.out.println(entity.toString());
}
}
测试结果