1. 概述
动态SQL:MyBatis对sql语句进行动态的拼接。
比如:
- 我们要查询姓名中带 M 和 高于 1000的员工信息;
- 可能有时候我们需要不带条件查询;
- 可能有时候我们需要模糊查询;
- 可能有时候需要根据多条件查询;
- 动态SQL可以帮助我们解决这些问题。
- 通过Mybatis提供的各种标签方法实现动态拼接sql。
以下业务场景,需要SQL语句是动态的,例如:
- 批量删除
-
delete from t_car where id in(1,2,3,4,5,6,......这里的值要是动态的,根据用户选择的id不同,值是不同的);
-
多条件查询
-
-
select * from t_car where brand like '丰田%' and guide_price > 30 and .....;
-
创建模块:mybatis-008-dynamic-sql
打包方式:jar
引入依赖:mysql驱动依赖、mybatis依赖、junit依赖、logback依赖
pojo:com.powernode.mybatis.pojo.Car
mapper接口:com.powernode.mybatis.mapper.CarMapper
引入配置文件:mybatis-config.xml、jdbc.properties、logback.xml
mapper配置文件:com/powernode/mybatis/mapper/CarMapper.xml
编写测试类:com.powernode.mybatis.test.CarMapperTest
拷贝工具类:SqlSessionUtil
2. if标签
-
作用:使用 if标签的test属性设置的判断条件,动态拼接SQL 语句。
-
test属性:为true时拼接if标签中的sql语句;为false时不会。
-
test属性中如何设置判断条件:
-
当传入pojo对象时,直接写POJO对象的属性名。
-
当使用了@Param注解,使用这个注解指定的别名,如@Param("brand"),那么这里写brand。
-
当没有使用参数注解时,只能写param1,param2,param3,arg0,arg1,arg2...
-
-
-
在mybatis的动态sql语句中,不能使用&&,只能使用and
需求:进行动态多条件查询。
可能的条件包括:品牌(brand)、指导价格(guide_price)、汽车类型(car_type)
CarMapper.java
package com.powernode.mybatis.mapper;
import com.powernode.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface CarMapper {
/**
* 多条件查询
* @param brand 品牌
* @param guidePrice 指导价
* @param carType 汽车类型
* @return
*/
List<Car> selectByMultiCondition(@Param("brand") String brand, @Param("guidePrice") Double guidePrice,@Param("carType") String carType);
}
CarMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- 语法格式:在sql语句的下面同级使用if双标签,if双标签里面写要动态拼接的SQL语句。-->
<mapper namespace="org.example.mapper.CarMapper">
<select id="selectByMultiCondition" resultType="Car">
select * from t_car where
<if test="brand != null and brand !=''">
brand like "%"#{brand}"%"
</if>
<if test="guidePrice != null and guidePrice != ''">
and guide_price > #{guidePrice}
</if>
<if test="carType != null and carType != ''">
and car_type = #{carType}
</if>
</select>
</mapper>
CarMapperTest.java
package com.powernode.mybatis.test;
import com.powernode.mybatis.mapper.CarMapper;
import com.powernode.mybatis.pojo.Car;
import com.powernode.mybatis.utils.SqlSessionUtil;
import org.junit.Test;
import java.util.List;
public class CarMapperTest {
@Test
public void testselectByMultiCondition(){
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> cars = mapper.selectByMultiCondition("奔驰",55.0,"新能源");
cars.forEach(car -> System.out.println(car));
sqlSession.close();
}
}
执行结果:
如果第一个条件为空,剩下两个条件不为空:会出现SQL语句拼接错误的问题,导致SQL语法错误,where后面出现了第二个条件的and。
修改测试程序为:
CarMapperTest.java
List<Car> cars = mapper.selectByMultiCondition("", 20.0, "燃油车");
执行结果:
这该怎么解决呢?
-
可以在where语句后面添加一个恒成立的条件。
执行结果:
- 如果三个条件都为空:也没有什么影响
CarMapperTest.java
List<Car> cars = mapper.selectByMultiCondition("", null, "");
执行结果:
- 三个条件都不为空呢?
CarMapperTest.java
List<Car> cars = mapper.selectByMultiCondition("丰田", 20.0, "燃油车");
执行结果:
3. where标签
作用:动态生成where子句
-
如果where标签里面所有的if标签都不成立,就不会生成where子句;只要有一个条件成立就会在SQL语句中拼接where关键字。
-
where标签可以自动去除 条件 前面多余的and或or,但不可以自动去除条件后面多余的and
继续使用if标签中的需求:
CarMapper.java
/**
* 根据多条件查询Car,使用where标签
* @param brand
* @param guidePrice
* @param carType
* @return
*/
List<Car> selectByMultiConditionWithWhere(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);
CarMapper.xml
<!-- 语法格式:在sql语句的下面同级使用where双标签 -->
<select id="selectByMultiConditionWithWhere" resultType="car">
select * from t_car
<where>
<if test="brand != null and brand != ''">
and brand like #{brand}"%"
</if>
<if test="guidePrice != null and guidePrice != ''">
and guide_price >= #{guidePrice}
</if>
<if test="carType != null and carType != ''">
and car_type = #{carType}
</if>
</where>
</select>
CarMapperTest.java
@Test
public void testSelectByMultiConditionWithWhere(){
CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);
List<Car> cars = mapper.selectByMultiConditionWithWhere("丰田", 20.0, "燃油车");
System.out.println(cars);
}
运行结果:
如果所有条件都是空:它可以自动去掉前面多余的and
CarMapperTest.java
List<Car> cars = mapper.selectByMultiConditionWithWhere("", null, "");
运行结果:
它可以自动去掉前面多余的and,但不可以自动去掉后面多余的and:
CarMapper.xml
<select id="selectByMultiConditionWithWhere" resultType="car">
select * from t_car
<where>
<if test="brand != null and brand != ''">
brand like #{brand}"%" and
</if>
<if test="guidePrice != null and guidePrice != ''">
guide_price >= #{guidePrice} and
</if>
<if test="carType != null and carType != ''">
car_type = #{carType}
</if>
</where>
</select>
CarMapperTest.java
// 让最后一个条件为空
List<Car> cars = mapper.selectByMultiConditionWithWhere("丰田", 20.0, "");
运行结果:
4. set标签
背景:要使用set标签,因为当提交的数据是空或""时,会把表中记录的对应字段也更新为空。
CarMapper接口
/**
* 根据id更新记录
* @param car
* @return
*/
int updateById(Car car);
CarMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.example.mapper.CarMapper">
<update id="updateById">
update t_car
set
car_num = #{carNum},
brand = #{brand},
guide_price = #{guidePrice},
produce_time = #{produceTime},
car_Type = #{carType}
where
id = #{id}
</update>
</mapper>
@Test
public void testUpdateById(){
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Car car = new Car(18L,null,"丰田霸道",null,null,"燃油车");
mapper.updateById(car);
sqlSession.commit();
sqlSession.close();
}
结果:
-
作用:动态生成UPDATE语句的SET子句。
-
如果set标签内的所有if标签都不成立,不会拼接set关键字,只要有一个条件成立就会在SQL语句中拼接set关键字。
-
会自动剔除sql语句末尾不相关的逗号
-
-
语法格式:sql语句下面同级使用set标签,然后在嵌套if标签来指定动态生成哪些sql语句。
CarMapper接口
/**
* 使用set标签来更新记录
* @param car
* @return
*/
int updateBySet(Car car);
CarMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.example.mapper.CarMapper">
<update id="updateBySet">
update t_car
<set>
<if test="carNum != null and carNum != ''">car_num = #{carNum},</if>
<if test="brand != null and brand != ''">brand = #{brand},</if>
<if test="guidePrice != null and guidePrice != ''">guide_price = #{guidePrice},</if>
<if test="produceTime != null and produceTime != ''">produce_time = #{produceTime},</if>
<if test="carType != null and carType != ''">car_type = #{carType}</if>
</set>
where
id = #{id}
</update>
<mapper/>
CarMapperTest.java
@Test
public void testupdateBySet(){
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Car car = new Car(18L,null,"丰田霸道",null,null,null);
mapper.updateBySet(car);
sqlSession.commit();
sqlSession.close();
}
执行结果:
5. trim标签
语法格式:和SQL语句同级,在其下面写trim标签。
trim标签的属性:
-
prefix:给trim标签中的sql语句前面添加内容,例如prefix="where",给trim标签中所有内容的前面加上where
-
suffix:在trim标签中的sql语句后添加内容
-
prefixOverrides:删除前缀
-
suffixOverrides:删除后缀,例如suffixOverrides="where|or",把trim标签中所有内容的后缀去掉and或or
CarMapper接口
/**
* 根据多条件查询Car,使用trim标签
* @param brand
* @param guidePrice
* @param carType
* @return
*/
List<Car> selectByMultiConditionWithTrim(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);
CarMapper.xml
<select id="selectByMultiConditionWithTrim" resultType="car">
select * from t_car
<trim prefix="where" suffixOverrides="and|or">
<if test="brand != null and brand != ''">
brand like "%"#{brand}"%" and
</if>
<if test="guidePrice != null and guidePrice != ''">
guide_price >= #{guidePrice} and
</if>
<if test="carType != null and carType != ''">
car_type = #{carType}
</if>
</trim>
</select>
CarMapperTest.java
@Test
public void testSelectByMultiConditionWithTrim(){
CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);
List<Car> cars = mapper.selectByMultiConditionWithTrim("丰田", 20.0, "");
System.out.println(cars);
}
当所有条件为空,where就不会被加上:
CarMapperTest.java
List<Car> cars = mapper.selectByMultiConditionWithTrim("", null, "");
运行结果:
6. foreach标签
-
作用:当传入数组或集合到Mapper文件中,使用foreach标签,遍历数组或集合中的每个元素,循环生成sql语句,实现动态生成sql。
-
foreach标签主要用于批量删除和批量插入。
-
foreach标签中有如下属性:
-
collection属性:要遍历的数组或集合名(指定mapper方法的数组或集合名)
-
item属性:用来代表数组或集合中的每个元素
-
separator属性:循环遍历的每对象间用什么分隔。
-
open属性:开始遍历前以什么开始
-
close属性:遍历完后以什么结束
-
应用:批量删除
先来看看以前如何批量删除数据库表中的记录。
delete from t_car where id in(1,2,3);
delete from t_car where id = 1 or id = 2 or id = 3;
下面使用foreach标签来批量删除。
- 用in集合运算符来删除
CarMapper接口
/**
* 批量删除,使用foreach标签
* @param ids
* @return
*/
int deleteByIds(@Param("ids") Long[] ids);
CarMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.example.mapper.CarMapper">
<delete id="deleteByIds">
<!-- delete from t_car where id in(1,2,3); -->
delete from t_car where id in(
<foreach collection="ids" item="id" separator=",">
#{id}
</foreach>
)
</delete>
</mapper>
CarMapperTest.java
@Test
public void testDeleteByIds(){
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Long[] ids = {13L,14L,15L};
mapper.deleteByIds(ids);
sqlSession.commit();
sqlSession.close();
}
运行结果:
-
用or来删除
CarMapper接口
/**
* 通过foreach完成批量删除
* @param ids
* @return
*/
int deleteBatchByForeach2(@Param("ids") Long[] ids);
CarMapper.xml
<delete id="deleteBatchByForeach2">
<!-- delete from t_car where id = 1 or id = 2 or id = 3; -->
delete from t_car where
<foreach collection="ids" item="id" separator="or">
id = #{id}
</foreach>
</delete>
CarMapperTest.java
@Test
public void testDeleteBatchByForeach2(){
CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);
int count = mapper.deleteBatchByForeach2(new Long[]{40L, 41L, 42L});
System.out.println("删除了几条记录:" + count);
SqlSessionUtil.openSession().commit();
}
运行结果:
应用:批量添加
先来看看以前如何批量添加记录到数据库表中。
insert into t_car values
(null,'1001','凯美瑞',35.0,'2010-10-11','燃油车'),
(null,'1002','比亚迪唐',31.0,'2020-11-11','新能源'),
(null,'1003','比亚迪宋',32.0,'2020-10-11','新能源')
下面使用foreach标签来批量添加。
CarMapper接口
/**
* 批量插入,一次多次插入多条car记录
* @param cars
* @return
*/
int insertBatch(@Param("cars")List<Car> cars);
CarMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.example.mapper.CarMapper">
<insert id="insertBatch">
insert into t_car values
<foreach collection="cars" item="car" separator=",">
(null,#{car.carNum},#{car.brand},#{car.guidePrice},#{car.produceTime},#{car.carType})
</foreach>
</insert>
<mapper/>
CarMapperTest.java
@Test
public void testInsertBatch(){
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Car car1 = new Car(null,"1200","帕萨特1",30.0,"2002-06-99","燃油车");
Car car2 = new Car(null,"1200","帕萨特2",30.0,"2002-06-99","燃油车");
Car car3 = new Car(null,"1200","帕萨特3",30.0,"2002-06-99","燃油车");
List<Car> cars = new ArrayList<>();
cars.add(car1);
cars.add(car2);
cars.add(car3);
mapper.insertBatch(cars);
sqlSession.commit();
sqlSession.close();
}
执行结果:
7. choose标签
choose标签没什么用,这里不述说。主要掌握if、where、foreach、set标签即可,trim标签用的也少。
8、SQL片段
把在sql中频繁使用的代码抽取出来,组成一个sql片段,然后在使用的地方包含进入即可。
sql片段里面不要包含where标签。
-
sql标签:用来定义sql片段
-
include标签:用来引用sql片段
-
作用:使代码能够复用,易维护。
<sql id="feildSql">
empno,ename,job,mgr,hiredate,sal,comm,deptno
</sql>
<sql id="whereSql">
<if test="ename != null and ename != ''">
ename like concat('%',#{ename},'%')
</if>
<if test="sal != null">
and sal=#{sal}
</if>
<if test="deptno != null">
and deptno=#{deptno}
</if>
</sql>
<select id="selectUseSql" parameterType="com.gs.entity.Emp" resultType="com.gs.entity.Emp">
select
<include refid="feildSql"></include>
from emp
<where>
<include refid="whereSql"></include>
</where>
</select>