Dynamic SQL

if

  • Include part of the WHERE clause depending on the condition

    <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>

  • Do not AND the first condition immediately following < where >

    choose-when-otherwise

  • You don’t use all of them, you just want to choose one of several to use

    <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

  • The where element inserts a WHRER substatement only if the child element returns the content

  • If the substatements begin with AND OR OR, the WHERE 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 customize the functionality of the Where element by customizing the trim element

    • For example, the custom trim element is equivalent to the where element. Removes everything specified in the PrefixOverrides property and inserts everything specified in the Prefix Overrides property:

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

      The PrefixOverrides property ignores sequences of text that are separated by pipes, and there must be Spaces between different sequences of text.

      • A custom trim element that is equivalent to the set element. Overrides suffix value Settings, and customizes prefix values:
      <trim prefix="SET" suffixOverrides=",">
    ...
</trim>

      set

  • The one used to dynamically update statements is called a set

  • The set element can be used to dynamically include columns that need to be updated, ignoring columns that do not

    <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>

  • The set element dynamically inserts the set keyword at the beginning of the line and removes the extra commas that are introduced when conditional statements are used to assign values to columns

    foreach

  • Use foreach when traversing collections, especially when building IN conditional 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 the collection item and index variable that can be used in the element body
    • Specifies the string that starts with open and ends with close, and the separator between the collection item iteration
    • Foreach does not mistakenly add extra delimiters

  • When using foreach:

    • Any iterable object, such as a List,Set,Map, or array, can be passed to foreach as a collection parameter

    • When using iterable objects or arrays:

      • Index is the ordinal number of the current iteration
      • The value of item is the element retrieved in this iteration

    • When using a collection of Map objects or Map.Entry objects

      • The index is the key

      • The item’s value

        script

  • If you want to use dynamic SQL statements in an annotated interface class, use the script element

    @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

  • You can use the bind element to create a variable outside of the OGNL expression and bind it to the context

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

    Multiple database support

  • If the DatabaseID is configured, you can use a variable named “_DatabaseID” in your dynamic code 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>

    Script languages are inserted into dynamic SQL

  • MyBatis version 3.2 starts to support insert scripting language
  • Allows you to insert a language driver and write dynamic SQL queries based on that language

  • Insert the language by implementing the LANGUAGeDriver interface:

    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 realizing the custom language driver, it can be set as the default language in the mybatis-config. XML file:

    <typeAliases>
  <typeAliase type="com.oxford.MyLanguageDriver" alias="myLanguage" />
</typeAliases>
<settings>
  <setting name="defaultScriptingLanguage" value="myLanguage" />
</settings>

  • You can also use the lang attribute to specify a language for a specific statement:

    <select id="selectBlog" lang="myLanguage">
  SELECT * FROM BLOG
</select>

  • Or use the @lang annotation on the Mapper interface:

    public interface Mapper {
  @Lang(MyLanguageDriver.class)
  @Select("SELECT * FROM BLOG")
  List<Blog> selectBlog();
}

  • MyBatis all XML tags in the XML document is provided by default MyBatis language, is driven by language org. Apache. The ibatis. Scripting. Xmltags. XmlLanguageDriver, alias as XML. Provide.

    Java API

  • The execution method of MyBatis is in the SQLSession class

    Statement execution method

  • These methods are used to execute SELECT,INSERT,UPDATE, and DELETE statements defined in the SQL mapping XML file

    • Each method receives the statement ID and a parameter object

    • Parameters can be primitive types (with auto-boxing support), wrapper classes, JavaBeans, POJOs, or maps

      selectOne
      <T> T selectOne(String statement, Object parameter);
      selectList
      <E> List<E> selectList(String statement, Object parameter);

  • The difference between selecTone and selectList is:

    • SelectOne must return either an object or a null value, and an exception will be thrown if more than one is returned

    • If it is not clear how many objects will be returned, use selectList

      selectCursor
      <T> Cursor<T> selectCursor(String statement, Object parameter);

  • The Cursor returns the same result as the List, except that the Cursor uses iterators to lazily load data

    SelectCursor (statement, param) {for (Entity Entity: entities) {for (Entity Entity: entities)}
    selectMap
    <K,V> Map<K,V> selectMap(String statement, Object parameter, String mapKey);

  • SelectMap converts multiple result sets to Map type values by taking one of the properties of the returned object as the key value and the object as the value value

    insert
    int insert(String statement, Object parameter);
    update
    int update(String statement, Object parameter);
    delete
    int delete(String statement, Obejct parameter);
  • If you need to see if an object exists, the best way to do this is to query for a count value, using either 0 or 1
  • Since not all statements require arguments, each of these methods has an overloaded form that does not require arguments

  • The INSERT, UPDATE, and DELETE methods return values representing the number of rows affected by the statement

    SELECT advanced version

  • Allows you to limit the range of rows returned
  • Provides 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);

  • RowBounds parameters:

    • Specifies that MyBatis skips the specified number of records and limits the number of results returned

    • The offset and limit values of the RowBounds class are passed in only during the constructor and cannot be modified at other times

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

  • ResultHandler parameters:

    • Allows you to customize the processing of the results per row
    • You can add them to lists, create maps and sets, and even discard each return value, keeping only the calculated statistics

  • ResultHandler interface:

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

  • ResultContext parameters:

    • Allows access to the result object and the number of objects that have currently been created
    • Provide a stop method that returns a Boolean. You can use this stop method to stop MyBatis from loading more results

  • There are two limitations to using ResultHandler:

    • When a method with a ResultHandler parameter is used, the data received is not cached

    • When using the high-level result-mapping set ResultMap,MyBatis will most likely need several rows of results to construct an object. If ResultHandler is used at this point, you might receive objects from the association or collection that have not been fully populated

      Clear the batch update method

  • When executorType is set to executorType.batch, you can use FlushStatements to clear out BATCH update statements cached in the JDBC driver class

    flushStatements
    List<BatchResult> flushStatements();

    Transaction control method

  • There are four ways to control transaction scope, which are not required if auto-commit is already set up or an external transaction manager is used

  • If you are using a JDBC transaction manager controlled by a Connection instance, you can use the following four methods:

    void commit();

void commit(boolean force);

void rollback();

void rollback(boolean force);
  • By default,MyBatis will not automatically commit transactions unless the insert, update, or delete methods are found to have changed the database

  • If you do not commit changes using these methods, you can pass a true value to the COMMIT and ROLLBACK method parameters to ensure that the transaction is committed properly

    • Note: In auto-commit mode or if an external transaction manager is used, setting the FORCE value does not apply to the session
  • In most cases, you don’t need to call rollback() because MyBatis will rollback without calling commit()
  • However, when you want to control transactions in detail in a session that may commit or rollback multiple times, you use the rollback operation