Mybatis series full solution (5) : the most complete network! Mybatis Mapper mapping file

Cover: Luo Xiaoxi

Author: Pan Pan

If not forced by life, who wants to carry a talent.

preface

Mybatis configuration file XML general overview, the content is very detailed (also very boring), because the length is too long, I expect you want to read it will take two sections of the subway bus to go to work…

However, there should be a full picture of the core configuration file config.xml of Mybatis, the elements of which are the SQL mapper that we are ready to introduce in this section. The last section introduces three ways to introduce SQL mapper. In this section, we will mainly talk about its top elements.

The real power of Mybatis lies in its statement mapping, which is its magic and cornerstone. Because it is so powerful, the MAPper’s XML file is relatively simple. If you compare it to JDBC code with the same functionality, you will immediately see that nearly 95% of the code is saved (95% is what Mybatis says on its website, and I will introduce it). Mybatis aims to reduce the cost of use and allow users to focus more on SQL code.

Mybatis series full solution brain map sharing, continuous update

Mybaits Series full solutions (continuously updated)

Mybatis series full solution (a) : handwriting a set of persistent layer frame
Mybatis series full solution (2) : Mybatis introduction and environment construction
Mybatis series full solution (3) : Mybatis simple CRUD use introduction
Mybatis series full solution (four) : the most complete network! Mybatis configuration file XML overview
Mybatis series full solution (5) : the most complete network! Mybatis Mapper mapping file
Mybatis series full solution (6) : Mybatis most core API you know how many?
Mybatis series full solution (7) : Dao layer two ways to achieve
Mybatis series full solution (8) : Mybatis dynamic SQL
Mybatis series full solution (9) : Complex mapping of Mybatis
Mybatis series full solution (10) : Mybatis annotation development
Mybatis series full solution (11) : Mybatis cache full solution
Mybatis plug-in development
Mybatis series full solution (13) : Mybatis code generator
Spring integrates Mybatis
Mybatis series full solution (15) : SpringBoot integrated Mybatis
Mybatis series full solution (16) : Mybatis source code analysis

Mapper overview of the mapper’s top-level elements

Different from other ORM frameworks such as Hibernate, Mybatis framework hopes that developers can directly manipulate the database to write SQL, rather than hide it and let developers face Java objects alone. For this purpose, Mybatis designed SQL mapper.

Mapper has nine top-level elements, basic skills introduced

Select: used for query, supports parameter transmission, and returns the specified result set.
Insert: used to add, support parameter transfer, return the specified result set;
Update: Used for updating, supporting parameter transmission and returning the specified result set.
Delete: used to delete, support parameter transmission, return the specified result set.
SQL: blocks of reusable statements referenced by other statements;
Cache: current namespace cache configuration;
Cache-ref: cache configurations that reference other namespaces;
ParameterMap: parameter mapping deprecated because it is not good enough
ResultMap: result set mapping, which is good;

Among them, the dynamic SQL (if, WHERE, foreach, etc.) used in the add, delete, change and query operation stitching SQL, as well as the complex mapping (1 to 1, 1 to many, many-to-many, etc.) used in the encapsulation of result sets, these two parts will be introduced in detail in a separate article later. In this article, we will simply point out.

Nine top-level elements, functional categories:

The top element, parameterMap, has been deprecated.

No matter how complex your SQL operations are, the most basic ideas will not escape the above four sections.

Namespace namespace

A complete Mapper mapping file, need to have constraint header XML with! The namespace attribute is used as the unique identifier of the mapper.

In school, Class one, Grade six, Number 23, can represent the only you.
When writing a Java class, the package name + the class name can represent a unique class.
Now, we write in Mybatis each SECTION of SQL statement, also has a unique representation, that is, “namespace identification + statement ID”, whether to distinguish business or to split services, Anyway, Mybatis makes every mapper.xml come with a unique namespace identifier.

Each section of SQL statement is uniquely defined. We use “namespace identifier + statement block ID” as the unique identifier in Mybatis. After combination, it can be used as the unique Key of local map set cache in Mybatis level 2 cache. It can also be used for mapped binding of Dao interfaces and as a unique proxy identifier. In short, we want to avoid naming conflicts and duplicate definitions, so having a unique identifier like this has at least 100 million advantages.

