有时候,静态的SQL语句并不能满足应用程序的需求。我们可以根据一些条件,来动态地构建 SQL语句。
例如,在Web应用程序中,有可能有一些搜索界面,需要输入一个或多个选项,然后根据这些已选择的条件去执行检索操作。我们可能需要根据用户选择的条件来构建动态的SQL语句。如果用户提供了任何一个条件,我们需要将那个条件添加到SQL语句的WHERE子句中。
!以下内容基于自己建的表和类!
1.标签被用来通过条件嵌入SQL片段,如果条件为true,则相应地SQL片段将会被添加到SQL语句中。
例如:
假定有一个课程搜索界面,设置了讲师(Tutor)下拉列表框,课程名称(CourseName)文本输入框,开始时间(StartDate)输入框,结束时间(EndDate)输入框,作为搜索条件。假定课讲师下拉列表是必须选的,其他的都是可选的。当用户点击搜索按钮时,需要显示符合条件的列表数据。
对应的sql映射文件,如下所示:
SELECT * FROM COURSES
WHERE TUTOR_ID= #{tutorId}AND NAME LIKE #{courseName}
AND START_DATE >= #{startDate}
AND END_DATE#{endDate}
映射接口:
public interfaceDynamicSqlMapper{
List searchCourses(Mapmap);
}
测试方法:
@Testpublic voidtest_searchCourses1(){
SqlSession sqlSession= null;try{
sqlSession=MyBatisSqlSessionFactory.openSession();
DynamicSqlMapper mapper= sqlSession.getMapper(DynamicSqlMapper.class);
Map map = new HashMap();
map.put("tutorId", 1);
map.put("courseName", "%Java%");
LocalDate date= LocalDate.of(2019, 1, 10);
map.put("startDate", date);
List courses =mapper.searchCourses(map);
courses.forEach(System.out::println);
}catch(Exception e) {
e.printStackTrace();
}
}
2.choose,when 和 otherwise 条件
有时候,查询功能是以查询类别为基础的。首先,用户需要先选择是通过讲师查询,还是课程名称查询,还是开始时间查询。然后根据选择的查询类别,输入相应的参数,再进行查询。
例如,页面中有一个下拉列表,可以选择查询的类别,可以选择根据讲师查询、根据课程名查询、根据时间查询等等,选择了列表之后,再输入关键字进行查询。
MyBatis提供了标签可以支持此类型的查询处理。 假设如果用户都没有选择,那么默认可以根据当前时间进行查询。
注意:mysql中now()表示当前时间 oracle需要使用sysdate
对应的sql映射文件,如下所示:
SELECT * FROM COURSES
WHERE TUTOR_ID = #{tutorId}
WHERE name like #{courseName}
WHERE start_date >= sysdate
测试方法:
@Testpublic voidtest_searchCourses2(){
SqlSession sqlSession= null;try{
sqlSession=MyBatisSqlSessionFactory.openSession();
DynamicSqlMapper mapper= sqlSession.getMapper(DynamicSqlMapper.class);
Map map = new HashMap();//map.put("searchBy", "Tutor");//map.put("tutorId", 1);
map.put("searchBy", "CourseName");
map.put("courseName", "%MyBatis%");
List courses =mapper.searchCourses(map);
courses.forEach(System.out::println);
}catch(Exception e) {
e.printStackTrace();
}
}
MyBatis计算中条件的值,并使用第一个值为TRUE的子句。如果没有条件为 true,则使用内的子句。
3.Where 条件
有时候,所有的查询条件应该是可选的。在需要使用至少一种查询条件的情况下,可以直接使用WHERE子句。
如果有多个条件,我们需要在条件中添加AND或OR。MyBatis提供了元素支持这种类型的动态SQL语句。
例如,在查询课程界面,假设所有的查询条件是可选的。
注意,元素只有在其内部标签有返回内容时才会在动态语句上插入WHERE条件语句。
并且,如果WHERE子句以AND或者OR打头,则打头的AND或OR将会被移除。
映射文件:
SELECT * FROM COURSES
TUTOR_ID= #{tutorId}
AND name like #{courseName}
AND start_date >= #{startDate}
测试方法:
@Testpublic voidtest_searchCourses3(){
SqlSession sqlSession= null;try{
sqlSession=MyBatisSqlSessionFactory.openSession();
DynamicSqlMapper mapper= sqlSession.getMapper(DynamicSqlMapper.class);
Map map = new HashMap();//map.put("tutorId", 1);//map.put("courseName", "JavaSE");//map.put("startDate", LocalDate.of(2019, 1, 10));
List courses =mapper.searchCourses(map);
courses.forEach(System.out::println);
}catch(Exception e) {
e.printStackTrace();
}
}
4.条件
元素和元素类似,但是提供了添加 前缀/后缀 或者 移除 前缀/后缀 的功能。
映射文件:
SELECT * FROM COURSES
TUTOR_ID = #{tutorId} and
name like #{courseName} and
prefix表示有一个if成立则插入where语句,没有if成立,就会去掉where直接查询
suffix表示后缀,和prefix相反
suffixOverrides="and"表示如果最后生成的sql语句多一个and,则自动去掉.
prefixOverrides的意思是处理前缀,和suffixOverrides相反
测试方法:
@Testpublic voidtest_searchCourses4(){
SqlSession sqlSession= null;try{
sqlSession=MyBatisSqlSessionFactory.openSession();
DynamicSqlMapper mapper= sqlSession.getMapper(DynamicSqlMapper.class);
Map map = new HashMap();//map.put("tutorId", 1);//map.put("courseName", "JavaSE");
List courses =mapper.searchCourses(map);
courses.forEach(System.out::println);
}catch(Exception e) {
e.printStackTrace();
}
}
5.foreach 循环
另外一个强大的动态SQL语句构造标签是。它可以迭代遍历一个数组或者列表,构造AND/OR条件或一个IN子句。
假设查询tutor_id为 1,3,6的讲师所教授的课程,我们可以传递一个tutor_id组成的列表给映射语句,然后通过遍历此列表构造动态SQL。
映射文件:
SELECT * FROM COURSES
OR tutor_id = #{tutorId}
映射接口:
public interfaceDynamicSqlMapper{
List searchCoursesByTutors(Mapmap);
}
测试方法:
@Testpublic voidtest_searchCoursesByTutors(){
SqlSession sqlSession= null;try{
sqlSession=MyBatisSqlSessionFactory.openSession();
DynamicSqlMapper mapper= sqlSession.getMapper(DynamicSqlMapper.class);
Map map = new HashMap();
List tutorIds = new ArrayList();
tutorIds.add(1);
tutorIds.add(3);
tutorIds.add(6);
map.put("tutorIds", tutorIds);
List courses =mapper.searchCoursesByTutors(map);
courses.forEach(System.out::println);
}catch(Exception e) {
e.printStackTrace();
}
}
和上面同样的功能,使用生成IN子句:
SELECT * FROM COURSES
tutor_id IN#{tempValue}
测试方法保持不变。
6.set 条件,专用于UPDATE更新操作
元素和元素类似,但是set元素只是针对update更新语句使用的。
update students
name=#{name},
email=#{email},
phone=#{phone},
where stud_id=#{studId}
这里,如果条件返回了任何文本内容,将会插入set关键字和其文本内容,并且会剔除将末尾的逗号","。
测试方法:
@Testpublic voidtest_updateStudent(){
SqlSession sqlSession= null;try{
sqlSession=MyBatisSqlSessionFactory.openSession();
DynamicSqlMapper mapper= sqlSession.getMapper(DynamicSqlMapper.class);
Student student= newStudent();
student.setStudId(45);
student.setEmail("xx@briup.com");
mapper.updateStudent(student);
sqlSession.commit();
}catch(Exception e) {
e.printStackTrace();
}
}