Dynamic SQL

if

• Include part of the WHERE clause based 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>
Copy the code

• Do not add AND to the first condition following < where >

choose-when-otherwise

• Do not use all conditions, just want to use one of 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>
Copy the code

where

• The WHERE element inserts a WHRER substatement only if the child element returns something
• If the substatement begins 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>
Copy the code

trim

• You can customize ittrimElement to customizewhereFunction of an element
  • For example, the custom trim element equivalent to the WHERE element. Removes all contents specified in the prefixOverrides attribute and inserts the contents specified in the prefix attribute:

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

  prefixOverridesProperty ignores text sequences separated by pipe charactersThere must be Spaces between different text sequences.

  • Custom trim element equivalent to set element. Overrides suffix value Settings, and custom prefix values:

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

set

• The one used for dynamically updated statements is called a set
• The set element can be used to dynamically contain columns that need to be updated, ignoring those that are 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>
Copy the code

• The set element inserts the set keyword dynamically at the beginning of the line and removes additional commas that are introduced when a conditional statement is used to assign values to a column

foreach

• Foreach is used 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>
Copy the code

• Foreach allows you to specify a collection:
  • Declares the collection item and index variables that can be used inside the element body
  • Specifies separator between the string beginning open and ending close and between the iteration of the collection item
  • Foreach does not mistakenly add extra delimiters
• With foreach:
  • Foreach can pass any iterable object, such as a List,Set,Map, or array object, as a collection parameter
  • When usingiterableorAn array ofWhen:
    • Index is the sequence number of the current iteration
    • The value of item is the element fetched in this iteration
  • When usingMapObject orMap.EntryCollection of objects
    • The index is the key
    • The item’s value

script

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

@update({""})	
})
void updateAuthorValues(Author author);
Copy the code

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>
Copy the code

Multiple database support

• If the databaseIdProvider is configured, you can use a variable named “_databaseId” in 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>
Copy the code

Insert scripting languages into dynamic SQL

• MyBatis version 3.2 now supports the insert scripting language
• Allows you to insert a language driver and write dynamic SQL queries based on that language
• Insert languages 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);
}
Copy the code

• After implementing the custom language driver, you can set it 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>
Copy the code

• You can also specify the language for a particular statement using the lang attribute:

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

• Or use @lang annotations on mapper interfaces:

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

• 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

• MyBatis is executed in the SqlSession class

Statement execution method

• These methods are used to execute the definition in the SQL mapping XML fileThe SELECT, INSERT, UPDATE and DELETEstatements
  • Each method receives the statement ID as well as the parameter object
  • Arguments can be primitive types (auto-boxing supported), wrapper classes, Javabeans, POJOs, or Maps

selectOne

<T> T selectOne(String statement, Object parameter);
Copy the code

selectList

<E> List<E> selectList(String statement, Object parameter);
Copy the code

• Selectones differ from selectLists:
  • SelectOne must return either an object or a null value. If more than one value is returned, an exception is thrown
  • If you don’t know how many objects will be returned, use a selectList

selectCursor

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

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

try (Cursor<Entity> entities = session.selectCursor(statement, param)) {
	for (Entity entity : entities) {
		// Process a single entity}}Copy the code

selectMap

<K,V> Map<K,V> selectMap(String statement, Object parameter, String mapKey); 
Copy the code

• SelectMap converts multiple result sets to map-type values by returning one of the object’s attributes as a key and the object as a value

insert

int insert(String statement, Object parameter);
Copy the code

update

int update(String statement, Object parameter);
Copy the code

delete

int delete(String statement, Obejct parameter);
Copy the code

• 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 parameters, these methods have an overloaded form that requires no parameters
• The INSERT, UPDATE, and DELETE methods return values representing the number of rows affected by the statement

Select Advanced Version

• Allows limiting 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);
Copy the code

selectCursor

<T> List<T> selectCursor(String statement, Object parameter, RowBounds rowBounds);
Copy the code

selectMap

<K, V> Map<K,V> selectMap(String statement, Object parameter, String mapKey, RowBounds rowBounds);
Copy the code

select

void select(String statement, Object parameter, ResultHandler<T> handler);
Copy the code

select

void select(String statement Object parameter, RowBounds rowBounds, ResultHandler<T> handler);
Copy the code

• RowBoundsParameters:
  • Specifies that MyBatis skips a specified number of records and limits the number of results returned
  • The values of offset and limit of the RowBounds class are passed in only during constructor time and cannot be changed otherwise

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

• ResultHandlerParameters:
  • Allows you to customize the processing of each line result
  • You can add them to lists, create maps and sets. You can even discard each returned value, keeping only the calculated statistics
• ResultHandler interface:

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

• ResultContextParameters:
  • The number of resulting objects and currently created objects that are allowed access
  • Provide a stop method that returns Boolean. You can use this stop method to stop MyBatis from loading more results
• useResultHandlerNote two limitations:
  • When using a method with a ResultHandler parameter, the received data is not cached
  • When using an advanced result mapping set, resultMap, It is likely that MyBatis will need several rows of results to construct an object. If you use a ResultHandler at this time, you may receive objects from the association or collection that have not been fully populated

Clear the batch update method

• BATCH When ExecutorType is set to executorType. BATCH, you can use flushStatements to flush BATCH update statements cached in JDBC driver classes

flushStatements

List<BatchResult> flushStatements(a);
Copy the code

Transaction control method

• There are four ways to control the scope of a transaction, and you don’t need to use these methods if you have automatic commit 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(a);

void commit(boolean force);

void rollback(a);

void rollback(boolean force);
Copy the code

• By default,MyBatis does not commit transactions automatically unless the database has been changed by calling the insert, update, or delete methods
• If you don’t commit your changes using these methods, you can use thecommitandrollbackMethod parameter passingtrueValue to ensure that the transaction is committed properly
  • Note: Setting force does not apply to session in auto-commit mode or when an external transaction manager is used
• In most cases, there is no need to call rollback() because MyBatis will complete the rollback without calling COMMIT ()
• However, when you want to control transactions in detail in a session that can commit or rollback multiple times, use the rollback operation