Select queries

Select queries are almost the most frequently used element, so Mybatis has worked hard on this area. The goal is to make our query operation simple by providing as much convenience as possible. A query to User could be written like this:

<select id="selectUser" parameterType="int" resultType="hashmap">
  select * from t_user where id = #{id}
</select>
Copy the code

Id attribute: its ID value is unique in the current mapper. XML namespace (although the same ID value is allowed in different mapper. XML namespaces)
ParameterType property: Represents the type of the parameter passed in, in this case the int (or Integer) type
ResultType attribute: represents the returned resultType, which specifies to return an object of hashMap type. Mybatis will automatically map the “field column name – field value” corresponding to the queried data table record to the key-value of the map set.

Of course, if you don’t want to receive query results via hashMap, you can specify the return type freely. Mybatis supports automatic JavaBean binding, we can automatically map the result set as long as the query returns the field name and JavaBean attribute name (or use camel name), for example, you create a Java class User. Contains two attributes id and name, so the result set can be specified as com.vo.user, and you’re done.

<select id="selectUser" 
    parameterType="int" resultType="com.vo.User">
  select * from t_user where id = #{id}
</select>
Copy the code

Note the parameter symbol:

#{id}
Copy the code

#{} tells MyBatis to create a PreparedStatement parameter. In JDBC, such a parameter is prepared by a “? To be passed to a new precompiled statement like this:

// Approximate JDBC code, non-mybatis code...
String selectUser = " select * from t_user where id = ? ";
PreparedStatement ps = conn.prepareStatement(selectUser);
ps.setInt(1,id);
Copy the code

#{} as a placeholder, ${} as a replacement, there is no one more important, but different application scenarios, appropriate choice.

If you want to prepare a PrepareStatement like JDBC, you can use #{} to replace placeholders with single quotes at the beginning and end. This prevents SQL injection risks.

For example, using the ${} operation to delete (very problematic!)

${$} = ${$}
delete from t_user where id = ${id}

// 2. Pass id to 1
delete from t_user where id = 1
// Result deletes record with id=1
    
// if id =1 or 1=1
delete from t_user where id = 1 or 1=1
// Drop all tables
Copy the code

Let’s see how #{} circumvents SQL injection:

// use #{} to prevent injection risks
delete from t_user where id = #{id}

// 2. Pass id to 1
delete from t_user where id = '1'
// Result deletes record with id=1
    
// if id =1 or 1=1
delete from t_user where id = '1 or 1=1'
SQL statement error, table data security
Copy the code

There’s nothing you can do to prevent SQL injection, but there’s nothing you can do to prevent SQL injection, but there are other things you can do with {}. For example, {} allows you to do dynamic table and column name substitutions flexibly, for example:

// select * from table
select * from ${tableName} 

// tableName = t_user
select * from t_user  

// 2
select * from t_user where ${colunmName} = ${value}

// colunmName = name, value = 'pan pan ', result
select * from t_user where name = 'pan pan'

// colunmName = id, value = 1
select * from t_user where id = 1
Copy the code

The {} method above is very flexible in substituting column and table names, but it does have SQL injection risks, so before you consider using #{} or {}, you need to evaluate the risks and avoid them. I recommend using #{} where possible.

Of course, the SELECT element allows you to configure many properties to configure the behavior details of each statement.

<select     
  id="selectUser" 
  parameterType="int"
  parameterMap="deprecated"
  resultType="hashmap"
  resultMap="personResultMap"
  flushCache="false"
  useCache="true"
  timeout="10"
  fetchSize="256"
  statementType="PREPARED"
  resultSetType="FORWARD_ONLY"
  databaseId="mysql"
  resultOrdered="false"
  resultSets="rs1,rs2,rs3">
  select * from t_user
</select>
Copy the code

Here is a detailed introduction, slightly lengthy, one breath to read it:

