13.Mybatis传参作为字段/表名时的注意事项

Tags:
Categories:

  少于1分钟    

今天遇到一个非常有意思的事情，一个小伙伴反馈，同样的sql，为啥直接通过mysql终端执行的结果，和mybatis的执行结果不一致，感觉有点鬼畜；然后看了一下，发现这是个比较典型的问题，#{}与${}的使用区别

接下来我们看一下这个问题，顺带也重新学习一下它们两的区别

I. 环境配置

我们使用SpringBoot + Mybatis + MySql来搭建实例demo

  • springboot: 2.2.0.RELEASE
  • mysql: 5.7.22

1. 项目配置

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

核心的依赖mybatis-spring-boot-starter，至于版本选择，到mvn仓库中，找最新的

另外一个不可获取的就是db配置信息，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. 数据库表

用于测试的数据库

CREATE TABLE `money` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名',
  `money` int(26) NOT NULL DEFAULT '0' COMMENT '钱',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `create_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=551 DEFAULT CHARSET=utf8mb4;

II. 场景复现

一个简单的demo来演示下使用姿势，根据传参，来指定排序的字段；

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

对应的xml如下

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

上面这个执行之后可能与我们预期的不一致，如下

1. 问题修复

上面的演示中，本来是希望根据传参进行排序，最后的执行结果会发现都是按照id进行排序

要解决上面这个问题，也很简单，将#改成$

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

再次测试如下，和我们的预期一致了

2. 原因分析

上面这个问题的关键原因在于 $ 与 #的本质区别，有过一点了解的小伙伴会知道$最终的效果是替换，而#则是占位

比如上面的两个，转成sql，对应如下

  • #{}: select * from money order by 'money' asc
    • 注意money作为字符串传入的
  • ${}: select * from money order by money asc
    • 注意money作为列名

上面的第一个sql，非常有意思，执行居然不会抛错，可以正常执行(注意，这个与数据库版本有关，并不是所有的版本都可以正常执行)

3. #{}与${}对比

#{} ${}
参数占位，相当于 ? 直接替换到sql的一部分
动态解析 -> 预编译 -> 执行 动态解析 -> 编译 -> 执行
变量替换是在DBMS 中 变量替换是在 DBMS 外
变量替换后，#{} 对应的变量自动加上单引号 '' 变量替换后，${} 对应的变量不会加上单引号 ''
防sql注入 不能防sql注入

注意事项：

select * from money where name = #{name}
select * from money where name = ${name}

如上面两条sql，在具体传参的时候，就会有一个显著的去呗

  • #{name}: 传参 一灰灰，对应sql如下
    • select * from money where name = '一灰灰'
  • ${name}: 传参 一灰灰，对应sql如下
    • select * from money where name = 一灰灰
    • 注意上面的sql中，name的传参没有引号，直接就是bad sql
    • 所以传参应该是 '一灰灰'，需要手动的加上单引号

使用姿势：

  • 能用 #{} 的地方就用 #{}，不用或少用 ${}
  • 表名作参数时，必须用 ${}
  • order by 时，必须用 ${}
  • 使用 ${} 时，要注意何时加或不加单引号，即 ${} 和 ‘${}’

III. 不能错过的源码和相关知识点

0. 项目

1. 微信公众号: 一灰灰Blog

尽信书则不如，以上内容，纯属一家之言，因个人能力有限，难免有疏漏和错误之处，如发现bug或者有更好的建议，欢迎批评指正，不吝感激

下面一灰灰的个人博客，记录所有学习和工作中的博文，欢迎大家前去逛逛

一灰灰blog