SpringBoot + Mybatis series pass parameter as field/table name note

This is the 25th day of my participation in the August Genwen Challenge.More challenges in August

SpringBoot Mybatis parameter as field/table name considerations

Today I met a very interesting thing, a friend feedback, the same SQL, why directly through mysql terminal execution results are inconsistent with mybatis execution results, I feel a bit spookily; Then I look at it and see that this is a typical problem, the difference between #{} and ${}

Let’s take a look at this problem and relearn the difference between the two

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

II. Scene replaying

A simple demo to demonstrate the use of posture, according to the pass parameter, to specify the sort of field;

List<MoneyPo> orderBy(@Param("order") String order);
Copy the code

The corresponding XML is as follows

<select id="orderBy" resultMap="BaseResultMap">
    select * from `money` order by #{order} asc
</select>
Copy the code

The above implementation may be inconsistent with our expectations, as follows

1. Troubleshooting

In the example above, we were hoping to sort by pass-through parameters, but the result will be sorted by ID

To solve the above problem, it is also easy to change # to $

<select id="orderBy" resultMap="BaseResultMap">
    select * from `money` order by ${order} asc
</select>
Copy the code

Again, the test is as expected

2. Cause analysis

The key reason for the above question lies in the essential difference between $and #. If you know a little bit, you will know that $is a replacement and # is a placeholder

For example, the above two are converted into SQL, which corresponds to the following

• # {}: select * from money order by 'money' asc
  • Notice that money is passed in as a string
• The ${}: select * from money order by money asc
  • Note money as the column name

The first SQL statement above, which is very interesting, does not throw the error, the execution of the normal (note that this is dependent on the database version, not all versions of the normal)

3. #{} vs. ${}

Matters needing attention:

select * from money where name = #{name}
select * from money where name = ${name}
Copy the code

As in the above two SQL, there will be a significant to go when the specific parameter is passed

• # {name} : ginsengA grayThe corresponding SQL is as follows
  • Select * from money where name = '1'
• The ${name} : ginsengA grayThe corresponding SQL is as follows
  • Select * from money where name = 1
  • Note that in the SQL above, the name parameter is not quoted, so it is just bad SQL
  • So the passing parameter should be'A Grey', you need to add single quotation marks manually

Use posture:

• Use #{} where you can use #{}, use ${} less or less
• ${}; ${};
• ${} is required when ordering by.
• Pay attention to when to add or not add single quotes, i.e. {}, when to add or not add single quotes, i.e. {} and ‘${}’

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

0. Project

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

1. Wechat official account: Yash Blog

As far as the letter is not as good, the above content is purely one’s opinion, due to the limited personal ability, it is inevitable that there are omissions and mistakes, if you find bugs or have better suggestions, welcome criticism and correction, don’t hesitate to appreciate

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