MyBatis 07-Dynamic SQL-Caching Mechanism-Reverse Engineering-Paging Plug-in for Java Framework

Keywords: Java Mybatis SQL Ehcache xml


You are studying on the first day of the New Year!Don't learn what to do, fight the landlords... people are not together.Learn, study makes me happy!In addition to the responsibility of poems and distant places, I also want to be responsible, but concubines can not do it. What should I do?What do you say? Why can someone else not do it because they do a little more than you do.What point?Just a little bit, as long as you do a little bit more, you won't do it!... Just a little bit. I've looked back at SE.Wan Zhang Tall Building rises flat, the foundation is firmly established, afraid of any storm

MyBatis Dynamic SQL

MyBatis provides dynamic SQL to solve the problem of splicing SQL statements through some uncertain conditions. Specifically, it provides labels <if> <where> <trim> <set> <select> <foreach> etc. Write extensible SQL statements

MyBatis uses powerful OGNL-based expressions to simplify operations

OGNL (Object Graph Navigation Language) Object Graph Navigation Language is a powerful expression language through which object attributes can be easily manipulated.Similar to an EL expression, for example:

Access Object Properties: *

Call method:) person.getName()

Call static properties/methods: @java.lang.Math@PI

Call the construction method: * new com.bean.Person('admin').name

Operator: +, -*, /,%

Logical Operator: * in, not in, >, >=, <, <=, ==,!=

Note: Escape characters are required for special symbols in xml such as ", >, <and so on


1) if>: used to complete simple judgments. There is only one attribute, test, used to determine whether a condition is valid

2) <where>: Add the WHERE keyword to the SQL statement to remove and / or before the first condition after where.

    <select id="getBook" resultType="main.beans.Book">
        SELECT id,title,author,price
        FROM books
            <if test="id != null"> and id= #{id}</if>
            <if test="title != null"> and title = #{title}</if>

3) <trim>: You can add content before and after a conditionally determined SQL statement or remove the specified content. Remove the first or last

Prefix: add prefix prefix prefix xOverrides: remove prefix

Suffix: add suffix; suffix xOverrides: remove suffix

    <delete id="deleteBook">
        DELETE FROM books
        <trim prefix="WHERE" suffixOverrides="and">
            <if test="id != null">id = #{id} and</if>
            <if test="title != null">itle = #{title} and</if>
        </trim >

4) <set>: In the modified operation, remove the commas that may be present in the last sentence of the SQL statement, that is, the commas that may be present in the last sentence of the SQL statement

    <update id="updateBook">
        UPDATE books
            <if test="title != null">title = #{title},</if>
            <if test="author != null">author = #{author},</if>
        where id = #{id}

5) The <sql> tag is used to extract reusable SQL fragments. Frequently used SQL fragments are extracted, not only whole SQL statements, but also fields.

id: Specifies that the extracted sql fragment unique identity is referenced

Quote: use <include refid="id ID ID ID ID ID ID ID ID id"></include>in any statement that needs to insert this sql fragment

    <sql id="bookFields">
    <insert id="insertBook" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO books (<include refid="bookFields"></include>)

6) Choose > <when> <otherwise>: Used for branch judgment, only one of the branches will be satisfied eventually. Similar to switch case statement.

    <select id="selectBookPrice" resultType="main.beans.Book">
        SELECT <include refid="bookFields"></include>
        FROM books
                <when test="id != null">id = #{id}</when>
                <otherwise>price > #{price}</otherwise>

7) <foreach>: mainly for circular iteration

collection: collection to iterate over

item: Variable for element assignment currently iterated out of the set

open: Start Character

close:End Character

Separator: Specifies the separator between elements


Iterates over a List collection: index denotes the subscript of the current element

* Iterated Map Collection: index denotes the key of the current element

Note: This is a bulk operation that requires adding allowMultiQueries=true to the url of the properties configuration to open the batch

    <select id="getBooks" resultType="main.beans.Book">
        SELECT <include refid="bookFields"></include>
        FROM books where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")" >

MyBatis Cache Mechanism

MyBatis contains a very powerful query cache feature that can be easily configured and customized.Caching can greatly improve query efficiency

2-level cache is defined by default in MyBatis system: 1-level cache, 2-level cache

