1, SQL label of mapping file
There are 9 top-level sql tags in the remapping file:
SQL splicing and operation: select, delete, update, insert, sql (include)
Cache: cache, cache ref
Parameter mapping: parameterMap (this label has been abolished. For parameter mapping, in-line parameter mapping can be used)
Solution map: resultMap
1. OGNL expression and #{}, ${}
Use of OGNL expressions
It is an expression language provided by apache. Its full name is Object Graphic Navigation Language.
It obtains data according to a certain syntax format. Syntax format is the way to use #{object. Object}.
#{user.username} it will first find the user object, then find the username attribute in the user object, and call the getUsername() method to get the value.
However, we specified the name of the entity class on the parameterType attribute, so we can omit user Instead, write username directly.
#{}, ${} difference
#{} represents a placeholder: through #{} placeholders, you can automatically convert JDBC type and Java type, which can effectively prevent sql injection. The value in {} can be simple type value or pojo type value. parameterType is simple type value and has only one parameter, then #{xxx}, xxx can be value or any value.
${} represents an sql splicing: the incoming content can be spliced in sql without jdbc type conversion. The Value in {} can be a simple type Value or pojo type Value. parameterType is a simple type Value and has only one parameter, then ${xxx}, xxx can only be Value.
Using ${} will cause the risk of sql injection, but it can realize the replacement of dynamic table and dynamic column names:
@Select("select * from user where ${column} = #{value}")
User findByColumn(@Param("column") String column, @Param("value") String value);
It should be noted that the final spliced value of ${xxx} does not carry ''. At this time, if you insert the element type as String and the corresponding database type as varchar, an error will be reported because the String does not carry ''
2. Get parameter value
Single parameter:
1. #{xxxx}: since there is only one parameter, the content in xxx can be any value
2. MyBatis before version 3.4.2 supports the use of #{0} - #{n} to pass parameters; In version 3.4.2 and later, if @ param annotation is not used, the parameters to be passed need to use #{arg0}-#{argn} or #{param1}-#{paramn}, in order
3. Use @ param("xxx") to specify the parameter name and take the value through #{xxx}
Multi parameter case:
Reference 2, 3
3,select
The tag attributes are as follows:
| id | A unique identifier in the namespace that can be used to reference this statement. |
|---|---|
| parameterType | The fully qualified name or alias of the class that will be passed into the parameters of this statement. This property is optional because MyBatis can infer the parameters of the specific incoming statement through the type handler. The default value is unset. |
| parameterMap | The property used to reference external parameterMap has been discarded. Please use inline parameter mapping and parameterType attribute. |
| resultType | The fully qualified name or alias of the class that you expect to return results from this statement. Note that if a collection is returned, it should be set to the type contained in the collection, not the type of the collection itself. Only one can be used between resultType and resultMap at the same time. |
| resultMap | A named reference to an external resultMap. Result mapping is the most powerful feature of MyBatis. If you understand it thoroughly, many complex mapping problems can be solved. Only one can be used between resultType and resultMap at the same time. |
| flushCache | When it is set to true, as long as the statement is called, the local cache and L2 cache will be emptied. The default value is false. |
| useCache | Setting it to true will cause the result of this statement to be cached by the secondary cache. The default value is true for the select element. |
| timeout | This setting is the number of seconds the driver waits for the database to return the requested result before throwing an exception. The default value is unset (database driven). |
| fetchSize | This is a recommended value for the driver. Try to make the number of result lines returned by the driver in batch each time equal to this setting value. The default value is unset (dependent drive). |
| statementType | Optional status, PREPARED or CALLABLE. This will make MyBatis use statement, PreparedStatement or CallableStatement respectively. The default value is PREPARED. |
| resultSetType | FORWARD_ONLY，SCROLL_ SENSITIVE, SCROLL_ Either intrinsic or DEFAULT (equivalent to unset). The DEFAULT value is unset (dependent on database driver). |
| databaseId | If the database vendor ID (databaseIdProvider) is configured, MyBatis will load all statements without databaseId or matching the current databaseId; If there are statements with and without, the statements without will be ignored. |
| resultOrdered | This setting is only applicable to nested result select statements: if it is true, it will be assumed that nested result sets or groups are included. When a main result row is returned, there will be no reference to the previous result set. This makes it possible to get nested result sets without running out of memory. Default: false. |
| resultSets | This setting applies only to multiple result sets. It will list the result sets returned after the statement is executed, and give each result set a name, with multiple names separated by commas. |
Intra row parameter mapping
In line parameter mapping refers to the parameters defined in #{xxx}. For example, specify a property to use a specific typehandler. See the example for details.
You can almost always determine the javaType based on the type of the parameter object, unless the object is a HashMap. At this time, you need to explicitly specify javaType to ensure that the correct type handler is used.
JDBC requires that if a column allows null values and will use null parameters, you must specify the JDBC type -- for example, when inserting a piece of data, if you insert a piece of data, a column in the database allows null values, but if the corresponding parameters of the column are null values, an error will be reported if you do not specify the JDBC type.
Example:
Update the Phone information. The type field stores the ordinal value of enum, which is processed by the EnumOrdinalTypeHandler processor instead of the default EnumTypeHandler to store the name value.
1. Entity class
public enum PhoneTypeEnum {
TYPE1("China Mobile"),
TYPE2("China Unicom");
private String name;
PhoneTypeEnum(String name) {
this.name = name;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Phone {
private String id;
private String phone;
private PhoneTypeEnum type;
}
2. Mapping file:
<update id="updateById" parameterType="phone">
UPDATE phone
SET phone = #{phone}, type = #{type, typeHandler = org.apache.ibatis.type.EnumOrdinalTypeHandler,
javaType = com.bihai.mybatis_study.bean.PhoneTypeEnum, jdbcType = VARCHAR}
WHERE id = #{id}
</update>
explain:
#The parameters specified in {type, xxx} are in-line parameters. For details, you can use those in-line parameters. You can refer to #parameterMap's sub tag and the attributes that parameter can set
<parameterMap id="" type="">
<parameter property="" typeHandler="" javaType="" jdbcType="" resultMap="" mode="" scale=""></parameter>
</parameterMap>
be careful:
Since the ordinal value is stored in the database, you should also use the corresponding typeHandler when querying. The code is as follows:
<resultMap id="resultMap" type="phone">
<id property="id" column="id"></id>
<result property="phone" column="phone"></result>
<result property="type" column="type"
jdbcType="VARCHAR"
javaType="com.bihai.mybatis_study.bean.PhoneTypeEnum"
typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"></result>
</resultMap>
<select id="selectById" resultMap="resultMap">
SELECT * FROM phone WHERE id = ${id}
</select>
Tips:
The types that can be set for JDBC type are at org apache. ibatis. type. Defined in jdbcType.
resultSetType
There are three types of attribute values:
- FORWARD_ONLY, only cursor forward access is allowed;
- SCROLL_SENSITIVE allows the cursor to scroll in both directions, but the data will not be updated in time, that is, if the data in the database is modified, it will not be updated in the resultSet in time;
- SCROLL_ Independent, allowing the cursor to scroll in both directions. If the data in the database has been modified, it will be updated to the resultSet in time;
Common JDBC result set reading:
// Allow scrolling cursor index result set
while( rs.next() ){
rs.getString("name");
}
// Of course, cursor positioning to the last position is also supported
rs.last();
// Scroll back
rs.previous();
4,update,insert,delete
The list of attributes is as follows:
<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">
| attribute | describe |
|---|---|
| id | A unique identifier in the namespace that can be used to reference this statement. |
| parameterType | The fully qualified name or alias of the class that will be passed into the parameters of this statement. This property is optional because MyBatis can infer the parameters of the specific incoming statement through the type handler. The default value is unset. |
| parameterMap | The property used to reference external parameterMap has been discarded. Please use inline parameter mapping and parameterType attribute. |
| flushCache | When it is set to true, as long as the statement is called, the local cache and L2 cache will be emptied. The default value is (for insert, update and delete statements) true. |
| timeout | This setting is the number of seconds the driver waits for the database to return the requested result before throwing an exception. The default value is unset (database driven). |
| statementType | Optional status, PREPARED or CALLABLE. This will make MyBatis use statement, PreparedStatement or CallableStatement respectively. The default value is PREPARED. |
| useGeneratedKeys | (only applicable to insert and update) this will make MyBatis use the getGeneratedKeys method of JDBC to retrieve the primary key generated inside the database (such as the auto increment field of relational database management systems such as MySQL and SQL Server). The default value is false. |
| keyProperty | (only applicable to insert and update) specify the attribute that can uniquely identify the object. MyBatis will use the return value of getGeneratedKeys or the selectKey sub element of the insert statement to set its value. The default value is unset. If there is more than one generated column, you can separate multiple attribute names with commas. |
| keyColumn | (only applicable to insert and update) set the column name of the generated key value in the table. In some databases (such as PostgreSQL), it must be set when the primary key column is not the first column in the table. If there is more than one generated column, you can separate multiple attribute names with commas. |
| databaseId | If the database vendor ID (databaseIdProvider) is configured, MyBatis will load all statements without databaseId or matching the current databaseId; If there are statements with and without, the statements without will be ignored. |
useGeneratedKeys,keyProperty,keyColumn
Auto generate primary key value:
Mode 1:
For MySQL and SQL Server that support self incrementing primary keys, you only need to set useGeneratedKeys and keyColumn as the target attribute:
<insert id="insertUser" useGeneratedKeys="true"
keyColumn="id">
insert into t_user (name) values (#{name})
</insert>
Mode 2:
For databases that do not support automatic generation of primary key columns and JDBC drivers that may not support automatic generation of primary keys, MyBatis has another method to generate primary keys:
<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>
The description of the selectKey element is as follows:
<selectKey keyProperty="id" resultType="int" order="BEFORE" statementType="PREPARED">
The order attribute in the selectKey has two choices: BEFORE and AFTER.
- BEFORE: means to execute the statement of selectKey first, then set the queried value to the corresponding attribute of JavaBean, and then execute the insert statement.
- AFTER: it means that AFTER statement is executed first, then selectKey statement is executed, and the value obtained by selectKey is set to the property in JavaBean. In the above example, if it is changed to AFTER, the inserted id will be null, but there will be a value in the returned JavaBean attribute.
Primary key backfill:
MyBatis supports the backfilling of primary keys, which can be JavaBeans or JavaBean lists
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into t_user (name) values (#{name})
</insert>
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into t_user (name) values
<foreach item="item" collection="list" separator="," open = "(" close = ")">
#{item.name}
</foreach>
</insert>
5. Cache and cache ref
The cache in MyBatis is divided into level-1 cache and level-2 cache. The level-1 cache is the cache of sqlsession level, while the level-2 cache is the cache of Mapper level. Multiple sqlsessions share the level-2 cache. When reading data, follow the following order: L2 cache - > L1 cache - > database.
L1 cache emptying:
SqlSession performs commit operations (insert, update and delete) and empties the first level cache in SqlSession. The purpose of this is to store the latest information in the cache and avoid dirty reading.
L2 cache emptying:
The L2 cache is transactional. This means that when SqlSession completes and commits, or completes and rolls back, but the insert/delete/update statement with flushCache=true is not executed, the cache will be updated.
To use L2 cache, you need to enable the following configurations:
1,
<settings>
<!-- Enable L2 cache globally -->
<setting name="cacheEnabled" value="true"/>
</settings>
2,
<cache xxx>
....
</cache>
cache
<cache type = "xxx.xxx.xxxCache" eviction="FIFO" flushInterval="60000" size="512" readOnly="true"/>
eviction: a clear strategy for caching,
- LRU – least recently used: removes objects that have not been used for the longest time. (default)
- FIFO – first in first out: remove objects in the order they enter the cache.
- SOFT – SOFT reference: removes objects based on garbage collector status and SOFT reference rules.
- WEAK – WEAK references: remove objects more actively based on garbage collector status and WEAK reference rules.
The flush interval (refresh interval) property can be set to any positive integer. The set value should be a reasonable amount of time in milliseconds. The default is not set, that is, there is no refresh interval. The cache will only be refreshed when the statement is called.
Size: (number of references) property can be set to any positive integer. Pay attention to the size of the object to be cached and the memory resources available in the running environment. The default value is 1024.
readOnly: (read only) 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 improvement. The read-write cache returns a copy of the cached object (through serialization). It will be slower, but safer, so the default value is false.
Custom cache
MyBatis runs to implement your own caching or create adapters for other third-party caching schemes to completely override caching behavior. Implementation org apache. ibatis. cache. Cache interface, and provide a constructor that accepts String parameter as id
public interface Cache {
String getId();
int getSize();
void putObject(Object key, Object value);
Object getObject(Object key);
boolean hasKey(Object key);
Object removeObject(Object key);
void clear();
}
Set properties. For example, you need to set properties in the Cache, and provide setxx method. You can use external value passing, and support ${xxx} to replace it with Profile properties Value defined in (this refers to the value of the properties setting in mybatis config):
<cache type="com.domain.something.MyCustomCache">
<property name="cacheFile" value="/tmp/my-custom-cache.tmp"/>
</cache>
-----------
<cache type="com.domain.something.MyCustomCache">
<property name="cacheFile" value="${cache.file}"/>
</cache>
Starting with version 3.4.2, MyBatis has supported an initialization method after all the properties are set. Need to implement org apache. ibatis. builder. Initializingobject interface
public interface InitializingObject {
void initialize() throws Exception;
}
be careful:
The configuration of the cache (such as purge policy, read or write), cannot be applied to the custom cache.
cache-ref
For statements with a certain namespace, only the cache of that namespace will be used for caching or refreshing. Using cache ref, you can share the same cache configuration and instances in multiple namespaces.
<cache-ref namespace="com.vo.UserMapper"/>
6. resultMap - result set mapping
resultMap
<resultMap id="" type="" autoMapping="" extends="">
...
<resultMap/>
| attribute | describe |
|---|---|
| id | A unique identifier in the current namespace that identifies a result mapping. |
| type | The fully qualified name of a class, or a type alias (for built-in type aliases, refer to the table above). |
| autoMapping | If this property is set, MyBatis will turn on or off automatic mapping for this result mapping. This attribute overrides the global attribute autoMappingBehavior. Default: unset. |
| extends | Inheritance is supported, but rarely used |
resultMap and constructor
Constructor injection allows you to set the value of the property for the class at initialization without exposing the public method. MyBatis also supports private properties and private JavaBean properties to complete injection, but some people prefer to inject through construction methods. The constructor element is born for this.
<resultMap id = "" type = "">
<constructor>
<idArg resultMap="" column="" jdbcType="" typeHandler="" javaType="" select="" columnPrefix="" name=""></idArg>
<arg ...></arg>
</constructor>
<resultMap/>
| attribute | describe |
|---|---|
| column | The column name in the database, or the alias of the column. In general, this and is passed to resultset The parameters of the getString (columnname) method are the same. |
| javaType | The fully qualified name of a Java class, or a type alias (for built-in type aliases, refer to the table above). If you map to a JavaBean, MyBatis can usually infer types. However, if you are mapping to a HashMap, you should explicitly specify a javaType to ensure that the behavior is consistent with expectations. |
| jdbcType | JDBC type. For the supported JDBC types, see "supported JDBC types" before this table. You only need to specify the JDBC type on columns that may perform inserts, updates, and deletions and allow null values. This is the requirement of JDBC, not MyBatis. If you program directly for JDBC, you need to specify this type for columns that may have null values. |
| typeHandler | We discussed the default type processor earlier. Using this property, you can override the default processor type. This property value is the fully qualified name of a type processor implementation class, or a type alias. |
| select | The ID of the mapping statement used to load the complex type attribute. It will retrieve data from the column specified in the column attribute and pass it to the select statement as a parameter. Please refer to related elements for details. |
| resultMap | The ID of the result map can map the nested result set to an appropriate object tree. It can be used as an alternative to using additional select statements. It can map the results of multi table join operations into a single ResultSet. Such a ResultSet will contain duplicate or partially duplicate result sets. In order to correctly map result sets to nested object trees, MyBatis allows you to "concatenate" result maps to solve the problem of nested result sets. For more information, please refer to the associated elements below. |
| name | The name of the constructor parameter. Starting with version 3.4.3, you can write arg elements in any order by specifying specific parameter names. See the explanation above. |
| columnPrefix | Specify column name prefix |
resultMap and id, result
Mapping of attribute fields
<resultMap id = "" type = "">
<id property="" column="" javaType="" typeHandler="" jdbcType=""></id>
<result ....></result>
<resultMap id = "" type = "">
resultMap, association and collection
Delayed loading
That is, load only when data is needed and not when data is not needed, which is also called lazy load (load on demand)
For example, it is not necessary to load all the account information when loading user information.
- Benefits:
Query from a single table first, and then from the associated table when data needs to be loaded. Compared with multi table query, the speed is improved.
- Disadvantages:
When there are a large number of data queries, the speed will slow down and affect the user experience.
- association and Collection have their own delayed loading function.
Usually one to many and many to many adopt delayed loading; Many to one, one to one, immediate loading.
Refer to fetchType attribute of association
entity
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Phone {
private String id;
private String phone;
private PhoneTypeEnum type;
private User user;
}
-------------------
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User {
private String id;
private String phoneId;
private String name;
private Integer age;
private List<Phone> phone;
}
N - 1,1 - 1
Mode 1:
Query the user corresponding to the phone through the phoneId
<resultMap id="userPhoneListResultMap" type="phone">
<id column="id" property="id"></id>
<result column="phone" property="phone"></result>
<result property="type" column="type"
jdbcType="VARCHAR"
javaType="com.bihai.mybatis_study.bean.PhoneTypeEnum"
typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"></result>
<association property="user" column="id" select="selectUser" javaType="user" fetchType="lazy"></association>
-----------------------------------------------------------------------
<select id="selectUser" resultType="user">
select * from user where phoneId = #{value};
</select>
<select id="getUserPhoneList" resultMap="userPhoneListResultMap">
select * from phone;
</select>
| attribute | describe |
|---|---|
| column | The column name in the database, or the alias of the column. In general, this and is passed to resultset The parameters of the getString (columnname) method are the same. Note: when using composite primary key, you can use the syntax of column="{prop1=col1,prop2=col2}" to specify multiple column names passed to nested Select query statements. This will cause prop1 and prop2 as parameter objects to be set as parameters corresponding to nested Select statements. |
| select | The ID of the mapping statement used to load the complex type attribute. It will retrieve data from the column specified by the column attribute and pass it to the target select statement as a parameter. Please refer to the following examples for details. Note: when using composite primary key, you can use the syntax of column="{prop1=col1,prop2=col2}" to specify multiple column names passed to nested select query statements. This will cause prop1 and prop2 as parameter objects to be set as parameters corresponding to nested select statements. |
| fetchType | Optional. Valid values are lazy and eager. When a property is specified, the global configuration parameter lazyloading enabled is ignored in the mapping and the value of the property is used. |
Pay special attention to the case of multiple parameters, which can be specified through column="{prop1=col1,prop2=col2}".
Disadvantages: although such queries are delayed loading, when the list of records is loaded and the list is traversed immediately to obtain nested data, all delayed loading queries will be triggered, and the performance may become very poor.
Mode 2:
The stored procedure executes the following query and returns two result sets.
select * from user where phoneId = #{id};
select * from phone where id = #{id};
<select id="getUserPhoneList" resultSets="phones,users" resultMap="userPhoneListResultMap" statementType="CALLABLE">
{call getPhoneAndUsers(#{id,jdbcType=VARCHAR,mode=IN})}
</select>
<resultMap id="userPhoneListResultMap" type="phone">
<id column="id" property="id"></id>
<result column="phone" property="phone"></result>
<result property="type" column="type"
jdbcType="VARCHAR"
javaType="com.bihai.mybatis_study.bean.PhoneTypeEnum"
typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"></result>
<association javaType="user" property="user" resultSet = "users" column="id" foreignColumn = "phoneId"></association>
| attribute | describe |
|---|---|
| column | When using multiple result sets, this attribute specifies the columns in the result set that match the foreignColumn (multiple column names are separated by commas) to identify the parent and child types in the relationship. |
| foreignColumn | Specify the column name corresponding to the foreign key. The specified column will match the column given by column in the parent type. |
| resultSet | Specifies the name of the result set used to load complex types. |
Mode 3:
Cascade the query results, and use resultMap to complete the mapping
<select id="getUserPhoneById" resultMap="userPhoneByIdResultMap">
SELECT p.id as phone_id,
phone,
type,
u.id as user_id,
name,
phoneId,
age
FROM phone p
LEFT JOIN user u
ON p.id = u.phoneId
WHERE p.id = #{value}
</select>
-------------------------------------------------------
<resultMap id="userPhoneByIdResultMap" type="phone">
<id column="id" property="phone_id"></id>
<result column="phone" property="phone"></result>
<result property="type" column="type"
jdbcType="VARCHAR"
javaType="com.bihai.mybatis_study.bean.PhoneTypeEnum"
typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"></result>
<association property="user" column="id" javaType="user" resultMap="userResultMap">
</association>
</resultMap>
<resultMap id="userResultMap" type="user">
<id property="id" column="user_id"></id>
<result property="name" column="name"></result>
<result property="phoneId" column="phoneId"></result>
<result property="age" column="age"></result>
</resultMap>
| attribute | describe |
|---|---|
| resultMap | The ID of the result mapping, which can map the associated nested result set to an appropriate object tree. It can be used as an alternative to using additional select statements. It can map the results of multi table join operations into a single ResultSet. Some of the data in such a ResultSet is duplicate. In order to correctly map result sets to nested object trees, MyBatis allows you to "concatenate" result maps to solve the problem of nested result sets. An example of using nested result mapping is after the table. |
| columnPrefix | When connecting multiple tables, you may have to use column aliases to avoid duplicate column names in the ResultSet. Specifying the columnPrefix column name prefix allows you to map columns with these prefixes to an external result map. For details, please refer to the following examples. |
| notNullColumn | By default, child objects are created only when at least one column mapped to an attribute is not empty. You can change the default behavior by specifying non empty columns on this attribute. After specifying, Mybatis will create a sub object only when these columns are non empty. You can specify multiple columns using comma separation. Default: unset. |
| autoMapping | If this property is set, MyBatis will turn on or off automatic mapping for this result mapping. This attribute overrides the global attribute autoMappingBehavior. Note that this attribute is not valid for external result mapping, so it cannot be used with select or resultMap elements. Default: unset. |
Mode 4
The cascading query is still used and the mapping relationship is established through resultMap, but it is not divided into two resultmaps
<select id="getUserPhoneById" resultMap="userPhoneByIdResultMap">
SELECT p.id as phone_id,
phone,
type,
u.id as user_id,
name,
phoneId,
age
FROM phone p
LEFT JOIN user u
ON p.id = u.phoneId
WHERE p.id = #{value}
</select>
---------------------------------------------------
<resultMap id="userPhoneByIdResultMap" type="phone">
<id column="id" property="phone_id"></id>
<result column="phone" property="phone"></result>
<result property="type" column="type"
jdbcType="VARCHAR"
javaType="com.bihai.mybatis_study.bean.PhoneTypeEnum"
typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"></result>
<association property="user" javaType="user">
<id property="id" column="user_id"></id>
<result property="name" column="name"></result>
<result property="phoneId" column="phoneId"></result>
<result property="age" column="age"></result>
</association>
</resultMap>
1 - N,N - N
Similar to the use of assocation in N - 1 and 1 - 1, 1 - N and N - N use collection. The attribute value of collection is mostly the same as that of assocation (an ofType is added to represent the type of collection).
7. Discriminator - discriminator
Sometimes, a database query may return multiple different result sets (but there are some connections in general). The discriminator element is designed to deal with this situation.
The definition of a discriminator needs to specify the column and javaType attributes.
If it matches the case of any discriminator, the result mapping specified by the case will be used. This process is mutually exclusive, that is, the remaining result mappings will be ignored (only one will be selected in the end)
<resultMap id="vehicleResult" type="Vehicle">
<id property="id" column="id" />
<result property="vin" column="vin"/>
<result property="year" column="year"/>
<result property="make" column="make"/>
<result property="model" column="model"/>
<result property="color" column="color"/>
<discriminator javaType="int" column="vehicle_type">
<case value="1" resultMap="carResult"/>
<case value="2" resultMap="truckResult"/>
<case value="3" resultMap="vanResult"/>
<case value="4" resultMap="suvResult"/>
</discriminator>
</resultMap>
---------------------------------------
More concise
<resultMap id="vehicleResult" type="Vehicle">
<id property="id" column="id" />
<result property="vin" column="vin"/>
<result property="year" column="year"/>
<result property="make" column="make"/>
<result property="model" column="model"/>
<result property="color" column="color"/>
<discriminator javaType="int" column="vehicle_type">
<case value="1" resultType="carResult">
<result property="doorCount" column="door_count" />
</case>
<case value="2" resultType="truckResult">
<result property="boxSize" column="box_size" />
<result property="extendedCab" column="extended_cab" />
</case>
<case value="3" resultType="vanResult">
<result property="powerSlidingDoor" column="power_sliding_door" />
</case>
<case value="4" resultType="suvResult">
<result property="allWheelDrive" column="all_wheel_drive" />
</case>
</discriminator>
</resultMap>
8,sql
It is used to extract duplicate sql fragments, and the attribute value can be used in the refid attribute or internal statement of the include element
<sql id="sometable">
${prefix}Table
</sql>
<sql id="someinclude">
from
<include refid="${include_target}"/>
</sql>
<select id="select" resultType="map">
select
field1, field2, field3
<include refid="someinclude">
<property name="prefix" value="Some"/>
<property name="include_target" value="sometable"/>
</include>
</select>
2, Dynamic SQL
The SQL subject that can be determined in the compilation stage is called static SQL. The SQL subject that cannot be determined in the compilation stage and needs to be determined in the running stage is called dynamic SQL. MyBatis supports dynamic SQL and the following nine types:
Single branch judgment: < if >
Multi branch judgment: < choose >, < when >, < otherwise >
Handling SQL splicing problems: < trim >, < where >, < set >
Loop: < foreach >
bind: <bind>
The label of dynamic sql is defined in XMLScriptBuilder:
// XML script tag builder
public class XMLScriptBuilder{
// Label node processor pool
private final Map<String, NodeHandler> nodeHandlerMap = new HashMap<>();
// constructor
public XMLScriptBuilder() {
initNodeHandlerMap();
//... Other initializations are not detailed or important
}
// initialization
private void initNodeHandlerMap() {
nodeHandlerMap.put("trim", new TrimHandler());
nodeHandlerMap.put("where", new WhereHandler());
nodeHandlerMap.put("set", new SetHandler());
nodeHandlerMap.put("foreach", new ForEachHandler());
nodeHandlerMap.put("if", new IfHandler());
nodeHandlerMap.put("choose", new ChooseHandler());
nodeHandlerMap.put("when", new IfHandler());
nodeHandlerMap.put("otherwise", new OtherwiseHandler());
nodeHandlerMap.put("bind", new BindHandler());
}
}
1. if tag
if is used to judge a single branch. It is embedded in the select / delete / update / insert tag and used in combination with test.
| attribute | effect |
|---|---|
| test | It is used for condition judgment and supports ONGL expression |
<select id="findUser">
select * from User where 1=1
<if test=" age != null and age != ''">
and age > #{age}
</if>
<if test=" name != null or name != '' ">
and name like concat(#{name},'%')
</if>
</select>
2,choose,when,otherwise
The three are used together, which is similar to switch - case - default in Java. The explanation is as follows:
< choose >: multi branch label at the top level, which is meaningless to use alone.
< when >: embedded in the choose tag. When a certain when condition is met, the corresponding code block is executed.
< otherwise >: embedded in the choose tag. If all the when conditions are not met, execute the otherwise code block, only ONE.
when attribute:
| attribute | effect |
|---|---|
| test | It is used for condition judgment and supports ONGL expression |
<select id="findUser">
select * from User where 1=1
<choose>
<when test=" age != null ">
and age > #{age}
</when>
<when test=" name != null ">
and name like concat(#{name},'%')
</when>
<otherwise>
and sex = 'male'
</otherwise>
</choose>
</select>
3,foreach
It is used for batch operation traversal, such as batch insertion, in, batch update, etc. When using foreach tag, you need to judge whether the incoming collection parameters (List/Map/Set, etc.) are empty, otherwise syntax exceptions will occur in dynamic SQL.
Properties of foreach:
| attribute | effect |
|---|---|
| collection | Required, attribute name of Map or array or list |
| item | Variable name. The value is each value traversed (it can be an object or basic type). If it is an object, use OGNL expression to get the value |
| index | The index attribute name is the current index value when traversing the list or array. When the iterated object is of Map type, the value is the key value of Map |
| open | The string spliced at the beginning of the loop content can be an empty string |
| close | The string spliced at the end of the loop content can be an empty string |
| separator | Delimiter of traversal element |
When is List:
Multi parameter case:
1. Use attribute names directly:
List<User> selectById(List<String> ids, String name);
<select id="selectById" parameterType="string" resultType="user">
SELECT * FROM user WHERE
<foreach collection="ids" item="e">
<if test = "e == '00001'">
id = #{e};
</if>
</foreach>
</select>
2. Using paramx: List, the first is param1, the second is param2, and so on
<select id="selectById" parameterType="string" resultType="user">
SELECT * FROM user WHERE
<foreach collection="param1" item="e">
<if test = "e == '00001'">
id = #{e};
</if>
</foreach>
</select>
3. Use @ param:
List<User> selectById(@Param("idList") List<String> ids, String name);
<select id="selectById" parameterType="string" resultType="user">
SELECT * FROM user WHERE
<foreach collection="idList" item="e">
<if test = "e == '00001'">
id = #{e};
</if>
</foreach>
</select>
Single parameter:
In addition to the above three, when the parameter is list and there is only one parameter, MyBatis will maintain a list, as follows:
<select id="selectById" parameterType="string" resultType="user">
SELECT * FROM user WHERE
<foreach collection="list" item="e">
<if test = "e == '00001'">
id = #{e};
</if>
</foreach>
</select>
When Array:
Multi parameter case:
At this time, as when the element is List, refer to the above
Single parameter:
Except for the case of multiple parameters, a single parameter contains a special case. When the parameter is array and there is only one parameter, MyBatis will maintain an array, as follows:
List<User> selectById(String[] ids);
<select id="selectById" parameterType="string" resultType="user">
SELECT * FROM user WHERE
<foreach collection="array" item="e">
<if test = "e == '00001'">
id = #{e};
</if>
</foreach>
</select>
When map:
It should be noted that the variable declared by the attribute index represents key and the variable declared by item represents value
Multi parameter case:
Use @ Param, paramx | argx
Single parameter:
Under single parameter, it can be used when the parameter is Map and there is only one parameter_ parameter
List<User> selectById(Map<String,String> ids);
<select id="selectById" parameterType="string" resultType="user">
SELECT * FROM user WHERE
<foreach collection="_parameter" item="e">
<if test = "e == '00001'">
id = #{e};
</if>
</foreach>
</select>
4,where
The traversal tag at the top level needs to be used together with the if tag. It is meaningless to use it alone, AND the where clause will be inserted only when the child element (such as the if tag) returns any content. In addition, if the beginning of the clause is "AND" OR ", the where tag will replace it AND remove it.
<select id="findUser">
select * from User
<where>
<if test=" age != null ">
and age > #{age}
</if>
<if test=" name != null ">
and name like concat(#{name},'%')
</if>
</where>
</select>
It is suggested to add and or at the beginning of each if clause
be careful:
If a comment is added after the where tag, when a child element meets the conditions, except <! --- > Comments will be ignored by where. Except for parsing, other comments such as / / OR / * * / OR – will be treated as the first clause element by where, resulting in the subsequent real first AND clause element OR or clause element not successfully replacing the prefix, resulting in syntax errors.
5,set
The top-level traversal tag needs to be used in conjunction with the if tag. It is meaningless to use it alone, and the set clause will be inserted only when the child element (such as the if tag) returns any content. In addition, if there is a comma "," at the beginning or end of the clause, it will be replaced and removed by the set tag.
<update id="updateUser">
update user
<set>
<if test="age !=null">
,age = #{age}
</if>
<if test="username !=null">
,username = #{username}
</if>
<if test="password !=null">
,password = #{password}
</if>
</set>
where id =#{id}
</update>
It is suggested to add at the beginning or end of each sentence,
be careful:
Note the same as above
6,trim
The implementation of where and set is based on trim, and the function is more powerful
Properties:
| attribute | describe |
|---|---|
| prefix | Prefix. When there is content in the trim element, the specified prefix will be inserted into the content |
| suffix | Suffix. When there is content in the trim element, the specified suffix will be inserted into the content |
| prefixesToOverride | Prefix removal supports multiple (use "|). When there is content in the trim element, the matching prefix string in the content will be removed. |
| suffixesToOverride | Suffix removal supports multiple (use "|). When there is content in the trim element, the matching suffix string in the content will be removed. |
where using trim:
<!--
Be careful when using trim Label Implementation where Label capability
Must be AND and OR Add space after,reference resources prefixList
-->
<trim prefix="WHERE" prefixOverrides="AND |OR |AND\n|OR\n|AND\r|OR\r|AND\t|OR\t" >
...
</trim>
public class WhereSqlNode extends TrimSqlNode {
private static List<String> prefixList = Arrays.asList("AND ","OR ","AND\n", "OR\n", "AND\r", "OR\r", "AND\t", "OR\t");
public WhereSqlNode(Configuration configuration, SqlNode contents) {
super(configuration, contents, "WHERE", prefixList, null, null);
}
}
set using trim:
<trim prefix="SET" prefixOverrides="," suffixOverrides=","> </trim>
public class SetSqlNode extends TrimSqlNode {
private static final List<String> COMMA = Collections.singletonList(",");
public SetSqlNode(Configuration configuration,SqlNode contents) {
super(configuration, contents, "SET", COMMA, null, COMMA);
}
}
7,bind
Create a variable and bind it to the context, that is, it is used by the context. It is basically useless