Dynamic SQL of Mybatis

Mybatis

12. Dynamic SQL

12.1 introduction

  • What is dynamic SQL: dynamic SQL refers to generating different SQL statements according to different conditions.

  • Using the feature of dynamic SQL can completely get rid of this pain.

  • Dynamic SQL elements are similar to JSTL or XML based text processors.

  • In previous versions of MyBatis, there were many elements that needed time to understand.

  • MyBatis3 has greatly reduced the types of elements. Now you only need to learn half of the original elements.

  • MyBatis uses powerful OGNL based expressions to eliminate most other elements.

  • Dynamic SQL is splicing SQL statements. We just need to ensure the correctness of SQL and arrange and combine them according to the SQL format.

  • Recommendations:

    • First write a complete SQL in Mysql, and then modify the corresponding to become our dynamic SQL implementation.
  • if 
    choose(when,otherwise)
    trim(where,set)
    foreach
    

12.2 construction environment

  • SQL statement

    • CREATE TABLE `blog` (
        `id` varchar(50) NOT NULL COMMENT 'Blog ID',
        `title` varchar(100) NOT NULL COMMENT 'Blog title',
        `author` varchar(30) NOT NULL COMMENT 'Blogger',
        `create_time` datetime NOT NULL COMMENT 'Creation time',
        `views` int(30) NOT NULL COMMENT 'Views'
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      
  • Create a basic project

    • Guide bag.

    • Write a configuration file.

    • Write entity classes.

      • public class Blog {
            private String id;
            private String title;
            private String author;
            private Date createTime;//The property name and field name are inconsistent
            private int views;
        }
        
    • Write the Mapper interface and Mapper.xml file corresponding to the entity class.

      • //insert data
        int addBlog(Blog blog);
        
      • <insert id="addBlog" parameterType="blog">
            insert into blog (id, title, author, create_time, views) values
                (#{id},#{title},#{author},#{createTime},#{views})
        </insert>
        
    • IDUtils tool class

      • @SuppressWarnings("all")  //Suppress warning
        public class IDUtils {
            public static String getId(){
                return UUID.randomUUID().toString().replaceAll("-","");
            }
            @Test
            public void test(){
                System.out.println(IDUtils.getId());
                System.out.println(IDUtils.getId());
                System.out.println(IDUtils.getId());
            }
        }
        
      • Suppress warning

        • @SuppressWarnings("all")
    • test

      • @Test
            public void testAddBlog() {
                SqlSession sqlSession = MybatisUtils.getSqlSession();
                BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
                Blog blog = new Blog();
                blog.setId(IDUtils.getId());
                blog.setTitle("Mybatis So simple");
                blog.setAuthor("Jcooling");
                blog.setCreateTime(new Date());
                blog.setViews(9999);
                mapper.addBlog(blog);
        
                blog.setId(IDUtils.getId());
                blog.setTitle("Java So simple");
                mapper.addBlog(blog);
        
                blog.setId(IDUtils.getId());
                blog.setTitle("Spring So simple");
                mapper.addBlog(blog);
        
                blog.setId(IDUtils.getId());
                blog.setTitle("Linux So simple");
                mapper.addBlog(blog);
                sqlSession.commit();
                sqlSession.close();
            }
        

12.3,IF

  • Interface class

    • //Query blog
      List<Blog> queryBlogIf(Map map);
      
  • Corresponding xml

    • <select id="queryBlogIf" parameterType="map" resultType="blog">
              select * from blog where 1=1
                 <if test="title!=null">
                     and title=#{title}
                 </if>
              <if test="author!=null">
                     and author=#{author}
                 </if>
          </select>
      
  • test

    • @Test
          public void testQueryBlogIf() {
              SqlSession sqlSession = MybatisUtils.getSqlSession();
              BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
              HashMap map = new HashMap();
             // map.put("title","Linux is so simple");
              map.put("author","JCooling");
              List<Blog> blogs = mapper.queryBlogIf(map);
              for (Blog blog : blogs) {
                  System.out.println(blog);
              }
              sqlSession.close();
          }
      

12.4,choose(when,otherwise)

  • Interface class

    • //query
      List<Blog> queryBlogChoose(Map map);
      
  • Corresponding xml

    •  <select id="queryBlogChoose"  parameterType="map" resultType="blog">
             select * from blog
             <where>
             <choose>
                 <when test="title!=null">
                     title=#{title}
                 </when>
                 <when test="author!=null">
                    and author=#{author}
                 </when>
               <otherwise>
                    and views=#{views}
               </otherwise>
             </choose>
             </where>
         </select>
      
  • test

    •  @Test
          public void testQueryBlogChoose() {
              SqlSession sqlSession = MybatisUtils.getSqlSession();
              BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
              HashMap map = new HashMap();
              map.put("title","Linux So simple");
             // map.put("author","JCooling");
              map.put("views","9999");
              List<Blog> blogs = mapper.queryBlogChoose(map);
              for (Blog blog : blogs) {
                  System.out.println(blog);
              }
              sqlSession.close();
          }
      

12.5,trim(where,set)

  • Interface class

    • //Query blog
          List<Blog> queryBlogIf(Map map);
      //Update blog
          int updateBlogSet(Map map);
      
  • Corresponding xml

    •  <select id="queryBlogIf" parameterType="map" resultType="blog">
               select * from blog 
           <where>
                  <if test="title!=null">
                      title=#{title}
                  </if>
               <if test="author!=null">
                      and author=#{author}
                  </if>
               </where>
        </select>
       
       <update id="updateBlogSet" parameterType="map">
            update blog
       <set>
       <if test="title!=null">
          title=#{title},
      </if>
      <if test="author!=null">
           author=#{author}
      </if>
      </set>
            where id=#{id}
      </update>
      
  • test

    •  @Test
           public void testQueryBlogIf() {
             //Ibid
           }
       @Test
           public void testUpdateBlogSet() {
               SqlSession sqlSession = MybatisUtils.getSqlSession();
               BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
               HashMap map = new HashMap();
               map.put("title","Linux So simple 2");
               //map.put("author","JCooling");
               map.put("id","66c94df75eb34c8eab1a82c415b28dc2");
               int i = mapper.updateBlogSet(map);
               if (i==1) {
                  System.out.println("Update succeeded");
              }else {
                  System.out.println("Update failed");
              }
              sqlSession.commit();
              sqlSession.close();
          }
      
  • The set element will dynamically prefix the set keyword and delete irrelevant commas.

  • trim

    • //prefixOverrides prefix
      //suffixOverrides suffix
      <trim prefix="where" prefixOverrides="and|or" suffixOverrides=""></trim>
      
  • The so-called dynamic SQL is still an SQL statement in essence, but we can execute a logical code at the SQL level.

12.6,foreach

  • Another common operation requirement of dynamic SQL is to traverse a set, usually when constructing IN conditional statements.

  • The foreach element is very powerful. It allows you to specify a collection and declare collection item s and index variables that can be used in the element body.

  • It also allows you to specify the beginning and end strings and place separators between the iteration results. Extra delimiters are not accidentally appended.

  • be careful

    • You can pass any iteratable object (such as List, Set, etc.), Map object or array object to foreach as a Set parameter.
    • When an iteratable object or array is used, index is the number of current iterations, and the value of item is the element obtained in this iteration.
    • When using a Map object (or a collection of Map.Entry objects), index is the key and item is the value.
  • Interface class

    •  //Check the blog of records 1, 2 and 3
        List<Blog> queryBlogForeach(Map map);
      
  • Corresponding xml

    •   <!--We are now passing on a universal message map´╝îthis map There can be a collection in!-->
       <select id="queryBlogForeach" parameterType="map" resultType="blog">
           select * from  blog_1
           <where>
           <foreach collection="ids" item="id" open="and ("  close=")" separator="or">
                 id=#{id}
           </foreach>
           </where>
       </select>
      
  • test

    •  @Test
           public void testQueryBlogForeach() {
               SqlSession sqlSession = MybatisUtils.getSqlSession();
               BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
               HashMap map = new HashMap();
               ArrayList<Integer> ids = new ArrayList<Integer>();
               ids.add(1);
               ids.add(2);
               map.put("ids",ids);
               List<Blog> blogs = mapper.queryBlogForeach(map);
               for (Blog blog : blogs) {
                   System.out.println(blog);
               }
               sqlSession.close();
           }
      

12.7 SQL fragment

  • Sometimes, we may extract some functional parts for reuse.

  • Interface class

    • //Query blog
          List<Blog> queryBlogIf1(Map map);
      
  • Corresponding xml

    •  //Extract common parts using SQL Tags
       <sql id="if-title-author">
               <if test="title!=null">
                   title=#{title}
               </if>
               <if test="author!=null">
                    and author=#{author}
               </if>
           </sql>
       //Use the include tag reference where necessary
       <select id="queryBlogIf1" parameterType="map" resultType="blog">
               select * from blog
               <where>
                   <include refid="if-title-author"></include>
               </where>
           </select>
      
  • test

    •  @Test
           public void testQueryBlogIf1() {
             //Ibid
           }
      
  • matters needing attention

    • It is best to define SQL fragments based on a single table.
    • Do not have a where tag.

Tags: Mybatis

Posted on Sun, 05 Dec 2021 06:22:21 -0500 by sincejan63