实体类 用小辣椒 @Data 就不用自己生成get/set toString
@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class Emp implements Serializable {
private Integer emp_id;
private String emp_name;
private Integer age;
private String gender;
}
接口
//根据员工的姓名 年龄 性别 查询出来结果不知道是一个还是多个用list集合
List<Emp> findDynamic(Emp emp);
mapper文件
if标签:通过test属性中的表达式判断标签中的内容是否有效(是否会拼接到sql中)
where 1=1
where标签:
1.where标签中有条件成立,会自动生成where关键字
2.若where中有条件不成立,会自动去掉多余的and,其中内容后多余的and无法去掉
3.若where标签中没有任何一个条件成立,则where标签不会有任何作用
<select id="findDynamic" resultType="Emp">
select * from emp
<where>
<if test="emp_name!=null and emp_name !=''">
emp_name=#{emp_name}
</if>
<if test="age !=null and age!=''">
and age=#{age}
</if>
<if test="gender!=null and gender!=''">
and gender=#{gender}
</if>
</where>
</select>
trim标签: 1.prefix、suffix在标签中内容前面或者后面添加指定内容 2.suffixOverrides、prefixOverrides在标签中前面或者后面去掉指定内容
trim标签
<select id="findDynamic" resultType="Emp">
select * from emp
<trim prefix="where" suffixOverrides="and">
<if test="emp_name!=null and emp_name !=''">
emp_name=#{emp_name} and
</if>
<if test="age !=null and age!=''">
age=#{age} and
</if>
<if test="gender!=null and gender!=''">
gender=#{gender}
</if>
</trim>
</select>
choose、when、otherwise 相当于if ...else if... else when 至少设置一个 otherwise最多设置一个
<select id="findDynamic" resultType="Emp">
select * from emp
<where>
<choose>
<when test="emp_name !=null and emp_name !=''">
emp_name=#{emp_name}
</when>
<when test="age !=null and age !=''">
age=#{age}
</when>
<when test="gender !=null and gender !=''">
gender=#{gender}
</when>
</choose>
</where>
</select>
测试类
public class EmpDynamicTest {
public static SqlSessionFactory ssf=null;
static {
try {
//读取配置文件
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//通过配置文件读取创建sql会话工厂
ssf = new SqlSessionFactoryBuilder().build(is);
} catch (IOException e) {
e.printStackTrace();
}
}
@Test
public void testIf(){
SqlSession sqlSession = ssf.openSession(true);
EmpDao mapper = sqlSession.getMapper(EmpDao.class);
Emp emp = new Emp(null,"",null,"男");
List<Emp> list = mapper.findDynamic(emp);
for (Emp emp1 : list) {
System.out.println(emp1);
}
}
}
foreach标签: collection:设置要循环的集合或者数组 item:用一个字符串表示一个数组或者一个集合中的每一个数据 separator:设置每次循环次数之间以什么分隔 open:循环内容以什么开始 close:循环内容以什么结束
批量添加
//实现批量添加
void addEmpForeach(@Param("emps") List<Emp> emps);
<insert id="addEmpForeach">
insert into emp values
<foreach collection="emps" item="emp" separator=",">
(null,#{emp.emp_name},#{emp.age},#{emp.gender},null )
</foreach>
</insert>
测试
@Test
public void testForeach(){
SqlSession sqlSession = ssf.openSession(true);
EmpDao mapper = sqlSession.getMapper(EmpDao.class);
Emp emp = new Emp(null,"张三",20,"男");
Emp emp1 = new Emp(null,"李四",20,"男");
Emp emp2 = new Emp(null,"王五",20,"女");
List<Emp> list = Arrays.asList(emp, emp1, emp2);
mapper.addEmpForeach(list);
}
批量删除
//批量删除
void deleteEmpForeach(@Param("ids") Integer[] ids);
<delete id="deleteEmpForeach">
delete from emp where emp_id in
<foreach collection="ids" separator="," item="id" open="(" close=")">
#{id}
</foreach>
</delete>
以or进行分隔
<delete id="deleteEmpForeach">
delete from emp where
<foreach collection="ids" separator="or" item="id" >
emp_id= #{id}
</foreach>
</delete>
@Test
public void testDelete(){
SqlSession sqlSession = ssf.openSession(true);
EmpDao mapper = sqlSession.getMapper(EmpDao.class);
Integer [] arr={11,12,13};
mapper.deleteEmpForeach(arr);
}
sql片段 可以记录一段sql,在要使用的地方进行include标签引入 <sql id="EmpCloumns"> delete from emp </sql> <include refid="deleteemp"></include>