Id mandatory. The unique identifier in the namespace can be referenced by Mybatis. Mybatis will throw an exception if the same combination of namespace + statement ID exists.
ParameterType Is optional. ParameterType is the class fully qualified name or alias of the parameter passed to the statement. It can be a basic type, map, or JavaBean.
ParameterMap is used to reference the property block of external parameterMap, which is deprecated. Use inline parameter mapping and the parameterType attribute in the future.
ResultType is optional, which defines the full path of the class. If automatic matching is allowed, the result set will be mapped by Javaben specification, or defined as int, double, float, etc. You can also use an alias, but it conforms to the alias specification and definition. Only one resultType and resultMap can be used simultaneously. (in everyday use, such as when we count the total number of results can be set to int);
ResultMap Specifies the named reference to an external resultMap. This parameter is optional. Result mapping is the most powerful feature of MyBatis. If you have a thorough understanding of it, many complex mapping problems can be easily solved. We will have an article to explain one-on-one, one-to-many and man-to-many in the future. Only one resultType and resultMap can be used simultaneously.
FlushCache optional: flushes the cache. When set to true, the local cache and level-2 cache will be flushed whenever a statement is called. Default: false.
UseCache is optional. Cache is used. If this parameter is set to true, the result of this statement will be cached by a level 2 cache.
Timeout optional, which 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 Specifies the total number of records. 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). Due to performance problems, it is recommended to do paging in SQL.
StatementType Can be STATEMENT, PREPARED, or CALLABLE. This will make MyBatis use Statement, PreparedStatement, or CallableStatement, respectively. The default value is PREPARED.
ResultSetType Can be configured using one of the following attributes: FORWARD_ONLY, SCROLL_SENSITIVE, SCROLL_INSENSITIVE, or DEFAULT (equivalent to unset). The DEFAULT value is unset (dependent on the database driver).
- FORWARD_ONLY, which allows cursor forward access only;
- SCROLL_SENSITIVE: Allows bidirectional cursor scrolling, but does not update data in a timely manner. If the data in the database has been modified, it will not be updated in the resultSet.
- SCROLL_INSENSITIVE allows the cursor to scroll both ways, updating the resultSet when data in the database has been modified.

We know that JDBC encapsulates query results through a ResultSet, which itself contains a set of results returned by the query statement. For example, the result set reads you often see in JDBC:

// Allows scrolling cursors to index result sets
while( rs.next() ){
    rs.getString("name");
}
// Also supports cursor positioning to the last position
rs.last();
// Scroll backwards
rs.previous();
Copy the code

If databaseIdProvider is configured, MyBatis will load all statements that do not contain the databaseId or that match the current databaseId. If both tagged and untagged statements are present, untagged statements are ignored.
ResultOrdered optional, this setting only applies to nested result SELECT statements: If true, it is assumed that nested result sets or groups are included, and no reference to previous result sets is 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 resultSets. It lists the result sets returned after statement execution and gives each result set a name, separated by commas.

Insert/update/delete Add/delete change

The data change statements INSERT, UPDATE, and DELETE are implemented very closely and are much simpler than the SELECT element.

<insert
  id="insertUser"
  parameterType="domain.vo.User"
  flushCache="true"
  statementType="PREPARED"
  keyProperty=""
  keyColumn=""
  useGeneratedKeys=""
  timeout="20">

<update
  id="updateUser"
  parameterType="domain.vo.User"
  flushCache="true"
  statementType="PREPARED"
  timeout="20">

<delete
  id="deleteUser"
  parameterType="domain.vo.User"
  flushCache="true"
  statementType="PREPARED"
  timeout="20">
Copy the code

Most of these attributes are the same as the SELECT element, but we’ll cover three different attributes:

useGeneratedKeys : (for INSERTS and Updates only) This will cause MyBatis to use JDBC’s getGeneratedKeys method to retrieve primary keys generated internally by the database (e.g. Auto-increment fields for relational database management systems like MySQL and SQL Server), default: false.
keyProperty : (for INSERT and UPDATE only) Specifies a property that uniquely identifies the object. MyBatis sets its value using the return value of getGeneratedKeys or the selectKey child element of the INSERT statement. Default: unset. If more than one column is generated, you can separate multiple attribute names with commas.
KeyColumn: (for insert and update only) sets the name of the column in the table where the generated key value is generated. In some databases (like PostgreSQL), this is mandatory when the primary keyColumn is not the first column in the table. If more than one column is generated, you can separate multiple attribute names with commas.

Let’s start by looking at examples of insert, UPDATE, and DELETE statements:

