MyBatis notes

preface

What is? Why?

MyBatis is an excellent persistence layer framework that supports customized SQL, stored procedures, and advanced mapping. MyBatis avoids almost all of the JDBC code and manual setting of parameters and fetching result sets.

MyBatis can use simple XML or annotations for configuration and raw mapping to map interfaces and Java’s POJOs (Plain Old Java Objects) to records in the database.

---

Jdbc-sql is sandwiched in Java code blocks, resulting in high coupling and hard coding internal problems. – SQL is not easy to maintain and may change frequently due to actual development requirements.

// The university code
public List<CompetitionInfo> ContestSearchByState(int state) {
    JdbcUnit jdbcUnit = JdbcUnit.getInstance();
    Connection con = jdbcUnit.createConn(); // Connect to the database
    String sql = "Select b.CompetitionName,a.StartTime,a.EndTime,a.ContestState,a.ContestObject,a.ContestexamType " +"from competition b,contest a where a.CompetitionId = b.CompetitionId and " +"a.ContestState=" + state + " GROUP BY b.CompetitionName";
System.out.println("List
      
        ContestSearchByState(int State)
       + sql);
    List<CompetitionInfo> competitionInfos = new ArrayList<CompetitionInfo>();
    CompetitionInfo competitionInfo = null;

    PreparedStatement ps = jdbcUnit.prepare(con, sql);
    try {
        ResultSet rs = ps.executeQuery(sql);
        while (rs.next()) {
            String CompetitionName=rs.getString(1);
            String StartTime=rs.getString(2);
            String EndTime=rs.getString(3);
            int ContestState=rs.getInt(4);
            int ContestObject=rs.getInt(5);
            String ContestexamType=rs.getString(6);

            competitionInfo=new CompetitionInfo(CompetitionName, StartTime, EndTime,ContestState,ContestObject,ContestexamType);
            competitionInfos.add(competitionInfo);
        }
        con.close();
        ps.close();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return  competitionInfos;
}
Copy the code

Hibernate and JPA – Long and complex SQL is not easy for Hibernate to work with. – SQL is automatically generated internally and cannot be easily optimized. – The fully automatic framework based on full mapping makes it difficult to partially map poJOs with a large number of fields. The database performance deteriorates.

// webapi
public interface TransRecordService {
    TransRecord getTransRecord(String enterpriseNum, String fgTradeNo);
    / /...
    List<TransRecord> findForActiveQuery(String enterpriseNum, String period);
    
    boolean updateState(TransRecord transRecord, WebAPIPayStateEnum webAPIPayStateEnum);
    boolean updateForQuery(String transRecordId, String state, String bankOrder, Date cycleDate);
    / /...
    boolean updateChannelType(String enterpriseNum, String fgTradeNo, String channelType);
    void updateBankOrderNo(String enterpriseNum, String fgOrderNo, String bankOrderNo);
    void updateCircPaymentNo(String enterpriseNum, String fgOrderNo, String circPaymentNo);
    / /...
}
Copy the code

Five-minute primer

1. The introduction of the jar

<! Mybatis is very lightweight, with only one core package.
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.4</version>
</dependency>

<! -- database connection package -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.48</version>
</dependency>

<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.4.0</version>
</dependency>

<! You need an adaptation package if you want to combine with Spring.
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>The 2.0.3</version>
</dependency>
Copy the code

2. Create an environment: mysql and Oracle table structures
3. Create test methods

SQL > select * from SqlSession; SQL > select * from SqlSession; SQL > select * from SqlSession
@Test
public void test1(a) throws Exception {
    String resource = "simple-mybatis-config.xml";
    InputStream inputStream =  Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();

    // The first parameter is the ID of statement in the mapping file, which is equal to =namespace+"."+ Statement ID
    // Second argument: specifies the parameter of type parameterType that matches the mapping file
    // sqlsession. selectOne Results in an object of type resultType matched in the mapping file
    QueryOrgInfoReq orgInfoReq = QueryOrgInfoReq.builder().id(2).build();
    OrgInfoPo po = sqlSession.selectOne("com.ariclee.mybatis.org.mapper.OrgInfoMapper.getBy", orgInfoReq);

    System.out.println(po);
    sqlSession.close();
}
Copy the code

Global configuration file

properties

Introduce the.properties file in Mabatis

<properties resource="external.properties">
    <! --<property name="username" value="dev_user"/>-->
    <! --<property name="password" value="F2Fa3! 33TYyg"/>-->
</properties>
Copy the code

setting

• MapUnderscoreToCamelCase: Indicates whether to enable camel name mapping

We’ll talk about that when we use it later.

typeAliases

Type aliases, renamed to short characters, reduce redundant writing of fully qualified class names. Case insensitive. Common type aliases need to be memorized roughly: simple type, with the first letter underlined. Package type, lowercase first letter.

Aliases can be batch specified using the Package subtag

<typeAliases>
    <package name="com.ariclee.mybatis.mapper"/>
</typeAliases>
Copy the code

Generally, custom aliases are not required, and abbreviations are difficult to find.

TypeHandlers:

Type handler, skipped.

plugins

Plugin, skip it.

Environments:

Configure different environments, using the default attribute to statically specify which environment to use. Such as:

<environments default="dev_oracle">
    <environment id="dev_mysql">
        <transactionManager type="jdbc"/>
        <dataSource type="POOLED">
            <property name="driver" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="JDBC: mysql: / / 172.18.12.64:3306 / SPMS? useSSL=false"/>
            <property name="username" value="rhf"/>
            <property name="password" value="rhf"/>
        </dataSource>
    </environment>

    <environment id="dev_oracle">
        <transactionManager type="jdbc"/>
        <dataSource type="POOLED">
            <property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
            <property name="url" value="JDBC: oracle: thin: @ 10.60.45.207:1521: dbtest"/>
            <property name="username" value="rhf"/>
            <property name="password" value="rhf"/>
        </dataSource>
    </environment>
</environments>
Copy the code

The environment neutron labels transactionManager and dataSource are mandatory.

• TransactionManager: the options for transactionManager type are JDBC and MANAGED.

• DataSource: type options have UNPOOLED | POOLED | JNDI. You can also customize DataSource types: implement the DataSourceFactory interface to return a custom DataSource

Just understand this part. You typically use the manager that comes with Spring to override the previous configuration.

databaseIdProvider

Database vendor id. Data vendor identification can be cumbersome, and the property tag can alias the vendor identification.

<databaseIdProvider type="DB_VENDOR">
    <property name="SQL Server" value="sqlserver"/>
    <property name="DB2" value="db2" />
    <property name="Oracle" value="oracle" />
    <property name="MySQL" value="mysql" />
</databaseIdProvider>
Copy the code

Once the alias is created, it can be referenced in a mapper file as follows:

<select id="getBy" databaseId="mysql"
        resultType="com.ariclee.mybatis.org.OrgInfoPo">
    SELECT * FROM org_info_test where 1=1 and id=#{id}
</select>

<select id="getBy" databaseId="oracle"
        resultType="com.ariclee.mybatis.org.OrgInfoPo">
    SELECT * FROM org_info_test where 1=1 and id=#{id}
</select>
Copy the code

The current geyBy method has different implementations under different databases. When connecting to Oracle database, Mybatis will automatically select the corresponding SQL implementation.

mapper

Write the SQL mapping file, registered in Mybatis.

<mappers>
    <mapper resource="./mapper/OrgInfoMapper.xml"/>
    <mapper class="com.ariclee.mybatis.org.mapper.OrgInfoAnnotationMapper"/>
</mappers>
Copy the code

When registering with the first resource form, Mybatis has no mandatory requirement on the path of mapper interface and XML file, because the mapper interface can be found in the mapping file as long as the mapping file can be located.

However, when using the second class method, there are several points to note:

• There is an XML file. The file name must be the same as that of the interface and it must be stored in the same directory
• There is no XML file, using annotations

If multiple SQL mapping files exist, you can use the package sub-tag to register them in batches.

Since the name attribute is filled in as a classpath, there is no problem with mapping statements and interfaces when using annotations.

When XML files are used, there may be situations where the XML file is not in the same folder as the interface. The problem is similar to the one above where the mapping file was not found when using class registration. To avoid this, you need to put the XML file together with the interface.java file. (You can also put it in the Resources file and create the corresponding mapper file)

<mappers>
    <package name="com.ariclee.mybatis.org.mapper"/>
</mappers>
Copy the code

The mapping file

Mapper XML file is the most important part of Mybatis. The XML file contains the following top-level elements (out of order) :

• cache: Cache configuration for the namespace.
• cache-ref: references the cache configuration of other namespaces.
• resultMap: describes how to load objects from a database result set, and is the most complex and powerful element.
• sql: A reusable block of statements that can be referenced by other statements.
• insert: mapping insert statement.
• update: mapping update statement.
• delete: mapping deletion statement.
• select: mapping query statement.

Processing parameters

# {}

For a single parameter, Mybatis does not make any special processing, just use #{}, no matter what value is entered in {}.

** Mybatis will also encapsulate parameters in Map if they are Set types such as List, Array, or Set. For Collection, the key is Collect. List and Set are more accurately encapsulated as List and Set. If it is an array, the corresponding key is array.

Mybatis encapsulates multiple parameters in Map with key param1… ParamN, #{} is the value of the Map, which looks like this:

<! -- OrgInfoPo getBy2(Integer id, String orgCode) -->
<select id="getBy2"
        resultType="com.ariclee.mybatis.org.OrgInfoPo">
    SELECT * FROM org_info_test where 1=1 and id=#{param1} and org_code=#{param2}
</select>
Copy the code

But this method is too unreadable. You can rename the Map key using the @param (“”) annotation, so you can directly access the value of the specified key parameter in the XML, as follows:

<! -- OrgInfoPo getBy2(@Param("id")Integer id, @Param("orgCode")String orgCode) -->
<select id="getBy2"
        resultType="com.ariclee.mybatis.org.OrgInfoPo">
    SELECT * FROM org_info_test where 1=1 and id=#{id} and org_code=#{orgCode}
</select>
Copy the code

If the parameter passed is an object (VO, DTO, PO, etc.), it can be accessed directly in the mapping file using #{attribute name} (also recommended); If the object is a Map, it can also be accessed using #{MapKey}.

Here are some examples of passing parameter access:

• OrgInfoPo getBy2(@Param("id")Integer id, String orgCode): id – # {id}; orgCode-#{param2}
• OrgInfoPo getBy2(Integer id, OrgInfoPo po): id – # {param1}; orgCode-#{param2.orgCode}

Best practice: Use@ParamAnnotate custom Param keys

Other attributes: javaType, jdbcType, Mode (stored procedure), numericScale, resultMap, typeHandler (processing enumeration), jdbcTypeName, Expression

While these options are powerful, most of the time you simply specify the attribute name, at most the jdbcType for columns that might be empty, and MyBatis will figure out the rest.

For numeric types, you can also set numericScale to specify the number of digits reserved after the decimal point.

#{height,javaType=double,jdbcType=NUMERIC,numericScale=2}
Copy the code

Null value handling is not recognized in Oracle environment

• #{orgName, jdbcType=VARCHAR}To a specific jdbcType, valid for a single parameter
• Specify NULL mapping in global Settings for the entire project

<! -- setting -->
<settings>
    <setting name="jdbcTypeForNull" value="NULL"/>
</settings>
Copy the code

Best practice: jdbcType is usually specified in some possibly empty column name

The ${}

Difference with #{} :

• # {}: Sets parameters to SQL statements in precompiled form
• The ${}: Retrieves values directly assembled into SQL files

<! - configuration - >
<select id="getBy3"
        resultType="com.ariclee.mybatis.org.OrgInfoPo">
    SELECT * FROM org_info_test where 1=1 and id=${id} and org_code=#{orgCode}
</select>

<! -- final SQL execution -->
SELECT * FROM org_info_test where 1=1 and id=2 and org_code=?
Copy the code

Application Scenarios:

• Dynamic table names: examples of SPMS merchant sub-tables, sub-tables by year, etc
• Sort: The name of the field following the order by statement

<insert>The label

• Increase when returning to the affected rows Integer, Long, Boolean (validation MYSQL and Oracle influence lines of difference)
• New returns the default primary key for MYSQL and Oracle
  • Automatically submit transaction issues
  • Oracle create sequence

<select>The label

attribute

attribute	describe
id	A unique identifier in the namespace that can be used to refer to this statement.Methods with the same name cannot exist, even if the parameters of the methods are different
parameterType	The class-fully qualified name or alias that will be passed for the argument to this statement.optionalBecause MyBatis can infer the parameters of a specific incoming statement from a TypeHandler, the default is unset.
resultType	The fully qualified name or alias of the class that is expected to return a result from this statement. Pay attention to,If a collection is returned, it should be set to the type contained in the collection, not the collection itself. Only one resultType and resultMap can be used simultaneously.
resultMap	Named reference to an external resultMap. Result mapping is the most powerful feature of MyBatis. If you understand it well, many complex mapping problems can be solved easily. Only one resultType and resultMap can be used simultaneously.
flushCache	Setting this to true causes the local and secondary caches to be cleared whenever a statement is called. Default: false.
useCache	Setting it to true will cause the results of this statement to be cached by the second level cache. Default: true for select elements.
timeout	This setting is the number of seconds the driver waits for the database to return the result of the request before throwing an exception. The default is unset (database driven).
fetchSize	This is a recommended value for drivers to try to make the number of rows returned per batch equal to this value. The default value is unset (dependent driver).
statementType	The value can be STATEMENT, PREPARED or CALLABLE. This will make MyBatis use Statement, PreparedStatement, or CallableStatement, respectively. The default value is PREPARED.
resultSetType	FORWARD_ONLY, SCROLL_SENSITIVE, SCROLL_INSENSITIVE, or one of DEFAULT (equivalent to unset), which defaults to unset (database driver dependent).
databaseId	If databaseIdProvider is configured, MyBatis will load all statements that do not have a databaseId or match the current databaseId. If both tagged and untagged statements are present, untagged statements are ignored.
resultOrdered	This setting applies only to nested result SELECT statements: if true, nested result sets or groups are assumed to be included, and no references to previous result sets are generated when a primary result row is returned. This keeps memory from running out when retrieving nested result sets. Default value: false.
resultSets	This setting only works with multiple result sets. It lists the result sets returned after statement execution and gives each result set a name, separated by commas.

ResultType, resultMap

ResultType: automatic encapsulation; ResultMap: User-defined javaBean encapsulation rules. Advanced result set mapping – resultType Is relatively simple. If the column name and class field name match or match the automatic mapping rules, the resultType is used. If do not, or need to use more powerful mapping choose resultMap – query statement must specify both a, but not at the same time specify log under Caused by: org. Apache. Ibatis. Executor. ExecutorException: A query was run and no Result Maps were found for the Mapped Statement ‘com.ariclee.mybatis.org.mapper.OrgInfoMapper.getBy3’. It’s likely that neither a Result Type nor a Result Map was specified.

• Return a Map where key is the column name and value is the value

<select id="getBy4" resultType="map">
    SELECT * FROM org_info_test where 1=1 and id=#{id}
</select>
Copy the code

• Return a Map: Map
  
  (MapKey)
  ,>

@MapKey("id")
Map<String, OrgInfoPo> getBy5(String orgCode);

<select id="getBy5" resultType="com.ariclee.mybatis.org.OrgInfoPo">
    SELECT * FROM org_info_test where 1=1 and org_code like #{orgCode}
</select>
Copy the code

Associative queries between entities: one to one

Assume that there is only one merchant hanging below an organization

public class OrgWithMchInfoPo {
    private MchInfoPO mch;
}
Copy the code

Mapping file resultMap

<! Object accessor implementation -->
<resultMap id="OrgInfoPoWithMchInfoMap1" type="com.ariclee.mybatis.org.OrgWithMchInfoPo">
    <id property="id" column="id"/>
    <result column="mit_id" property="mch.id"/>
    <result column="mit_code" property="mch.code"/>
    <result column="mit_name" property="mch.name"/>
</resultMap>

<! -- Association tag implementation -->
<resultMap id="OrgInfoPoWithMchInfoMap1" type="com.ariclee.mybatis.org.OrgWithMchInfoPo">
    <id property="id" column="id"/>
    <association property="mch" javaType="com.ariclee.mybatis.mch.MchInfoPO">
        <result column="mit_id" property="id"/>
        <result column="mit_code" property="code"/>
        <result column="mit_name" property="name"/>
    </association>
</resultMap>

<select id="getBy6" resultMap="OrgInfoPoWithMchInfoMap1">
    SELECT
            oit.id as id,
            oit.org_code as org_code,
            oit.org_name as org_name,
            mit.id as mit_id,
            mit.code as mit_code,
            mit.name as mit_name
    FROM org_info_test oit left join mch_info_test mit on oit.id=mit.org_id
    where 1=1 and oit.id=#{id}
</select>
Copy the code

<association>Step by step query + delay query

<resultMap id="OrgInfoPoWithMchInfoMap1_1" type="com.ariclee.mybatis.org.OrgWithMchInfoPo">
    <id property="id" column="id"/>
    <association property="mch"
                    javaType="com.ariclee.mybatis.mch.MchInfoPO"
                    select="getMchInfoByOrgId" column="id" fetchType="lazy">
    </association>
</resultMap>

<! -- Primary method -->
<select id="getBy6_1" resultMap="OrgInfoPoWithMchInfoMap1_1">
    SELECT *
    FROM org_info_test
    where 1=1 and id=#{id}
</select>
<! -- Query merchant by organization ID -->
<select id="getMchInfoByOrgId" resultType="com.ariclee.mybatis.mch.MchInfoPO">
    SELECT *
    FROM mch_info_test
    where 1=1 and org_id=#{id}
</select>
Copy the code

After the step query SQL mapping file is written, the lazyLoadingEnabled switch or the Association fetchType attribute must be used to specify it. The official website explains as follows:

LazyLoadingEnabled: global switch of lazy loading. When enabled, all associated objects are lazily loaded. The fetchType attribute can be set to override the on/off state of an item in a particular association.

{key1=v1, key2=v2}”

<association property="mch"
    javaType="com.ariclee.mybatis.mch.MchInfoPO"
    select="getMchInfoByOrgId" column="{orgId=id,orgId=id}" fetchType="lazy">
</association>
Copy the code

Associative queries between entities: one to many

Assume that an organization has multiple merchants attached to it

public class OrgWithMchInfoPo2 {
    private List<MchInfoPO> mchs;
}
Copy the code

Query a table once

<resultMap id="OrgInfoPoWithMchInfoMap2" type="com.ariclee.mybatis.org.OrgWithMchInfoPo2">
    <id property="id" column="id"/>
        <collection property="mchs" ofType="com.ariclee.mybatis.mch.MchInfoPO">
        <id column="mit_id" property="id"/>
        <result column="mit_code" property="code"/>
        <result column="mit_name" property="name"/>
    </collection>
</resultMap>
Copy the code

Collection also supports piecewise query and delayed loading, and mapping file resultMap writing

<resultMap id="OrgInfoPoWithMchInfoMap2" type="com.ariclee.mybatis.org.OrgWithMchInfoPo2">
    <id property="id" column="id"/>
    <collection property="mchs" select="getMchInfosByOrgId" column="id" />
</resultMap>
Copy the code

Examples of using channel and channel_param

discriminatordiscriminator

Determine the value of a column and change the encapsulation behavior. For example: If the id is 1, the associated merchant information is displayed. If id is 2, the orgName field is assigned.

<resultMap id="OrgInfoPoWithMchInfoMap3" type="com.ariclee.mybatis.org.OrgWithMchInfoPo">
    <id property="id" column="id"/>
    <discriminator javaType="integer" column="id">
        <case value="1">
            <association property="mch"
                    javaType="com.ariclee.mybatis.mch.MchInfoPO"
                    select="getMchInfoByOrgId" column="id" >
            </association>
        </case>
        <case value="2">
            <result property="orgName" column="org_name"/>
        </case>
    </discriminator>
</resultMap>

<select id="getBy8" resultMap="OrgInfoPoWithMchInfoMap3">
    SELECT * FROM org_info_test where 1=1 and id=#{id}
</select>
Copy the code

Examples using McH_info and xxx_mch_info

<update>The label

<update id="update1">
    update org_info_test set org_name = #{orgName} where id=#{id}
</update>
Copy the code

<delete>The label

<! -- Delete single stroke -->
<delete id="delete1">
    delete from org_info_test where id = #{id}
</delete>
<!-- 批量删除 -->
<delete id="batchDelete1">
    delete from org_info_test where id in
    <foreach collection="ids" separator="," item="id" open="(" close=")">
        #{id}
    </foreach>
</delete>
Copy the code

Dynamic SQL

If you’ve ever used JDBC or a similar framework, you can understand how painful it can be to concatenate SQL statements based on different conditions, such as making sure you don’t forget to add the necessary whitespace and removing the comma from the last column name of the list.

Dynamic SQL contains tags: if, Choose (when, otherwise), trim (WHERE, set), and foreach

OGNL expressions

The official document: commons.apache.org/proper/comm…

Common syntax:

• e1 or e2
• e1 and e2
• e1 == e2,e1 eq e2
• e1 ! = e2,e1 neq e2
• E1 lt e2: less than
• E1 LTE E2: less than or equal to other GT (greater than or equal to), GTE (greater than or equal to)
• e1 in e2
• e1 not in e2
• e1 + e2,e1 * e2,e1/e2,e1 – e2,e1%e2
• ! E,not e: negative
• E.thod (args) : Calls object methods
• E. property: value of an object property
• E1 [e2] : values by index, List, array, and Map
• @class@method(args) : Call a static method of the class
• @class@field: Static field value of the call class

Usage Scenarios:

• In dynamic SQL expressions:<if test="">,<bind value="">
• The ${}Parameter, in# {}Do not apply in

Notice the escape characters “&” and so on in XML documents

<if>The label

Conditions behind and problem solution:

1. Where 1=1
2. Mybatis recommended<where>Label (will only remove the excess in front, have to agree to put all in front)

Prefixes the condition string with the WHERE string and overrides the AND character

<trim prefix="where" prefixOverrides="" suffix="" suffixOverrides="and">
    <if test="orgCodeLike! = "">
        org_code like #{orgCodeLike} and
    </if>
</trim>
Copy the code

<choose>The label

When a condition is met, it breaks immediately and does not perform the following operations. When simultaneously meeting orgCodeLike! And id = ‘ ‘! If = null, id conditions will not be concatenated.

<select id="getBy12" resultType="com.ariclee.mybatis.org.OrgWithMchInfoPo">
    SELECT * FROM org_info_test
    <where>
        <choose>
            <when test="orgCodeLike! = "">
                and org_code like #{orgCodeLike}
            </when>
            <when test="id ! = null">
                id=#{id}
            </when>
        </choose>
    </where>
</select>
Copy the code

<set>The label

Where encapsulates query conditions and set encapsulates update conditions. The essence is to remove the extra comma problem after the update field

<update id="update3">
    update org_info_test
    <set>
        <if test="orgCode != ''">
            org_code=#{orgCode},
        </if>
        <if test="orgName != ''">
            org_name=#{orgName},
        </if>
        <if test="createTime ! = null">
            create_time=#{createTime},
        </if>
    </set>
    where id=#{id}
</update>
Copy the code

You can also use
for the same purpose

<update id="update3">
    update org_info_test
    <trim prefix="set" prefixOverrides="" suffix="" suffixOverrides=",">
        <if test="orgCode != ''">
            org_code=#{orgCode},
        </if>
        <if test="orgName != ''">
            org_name=#{orgName},
        </if>
        <if test="createTime ! = null">
            create_time=#{createTime},
        </if>
    </trim>
    where id=#{id}
</update>
Copy the code

foreachThe label

• Batch delete

<delete id="batchDelete1">
    delete from org_info_test where id in
    <foreach collection="ids" separator="," item="id" open="(" close=")">
        #{id}
    </foreach>
</delete>
Copy the code

• There are two ways to batch add data in MYSQL

<insert id="batchAdd1">
    <! -- Way 1 -->
    insert into org_info_test(org_code, org_name, create_time) values-->
    <foreach collection="pos" item="po" separator=",">
        (#{po.orgCode}, #{po.orgName}, #{po.createTime})
    </foreach>

    <! -- Need to manually open, not only can batch save, also can batch update -->
    <! -- mysql allowmultiqueries -->
    <foreach collection="pos" item="po" separator=";">
        insert into org_info_test(org_code, org_name, create_time) values
        (#{po.orgCode}, #{po.orgName}, #{po.createTime})
    </foreach>
</insert>
Copy the code

• You can add a batch table using either of the following methods: BEGIN End + temporary table

<insert id="batchAdd2">
    begin
        <foreach collection="pos" item="po">
            insert into org_info_test (id, org_code, org_name, create_time)
            values(t_org_info_test_seq.nextval, #{po.orgCode}, #{po.orgName}, #{po.createTime});
        </foreach>
    end;
</insert>
Copy the code

The second temporary is more complicated, ellipsis;

Two built-in parameters:_paramter,_databaseId

_paramter: encapsulated parameter Map. Add null judgment to improve robustness

<select id="getBy13" resultType="com.ariclee.mybatis.org.OrgWithMchInfoPo">
    SELECT * FROM org_info_test where 1=1
    <if test="_parameter ! = null">
        and id=#{id}
    </if>
</select>
Copy the code

bindThe label

<select id="getBy14" resultType="com.ariclee.mybatis.org.OrgWithMchInfoPo">
    <bind name="_orgCodeLike" value="'%'+orgCodeLike+'%'" />
    SELECT * FROM org_info_test where 1=1
        and org_code like #{_orgCodeLike}
</select>
Copy the code

sqlThe label

Extract reusable SQL fragments. Use the include tag to reference.

<sql id="common_condition">1 = 1<if test="appCodeList ! = null and appCodeList.size()>0">
        and application_code in
        <foreach collection="appCodeList" open="(" separator="," close=")" item="appCodeItem">#{appCodeItem}
        </foreach>
    </if>
</sql>

<select id="queryOverviewPageData" resultMap="OverviewPageMap">
    select
    sum(cost) as cost
    from daily_trans_report
    where <include refid="common_condition"/>
</select>
Copy the code

Caching mechanisms

MyBatis includes a very powerful query caching feature that is easy to configure and customize. Caching can greatly improve query efficiency. The MyBatis framework defines two levels of caching by default: level 1 cache and level 2 cache.

1. By default, only level 1 caching (SQLSession-level caching, also known as local caching) is enabled.
2. Level 2 cache needs to be manually enabled and configured based on namespace level cache.
3. To improve scalability. MyBatis defines the Cache interface Cache. You can customize a level 2 Cache by implementing a Cache interface.

Level 1 cache

Local cache is a local cache whose scope is sqlSession by default. ** When a Session is flushed or closed, all caches in that Session are flushed. ** You can call clearCache() to clear the local cache. If the localCacheScope value is set to STATEMENT, level-1 caching is disabled.

There are four cases of level 1 cache failure:

1. Different SQLSessions correspond to different level 1 caches
2. The same SqlSession but different query conditions
3. Any add, delete, or change operation is performed during two queries in the same SqlSession
4. The cache was manually cleared during two queries of the same SqlSession

The second level cache

Second level cache: the namespace level of global scoped cache.

• Level-2 cache is disabled by default. You need to configure it manually
• MyBatis provides the interface and implementation of level 2 cache, requiring POJO to implement Serializable interface
• The level 2 cache takes effect only after the SqlSession is closed or committed

Attributes of a cache label

<cache
  eviction="FIFO"
  flushInterval="60000"
  size="512"
  readOnly="true"/>
Copy the code

This more advanced configuration creates a FIFO cache, refreshed every 60 seconds, that can store up to 512 references to result objects or lists, and the returned objects are considered read-only, so modifying them could cause conflicts among callers in different threads. The cleanup policies available are:

• LRU – Least recently used: Removes the object that has not been used for the longest time. (the default)

• FIFO – First in, first out: Objects are removed in the order in which they enter the cache.

• SOFT – SOFT reference: Objects are removed based on garbage collector status and SOFT reference rules.

• WEAK – WEAK references: Objects are removed more aggressively based on garbage collector state and WEAK reference rules.

• The flushInterval property: can be set to any positive integer. The value should be a reasonable amount of time in milliseconds. The default is no, that is, there is no refresh interval, and the cache is flushed only when the statement is called.

• Size (number of references) property: Can be set to any positive integer, taking into account the size of the object to cache and the memory resources available in the runtime environment. The default value is 1024.

• ReadOnly property: Can be set to true or false. A read-only cache returns the same instance of the cache object to all callers. Therefore, these objects cannot be modified. This provides a significant performance boost. A read-write cache returns (through serialization) a copy of the cached object. It’s slower, but safer, so the default is false.

Procedure for enabling level 2 cache:

1. Enable in global configuration: Settings > cacheEnabled (can be omitted, default is true)
2. Open in a mapping file:<cache />
3. The entity class returned by the Mapper method needs to implement the Serializable interface

Interaction with cache configurations in other tags:

1. selectIn the labeluseCacheWhat’s on and off is level 2 caching
2. insert,update,deleteIn the labelflushCacheThis is enabled by default and affects both level 1 and level 2 caches
3. sqlSession.clearCache()This method only affects the current level 1 cache
4. In global configurationlocalCacheScopeThe default value is SESSION (level 1 caching enabled). If you set STATEMENT to STATEMENT, level 1 caching is disabled

Spring integration

Official documentation: mybatis.org/spring/gett…

1. Importing an Adaptation Package

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>The 2.0.3</version>
</dependency>
Copy the code

2. Spring General Configuration

<! -- Data source configuration -->
<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" >
    <property name="url" value="JDBC: mysql: / / 172.18.12.64:3306 / SPMS? useSSL=false"/>
    <property name="username" value="rhf"/>
    <property name="password" value="rhf"/>
</bean>

<! -- Transaction manager configuration -->
<bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource"/>
</bean>

<! - comments -- >
<tx:annotation-driven transaction-manager="txManager"/>
Copy the code

3. Mybatis adaptive configuration

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="configLocation" value="classpath:mybatis-config-with-spring.xml"/>
    <property name="mapperLocations" value="classpath:mapper/*.xml"/>
</bean>

<! -->
<mybatis:scan factory-ref="sqlSessionFactory" base-package="com.ariclee.mybatis.org.mapper"/>
<! -- Old version mode -->
<! --<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">-->
    <! --<property name="basePackage" value="com.ariclee.mybatis.org.mapper"/>-->
    <! --<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>-->
<! --</bean>-->
Copy the code

extension

Paging Plug-in (PageHelper)

Official documentation: github.com/pagehelper/…

1. The introduction of the jar

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.5</version>
</dependency>
Copy the code

2. Add the MyBatis configuration

<plugins>
    <plugin interceptor="com.github.pagehelper.PageInterceptor"/>
</plugins>
Copy the code

3. use

// Use the return value to get paging information
Page<Object> page = PageHelper.startPage(1.3);
List<OrgWithMchInfoPo> res1 = mapper.getBy16();
res1.forEach(System.out::println);

System.out.println("Total pages:" + page.getPages());
System.out.println("Total number of items:" + page.getTotal());

// Get page information after wrapping with PageInfo
PageHelper.startPage(1.3);
List<OrgWithMchInfoPo> res1 = mapper.getBy16();
PageInfo<OrgWithMchInfoPo> pageInfo = new PageInfo<>(res1);
res1.forEach(System.out::println);

System.out.println("Total pages:" + pageInfo.getPages());
System.out.println("Total number of items:" + pageInfo.getTotal());
Copy the code

Custom type handling

1. implementationBaseTypeHandlerinterface

Here is a generic enumeration processor

@MappedTypes(value = {
    SceneEnum.class
})
public class EnumValueTypeHandler<E extends LabelAndValue> extends BaseTypeHandler<E> {

    private Class<E> type;

    private final E[] enums;

    public EnumValueTypeHandler(Class<E> type) {
        if (type == null) {
            throw new IllegalArgumentException("Type argument cannot be null");
        }
        this.type = type;
        this.enums = type.getEnumConstants();
        if (this.enums == null) {
            throw new IllegalArgumentException(type.getSimpleName() + " does not represent an enum type."); }}@Override
    public void setNonNullParameter(PreparedStatement ps, int i, E parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, parameter.getValue());
    }

    @Override
    public E getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String value = rs.getString(columnName);
        if (rs.wasNull()) {
            return null;
        }
        else {
            try {
                return getEnumByValue(value);
            } catch (Exception ex) {
                throw new IllegalArgumentException(
                        "Cannot convert " + value + " to " + type.getSimpleName() + " by ordinal value.", ex); }}}protected E getEnumByValue(String value) {
        for (E e : enums) {
            if (e.getValue().equals(value)) {
                returne; }}return null;
    }

    @Override
    public E getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String i = rs.getString(columnIndex);
        if (rs.wasNull()) {
            return null;
        } else {
            try {
                return getEnumByValue(i);
            } catch (Exception ex) {
                throw new IllegalArgumentException(
                        "Cannot convert " + i + " to " + type.getSimpleName() + " by ordinal value.", ex); }}}@Override
    public E getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String i = cs.getString(columnIndex);
        if (cs.wasNull()) {
            return null;
        } else {
            try {
                return getEnumByValue(i);
            } catch (Exception ex) {
                throw new IllegalArgumentException(
                        "Cannot convert " + i + " to " + type.getSimpleName() + " by ordinal value.", ex); }}}}Copy the code

2. Specify a type handler in a global configuration file or in a single method

<! -- Global specify -->
<typeHandlers>
    <typeHandler javaType="com.ariclee.mybatis.org.SceneEnum" handler="com.ariclee.mybatis.org.EnumValueTypeHandler"/>
</typeHandlers>

<! -- Specify when in use -->
<resultMap id="OrgInfoMap" type="com.ariclee.mybatis.org.OrgInfoPo">
    <result column="scene" property="scene" javaType="com.ariclee.mybatis.org.SceneEnum" typeHandler="com.ariclee.mybatis.org.EnumValueTypeHandler"/>
</resultMap>

<insert id="insertWithEnum">
    insert into org_info_test (org_code, org_name, create_time, scene)
    values(#{orgCode}, #{orgName}, #{createTime}, #{scene, typeHandler=com.ariclee.mybatis.org.EnumValueTypeHandler});
</insert>
Copy the code

Reverse engineering

The working principle of

Plug-in development

reference

• Silicon Valley MyBatis video tutorial
• Mybatis Technology Insider
• Mybatis.org/mybatis-3/z…