How to Determine the Type of Mybatis Parameter Transmission
- 2021-12-04 18:34:59
- OfStack
2. Database tables
II. Determination of parameter transmission types 1. The parameter type is shaping
2. Specify jdbcType
3. The type of transmission is String
4. TypeHandler implements parameter replacement and forces quotation marks
5. Summary
Recently, when discussing the difference between # {} and ${}, some friends mentioned that # {} is replaced by string. As far as I personally understand, its main function is to occupy space, and the final replacement result is not necessarily string mode. For example, when our parameter transmission type is shaping, the final spliced sql should also be shaping, not string mode
Next, let's look at how different parameter types in the mapper interface are replaced in the final splicing sql
I. Environment Configuration
We use SpringBoot + Mybatis + MySql to build the instance demo
springboot: 2.2.0.RELEASE
mysql: 5.7.22
1. Project configuration
<dependencies>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>
The core depends on mybatis-spring-boot-starter. For version selection, go to the mvn repository and find the latest
The other one that is not available is db configuration information, appliaction. yml
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password:
2. Database tables
Database for testing
CREATE TABLE `money` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT '' COMMENT ' User name ',
`money` int(26) NOT NULL DEFAULT '0' COMMENT ' Money ',
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ' Creation time ',
`update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ' Update time ',
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=551 DEFAULT CHARSET=utf8mb4;
Test data, mainly the name field, is a string with a value of 1 number
INSERT INTO `money` (`id`, `name`, `money`, `is_deleted`, `create_at`, `update_at`)
VALUES
(120, '120', 200, 0, '2021-05-24 20:04:39', '2021-09-27 19:21:40');
II. Determination of parameter transmission types
This article ignores the details of po, mapper interface and xml file in mybatis. Interested partners can directly view the bottom source code (or view the previous blog post)
1. The parameter type is shaping
For the above case, define an interface to query data according to name, but this name parameter type is integer
mapper interface:
/**
* int Type, final sql The parameter in is also replaced by the parameter in int
* @param name
* @return
*/
List<MoneyPo> queryByName(@Param("name") Integer name);
The corresponding xml file is as follows
<select id="queryByName" resultMap="BaseResultMap">
select * from money where `name` = #{name}
</select>
The above writing is very common. Our question now is, if the parameters are integers, will the final sql be name = 120 or name = '120'?
So how do you determine what the resulting sql looks like? This paper introduces a way to directly output mysql to execute sql log
Open the sql execution log by executing the following two commands on the mysql server
set global general_log = "ON";
show variables like 'general_log%';
When we access the above interface, we will find that the sql statement finally sent to mysql is still an integer after parameter replacement
select * from money where `name` = 120
2. Specify jdbcType
When you use # {}, ${}, you sometimes see that jdbcType is specified in addition to parameters, so will specifying this in xml affect the final sql generation?
<select id="queryByNameV2" resultMap="BaseResultMap">
select * from money where `name` = #{name, jdbcType=VARCHAR} and 0=0
</select>
The generated sql is as follows
select * from money where `name` = 120 and 0=0
From the actual sql, this jdbcType does not affect the final sql parameter splicing, so what is it mainly used for? (This applies primarily to exceptions that may occur when an null is passed in.)
3. The type of transmission is String
When we pass the parameter type string, the final sql reasoning should be enclosed in quotation marks
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password:
Corresponding xml
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password:
The final sql generated above is as follows
select * from money where `name` = '120' and 1=1
4. TypeHandler implements parameter substitution and forces quotation marks
After reading the above sections, you can basically get a simple inference (of course, it needs to be analyzed from the source code)
sql parameter replacement is not simply replaced by string, but actually determined by the parameter type of java. If the parameter type of java is string, the spliced sql is string format; The pass parameter is an integer, and the spliced sql is also an integer
Then the question comes, why do you want to know this?
The key point lies in the problem of index failure
For example, an index is added to name in this example. When our sql is select * from money where name = 120, the index cannot be walked. If you want to walk the index, the passed parameters must be strings, and implicit type conversion cannot occur
Based on this, we have an application scenario. In order to avoid the inability to walk the index due to the problem of parameter transmission type, we hope that the parameter transmission of name, no matter what the actual parameter type is, the final spliced sql is in the format of string;
We implement this scenario with the help of a custom TypeHandler
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password:
Then in xml, specify TypeHandler
/**
* Through the custom TypeHandler To realize java <-> jdbc Type, so that the real-time incoming is int/long , will also turn into String
* @param name
* @return
*/
List<MoneyPo> queryByNameV4(@Param("name") Integer name);
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password:
The sql output by the above writing method will carry a single quotation mark, which can solve the problem that the parameter type is wrong from the source and the index cannot be walked finally
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/story?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password:
5. Summary
This paper uses a simple example to test the effect of different parameter types on the final sql generation in Mapper interface
When the parameter type is an integer, the final sql parameter replacement is also an integer (# {} is not a simple string replacement)
When the parameter type is a string, the final sql parameter replacement will automatically carry '' (${} Note that it will not be automatically enclosed with single quotation marks, so you need to add it manually)
When we hope that the final sql is a string replacement regardless of the type of reference, we can use the custom TypeHandler to realize it, which can avoid the index problem caused by implicit type conversion from the source
Finally, the question comes. Is the above conclusion reliable? Where is the final sql in mybatis spliced? What is the process of sql splicing?
About the whole process of sql splicing, the follow-up blog post will be launched soon. I am 1 gray. I will help you to praise, collect the price and give an evaluation