目录
一:动态SQL
1.if标签
2.where标签
3.trim标签
4.set标签
5.choose when otherwise
6.foreach标签
7.sql标签与include标签(了解)
一:动态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 .....;
修改
需求:多条件查询
可能的条件包括:品牌(brand)、辅导价格(guide_price)、汽⻋类型(car_type)等
1.if标签
三兄弟之一:CarMapper接口,编写办法
参数有多个,运用@Param注解进行变量的界说,增强可读性!
package com.bjpowernode.mybatis.mapper;
import com.bjpowernode.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface CarMapper {
// 多条件查询,运用if
List<Car> selectByMultiCondition(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);
}
三兄弟之二:CarMapper.xml文件,编写sql句子
运用if标签,当条件满意就会把SQL句子拼接上去:
①if标签中test特点是有必要的,test特点一般是一个表达式,对应的值是true或许false。
②test特点能够运用的是:
榜首:当运用了@Param注解,test中要呈现的有必要是@Param注解指定的参数名;
第二:当没有运用@Param注解,test中呈现的是:arg0、arg1或许param1、param2….
第三:当运用了POJO,test中呈现的是POJO类的特点;
③在MyBatis的动态SQL傍边,假如多个条件一起满意,不能运用&&,只能运用and。
<?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="com.bjpowernode.mybatis.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>
三兄弟之三:CarMappeTest类,用来编写测验类
①假定三个参数都不为空,查询的成果彻底没问题!
②假定三个参数都为空,就会呈现问题,本来的SQL句子就变成:select * from t_car where,这是不符合语法的!怎么办?在where后边加上一个恒建立的条件 1=1
③假定榜首个条件不为空,后两个两条都为空,那便是把榜首条SQL句子直接拼上去,也会呈现问题:select * from t_car where 1==1 brand like “%”?”%”,两个条件中间需求一个and作为连接,所以榜首条句子前面也要加上and
package com.bjpowernode.mybatis.test;
import com.bjpowernode.mybatis.mapper.CarMapper;
import com.bjpowernode.mybatis.pojo.Car;
import com.bjpowernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
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("比亚迪",2.0,"新能源");
// 假定三个条件都为空
List<Car> cars = mapper.selectByMultiCondition("",null,"");
// 假定榜首个不为空,后两个为空
List<Car> cars = mapper.selectByMultiCondition("比亚迪",null,"");
cars.forEach(car -> System.out.println(car));
sqlSession.close();
}
}
所以终究的SQL句子修改为:增加1=1 和 and条件
<?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="com.bjpowernode.mybatis.mapper.CarMapper">
<select id="selectByMultiCondition" resultType="Car">
select * from t_car where 1=1
<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>
</select>
</mapper>
2.where标签
where标签的作⽤:让where⼦句更加动态智能
①一切条件都为空时,where标签保证不会⽣成where⼦句。
②⾃动去除某些条件前⾯剩余的and或or。
三兄弟之一:CarMapper接口,编写办法
整个办法的结构不变,就改了一下办法名
package com.bjpowernode.mybatis.mapper;
import com.bjpowernode.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface CarMapper {
// 运用where标签,让where子句更加的智能
List<Car> selectByMultiConditionWithWhere(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);
}
三兄弟之二:CarMapper.xml文件,编写sql句子
运用where标签专门处理where子句,所以咱们就能够依照本来的思路进行SQL句子的编写
注:假如把and写在句子后边,不能去除去,只能去除去前面剩余的and,例如:brand like “%”#{brand}”%” and
<?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="com.bjpowernode.mybatis.mapper.CarMapper">
<select id="selectByMultiConditionWithWhere" 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>
</mapper>
三兄弟之三:CarMappeTest类,用来编写测验类
①假定三个都为空,where标签就不会生成where子句;
②假定榜首个为空,后边两个都不为空,那么第二个带and的子句就会拼接上去,where标签会主动剔除去剩余的and;
package com.bjpowernode.mybatis.test;
import com.bjpowernode.mybatis.mapper.CarMapper;
import com.bjpowernode.mybatis.pojo.Car;
import com.bjpowernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class CarMapperTest {
@Test
public void testSelectByMultiConditionWithWhere(){
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
// 三个都为空
List<Car> cars = mapper.selectByMultiCondition("",null,"");
// 假定榜首个为空,后两个不为空
List<Car> cars = mapper.selectByMultiCondition("",2.0,"新能源");
cars.forEach(car -> System.out.println(car));
sqlSession.close();
}
}
3.trim标签
trim标签的特点:
①prefix:在trim标签中的句子前增加内容(加前缀)
②suffix:在trim标签中的句子后增加内容 (加后缀)
③prefixOverrides:前缀覆盖掉(去掉前缀)
④suffixOverrides:后缀覆盖掉(去掉后缀)
三兄弟之一:CarMapper接口,编写办法
package com.bjpowernode.mybatis.mapper;
import com.bjpowernode.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface CarMapper {
// 运用trim标签
List<Car> selectByMultiConditionWithTrim(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);
}
三兄弟之二:CarMapper.xml文件,编写sql句子
不运用where标签,运用prefix和suffixOverrides特点来完结查询操作,而且咱们把and放到后边,也相同能完结操作,例如:
①prefix=”where”,表明在trim标签一切内容的前面增加where
②suffixOverrides=”and|or”,表明把trim标签中内容的后缀and或or去掉
<?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="com.bjpowernode.mybatis.mapper.CarMapper">
<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>
</mapper>
三兄弟之三:CarMappeTest类,用来编写测验类
①三个都为空,也是彻底没有问题的,前缀增加where也不是随便就加的,需求先判别子句有没有,有子句才会加where
②榜首个不为空,其他都为空,这样where子句后缀就多了一个and,前面咱们界说的是suffixOverrides=”and|or”,把trim标签中内容的后缀and或or去掉,也是彻底没问题的
package com.bjpowernode.mybatis.test;
import com.bjpowernode.mybatis.mapper.CarMapper;
import com.bjpowernode.mybatis.pojo.Car;
import com.bjpowernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class CarMapperTest {
@Test
public void testSelectByMultiConditionWithTrim(){
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
// 三个都不为空
List<Car> cars = mapper.selectByMultiConditionWithTrim("", null, "");
// 榜首个不为空,其它都为空
List<Car> cars = mapper.selectByMultiConditionWithTrim("比亚迪", null, "");
cars.forEach(car -> System.out.println(car));
sqlSession.close();
}
}
4.set标签
主要使⽤在update句子傍边,⽤来⽣成set关键字,一起去掉最终剩余的“,”
⽐如:咱们只更新提交的不为空的字段,假如提交的数据是空或许””,那么这个字段咱们将不更新。
(1)测验运用开端的方法进行更新
三兄弟之一:CarMapper接口,编写办法
package com.bjpowernode.mybatis.mapper;
import com.bjpowernode.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface CarMapper {
// 经过id进行更新
int updateById(Car car);
}
三兄弟之二:CarMapper.xml文件,编写sql句子
<?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="com.bjpowernode.mybatis.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>
三兄弟之三:CarMappeTest类,用来编写测验类
package com.bjpowernode.mybatis.test;
import com.bjpowernode.mybatis.mapper.CarMapper;
import com.bjpowernode.mybatis.pojo.Car;
import com.bjpowernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class CarMapperTest {
@Test
public void testUpdateById(){
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Car car = new Car(1L, null, null, null, null, "新能源");
int count = mapper.updateById(car);
System.out.println(count);
sqlSession.commit();
sqlSession.close();
}
}
问题:本来现已存在的数据,可是咱们更新时并没有传数据进去,它会把本来现已存的数据更新为空!
修改
(2)运用set标签进行更新
三兄弟之一:CarMapper接口,编写办法
package com.bjpowernode.mybatis.mapper;
import com.bjpowernode.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface CarMapper {
// 经过set标签进行更新
int updateBySet(Car car);
}
三兄弟之二:CarMapper.xml文件,编写sql句子
这儿的set标签实践上有两个功用:
①完结数据的更新,关于没有赋值的字段不会更新,只更新提交数据不为空的字段
②删去剩余的逗号,比如:最终一个字段car_type为空,这样就会剩余一个逗号出来,set标签能够去除去这个剩余的逗号
<?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="com.bjpowernode.mybatis.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>
三兄弟之三:CarMappeTest类,用来编写测验类
package com.bjpowernode.mybatis.test;
import com.bjpowernode.mybatis.mapper.CarMapper;
import com.bjpowernode.mybatis.pojo.Car;
import com.bjpowernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class CarMapperTest {
@Test
public void testUpdateBySet(){
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Car car = new Car(2L, null, "奔驰C200", null, null, "新能源");
int count = mapper.updateBySet(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{
}
只需⼀个分⽀会被挑选!
需求:先依据品牌查询,假如没有供给品牌,再依据辅导价格查询,假如没有供给辅导价格,就依据⽣产⽇期查询。
三兄弟之一:CarMapper接口,编写办法
package com.bjpowernode.mybatis.mapper;
import com.bjpowernode.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface CarMapper {
// 依据chose when otherwise
List<Car> selectByChoose(@Param("brand") String brand, @Param("guidePrice") Double guidePrice, @Param("carType") String carType);
}
三兄弟之二:CarMapper.xml文件,编写sql句子
三个条件:实践上是三选一,只需有其中一条执行了,其它分支就不会执行;所以都不需求加and
<?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="com.bjpowernode.mybatis.mapper.CarMapper">
<select id="selectByChoose" resultType="Car">
select * form 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>
car_type = #{carType}
</otherwise>
</choose>
</where>
</select>
</mapper>
三兄弟之三:CarMappeTest类,用来编写测验类
①当三个条件都不为空,就依照榜首个条件进行查询;当时面的条件为空,就顺次依照后边的次序进行查询
②特殊情况的三个条件都为空,实践上走的是最终otherwise标签的查询句子,只不过是把null传过去,查询不到数据罢了
package com.bjpowernode.mybatis.test;
import com.bjpowernode.mybatis.mapper.CarMapper;
import com.bjpowernode.mybatis.pojo.Car;
import com.bjpowernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class CarMapperTest {
@Test
public void testSelectByChoose(){
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
// 都不为空,依照榜首个字段进行查询
List<Car> cars = mapper.selectByChoose("比亚迪", 2.0, "新能源");
// 都为空,依照最终一个字段进行查询,把null传过去
List<Car> cars = mapper.selectByChoose("", null, "");
cars.forEach(car -> System.out.println(car));
sqlSession.close();
}
}
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','新能源')
(1) 批量删去
三兄弟之一:CarMapper接口,编写办法
办法的参数是一个数组,这是榜首次接触,要点掌握!
package com.bjpowernode.mybatis.mapper;
import com.bjpowernode.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface CarMapper {
// 批量删去,foreach标签
int deleteByIds(@Param("ids") Long[] ids);
}
三兄弟之二:CarMapper.xml文件,编写sql句子
(1)foreach标签的特点:
①collection: 指定数组或调集
②item: 代表数组或调集中的元素,其实便是变量
③separater: 循环之间的分隔符,肯定是逗号,由于SQL句子是这样的方式:delete from t_car where id in(1,2,3);
④open: foreach循环拼接的一切SQL句子的最前面以什么开端
⑤close: foreach循环拼接的一切SQL句子的最终边以什么完毕
(2)collection参数怎么传一个数组或许调集?直接把ids拿过来?这样会报错,错误信息是:【array,arg0】阐明底层创建了一个Map调集存储数据,例如:map.put(“array”,数组)或许map.put(“arg0”,数组);所以默许参数要运用array或许arg0;可是为了可读性,上面运用了@Param注解,就能够直接运用ids了
(3)榜首、二种办法运用的是in的方法,分隔符用的是” 逗号, “;第三种办法运用or的方法,分隔符实践上便是or
<?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="com.bjpowernode.mybatis.mapper.CarMapper">
<delete id="deleteByIds">
<!--榜首种办法-->
delete from t_car where id in(
<foreach collection="ids" item="id" separator=",">
#{id}
</foreach>
)
<!--第二种办法-->
delete from t_car where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
<!--第三种办法:运用or的方式-->
delete from t_car where
<foreach collection="ids" item="id" separator="or">
id=#{id}
</foreach>
</delete>
</mapper>
三兄弟之三:CarMappeTest类,用来编写测验类
预备一个对应的数组传过去,数组中的数据是咱们要删去元素对应的id
package com.bjpowernode.mybatis.test;
import com.bjpowernode.mybatis.mapper.CarMapper;
import com.bjpowernode.mybatis.pojo.Car;
import com.bjpowernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class CarMapperTest {
@Test
public void testDeleteByIds(){
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
// 预备一个数组
Long[] ids = {1L,2L,4L};
int count = mapper.deleteByIds(ids);
System.out.println(count);
sqlSession.commit();
sqlSession.close();
}
}
(2)批量刺进
三兄弟之一:CarMapper接口,编写办法
批量刺进:参数不在是一个数组,而是一个List调集,而且运用@Param注解起别名。
package com.bjpowernode.mybatis.mapper;
import com.bjpowernode.mybatis.pojo.Car;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface CarMapper {
// 批量刺进,一次刺进多条Car信息,foreach标签
int insertBatch(@Param("cars") List<Car> cars);
}
三兄弟之二:CarMapper.xml文件,编写sql句子
①这儿最主要的便是如何传参?咱们运用的是调集里面的元素Car,所以参数便是Car对应的特点名,直接“点”就能够了。
②相同这儿调集中的元素也是经过逗号的方式作为分隔符,所以separator的参数也是” , “
<?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="com.bjpowernode.mybatis.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>
三兄弟之三:CarMappeTest类,用来编写测验类
预备Car目标的数据,然后把这些Car目标的数据增加到List调集傍边,最终把这个List调集作为参数传到办法傍边
package com.bjpowernode.mybatis.test;
import com.bjpowernode.mybatis.mapper.CarMapper;
import com.bjpowernode.mybatis.pojo.Car;
import com.bjpowernode.mybatis.utils.SqlSessionUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class CarMapperTest {
@Test
public void testInsertBatch(){
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
// 预备数据
Car car1 = new Car(null,"1200", "帕萨特1", 30.0, "2020-11-11", "燃油车");
Car car2 = new Car(null,"1201", "帕萨特2", 30.0, "2020-11-11", "燃油车");
Car car3 = new Car(null,"1202", "帕萨特3", 30.0, "2020-11-11", "燃油车");
// 把数据增加到List调集傍边
List<Car> cars = new ArrayList<>();
cars.add(car1);
cars.add(car2);
cars.add(car3);
// 把调集传过去
int count = mapper.insertBatch(cars);
System.out.println(count);
sqlSession.commit();
sqlSession.close();
}
}
7.sql标签与include标签(了解)
(1)sql标签:⽤来声明sql⽚段,有id特点作为唯一标识。
(2)include标签:调用refid特点(传sql标签的id),将声明的sql⽚段包含到某个sql句子傍边
作⽤:代码复⽤,易保护。
<!--重复的代码抽取出来-->
<sql id="carColumnNameSql">
id,
car_num as carNum,
brand,
guide_price as guidePrice,
produce_time as produceTime,
car_type as carType
</sql>
<!--代码复用-->
<select id="selectAll" resultType="Car">
select
<include refid="carColumnNameSql"/>
from t_car;
</select>