1.参考官方文档
? if:字符判断
? choose (when, otherwise):分支选择
? trim (where, set):字符串截取;其中where标签封装查询条件,set标签封装修改条件
? foreach
2.if案例
1)在EmployeeMapper接口中添加一个方法:
//携带了哪个字段,查询条件就带上哪个字段的值
public List getEmployeeByConditionIf(Employee employee);
2).如果要写下列的SQL语句,只要是不为空,就作为查询条件,如下所示,这样写实际上是有问题的,所以我们要写成动态SQL语句:
select *from tbl_employee where id = #{id} and user_name = #{userName} and email = #{email} and gender = #{gender}
3)用if标签改写为动态SQL,如下所示:
select *from tbl_employee
where
id = #{id}
and user_name = #{userName}
and email = #{email}
and gender = #{gender}
4).测试代码:
@Test
public void testGetEmployee(){
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
employee.setId(1);
employee.setUserName("张三丰");
employee.setEmail("sunwukong@163.com");
employee.setGender(1);
List list = mapper.getEmployeeByConditionIf(employee);
System.out.println(list);
}
但是仔细来说,上面的sql语句是有问题的,当我们不给动态sql语句传递id值的时候,sql语句的拼装就会有问题!
解决办法:
1>.给where后面加上1=1,以后的条件都可以使用and xxx了
2>.mybatis可以使用where标签来将所有的查询条件包括在内。mybatis就会将where标签中拼装的sql,
多出来的and或者or去掉!//需要注意:where标签只会去掉第一个多出来的and或者or
3.>也就是说使用where标签有时候还是不能解决问题的,那怎么办呢?我们这里可以使用trim标签!
2.trim标签:可以自定义字符串的截取规则
select *from tbl_employee
id = #{id} and
user_name = #{userName} and
email = #{email} and
gender = #{gender}
3.choose标签:分支选择,类似于Java中的带了break的switch...case
choose (when, otherwise):如果带了id,就用id查,如果带了userName就用userName查,只会进入其中一个!
案例演示:
1>.在EmployeeMapper接口中添加一个方法:
public List getEmployeeByConditionChoose(Employee employee);
2>.sql映射文件
select *from tbl_employee
id = #{id}
user_name like #{userName}
email = #{email}
1=1
4.trim 中的set标签(where, set):字符串截取;其中where标签封装查询条件,set标签封装修改条件
set元素会动态前置set关键字,同时也会消除无关的逗号。
1).在EmployeeMapper中添加一个更新的方法,如下所示:
public void updateEmp(Employee employee);
2)在sql映射文件中,填写相应的sql语句,如下所示【set标签可以将字段后面的逗号去掉】:
update tbl_employee
user_name = #{userName},
email = #{email},
gender = #{gender},
where id = #{id}
测试类代码为:
@Test
public void testGetEmployee(){
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
employee.setId(1);
employee.setUserName("哈哈");
employee.setEmail("sunwukong@163.com");
employee.setGender(1);
mapper.updateEmp(employee);
}
//当然上面的set标签我们也可以使用trim标签来代替,如下所示:
update tbl_employee
user_name = #{userName},
email = #{email},
gender = #{gender},
where id = #{id}
5.foreach:遍历元素
动态SQL的另一个常用的操作是需要对一个集合进行遍历,通常在构建in条件语句的时候!
foreach元素允许指定一个集合,声明集合项和索引变量,并可以指定开闭匹配的字符串以及在迭代之间放置分隔符。
案例演示:
1>.在EmployeeMapper接口中加入一个方法,如下所示:
public List getEmpsByConditionForeach(@Param("ids") List ids);
2>.在MyBatis的sql映射文件中写相应的代码:
select * from tbl_employee where id in
#{id}
3.>测试类代码为:
@Test
public void testGetEmployee(){
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
List asList = Arrays.asList(1,2,3,4);
List emps = mapper.getEmpsByConditionForeach(asList);
for (Employee employee : emps) {
System.out.println(employee);
}
}
foreach标签还可以用于批量保存数据,如下所示:
1.在EmployeeMapper接口类中添加批量插入的方法:
public void addEmps(@Param("emps") List emps);
2.在EmployeeMapper.xml的sql映射文件中添加响应的语句:
INSERT INTO tbl_employee(user_name,gender,email,d_id) VALUES
(#{emp.userName},#{emp.gender},#{emp.email},#{emp.depart.id})
3.测试代码:
@Test
public void testGetEmployee(){
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
List emps = new ArrayList();
emps.add(new Employee(0, 1, "allen", "allen@163.com", new Department(1)));
emps.add(new Employee(0, 0, "tom", "tom@163.com", new Department(2)));
emps.add(new Employee(0, 1, "mux", "mux@163.com", new Department(1)));
mapper.addEmps(emps);
}