动态sql
- 1、if标签
- 2、where标签
- 3、trim标签
- 4、set标签
- 5、choose when otherwise
- 6 、foreach
- 6.1 用in来删除
- 6.2 用or来删除
- 6.3 批量添加
- 7、 sql标签与include标签
1、if标签
需求:多条件查询。
可能的条件包括:品牌(brand)、指导价格(guide_price)、汽车类型(car_type)
List<Car> selectCar(@Param("brand") String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
<select id="selectCar" 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>
@Test
public void testmanyParams(){
SqlSession sqlSession = MybatisUtils.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> cars = mapper.selectCar("丰田", 1.0, "氢能源");
cars.forEach(car -> System.out.println(car));
sqlSession.close();
}
但是如果第一个为空,第二三个不为空呢?
我们可以看到 出现了错误,即前边多了一个and。
此时就需要有where 标签
2、where标签
where标签的作用:让where子句更加动态智能。
● 所有条件都为空时,where标签保证不会生成where子句。
● 自动去除某些条件前面多余的and或or。
继续使用if标签中的需求。
<select id="selectCar" 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>
</where>
</select>
如果让第一个为空,此时不会出现错误,会自动帮我们去掉多余的and。
如果and 在后边,则无法去掉.只能去掉前边的and或者or
<select id="selectCar" 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>
3、trim标签
可以自动增加前缀,后缀,删除前缀,删除后缀。
举例
会自动帮助我们增加前缀 where 删除多余的后缀and
<select id="selectCar" resultType="car">
select * from t_car
<trim prefix="where" suffixOverrides="and">
<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>
@Test
public void testmanyParams(){
SqlSession sqlSession = MybatisUtils.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> cars = mapper.selectCar("丰田", null, "");
cars.forEach(car -> System.out.println(car));
sqlSession.close();
}
4、set标签
主要使用在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>
@Test
public void testUpdateWithSet(){
SqlSession sqlSession = MybatisUtils.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Car car = new Car(8,"1001","丰田霸道2",10.0,"",null);
int count = mapper.updateWithSet(car);
System.out.println(count);
sqlSession.commit();
sqlSession.close();
}
5、choose when otherwise
这三个标签是在一起使用的:
<choose>
<when></when>
<when></when>
<when></when>
<otherwise></otherwise>
</choose>
等同于
if(){
}else if(){
}else if(){
}else if(){
}else{
}
只有一个分支会被选择!!!!
需求:先根据品牌查询,如果没有提供品牌,再根据指导价格查询,如果没有提供指导价格,就根据生产日期查询。
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>
@Test
public void testSelectWithChoose(){
CarMapper mapper = SqlSessionUtil.openSession().getMapper(CarMapper.class);
//List<Car> cars = mapper.selectWithChoose("丰田霸道", 20.0, "2000-10-10");
//List<Car> cars = mapper.selectWithChoose("", 20.0, "2000-10-10");
//List<Car> cars = mapper.selectWithChoose("", null, "2000-10-10");
List<Car> cars = mapper.selectWithChoose("", null, "");
System.out.println(cars);
}
只会执行一个分支!!一定会执行一个,如果前几个都没有对应上,就一定会执行最后一个。
6 、foreach
循环数组或集合,动态生成sql,比如这样的SQL:
批量删除
delete from t_car where id in(1,2,3);
delete from t_car where id = 1 or id = 2 or id = 3;
批量添加
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','新能源')
6.1 用in来删除
int deleteBatchByForeach(@Param("ids") Integer[] ids);
<delete id="deleteBatchByForeach">
delete from t_car where id in
(
<foreach collection="ids" item="id" separator=",">#{id}
</foreach
>)
</delete>
也可以
<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(){
SqlSession sqlSession = MybatisUtils.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
int count = mapper.deleteBatchByForeach(new Integer[]{2, 3, 4});
System.out.println("删除了几条记录:" + count);
sqlSession.commit();
}
6.2 用or来删除
<delete id="deleteBatchByForeach">
delete from t_car where
<foreach collection="ids" item="id" separator="or">
id=#{id}
</foreach
>
</delete>
@Test
public void testDeleteBatchByForeach(){
SqlSession sqlSession = MybatisUtils.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
int count = mapper.deleteBatchByForeach(new Integer[]{2, 3, 4});
System.out.println("删除了几条记录:" + count);
sqlSession.commit();
}
6.3 批量添加
int insertBatchByForeach(@Param("cars") List<Car> cars);
<insert id="insertBatchByForeach">
insert into t_car values
<foreach collection="cars" item="car" separator=",">
(null,#{car.carNum},#{car.brand},#{car.guidePrice},#{car.produceTime},#{car.carType})
</foreach>
</insert>
@Test
public void testInsertBatchByForeach(){
SqlSession sqlSession = MybatisUtils.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Car car1 = new Car(null, "2001", "兰博基尼", 100.0, "1998-10-11", "燃油车");
Car car2 = new Car(null, "2001", "兰博基尼", 100.0, "1998-10-11", "燃油车");
Car car3 = new Car(null, "2001", "兰博基尼", 100.0, "1998-10-11", "燃油车");
List<Car> cars = Arrays.asList(car1, car2, car3);
int count = mapper.insertBatchByForeach(cars);
System.out.println("插入了" + count+"条记录");
sqlSession.commit();
sqlSession.close();
}
7、 sql标签与include标签
sql标签用来声明sql片段
include标签用来将声明的sql片段包含到某个sql语句当中
作用:代码复用。易维护
<sql id="carCols">id,car_num carNum,brand,guide_price guidePrice,produce_time produceTime,car_type carType</sql>
<select id="selectAllRetMap" resultType="map">
select <include refid="carCols"/> from t_car
</select>
<select id="selectAllRetListMap" resultType="map">
select <include refid="carCols"/> carType from t_car
</select>
<select id="selectByIdRetMap" resultType="map">
select <include refid="carCols"/> from t_car where id = #{id}
</select>