Mybatis dynamic SQL and cache mechanism example explanation

Sometimes it is necessary to dynamically splice SQL statements according to the parameters to be queried

Common labels:

-If: character judgment

-Choose [when... Otherwise]: branch selection

-Trim [where, set]: String interception, where the where tag encapsulates query conditions and the set tag encapsulates modification conditions

- foreach：

If case

1) Add a method to the employeemapper interface file

2) If you want to write the following SQL statements, as long as they are not empty, they will be used as query conditions, as shown below. In fact, there is a problem in writing them, so we want to write them as dynamic SQL statements:

3) Rewrite the if tag to dynamic SQL as follows:

Test: judgment expression (ognl): ognl refers to PPT or official documents.

Test takes the value from the parameter to judge

When encountering special symbols, you should write escape characters, such as < > respectively <, >

4) Test code

But carefully speaking, there is a problem with the above SQL statements. When we do not pass the ID value to the dynamic SQL statements, there will be a problem in the assembly of SQL statements! [name preceded by an and]

-Where tag

terms of settlement

1. Add 1 = 1 after where, and XXX can be used for future conditions

2. You can use the where tag to include all query criteria

Mybatis will remove the SQL assembled in the where tag and the extra and or!

3. Note: the where tag will only remove the first extra and or

In other words, using the where tag sometimes can't solve the problem. What should I do? We can use trim tag here!

-Trim tag: you can customize the string interception rules

The extra and or where tags in the back cannot solve the problem of prefix = "": prefix: trim. The tag body is the result of the whole string concatenation. Prefix adds a prefix to the whole string after concatenation prefixoverrides = "": prefix overlay: remove the redundant characters in front of the whole string suffix = "": suffix suffix add a suffix suffix suffix overrides = "": suffix overlay: remove the redundant characters behind the whole string

-Choose tag: branch selection, similar to the switch with break in Java case

It is equivalent to jumping out after ensuring that the first case meets the requirements

Case presentation:

1. Add a method in the employeemapper interface

2. SQL mapping file

-Set tag: String interception, which can be written in trim

The set element will dynamically prefix the set keyword and eliminate irrelevant commas

1) Add an updated method in employeemapper

2) In the SQL mapping file, fill in the corresponding SQL statement, as shown below [set tag can remove the comma after the field]

3) The test class code is

Replace the set tag with the trim tag

-Foreach: traversing elements

Another common operation of dynamic SQL is to traverse a collection, usually when building in conditional statements!

The foreach element allows you to specify a collection, declare collection items and index variables, specify open and close matching strings, and place separators between iterations.

Case presentation:

1. Add a method in the employeemapper interface

2. Write the corresponding code in the SQL mapping file of mybatis

3. Test code

Foreach tags can also be used to save data in batches,

1. Add a batch insert method in the employeemapper interface class

2. In employeemapper Add a response statement to the SQL mapping file of XML

Collection is used in foreach. Collection is the parameter passed from mapper interface, and separator is to remove the intermediate symbol

3. Test code

Mybatis caching mechanism

Mybatis includes a very powerful query caching feature that can be easily configured and customized. Caching can greatly improve query efficiency.

Only in mybatis, not in SSM integration file, because sqlsession is defined in bean Sqlsession cannot be redefined in XML

Two level cache is defined by default in mybatis system.

L1 cache and L2 cache

L1 cache: (local cache): sqlsession level cache. L1 cache is always on and cannot be closed. Methods are not shared!

The data queried during the same session with the database is placed in the local cache.

In the future, if you need to obtain the same data, you can get it directly from the cache. There is no need to query the database;

L2 cache (global cache):

C1. By default, only the first level cache (sqlsession level cache, also known as local cache) is enabled.

C2. L2 cache needs to be manually enabled and configured. It is based on namespace level cache.

C3. To improve scalability. Mybatis defines the cache interface cache. We can customize the L2 cache by implementing the cache interface.

L1 cache:

Case: Test L1 cache [enabled by default]

