if标签
1、if标签中的test属性是必须的
2、if标签中test属性的值是false或者true
3、如果test是true,则if标签中的sql语句就会拼接,反之,则不会拼接
4、test属性中可以使用的是:
当使用了@Param注解,那么test中要出现的是@Param注解指定的参数名,@Param(“brand”),那么这里只能使用brand
当没有使用@Param注解,那么test中要出现的是:param1,param2,arg0,arg1…
当使用了POJO,那么test中出现的是POJO类的属性名。
5、在mybatis的动态SQL当中,不能使用&&,只能使用and。
//CarMapper.java
List<Car> selectByMultiCondition(@Param("brand")String brand,@Param("guidePrice") Double guidePrice,@Param("carType") String carType);
<!-- CarMapper.xml -->
<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>
//CarMapperTest.java
@Test
public void testSelectById(){
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> cars = mapper.selectByMultiCondition("比亚迪",2.0,"新能源");
cars.forEach(car -> System.out.println(car));
sqlSession.close();
}
↑但是上面写的SQL语句明显存在着逻辑的问题,如果第一个判断语句为false,里面的SQL语句没有执行而后面的加上了,就存在SQL语句的语法错误
所以改为下面这种语句
<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>
where标签
where标签的作用:让where子句更加多态智能
- 所有条件都为空时,where标签保证不会生成where子句。
- 自动去除某些条件前面多余的and或or
wher标签是专门负责where子句动态生成的
<select id="selectByMultiConditionWhere" 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>
trim标签
trim标签的属性
- prefix:在trim标签中的语句前添加内容
- suffix:在trim标签中的语句后添加内容
- prefixOverrides:前缀覆盖掉(去掉)
- suffixOverrides:后缀覆盖掉(去掉)
<select id="selectByMultiConditionTrim" 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>
prefix="where"
是在trim标签所有内容的前面添加wheresuffixOverrides="and|or"
把trim标签中内容的后缀and或or去掉
set标签
主要使用在update语句当中,用来生成set关键字,同时去掉最后多余的“,”
比如我们只更新提交的不为空的字段,如果提交的数据是空,或者“ ”,那么这个字段我们将不更新
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 testBySet(){
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Car car = new Car(5L,null,null,"丰田sucker",null,null);
mapper.updateWithSet(car);
sqlSession.commit();
sqlSession.close();
}
choose when otherwise
这三个标签是在一起使用的
相当于if、elseif、else
只有一个分支会被选择
List<Car> selectByChoose(@Param("brand")String brand,@Param("guidePrice")Double guidePrice,@Param("carType")String carType);
<select id="selectByChoose" 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>
car_type = #{carType}
</otherwise>
</choose>
</where>
</select>
@Test
public void testchoose(){
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
List<Car> cars = mapper.selectByChoose("", 5.0,null);
cars.forEach(car -> System.out.println(car));
sqlSession.close();
}
批量删除
int deleteByIds(String ids[]);
foreach标签的属性:
- collection:指定数组或者集合
- item:代表数组或集合中的元素
- separator:循环之间的分隔符
<delete id="deleteByIds">
delete from t_car where id in(
<foreach collection="ids" item="baga" separator=",">
#{baga}
</foreach>
)
</delete>
@Test
public void testDelteAll(){
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Long[] ids ={7L, 8L, 9L};
int i = mapper.deleteByIds(ids);
System.out.println(i);
sqlSession.commit();
sqlSession.close();
}
批量插入
int insertBatch(@Param("cars")List<Car> cars);
<insert id="insertBatch">
insert into t_car values
<foreach collection="cars" item="car" separator="," >
(null,#{car.carNum},#{car.brand},#{car.guidePrice},#{car.carType},#{car.produceTime})
</foreach>
</insert>
@Test
public void testInsertBatch(){
SqlSession sqlSession = SqlSessionUtil.openSession();
CarMapper mapper = sqlSession.getMapper(CarMapper.class);
Car car1 = new Car(null,"八嘎亚路1",30.8,"燃油第",null,"111");
Car car2 = new Car(null,"八嘎亚路2",30.8,"燃油第",null,"111");
Car car3 = new Car(null,"八嘎亚路3",30.8,"燃油第",null,"111");
List<Car> cars = new ArrayList<>();
cars.add(car1);
cars.add(car2);
cars.add(car3);
mapper.insertBatch(cars);
sqlSession.commit();
sqlSession.close();
}
sql标签与include标签
sql标签用来声明sql片段
include标签用来将声明的sql片段包含到某个sql语句当中
作用:代码复用,易维护
<sql id="carColumnNameSql">
id,
car_num as carNum,
brnd,
guide_price as guidePrice,
produce_time as produceTime,
car_type as carType
</sql>
<select id="...." resultType="car">
select
<include refid="carColumnNameSql">
from t_car where id = #{id}
</select>