经验首页 前端设计 程序设计 Java相关 移动开发 数据库/运维 软件/图像 大数据/云计算 其他经验
当前位置：技术经验 » Java相关 » Spring Boot » 查看文章
mybatis中的动态SQL语句
来源：cnblogs　　作者：Magic_Li　　时间：2019/10/16 12:40:56　　对本文有异议

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

例如，在Web应用程序中，有可能有一些搜索界面，需要输入一个或多个选项，然后根据这些已选择的条件去执行检索操作。我们可能需要根据用户选择的条件来构建动态的SQL语句。如果用户提供了任何一个条件，我们需要将那个条件添加到SQL语句的WHERE子句中。

！以下内容基于自己建的表和类！

1.<if>标签被用来通过条件嵌入SQL片段，如果条件为true，则相应地SQL片段将会被添加到SQL语句中。
例如：
假定有一个课程搜索界面，设置了讲师（Tutor）下拉列表框，课程名称（CourseName）文本输入框，开始时间（StartDate）输入框，结束时间（EndDate）输入框，作为搜索条件。假定课讲师下拉列表是必须选的，其他的都是可选的。当用户点击搜索按钮时，需要显示符合条件的列表数据。

对应的sql映射文件，如下所示：

  1. <!-- 独立的Course封装映射 -->
  2. <resultMap type="Course" id="CourseResult"> 
  3. <id column="course_id" property="courseId" /> 
  4. <result column="name" property="name" /> 
  5. <result column="description" property="description" /> 
  6. <result column="start_date" property="startDate" /> 
  7. <result column="end_date" property="endDate" /> 
  8. </resultMap>
  1. <!-- 查询Course的select语句，里面加入了if条件判断 -->
  2. <select id="searchCourses" parameterType="map" resultMap="CourseResult">
  3. SELECT * FROM COURSES 
  4. WHERE TUTOR_ID= #{tutorId} 
  5. <if test="courseName != null"> 
  6. AND NAME LIKE #{courseName} 
  7. </if> 
  8. <if test="startDate != null"> 
  9. AND START_DATE >= #{startDate} 
  10. </if> 
  11. <if test="endDate != null"> 
  12. AND END_DATE <![CDATA[ <= ]]> #{endDate} 
  13. </if> 
  14. </select>

映射接口：

  1. public interface DynamicSqlMapper{ 
  2. List<Course> searchCourses(Map<String, Object> map); 
  3. }

测试方法：

  1. @Test
  2. public void test_searchCourses1(){
  4. SqlSession sqlSession = null;
  5. try {
  6. sqlSession = MyBatisSqlSessionFactory.openSession();
  8. DynamicSqlMapper mapper = sqlSession.getMapper(DynamicSqlMapper.class);
  10. Map<String,Object> map = new HashMap<String,Object>(); 
  11. map.put("tutorId", 1); 
  12. map.put("courseName", "%Java%"); 
  14. LocalDate date = LocalDate.of(2019, 1, 10);
  15. map.put("startDate", date);
  17. List<Course> courses = mapper.searchCourses(map);
  19. courses.forEach(System.out::println);
  21. } catch (Exception e) {
  22. e.printStackTrace();
  23. }
  24. }

 

2.choose,when 和 otherwise 条件
有时候，查询功能是以查询类别为基础的。首先，用户需要先选择是通过讲师查询，还是课程名称查询，还是开始时间查询。然后根据选择的查询类别，输入相应的参数，再进行查询。

例如，页面中有一个下拉列表，可以选择查询的类别，可以选择根据讲师查询、根据课程名查询、根据时间查询等等，选择了列表之后，再输入关键字进行查询。

MyBatis提供了<choose>标签可以支持此类型的查询处理。 假设如果用户都没有选择，那么默认可以根据当前时间进行查询。

注意:mysql中now()表示当前时间 oracle需要使用sysdate

对应的sql映射文件，如下所示：

  1. <select id="searchCourses" parameterType="map" resultMap="CourseResult"> 
  2. SELECT * FROM COURSES 
  3. <choose> 
  4. <when test="searchBy == 'Tutor'"> 
  5. WHERE TUTOR_ID = #{tutorId} 
  6. </when> 
  7. <when test="searchBy == 'CourseName'"> 
  8. WHERE name like #{courseName} 
  9. </when> 
  10. <otherwise> 
  11. WHERE start_date >= sysdate 
  12. </otherwise> 
  13. </choose> 
  14. </select>