By default, only one level of cache (SqlSession level cache, also known as local cache) is turned on.

Level 2 caches need to be manually turned on and configured; they are namespace-based caches.To improve scalability.MyBatis defines a cache interface Cache that supports third-party caching.

Level 1 Cache

1) Local Cache, which is the local Cache with the default scope of sqlSession.Each sqlSession object has its own first-level Cache and is not shared independently of each other.When Session flush or close, all Caches in that Session will be emptied.

2) The local cache cannot be turned off, but clearCache() can be called to empty the local cache or to change the scope of the cache.

3) After mybatis 3.1, you can configure the scope of the local cache. Configure in mybatis.xml

4) The working mechanism of the first level cache, where any data queried during the same session is stored in a Map of the current SqlSession

key: hashCode+Query SqlId+Written sql Query Statement+Parameters

Caching mechanism:

Based on the same sqlSession queries multiple times, each query will first get data from the cache, if no data is obtained from the cache, it will get data from the database, and then store the data in the first level cache.

Failure of Level 1 Cache

1) Different SqlSession s correspond to different first-level caches

2) Same SqlSession but different query conditions

3) Any addition or deletion was performed during two queries of the same SqlSession
Additions and deletions empty the cache by default.

4) Manually empty the cache during two queries of the same SqlSession

Summary: When the same SqlSession queries the same statement multiple times without adding or deleting changes or manually refreshing, closing or emptying the clearCache() cache, the data will be retrieved directly from the cache.If the second level cache is turned on, it will read from the second level cache first. If the second level cache is not read from the first level cache anymore, if you only want to read from the first level cache, you can set useCache="false" in the corresponding <select>configuration properties to turn off the current second level cache, and note that adding or deleting does not involve caching mechanism

Secondary Cache

Default off, use needs to satisfy three conditions to be turned on

Second level cache: namespace level cache (sqlSessionFacotry) level cache, which has a broader scope but needs to be committed or closed within sqlSessionFacotry before it takes effect.That is, the cache in sqlSession is stored in the secondary cache.

Steps for using a secondary cache:

(1) Open secondary cache in global configuration file <setting name="cacheEnabled" value="true"/>

(2) Configure the cache <cache /> using the cache tag in the mapping file (<mapper>configuration file) that needs to use the secondary cache

(3) Note: POJO needs to implement Serializable interface

<cache> Label Properties

eviction="LRU": Cache Recycling Policy: LRU is the default.

LRU - least recently used: removes objects that have not been used for the longest time.

FIFO - FIFO: Remove objects in the order they enter the cache.

SOFT - Soft Reference: Remove objects based on garbage collector status and soft reference rules.

WEAK - Weak References: Remove objects based on garbage collector status and weak reference rules more actively.

flushInterval: refresh interval in milliseconds

Default is not set, that is, there is no refresh interval, and the cache refreshes only when the statement is invoked

(3) size: number of references, positive integer

Represents the maximum number of objects a cache can store, which is too large to cause memory overflow

readOnly: Read-only, default is false.Setting true improves efficiency but is not required for safety

_type: Introducing a cache class library (third-party caching)

Other related property settings

(1) cacheEnable for global setting s:

Configure the switch for the secondary cache, which is always on.

2) The useCache attribute of the select tag:

Configure whether this select uses a secondary cache.Level 1 cache is always used

3) flushCache property of SQL tag:

Add or delete to change the default flushCache=true.After sql executes, both primary and secondary caches are cleared.

Query default flushCache=false.

4) sqlSession.clearCache(): used only to clear the first level cache.

Secondary Cache Mechanism

Based on the same sqlSessionFactory, multiple queries take precedence over secondary caches to obtain data. Secondary caches do not get data, secondary caches do not get data, primary caches do not get data, and query data directly in the database.

After querying, store the data directly in the first level cache, and when sqlSession is committed or closed, the data in the first level cache is cached in the second level cache.

EhCache third-party cache

EhCache is a Java-only in-process cache framework that is fast, compact, and the default CacheProvider in Hibernate

Steps to integrate EhCache caches:

(1) Import ehcache packages, as well as consolidation packages, log packages



(2) Write ehcache.xml configuration file

Configuring cache tags

      <cache type="org.mybatis.caches.ehcache.EhcacheCache"></cache>