<insert id="insertUser">
  insert into t_user (id,name) 
  values (#{id},#{name})
</insert>

<update id="updateUser">
  update t_user set name = #{name} where id = #{id}
</update>

<delete id="deleteUser">
  delete from t_user where id = #{id}
</delete>
Copy the code

As mentioned earlier, insert statements have richer configuration rules, with additional attributes and child elements to handle primary key generation, as well as multiple generation methods.

First, if your database supports automatic generation of primary key fields (such as MySQL and SQL Server), you can set useGeneratedKeys= “true” and then set keyProperty to the target property. For example, if the t_USER table above already uses auto-generation on the ID column, the statement could be modified to read:

<insert id="insertUser" useGeneratedKeys="true"
    keyProperty="id">
  insert into t_user (name) values (#{name})
</insert>
Copy the code

If your database also supports multi-row inserts, you can also pass in an array or collection of users and return the automatically generated primary key.

<insert id="insertUser" useGeneratedKeys="true"
    keyProperty="id">
  
  insert into t_user (name) values  
    
  <foreach item="item" collection="list" separator=",">
    (#{item.name})
  </foreach>
</insert>
Copy the code

For databases that do not support automatic primary key column generation, and for JDBC drivers that may not support automatic primary key generation, MyBatis has another way to generate primary keys.

Here’s a simple (and silly) example that generates a random ID (not recommended for practical use, just to show how flexible and forgiving MyBatis is in handling the problem) :

<insert id="insertUser">
 
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    select CAST(RANDOM()*1000000 as INTEGER) a from SYSIBM.SYSDUMMY1
  </selectKey>
    
  insert into t_user (id, name)
  values  (#{id}, #{name})
</insert>
Copy the code

In the above example, the statement in the selectKey element is run and the ID of the User is set before the insert statement is invoked. This enables the database to automatically generate the primary key similar behavior, while keeping the Java code concise.

The selectKey element is described as follows:

<selectKey
  keyProperty="id"
  resultType="int"
  order="BEFORE"
  statementType="PREPARED">
Copy the code

The order attribute in the selectKey has two options: BEFORE and AFTER.

BEFORE: The selectKey statement is executed, the queried value is set to the JavaBean property, and then the INSERT statement is executed.
AFTER: The AFTER statement is executed, followed by the selectKey statement, and the value of the selectKey is set to the properties in the JavaBean. If you change it to AFTER in the above example, the inserted ID will be null, but the returned JavaBean property will have a value.

Cache cache

Caches are particularly common in Internet systems, where data is stored in memory. MyBatis has a powerful built-in transactional query caching mechanism that can be easily configured and customized. To make it more powerful and easy to configure, we have made a number of improvements to the caching implementation in MyBatis 3.

By default, only local session caching is enabled (that is, level 1 caching, sqlSession level), which only caches data within a session. To enable global level 2 caching, add the following code to the global config file config.xml:

<! -- Enable level 2 cache --> 
<settings> 
    <setting name="cacheEnabled" value="true"/> </settings>
Copy the code

Second, enable caching in the usermapper.xml file:

<! -- Enable level 2 cache --> 
<cache></cache>
Copy the code

That’s basically it. This simple statement has the following effect:

The results of all SELECT statements in the mapping statement file will be cached.
All INSERT, UPDATE, and DELETE statements in the mapping statement file flush the cache.
The cache uses the Least Recently Used algorithm (LRU) algorithm to clear unwanted caches.
The cache is not flushed regularly (that is, there are no flush intervals).
The cache holds 1024 references to lists or objects (whichever is returned by the query method).
The cache is treated as a read/write cache, which means that the retrieved object is not shared and can be safely modified by the caller without interfering with potential changes made by other callers or threads.

The cache only works on statements in the mapping file where the cache label resides. If you use a mix of Java APIS and XML mapping files, statements in the common interface will not be cached by default. You need to specify the cache scope using the @Cachenamespaceref annotation.

These attributes can be modified through the attributes of the cache element. Such as:

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

The above represents a more advanced set of cache configurations, where a FIFO cache is first created, refreshed every 60 seconds, and can store up to 512 references to the result object or list. The returned objects are then set to read-only, so modifying them may conflict with callers in different threads.

The cache can be cleared by:

LRU – Least recently used: Removes the object that has not been used for the longest time.
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 default cleanup policy is LRU

The flushInterval property can be set to any positive integer, and 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.

The size attribute can be set to any positive integer, taking into account the size of the object to be cached and the memory resources available in the runtime environment. The default value is 1024.

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

Level 2 caching is transactional. This means that the level 2 cache is flushed either when the SqlSession completes and commits (COMMIT) or when the SqlSession completes and rolls back (close). Regardless of whether flushCache=true is configured.

Mybatis cache includes level 1 cache (sqlSession level) and level 2 cache (Mapper level), so mapper mapper is configured with level 2 cache, we first know about this concept, because we will later introduce these two kinds of cache in detail, and also explain how to customize cache. Because the default cache of Mybatis is stored locally in the data structure of Map, so the custom cache can expand the storage medium to disk or database redis, etc. Level 1 cache is enabled by default, and level 2 cache needs to be manually enabled, which will be explained in detail in the future and foretold in advance.

Cache acquisition order: Level 2 Cache > Level 1 Cache > Database

Cache-ref references the cache

If you recall the contents of the cache, statements in a namespace are only cached or flushed using the cache in that namespace. But you may want to share the same cache configuration and instances in multiple namespaces. To do this, you can use the cache-ref element to reference another cache.

<cache-ref namespace="com.vo.UserMapper"/>
Copy the code

SQL statement block

This element can be used to define reusable SNIPpets of SQL code for use in other statements. Parameters can be determined statically (at load time), and different parameter values can be defined in different include elements. Such as:

<sql id="userColumns"> 
    ${alias}.id,${alias}.name 
</sql>
Copy the code

This SQL fragment can be used in other statements, such as:

<select id="selectUsers" resultType="map">
  select
    <include refid="userColumns">
        <property name="alias" value="t1"/>
    </include>.<include refid="userColumns">
        <property name="alias" value="t2"/>
    </include>
  from t_user t1 cross join t_user t2
</select>
Copy the code

You can also use attribute values in the reFID attribute of an include element or in multi-layer internal statements, for example:

<sql id="sql1">
  ${prefix}_user
</sql>

<sql id="sql2">
  from
    <include refid="${include_target}"/>
</sql>

<select id="select" resultType="map">
  select
    id, name
  <include refid="sql2">
    <property name="prefix" value="t"/>
    <property name="include_target" value="sql1"/>
  </include>
</select>
Copy the code

ParameterMap Parameter mapping

The parameterMap element is officially deprecated and will be phased out in future versions. We recommend using JavaBean to pass value parameters. This is one of the reasons why parameterMap was abandoned. Since parameterType is a good substitute for parameterMap and can also be used to customize javabean-type arguments, parameterMap is a natural exit.

conclusion

I have been hoping that they only output views, rather than the output dictionary, but some knowledge points are extremely miscellaneous, knowledge output is really a difficult task, how to not only comb the complete context of knowledge, but also speak in simple and easy to understand, concise and comprehensive, is indeed the follow-up article decomposition output research direction.

Mybatis: The most hardcore API of Mybatis, do you know how many? .

BIU ~ the article continues to update, wechat search “Pan Pan and his friends” the first time to read, there are surprises at any time. This article will be included in GitHub github.com/JavaWorld, hot technology, framework, face, solutions, we will be the most beautiful posture in the first time, welcome Star ~ our future more than articles! Want to enter the reader group of partners welcome to my personal number: Panshenlian, remarks “add group” in our group huan Chat ~

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Mybatis series full solution (5) : the most complete network! Mybatis Mapper mapping file

preface

Mybaits Series full solutions (continuously updated)

directory

Mapper overview of the mapper’s top-level elements

Namespace namespace

Select queries

Insert/update/delete Add/delete change

Cache cache

Cache-ref references the cache

SQL statement block

ParameterMap Parameter mapping

conclusion

Mybatis series full solution (5) : the most complete network! Mybatis Mapper mapping file

preface

Mybaits Series full solutions (continuously updated)

directory

Mapper overview of the mapper’s top-level elements

Namespace namespace

Select queries

Insert/update/delete Add/delete change

Cache cache

Cache-ref references the cache

SQL statement block

ParameterMap Parameter mapping

conclusion

Related Posts

Introduction to JSP specification

Java object reference

System Tuning – Lessons learned from JVM tuning