测试方法：

  1. @Test
  2. public void test_searchCourses2(){
  4. SqlSession sqlSession = null;
  5. try {
  6. sqlSession = MyBatisSqlSessionFactory.openSession();
  8. DynamicSqlMapper mapper = sqlSession.getMapper(DynamicSqlMapper.class);
  10. Map<String,Object> map = new HashMap<String,Object>(); 
  11. //    map.put("searchBy", "Tutor"); 
  12. //    map.put("tutorId", 1); 
  13. map.put("searchBy", "CourseName"); 
  14. map.put("courseName", "%MyBatis%"); 
  16. List<Course> courses = mapper.searchCourses(map);
  18. courses.forEach(System.out::println);
  20. } catch (Exception e) {
  21. e.printStackTrace();
  22. }
  23. }

MyBatis计算<choose>中条件的值，并使用第一个值为TRUE的子句。如果没有条件为 true，则使用<otherwise>内的子句。

 

3.Where 条件
有时候，所有的查询条件应该是可选的。在需要使用至少一种查询条件的情况下，可以直接使用WHERE子句。
如果有多个条件，我们需要在条件中添加AND或OR。MyBatis提供了<where>元素支持这种类型的动态SQL语句。

例如，在查询课程界面，假设所有的查询条件是可选的。

注意，<where>元素只有在其内部标签有返回内容时才会在动态语句上插入WHERE条件语句。
并且，如果WHERE子句以AND或者OR打头，则打头的AND或OR将会被移除。

映射文件：

  1. <select id="searchCourses" parameterType="map" resultMap="CourseResult"> 
  2. SELECT * FROM COURSES 
  3. <where> 
  4. <if test="tutorId != null "> 
  5. TUTOR_ID= #{tutorId} 
  6. </if> 
  7. <if test="courseName != null"> 
  8. AND name like #{courseName} 
  9. </if> 
  10. <if test="startDate != null"> 
  11. AND start_date >= #{startDate} 
  12. </if> 
  13. </where> 
  14. </select>

测试方法：

  1. @Test
  2. public void test_searchCourses3(){
  4. SqlSession sqlSession = null;
  5. try {
  6. sqlSession = MyBatisSqlSessionFactory.openSession();
  8. DynamicSqlMapper mapper = sqlSession.getMapper(DynamicSqlMapper.class);
  10. Map<String,Object> map = new HashMap<String,Object>(); 
  11. //map.put("tutorId", 1); 
  12. //map.put("courseName", "JavaSE"); 
  13. //map.put("startDate", LocalDate.of(2019, 1, 10)); 
  15. List<Course> courses = mapper.searchCourses(map);
  17. courses.forEach(System.out::println);
  19. } catch (Exception e) {
  20. e.printStackTrace();
  21. }
  22. }

 

4.<trim>条件
<trim>元素和<where>元素类似，但是<trim>提供了添加 前缀/后缀 或者 移除 前缀/后缀 的功能。

映射文件：

  1. <select id="searchCourses" parameterType="map" resultMap="CourseResult"> 
  2. SELECT * FROM COURSES 
  3. <trim prefix="WHERE" suffixOverrides="and"> 
  4. <if test=" tutorId != null ">
  5. TUTOR_ID = #{tutorId} and
  6. </if> 
  7. <if test="courseName != null"> 
  8. name like #{courseName} and
  9. </if> 
  10. </trim> 
  11. </select>

prefix表示有一个if成立则插入where语句，没有if成立，就会去掉where直接查询
suffix表示后缀,和prefix相反

suffixOverrides="and"表示如果最后生成的sql语句多一个and,则自动去掉.
prefixOverrides的意思是处理前缀,和suffixOverrides相反

测试方法：

  1. @Test
  2. public void test_searchCourses4(){
  4. SqlSession sqlSession = null;
  5. try {
  6. sqlSession = MyBatisSqlSessionFactory.openSession();
  8. DynamicSqlMapper mapper = sqlSession.getMapper(DynamicSqlMapper.class);
  10. Map<String,Object> map = new HashMap<String,Object>(); 
  11. //    map.put("tutorId", 1); 
  12. //    map.put("courseName", "JavaSE"); 
  14. List<Course> courses = mapper.searchCourses(map);
  16. courses.forEach(System.out::println);
  18. } catch (Exception e) {
  19. e.printStackTrace();
  20. }
  21. }

 

5.foreach 循环
另外一个强大的动态SQL语句构造标签是<foreach>。它可以迭代遍历一个数组或者列表，构造AND/OR条件或一个IN子句。

假设查询tutor_id为 1，3，6的讲师所教授的课程，我们可以传递一个tutor_id组成的列表给映射语句，然后通过<foreach>遍历此列表构造动态SQL。

映射文件：

  1. <select id="searchCoursesByTutors" parameterType="map" resultMap="CourseResult"> 
  2. SELECT * FROM COURSES 
  3. <if test="tutorIds != null"> 
  4. <where> 
  5. <!-- 在这里的 tutorId指的是集合中存入准备查询的tutor_id-->
  6. <foreach item="tutorId" collection="tutorIds"> 
  7. OR tutor_id = #{tutorId} 
  8. </foreach> 
  9. </where> 
  10. </if> 
  11. </select>

