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标签
-
作用：动态拼接SQL 语句，使用 if标签的test属性设置的判断条件。
-
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>
扫一扫
1075
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