A select query statement will be returned,

True will be returned, indicating that EMP and emp2 are caches, not lookups

There are [4 kinds] of L1 cache invalidation (if the current L1 cache is not used, the effect is that you need to query the database again)

1. Unlike sqlsession, redefine sqlsession

Two select statements will be returned

False will be returned, indicating that emp2 is not the cache of EMP

2. The sqlsession is the same, but the query conditions are different [this data does not exist in the current cache]

It is equivalent to searching again according to different conditions

3. The sqlsession is the same, but the addition, deletion and modification are performed between the two queries [this addition, deletion and modification may have an impact on the current data]

Because the cache is automatically refreshed by default

4. The sqlsession is the same. The L1 cache is cleared manually [cache empty]

The cache was cleared manually, so it had to be looked up again

L2 cache:

[global cache]: cache based on namespace level: one namespace corresponds to one L2 cache.

[the range of L1 cache is still too small. Every time sqlsession is closed, the data in L1 cache disappears]

So from this point of view: the cache that can cross sqlsession is L2 cache!

Working mechanism:

1. For a session, query a piece of data, and the data will be placed in the first level cache of the current session.

2. If the session is closed, the data in the L1 cache will be saved to the L2 cache; The new session query information can be referenced in the L2 cache.

The data found in different namespaces will be placed in their corresponding cache (map)

Effect: the data is fetched from the L2 cache

The detected data will be placed in the L1 cache by default.

Only after the session is committed or closed, the data in the L1 cache will be transferred to the L2 cache.

Note: in which mapper If the < cache > cache tag is enabled in the XML file, the secondary cache is enabled in which mapper.

Case:

1) Enable global L2 cache configuration:

2) Go to mapper Configuring the use of L2 cache in XML

Where attribute:

Occurrence = "FIFO": cache recycling policy:

Least recently used by LRU C: remove objects that have not been used for the longest time.

FIFO C first in first out: remove objects in the order they enter the cache.

Soft C soft reference: removes objects based on garbage collector status and soft reference rules.

Weak C weak references: more actively remove objects based on garbage collector state and weak reference rules.

The default is LRU.

Flush interval: cache refresh interval? How often is the cache emptied? It is not emptied by default. Set a millisecond value.

Size: number of references, positive integer, 1024 by default

Represents the maximum number of objects that can be stored in the cache. Too large can easily lead to memory overflow

Readonly: whether to read only, true / false

True: read only cache; Mybatis believes that all operations to get data from the cache are read-only and will not modify the data.

In order to speed up the retrieval speed, mybatis will directly give the reference of data in the cache to the user. Unsafe, fast.

False: not read-only: mybatis thinks the acquired data may be modified.

Mybatis will clone one using serialization deserialization technology. Safe, slow. Type: specify the full class name of the custom cache and implement the cache interface!

3) Our POJO needs to implement the serialization interface [implements serializable]

4) The previous sqlsession object must be closed first

Test:

You can see that only one SQL statement is sent, and the data obtained from the secondary cache during the second query does not send a new SQL statement.

It should be noted that the L2 cache can only be used when the L1 cache is closed.

Note: in which mapper If the < cache > cache tag is enabled in the XML file, the secondary cache is enabled in which mapper.

Cache related settings / properties:

1) Cacheenabled = "true": false: close the cache (L2 cache is closed) [L1 cache is always available]

2) Each select tag has usecache = "true";

False: do not use cache (L1 cache is still used, L2 cache is not used)

3) Each addition, deletion and modification tag has a flushcache = "true": after the addition, deletion and modification are completed, the cache will be clear [the L1 and L2 caches will be emptied]

Query label: flushcache = "false"

If flushcache = true; The cache will be emptied before each query. The cache is not used!

summary

The above is a detailed explanation of mybatis dynamic SQL and caching mechanism introduced by Xiaobian. I hope it will be helpful to you. If you have any questions, please leave me a message and Xiaobian will reply to you in time. Thank you very much for your support for the programming tips website!