映射接口:

  1. public interface DynamicSqlMapper{ 
  2. List<Course> searchCoursesByTutors(Map<String,Object> map); 
  3. }

测试方法：

  1. @Test
  2. public void test_searchCoursesByTutors(){
  4. SqlSession sqlSession = null;
  5. try {
  6. sqlSession = MyBatisSqlSessionFactory.openSession();
  8. DynamicSqlMapper mapper = sqlSession.getMapper(DynamicSqlMapper.class);
  10. Map<String,Object> map = new HashMap<String,Object>(); 
  12. List<Integer> tutorIds = new ArrayList<Integer>(); 
  13. tutorIds.add(1); 
  14. tutorIds.add(3); 
  15. tutorIds.add(6); 
  17. map.put("tutorIds", tutorIds); 
  19. List<Course> courses = mapper.searchCoursesByTutors(map);
  21. courses.forEach(System.out::println);
  23. } catch (Exception e) {
  24. e.printStackTrace();
  25. }
  26. }

和上面同样的功能，使用<foreach>生成IN子句：

  1. <select id="searchCoursesByTutors" parameterType="map" resultMap="CourseResult"> 
  2. SELECT * FROM COURSES 
  3. <if test="tutorIds != null"> 
  4. <where> 
  5. tutor_id IN 
  6. <foreach item="tempValue" collection="tutorIds" open="(" separator="," close=")"> 
  7. #{tempValue} 
  8. </foreach> 
  9. </where> 
  10. </if> 
  11. </select>

测试方法保持不变。

 

6.set 条件，专用于UPDATE更新操作

<set>元素和<where>元素类似，但是set元素只是针对update更新语句使用的。

  1. <update id="updateStudent" parameterType="Student"> 
  2. update students 
  3. <set> 
  4. <if test="name != null">name=#{name},</if> 
  5. <if test="email != null">email=#{email},</if> 
  6. <if test="phone != null">phone=#{phone},</if> 
  7. </set> 
  8. where stud_id=#{studId} 
  9. </update>

这里，如果<if>条件返回了任何文本内容，<set>将会插入set关键字和其文本内容，并且会剔除将末尾的逗号","。

测试方法：

  1. @Test
  2. public void test_updateStudent(){
  4. SqlSession sqlSession = null;
  5. try {
  6. sqlSession = MyBatisSqlSessionFactory.openSession();
  8. DynamicSqlMapper mapper = sqlSession.getMapper(DynamicSqlMapper.class);
  10. Student student = new Student();
  11. student.setStudId(45);
  12. student.setEmail("xx@briup.com");
  14. mapper.updateStudent(student);
  16. sqlSession.commit();
  18. } catch (Exception e) {
  19. e.printStackTrace();
  20. }
  21. }

 

原文链接：http://www.cnblogs.com/Magic-Li/p/11683937.html

 友情链接：直通硅谷 　点职佳 　北美留学生论坛

Spring Boot热门文章

  • 史上最详 Thymeleaf 使用教程
  • Spring Boot如何通过java -jar启动
  • Spring Boot 2.x（六）：优雅的统一返回值
  • Springboot使用Logback实现日志配置与异常记录
  • 使用SpringBoot+EasyExcel+Vue实现excel表格的导入和导出详解
  • spring boot上传文件超出大小异常无法捕获问题
  • paging组件的用法和意义
  • springboot2.0集成rabbitmq的示例代码
  • Spring MVC学习step1——框架熟悉
  • spring boot ${}占位符不起作用的解决方案

    • Spring Boot推荐文章

  • 3分钟带你搞定Spring Boot中Schedule
  • 不单独部署注册中心，又要具备注册中心的功能，我能上天！
  • 从零开始学Spring Boot系列-集成Spring Security实现用户认证与授权
  • 不单独部署注册中心，又要具备注册中心的功能，咋不让我上天？
  • EasyExcel 无法读取图片？用poi写了一个工具类
  • 【进阶篇】使用 Stream 流对比两个集合的常用操作分享
  • 关于DDD和COLA的一些总结和思考
  • SnakeYaml反序列化分析
  • RestTemplate进行https请求时适配信任证书
  • Kryo反序列化链分析

    • 本站QQ群：前端 618073944 | Java 606181507 | Python 626812652 | C/C++ 612253063 | 微信 634508462 | 苹果 692586424 | C#/.net 182808419 | PHP 305140648 | 运维 608723728

    W3xue 的所有内容仅供测试，对任何法律问题及风险不承担任何责任。通过使用本站内容随之而来的风险与本站无关。
    关于我们  |  意见建议  |  捐助我们  |  报错有奖  |  广告合作、友情链接（目前9元/月）请联系QQ：27243702 沸活量
    皖ICP备17017327号-2　皖公网安备34020702000426号