深入理解MyBatis中的动态SQL语句

有时候，静态的SQL语句并不能满足应用程序的需求。我们可以根据一些条件，来动态地构建 SQL语句。 例如，在Web应用程序中，有可能有一些搜索界面，需要输入一个或多个选项，然后

    <p>有时候，静态的SQL语句并不能满足应用程序的需求。我们可以根据一些条件，来动态地构建 SQL语句。</p>

例如，在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

映射接口：

public interface DynamicSqlMapper{ List searchCourses(Map map); }

测试方法：

@Testpublic void test_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 void test_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 void test_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 void test_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 interface DynamicSqlMapper{ List searchCoursesByTutors(Map map); }

测试方法：

@Testpublic void test_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 void test_updateStudent(){

SqlSession sqlSession = null;try {sqlSession = MyBatisSqlSessionFactory.openSession();

DynamicSqlMapper mapper = sqlSession.getMapper(DynamicSqlMapper.class);

Student student = new Student();student.setStudId(45);student.setEmail("xx@558idc.com");

mapper.updateStudent(student);

sqlSession.commit();

} catch (Exception e) {e.printStackTrace();}}