Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.

Recently had a friend in discussing the difference between the # {} and ${}, has mentioned the # {} is replaced with string, and my personal understanding is that its role is largely placeholder, eventually replace string is not necessarily the result of the way, such as when we pass parameter type is plastic, eventually joining together of SQL, the reasonable should be plastic, instead of a string

Let’s take a look at how different parameter types in the Mapper interface are replaced in the final concatenation SQL

I. Environment configuration

We use SpringBoot + Mybatis + MySql to build the example 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>
Copy the code

The core relies on mybatis-spring-boot-starter. For version selection, go to the MVN repository and find the latest one

Another unavailable db configuration information is 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:
Copy the code

2. Database tables

Database for testing

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'Username',
  `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;
Copy the code

The test data, mainly the name field, is a string with a value of one number

INSERT INTO `money` (`id`, `name`, `money`, `is_deleted`, `create_at`, `update_at`)
VALUES
	(120.'120'.200.0.'the 2021-05-24 20:04:39'.'the 2021-09-27 19:21:40');
Copy the code

II. Determine the parameter type

This article ignores the details of Po, mapper interface and XML file in Mybatis. Interested partners can directly view the source code at the bottom (or view the previous blog can also be).

1. The parameter type is integer

For the above case, define an interface to query data by name, but the name parameter is an integer

Mapper interfaces:

/** * int; /** * int@param name
 * @return* /
List<MoneyPo> queryByName(@Param("name") Integer name);
Copy the code

The corresponding XML file is as follows

<select id="queryByName" resultMap="BaseResultMap">
    select * from money where `name` = #{name}
</select>
Copy the code

Name = 120; name = ‘120’; name = ‘120’;

So how do you determine what the resulting SQL will look like? This section describes a direct output mysql SQL execution log method

Run the following two commands on the mysql server to enable SQL execution logging

set global general_log = "ON";
show variables like 'general_log%';
Copy the code

When we access the above interface, we will find that the SQL statement sent to mysql is still integer after parameter substitution

select * from money where `name` = 120
Copy the code

2. Specify the jdbcType

When using #{}, ${}, we sometimes see jdbcType specified in addition to the parameter. Does this have any effect on the final SQL generation if we specify this in the XML?

<select id="queryByNameV2" resultMap="BaseResultMap">
    select * from money where `name` = #{name, jdbcType=VARCHAR} and 0=0
</select>
Copy the code

The generated SQL is as follows

select * from money where `name` = 120 and 0=0
Copy the code

In actual SQL, this jdbcType does not affect the final SQL parameter concatenation, so what is it mainly used for? (This applies mainly to type conversion exceptions that may occur when null is passed in.)

3. The parameter type is String

When we pass arguments of type string, the final SQL sermon should be quoted

/** * if the parameter type is string, the "*" is automatically added@param name
 * @return* /
List<MoneyPo> queryByNameV3(@Param("name") String name);
Copy the code

The corresponding XML

<select id="queryByNameV3" resultMap="BaseResultMap">
    select * from money where `name` = #{name, jdbcType=VARCHAR} and 1=1
</select>
Copy the code

The final SQL generated above is as follows

select * from money where `name` = '120' and 1=1
Copy the code

4. TypeHandler implements mandatory quotation marks for parameter substitution

After reading the above sections, you can basically draw a simple inference (of course, it needs to be analyzed from the source code)

  • SQL parameter replacement is not simply replaced by a string. In fact, it is determined by the parameter Java parameter type. If Java parameter type is string, the CONCatenated SQL is string format. The parameter is an integer, and the SQL is also an integer

So the question is, why do we need to know this?

  • The key issue is index failure

SQL > select * from money where name = 120; SQL > select * from money where name = 120

Based on this, we have an application scenario. In order to avoid index failure due to parameter type problems, we want the name parameter to be passed, regardless of the actual parameter type, the final concatenated SQL, is a string format.

We implement this scenario with the help of a custom TypeHandler

@MappedTypes(value = {Long.class, Integer.class})
@MappedJdbcTypes(value = {JdbcType.CHAR, JdbcType.VARCHAR, JdbcType.LONGVARCHAR})
public class StrTypeHandler extends BaseTypeHandler<Object> {

    /** * Java type to JDBC type **@param ps
     * @param i
     * @param parameter
     * @param jdbcType
     * @throws SQLException
     */
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, String.valueOf(parameter));
    }

    /** * JDBC type to Java type **@param rs
     * @param columnName
     * @return
     * @throws SQLException
     */
    @Override
    public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return rs.getString(columnName);
    }

    @Override
    public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return rs.getString(columnIndex);
    }

    @Override
    public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        returncs.getString(columnIndex); }}Copy the code

Then, in the XML, specify TypeHandler

/** * Implements Java <-> JDBC type interchange via a custom TypeHandler, which is passed in as int/long and converted to String *@param name
 * @return* /
List<MoneyPo> queryByNameV4(@Param("name") Integer name);
Copy the code
<select id="queryByNameV4" resultMap="BaseResultMap">
    select * from money where `name` = #{name, jdbcType=VARCHAR, typeHandler=com.git.hui.boot.mybatis.handler.StrTypeHandler} and 2=2
</select>
Copy the code

In this way, the output SQL will carry a single quotation mark, which can solve the problem from the source of the incorrect parameter type, resulting in the failure of the index

select * from money where `name` = '120' and 2=2
Copy the code

5. Summary

This article uses a simple example to test the impact of different parameter types in the Mapper interface on the final SQL generation

  • If the parameter type is integer, the final SQL parameter substitution is also integer.# {}It’s not a simple string substitution.
  • If the parameter type is string, the final SQL parameter replacement is carried automatically' ' （The ${}Note that it does not automatically enclose single quotes, you need to add them manually.)

When we want to generate a string substitution for whatever type of argument we pass, we can use a custom TypeHandler to do this. This way, we can avoid indexing problems caused by implicit type conversion at the source

Finally, the question comes, is the above conclusion reliable? Where is the final SQL concatenated in Mybatis? What is the flow of this SQL concatenation?

About the whole process of SQL splicing, the follow-up blog will be online soon, I am a grey, pass by the big guy to help point a thumbs-up, price collection, give an evaluation

III. Can’t miss the source code and related knowledge points

0. Project

  • Project: github.com/liuyueyi/sp…
  • Source: github.com/liuyueyi/sp…

Series of blog posts:

  • 【DB series 】Mybatis series of CURD basic use posture
  • 【DB series 】Mybatis series of CURD basic use posture – notes
  • 【DB series 】 Several posture of Mybatis parameter transfer
  • 【DB series 】 The use of escape characters in Mybatis

1. Wechat official account: Yash Blog

All letter is better than no book, the above content, purely one’s words, due to the limited personal ability, it is hard to avoid omissions and mistakes, such as finding bugs or better suggestions, welcome criticism and correction, not grudging gratitude

Below a gray personal blog, record all the study and work of the blog, welcome everyone to go to stroll

  • A grey Blog Personal Blog blog.hhui.top
  • A Grey Blog-Spring feature Blog Spring.hhui.top