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