动态SQL是MyBatis中的一个非常重要的特性,它可以帮助生成灵活的SQL语句,以应对不同的查询需求。以下是动态SQL的一些知识点详细介绍:
1. 什么是动态SQL
动态SQL指的是根据不同的输入参数或条件,动态生成SQL语句的能力。MyBatis 提供了一些标签,用于在映射文件中编写动态 SQL 语句,以满足灵活的查询、插入、更新、删除等需求。
2. 动态SQL标签
MyBatis 提供了以下常用的动态SQL标签:
1. <if>
标签:根据条件来决定是否包含某段 SQL。
/*** 根据多条件查询Car* @param brand* @param guidePrice* @param carType* @return*/List<Car> selectByMultiCondition(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);
<!--#{}里面写的是对应类名的变量名-->
<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>
@Testpublic void testSelectByMultiCondition(){CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);List<Car> cars = mapper.selectByMultiCondition("丰田", 20.0, "燃油车");System.out.println(cars);}
使用的注意事项
-
参数与占位符的匹配:
#{}
里面应该填的是@Param
注解中重命名后的参数名。如果是简单类型参数(如String
、Integer
等),则直接使用参数名。- 如果参数是一个对象(如
Car
对象),那么应该使用对象的属性名,例如#{car.brand}
。
-
条件拼接问题:
- 在
<if>
标签中,拼接的条件可能存在语法错误,特别是在多个条件之间需要正确处理AND
或者OR
。 - 如果第一个
<if>
中的条件不成立,而第二个或第三个条件成立,那么生成的 SQL 可能会有多余的AND
,需要小心处理。
- 在
- 例如
- 如果所有条件(
brand
、guidePrice
、carType
)都不满足,则where 1=1
依然存在,这虽然能避免空WHERE
的问题,但1=1
是多余的,且在有大量条件时会显得非常繁琐。 - 如果不使用
where 1=1
,而是直接在<if>
标签中加上AND
,则如果第一个条件不成立可能会生成以下错误 SQL:select * from t_car where AND ...(错误)
- 如果所有条件(
解决方案
为了避免上述错误,可以使用 MyBatis 提供的 <where>
或 <trim>
标签来生成动态 SQL:
<where>
标签:自动处理WHERE
和条件之间的AND
或OR
,避免生成空WHERE
子句或多余的逻辑运算符。<trim>
标签:可以手动设置前缀(如WHERE
),并去除前缀中多余的逻辑运算符
2. <choose>
、<when>
和 <otherwise>
标签:
类似于 switch-case
,用于多条件选择。
/**
* 使用choose when otherwise标签查询
* @param brand
* @param guidePrice
* @param produceTime
* @return
*/
List<Car> selectWithChoose(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("produceTime") String produceTime);
<select id="selectWithChoose" resultType="car">select * from t_car<where><choose><when test="brand != null and brand != ''">brand like #{brand}"%"</when><when test="guidePrice != null and guidePrice != ''">guide_price >= #{guidePrice}</when><otherwise>produce_time >= #{produceTime}</otherwise></choose></where>
</select>
只有一个分支会被选择!!!!如果所有 <when>
标签的条件都不成立,MyBatis 会自动执行 <otherwise>
标签中的逻辑,这就相当于 Java 中的 default
部分。
3. <where>
标签:
用于拼接 WHERE
子句,自动处理条件的前置逻辑,如 AND
或 OR
。
-
所有条件都为空时,where标签保证不会生成where子句。
-
自动去除某些条件前面多余的and或or。注意后面的and/or不可以自动去除
<select id="findUserByParams" parameterType="map" resultType="User">SELECT * FROM user<where><if test="username != null">username = #{username}</if><if test="age != null">AND age = #{age}</if></where>
</select>
<where>
标签可以确保生成的 SQL 以 WHERE
开头,并且会自动处理冗余的 AND
。
4. <trim>
标签:
可以在 SQL 片段前后加上或去除指定的字符,比如加上 WHERE
或去除多余的 AND
。
trim标签的属性:
-
prefix:在trim标签中的语句前添加内容
-
suffix:在trim标签中的语句后添加内容
-
prefixOverrides:前缀覆盖掉(去掉)
-
suffixOverrides:后缀覆盖掉(去掉)
/**
* 根据多条件查询Car,使用trim标签
* @param brand
* @param guidePrice
* @param carType
* @return
*/
List<Car> selectByMultiConditionWithTrim(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);
<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>
如果字段都为空 ,那么最终的 SQL 语句是:
SELECT * FROM t_car
这意味着没有任何过滤条件,结果是查询 t_car
表中的所有数据。
5.<set>
标签:
确保生成的 SQL 中,不会因为字段之间的逗号导致语法错误。
主要使用在update语句当中,用来生成set关键字,同时去掉最后多余的“,” 比如我们只更新提交的不为空的字段,如果提交的数据是空或者"",那么这个字段我们将不更新。
/**
* 更新信息,使用set标签
* @param car
* @return
*/
int updateWithSet(Car car);
<update id="updateWithSet">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>
6. foreach标签
在 MyBatis 中,<foreach>
标签用于动态地遍历集合,例如数组、List
、Set
等,以便生成动态 SQL。<foreach>
通常用于批量插入、更新或查询操作,是构建复杂 SQL 的强大工具。以下是关于 <foreach>
标签的详细介绍:
1. 基本语法和属性
<foreach>
标签的典型结构如下:
<foreach collection="集合" item="变量名" index="索引名" open="开始字符" close="结束字符" separator="分隔符">SQL 片段
</foreach>
collection
:指定要遍历的集合。集合可以是数组、List
、Set
、Map
等,支持传递的参数名。item
:表示每次遍历的当前元素。index
(可选):表示当前遍历的索引(对于List
、数组等来说是下标,对于Map
是键)。open
(可选):遍历生成的 SQL 片段的开头部分。close
(可选):遍历生成的 SQL 片段的结尾部分。separator
(可选):遍历元素之间的分隔符。
2. 示例用法
2.1 批量插入
假设我们有一个 car
表,我们想要一次性插入多条记录:
public interface CarMapper {// 批量插入汽车信息的方法int insertCars(@Param("carList") List<Car> carList);
}
<insert id="insertCars">INSERT INTO t_car (car_num, brand, guide_price, produce_time, car_type)VALUES<foreach collection="carList" item="car" separator=",">(#{car.carNum}, #{car.brand}, #{car.guidePrice}, #{car.produceTime}, #{car.carType})</foreach>
</insert>
解释:
collection="carList"
:表示要遍历的集合名为carList
。item="car"
:每次遍历的元素存储在变量car
中。separator=","
:每个VALUES
片段之间使用逗号分隔。
假设 carList
中有三条记录,那么生成的 SQL 将类似于:
INSERT INTO t_car (car_num, brand, guide_price, produce_time, car_type)
VALUES
('C001', 'Toyota', 15000, '2023-01-01', 'SUV'),
('C002', 'Honda', 12000, '2022-12-01', 'Sedan'),
('C003', 'Ford', 18000, '2023-02-01', 'Truck')
2.2 批量更新
假设要批量更新汽车的价格,我们可以这样做:
<update id="updateCarPrices"><foreach collection="carList" item="car" separator=";">UPDATE t_carSET guide_price = #{car.guidePrice}WHERE car_num = #{car.carNum}</foreach>
</update>
解释:
- 这里的
<foreach>
标签用于批量更新每个汽车的价格。 separator=";"
:生成的多个UPDATE
语句之间用分号分隔。
生成的 SQL 将类似于:
UPDATE t_car SET guide_price = 15000 WHERE car_num = 'C001';
UPDATE t_car SET guide_price = 12000 WHERE car_num = 'C002';
UPDATE t_car SET guide_price = 18000 WHERE car_num = 'C003';
2.3 使用 IN
语句的查询
批量查询某些条件的记录,可以使用 IN
语句配合 <foreach>
标签:
<select id="selectCarsByTypes" resultType="car">SELECT * FROM t_carWHERE car_type IN<foreach collection="carTypes" item="type" open="(" separator="," close=")">#{type}</foreach>
</select>
解释:
collection="carTypes"
:表示要遍历的集合名为carTypes
。item="type"
:每次遍历的当前元素是type
。open="("
和close=")"
:在生成的 SQL 片段两侧添加括号。separator=","
:每个元素之间用逗号分隔。
假设 carTypes
是 ["SUV", "Sedan", "Truck"]
,那么生成的 SQL 将类似于:
SELECT * FROM t_car WHERE car_type IN ('SUV', 'Sedan', 'Truck')
2.4 用in来删除
/**
* 通过foreach完成批量删除
* @param ids
* @return
*/
int deleteBatchByForeach(@Param("ids") Long[] ids);
<!--
collection:集合或数组
item:集合或数组中的元素
separator:分隔符
open:foreach标签中所有内容的开始
close:foreach标签中所有内容的结束
-->
<delete id="deleteBatchByForeach">delete from t_car where id in<foreach collection="ids" item="id" separator="," open="(" close=")">#{id}</foreach>
</delete>
@Test
public void testDeleteBatchByForeach(){CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);int count = mapper.deleteBatchByForeach(new Long[]{40L, 41L, 42L});System.out.println("删除了几条记录:" + count);SqlSessionUtil.openSession().commit();
}
2.5 用or来删除
/**
* 通过foreach完成批量删除
* @param ids
* @return
*/
int deleteBatchByForeach2(@Param("ids") Long[] ids);
<delete id="deleteBatchByForeach2">delete from t_car where<foreach collection="ids" item="id" separator="or">id = #{id}</foreach>
</delete>