Mybatis——动态sql

if标签

用于判断条件是否成立。使用test属性进行条件判断，如果条件为true，则拼接sql。

<where>标签用于识别语句是否需要连接词and，识别sql语句。

package com.t0.maybatisc.mapper;import com.t0.maybatisc.pojo.Emp;
import org.apache.ibatis.annotations.*;import java.time.LocalDate;
import java.util.List;@Mapper
public interface EmpMapper {
//    条件查询public List<Emp> list( String name,  Short gender,  LocalDate begin,  LocalDate end);}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.t0.maybatisc.mapper.EmpMapper"><select id="list" resultType="com.t0.maybatisc.pojo.Emp">select * from emp<where><if test="name != null">name like concat('%',#{name},'%')</if><if test="gender != null">and gender = #{gender}</if><if test="begin != null and end != null">and entrydate between #{begin} and #{end}</if></where>order by update_time desc</select>
</mapper>

//    根据条件查询@Testpublic void list(){
//        List<Emp> empList =empMapper.list("张",(short) 1,LocalDate.of(2010,01,01),LocalDate.of(2020,01,01));List<Emp> empList =empMapper.list(null,(short)1,null,null);System.out.println(empList);}
}

用于select、upgrade。

foreach标签

package com.t0.maybatisc.mapper;import com.t0.maybatisc.pojo.Emp;
import org.apache.ibatis.annotations.*;import java.time.LocalDate;
import java.util.List;@Mapper
public interface EmpMapper {
//    多条删除public  void deleteByids(List<Integer> ids);}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.t0.maybatisc.mapper.EmpMapper"><select id="deleteByids" resultType="com.t0.maybatisc.pojo.Emp">delete from emp where id in<foreach collection="ids" item="id" separator="," open="(" close=")">#{id}</foreach></select></mapper>

collection="ids" item="id" separator="," open="(" close=")"

    @Testpublic void TestdeleteByids(){List<Integer> ids = Arrays.asList(19,18,16);empMapper.deleteByids(ids);}

sql和include

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.t0.maybatisc.mapper.EmpMapper"><sql id="commonS">select id,username,password,name,gender,image,job,entrydate,dept_id,create_time,update_timefrom emp</sql><select id="list" resultType="com.t0.maybatisc.pojo.Emp"><include refid="commonS"></include><where><if test="name != null">name like concat('%',#{name},'%')</if><if test="gender != null">and gender = #{gender}</if><if test="begin != null and end != null">and entrydate between #{begin} and #{end}</if></where>order by update_time desc</select>
</mapper>