Third-party caches, like second-level caches, require setting s with cacheEnable s set to true and serialization interface Serializable

<?xml version="1.0" encoding="UTF-8"?>
<ehcache xmlns:xsi=""
 <!-- Disk Save Path -->
 <diskStore path="F:\code\mysql\ehcache" />

MyBatis Reverse Engineering

MyBatis Generator, or MBG, is a custom code generator for MyBatis framework users that can quickly generate corresponding mapping files, interfaces, and bean classes from tables.Supports basic add-delete checks and QBC-style conditional queries.But definitions of these complex sql s, such as table joins, stored procedures, and so on, need to be written manually

Mybatis uses reverse engineering steps
1. Add jar packages related to reverse engineering.


2. Configure reverse engineering configuration files: mbg.xml ==> generated version, javaBean, Mapper interface, mapping file generation strategy, analyzed table.

The mbg.xml file is placed directly under the project project project root directory for easy path configuration

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-// MyBatis Generator Configuration 1.0//EN"
            targetRuntime: Execute the generated reverse engineering version
                  MyBatis3Simple: Generate Basic CRUD
                  MyBatis3: Generate Conditional CRUD
    <context id="DB2Tables" targetRuntime="MyBatis3">
        <!--        Database Connection Settings-->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
        <!-- javaBean Generation strategy bean Storage Path-->
        <javaModelGenerator targetPackage="main.beans" targetProject=".\src">
            <property name="enableSubPackages" value="true"/>
            <property name="trimStrings" value="true"/>
        <!-- SQL Generation Policy for Mapping Files mapper.xml File Path-->
        <sqlMapGenerator targetPackage="main.mapper" targetProject=".\conf">
            <property name="enableSubPackages" value="true"/>
        <!-- Mapper Interface Generation Policy mapper Interface Path-->
        <javaClientGenerator type="XMLMAPPER" targetPackage="main.mapper" targetProject=".\src">
            <property name="enableSubPackages" value="true"/>
        <!-- Table name corresponding to reverse analysis javaBean name-->
        <table tableName="books" domainObjectName="Book"></table>
        <table tableName="users" domainObjectName="User"></table>

3. Execute the generated code.

    public void testMbg() throws Exception {
        List<String> warnings = new ArrayList<String>();
        boolean overwrite = true;
        File configFile = new File("mbg.xml");
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(configFile);
        DefaultShellCallback callback = new DefaultShellCallback(overwrite);
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config,callback, warnings);

PageHelper Paging Plugin

PageHelper is a very convenient third-party paging plugin for MyBatis.Two very powerful class libraries, PageHelper and PageInfo, are provided internally.

Use steps

1) Import related packages pagehelper-5.0.0.jar and jsqlparser-0.9.5.jar

2) Configure paging plug-ins in the MyBatis global configuration file, note that label locations are not out of order

        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>

3) Use: Open a paging query before querying a collection

        Page<Book> page = PageHelper.startPage(3,2 );
        List<Book> books = mapper.selectByExample(bookExample);
        //Query out the current page 3, which shows two pieces of book information per page, and get more page number related values
        books.forEach((book -> System.out.println("book = " + book)));
        PageInfo<Book> info = new PageInfo<>(books,3);
        System.out.println("=============Get information about detailed paging=================");
        System.out.println("Current Page: " + info.getPageNum());
        System.out.println("PP or P: " + info.getPages());
        System.out.println("Total number of bars: " + info.getTotal());
        System.out.println("Number of bars per page: " + info.getPageSize());
        System.out.println("Is it the first page: " + info.isIsFirstPage());
        System.out.println("Is it the last page: " + info.isIsLastPage());
        System.out.println("Is there a previous page: " + info.isHasPreviousPage());
        System.out.println("Is there a next page: " + info.isHasNextPage());
        System.out.println("============Paging logic===============");
        int[] nums = info.getNavigatepageNums();
        for (int num : nums) {
            System.out.println("num = " + num);

Page Object

Set paging information by PageHelper.startPage (number of pages, bars) before querying, which returns Page objects

- PageInfo object

After querying the data, encapsulating the query results with a PageInfo object allows you to obtain more detailed paging information and complete paging logic

SSM Profile

Posted by smith.james0 on Fri, 24 Jan 2020 22:45:11 -0800