Use the Batis parsing framework! Basic introduction to database related APIs

Time:2021-7-30

Dynamic SQL

if

  • Include according to conditionswherePart of clause

    <select id="findActiveBlogLike" resultType="Blog">
      SELECT * FROM BLOG WHERE state = 'ACTIVE'
      <where>
          <if test="title != null">
              AND title like #{title}
          </if>
          <if test="author != null and author.name != null">
              AND author_name like #{author.name}
          </if>
      </where>
    </select>
  • Then< where >Don’t add the first conditionAND

    choose-when-otherwise

  • Don’t use all conditions, just want to select one from multiple conditions

    <select id="findActiveBlogLike" resultType="Blog">
      SELECT * FROM BLOG WHERE state = 'ACTIVE'
      <choose>
          <when tset="title != null">
              AND title like #{title}
          </when>
          <when test="author != null and author.name != null">
              AND author_name like #{author.name}
          </when>
          <otherwise>
              AND featured = 1
          </otherwise>
      </choose>
    </select>

    where

  • whereThe element is inserted only if the child element returns contentWHRERChild statement of
  • If the beginning of the sub statement isANDperhapsOR,whereThe element removes these

    <select id="findActiveBlogLike" resultType="Blog">
      SELECT * FROM BLOG
      <where>
          <if test="state != null">
              state = #{state}
          </if>
          <if test="title != null">
              AND title like #{title}
          </if>
          <if test="author != null and author.name != null">
              AND author_name like #{author.name}
          </if>
      </where>
    </select>

    trim

  • You can customizetrimElement to customizewhereFunction of element

    • Like andwhereElement equivalent customizationtrimElement. Will remove allprefixOverridesProperty and insertprefixProperty:

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

      prefixOverridesProperty ignores text sequences separated by pipe characters, there must be spaces between different text sequences

      • andsetElement is equivalent to the custom trim element. It overrides the suffix value setting, and the custom prefix value:
      <trim prefix="SET" suffixOverrides=",">
          ...
      </trim>

      set

  • The method used to dynamically update statements is calledset
  • setElement can be used to dynamically include columns that need to be updated, ignoring columns that do not need to be updated

    <update id="updateAuthorIfNecessary">
      update Author
          <set>
              <if test="username != null">
                  username = #{username},
              </if>
              <if test="password != null">
                  password = #{password},
              </if>
              <if test="email != null">
                  email = #{email},
              </if>
              <if test="bio != null">
                  bio = #{bio}
              </if>
          </set>
      where id = #{id}
    </update>
  • setThe element inserts the beginning of the line dynamicallySETKeyword, and extra commas are deleted, which are introduced when using conditional statements to assign values to columns

    foreach

  • Used when traversing a collectionforeach,Especially in buildingINConditional statements

    <select id="selectPostIn" resultType="domain.blog.Post">
      SELECT * 
      FROM POST p
      WHERE ID IN
      <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
          #{item}
      </foreach> 
    </select>
  • Foreach allows you to specify a collection:

    • Declares a collection item that can be used within an element bodyitemAnd indexindexvariable
    • Specify startopenAnd endingcloseString and separator between iterations of collection itemsseparator
    • Foreach does not incorrectly add extra delimiters
  • When using foreach:

    • Any iteratable object, such asList, set, map object or array objectPassed as a collection parameter toforeach
    • When usedIteratable objectperhapsarrayWhen:

      • indexIs the sequence number of the current iteration
      • itemThe value of is the element obtained in this iteration
    • When usedMapObject orMap.EntryCollection of objects

      • indexYes key
      • itemIs value

        script

  • If you want to use dynamic SQL statements in annotated interface classes, you can usescriptelement

    @update({"<script>",
              "update Author",
                  "<set>",
                      "<if test='username != null'>username=#{username},</if>",
                      "<if test='password != null'>password=#{password},</if>",
                      "<if test='email != null'>email=#{email},</if>",    
                  "</set>",
                  "where id=#{id}",
                  "</script>"})    
    })
    void updateAuthorValues(Author author);

    bind

  • have access tobindElement creates a variable outside the ognl expression and binds it to the context

    <select id="selectBlogsLike" resultType="Blog">
      <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
      SELECT * FROM BLOG
      WHERE title LIKE #{pattern}
    </select>

    Multi database support

  • If configureddatabaseIdProvider,You can use the name in dynamic code“_databaseId”To build specific statements for different databases

    <insert id="insert">
      <selectKey keyProperty="id" resultType="int" order="BEFORE">
          <if test="_databaseId == 'oracle'">
              select seq_users.nextval from dual
          </if>
          <if test="_databaseId == 'db2'">
              select nextval for seq_users from sysibm.sysdummy1
          </if>
      </selectKey>
      insert into users values (#{id}, #{name})
    </insert>

    Insert script language into dynamic SQL

  • Mybatis version 3.2 supports insert scripting language
  • Allows you to insert a language driver and write dynamic SQL queries based on this language
  • Through implementationLanguageDriverInterface insertion language:

    public interface LanguageDriver {
      ParameterHandler createParameterHandler(MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql);
      
      SqlSource createSqlSource(Configuration configuration, XNode script, Class<?> parameterType);
      SqlSource crateSqlSource(Configuration configuration, String script, Class<?> parameterType);
    }
  • After implementing the custom language driver, you canmybatis-config.xmlSet as default language in file:

    <typeAliases>
      <typeAliase type="com.oxford.MyLanguageDriver" alias="myLanguage" />
    </typeAliases>
    <settings>
      <setting name="defaultScriptingLanguage" value="myLanguage" />
    </settings>
  • You can also uselangProperty to specify the language for a specific statement:

    <select id="selectBlog" lang="myLanguage">
      SELECT * FROM BLOG
    </select>
  • Or use it on the mapper interface@LangNotes:

    public interface Mapper {
      @Lang(MyLanguageDriver.class)
      @Select("SELECT * FROM BLOG")
      List<Blog> selectBlog();
    }
  • All XML tags in the XML file in mybatis are provided by the default mybatis language and are language drivenorg.apache.ibatis.scripting.xmltags.XmlLanguageDriver,Alias isxml.Provided

    Java API

  • The execution method of mybatis isSqlSessionIn class

    Statement execution method

  • These methods are used to perform the operations defined in the SQL mapping XML fileSelect, insert, update and deletesentence

    • Each method receives a statementIDas well asParameter object
    • Parameters can bePrimitive type (support auto boxing), wrapper class, JavaBean, POJO or map

      selectOne
      <T> T selectOne(String statement, Object parameter);
      selectList
      <E> List<E> selectList(String statement, Object parameter);
  • The differences between selectone and selectlist are:

    • selectOneMust returnAn object or null value,If there is more than one return value, an exception will be thrown
    • If it is not clear how many objects will be returned, useselectList

      selectCursor
      <T> Cursor<T> selectCursor(String statement, Object parameter);
  • cursorCursorAnd listListThe returned results are the same except that the cursoriterator The lazy loading of data is realized

    try (Cursor<Entity> entities = session.selectCursor(statement, param)) {
      for (Entity entity : entities) {
          //Processing individual entities
      }
    }
    selectMap
    <K,V> Map<K,V> selectMap(String statement, Object parameter, String mapKey); 
  • selectMapOne of the properties of the returned object iskeyValue, using the object asvalueValue to convert multiple result sets toMapType value

    insert
    int insert(String statement, Object parameter);
    update
    int update(String statement, Object parameter);
    delete
    int delete(String statement, Obejct parameter);
  • if necessaryCheck whether an object exists,The best way isQuery a count value, using 0 or 1
  • Since not all statements require parameters, these methods have an overloaded form that does not require parameters
  • insert, updateanddeleteThe return value of the method represents the number of rows affected by the statement

    Select advanced version

  • Allows you to limit the range of returned rows
  • Provide custom result processing logic
  • It is usually used when the data set is very large

    selectList
    <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds);
    selectCursor
    <T> List<T> selectCursor(String statement, Object parameter, RowBounds rowBounds);
    selectMap
    <K, V> Map<K,V> selectMap(String statement, Object parameter, String mapKey, RowBounds rowBounds);
    select
    void select(String statement, Object parameter, ResultHandler<T> handler);
    select
    void select(String statement Object parameter, RowBounds rowBounds, ResultHandler<T> handler);
  • RowBoundsParameters:

    • appointMyBatisSkip the specified number of records and limit the number of returned results
    • The offset and limit values of the rowbounds class can only be passed in during the constructor, and cannot be modified at other times

      int offset = 100;
      int limit = 25;
      RowBounds rowBounds = new RowBounds(offset, limit);
  • ResultHandlerParameters:

    • Allows you to customize the processing of each row of results
    • You can add it to the list, create maps and sets, and even discard each return value, retaining only the calculated statistical results
  • Resulthandler interface:

    package org.apache.ibatis.session;
    public interface ResultHandler<T> {
      void handlerResult(ResultContext<? extends T> context);
    }
  • ResultContextParameters:

    • Allow access to result objects and the number of objects currently created
    • Provide a return value ofBooleanYesstopMethod, you can use thisstopMethod to stopMyBatisLoad more results
  • useResultHandlerTwo limitations should be noted:

    • Use beltResultHandlerParameter, the received data will not be cached
    • When using advanced result mapping setsresultMapTime,MyBatisIt is likely that several lines of results are required to construct an object. If it is used at this timeResultHandler,Association may be receivedassociationOr setcollectionObjects in that have not been fully populated

      Clear batch update method

  • WhenExecutorTypeSet toExecutorType.BATCHYou can useflushStatementsClear the batch update statements cached in the jdbc driver class

    flushStatements
    List<BatchResult> flushStatements();

    Transaction control method

  • There are four methods to control the transaction scope. If it has beenAutomatic commit is set or external transaction manager is used,You don’t need to use these methods
  • If in useConnectionInstance controlledJDBCTransaction manager, you can use the following four methods:

    void commit();
    
    void commit(boolean force);
    
    void rollback();
    
    void rollback(boolean force);
  • By default,MyBatisTransactions are not committed automatically unless a call is foundInsert, update or deleteMethod changes the database
  • If you do not use these methods to submit changes, you cancommitandrollbackMethod parameter passed intrueValue to ensure that the transaction is committed normally

    • be careful:In auto commit mode or with an external transaction manager, setforceValue pairsessioninvalid
  • In most cases, there is no need to callrollback(),becauseMyBatisWill not be calledcommit()Rollback operation completed on
  • However, when you want to commit or rollback multiple times in asessionTo control transactions in detail, you need to use rollbackrollbackoperation