• Reference from official documentation

An overview,

• Dynamic SQL is one of the powerful features of MyBatis.
• Dynamic SQL enables you to dynamically concatenate SQL statements, realizing multiple functions without modifying or creating multiple labels.

Second, the if

• Only when theifThe contents of the if tag are concatenated into an SQL statement only if the conditions within the tag are met

  <select id="selectBlogByIf" parameterType="map" resultType="blog">
      select id, name, author, release_date, view from blog
      <where>
          <if test="id ! = null">
              id = #{id}
          </if>
          <if test="view ! = null">
              and view >= #{view}
          </if>
      </where>
  </select>
  Copy the code

    @Test
    public void selectBlogByIfTest(a) {
      try (SqlSession sqlSession = Utils.getSqlSession()) {
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap();
        map.put("id".1);
        //map.put("view", 10);List<Blog> blogs = mapper.selectBlogByIf(map); blogs.forEach(logger::info); }}Copy the code

  • When the map only addsidKey, which generates the statementwhere id = #{id}; If only addviewKey, that generates the statementwhere view = #{view}; The statement is generated when both are addedwhere id = #{id} and view = #{view}; If nothing is added, nothing is filtered

Choose, when, otherwise

• The implementation selects one of several conditions, similar to Java’sswitch-case

  <select id="selectBlogByChoose" parameterType="map" resultType="blog">
      select id, name, author, release_date, view from blog
      <where>
          <choose>
              <when test="id ! = null">
                  id = #{id}
              </when>
              <when test="name ! = null">
                  name = #{name}
              </when>
          </choose>
      </where>
  </select>
  Copy the code

    @Test
    public void selectBlogByChooseTest(a) {
      try (SqlSession sqlSession = Utils.getSqlSession()) {
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap();
        //map.put("id", 2);
        map.put("name"."10"); List<Blog> blogs = mapper.selectBlogByChoose(map); blogs.forEach(logger::info); }}Copy the code

4. Trim, where, set

• To implement a filter that can run successfully with or without additional filters, you can change thewhereExtract it out as a label, only inwhereAdd only when the content in thewhere. See the above examples.
• whereThe element is inserted only if the child element returns anythingWHEREClause. Also, if the clause begins withAND 或 OR.whereThe element will also remove them
• setThe element is dynamically inserted at the beginning of the rowSETKeyword, and will delete the additional comma

  <update id="updateBlog" parameterType="map">
      update blog
      <set>
          <if test="name ! = null">
              name = #{name},
          </if>
          <if test="author ! = null">
              author = #{author},
          </if>
          <if test="release_date">
              name = #{releaseDate},
          </if>
          <if test="view ! = null">
              view = #{view},
          </if>
      </set>
      <where>
          <if test="id ! = null">
              id = #{id}
          </if>
      </where>
  </update>
  Copy the code

    @Test
    public void updateBlogTest(a) {
      try (SqlSession sqlSession = Utils.getSqlSession()) {
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap();
        map.put("id".1);
        map.put("view".10);
        //map.put("author", "author");
        map.put("name"."python");
        //map.put("releaseDate", LocalDate.now());
        int res = mapper.updateBlog(map);
        sqlSession.commit();
        if (res > 0)
          logger.info("Modified successfully");
        else
          logger.info("Modification failed"); }}Copy the code

• ifwhereElements that are not quite what you expect can be customizedtrimElement to customizewhereThe functionality of the element. For example, andwhereElement equivalent customizationtrimElements for

  <trim prefix="WHERE" prefixOverrides="AND |OR ">.</trim>
  Copy the code

  Equivalent toset

  <trim prefix="SET" suffixOverrides=",">.</trim>
  Copy the code

  Prefix indicates the content inserted at the beginning of the sentence if it exists; Suffix indicates the content inserted at the end of the sentence if the content exists; PrefixOverrides the part to be deleted if the beginning of the content exists; SuffixOverrides indicates the part to be deleted if the end of the content exists.

Five, the foreach

• Another common use of dynamic SQL is traversal of collections (especially in builds)INConditional statement)

  <select id="selectBlogsByIds" resultType="blog" parameterType="list">
      select *
      from blog
      where id in
      <foreach item="id" collection="list" open="(" separator="," close=")">
          #{id}
      </foreach>
  </select>
  Copy the code

  “Item” is the name of each element in the collection, “open” is the start symbol, “close” is the end symbol, and “separator” is the separator

    @Test
    public void selectBlogsByIdsTest(a) {
      try (SqlSession sqlSession = Utils.getSqlSession()) {
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        List<Blog> blogs = mapper.selectBlogsByIds(Arrays.asList(1.2.3)); blogs.forEach(logger::info); }}Copy the code

• Bulk insert

  int insertAll(@Param("users") List<User> users);
  Copy the code

  <insert id="insertAll">
      insert user(id, name, address) values
      <foreach collection="users" item="user" separator=",">
        (#{user.id}, #{user.name}, #{user.address})
      </foreach>
  </insert>
  Copy the code

    @Test
    public void testInsertAll(a) {
      List<User> users = new ArrayList<>();
      for (int i = 0; i < 1000; i++) {
        users.add(new User(i + 1, UUID.randomUUID().toString().substring(0.5), UUID.randomUUID().toString().substring(0.10)));
      }
      try (SqlSession sqlSession = Utils.getSqlSession()) {
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int res = mapper.insertAll(users);
        sqlSession.commit();
        if (res > 0)
          System.out.println("Insert successful");
        else
          System.out.println("Insert failed"); }}Copy the code

Sixth, the script

• To use dynamic SQL in annotated mapper interface classes, you can use script elements (annotation development configuration, see more about thisAnnotations to develop)

  @Update({"" })
    int updateAuthorValues(@Param("author") String author, @Param("id") int id);
  Copy the code

Seven, the bind

• bindThe tag allows you to create a variable, preprocess the variable, and concatenate it in a statement, often used in fuzzy queries

  <select id="selectBlogsByFuzzyQuery" resultType="blog" parameterType="string">
      <bind name="pattern" value="'%' + content + '%'"/>
      select *
      from blog
      where author like #{pattern}
  </select>
  Copy the code

  The fuzzy query contains the content content

    @Test
    public void selectBlogsByFuzzyQueryTest(a) {
      try (SqlSession sqlSession = Utils.getSqlSession()) {
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        List<Blog> zhang = mapper.selectBlogsByFuzzyQuery("Zhang"); zhang.forEach(logger::info); }}Copy the code