MyBatis 中 # 与 $ 的区别

MyBatis 其实老早就用过，后来因为业务的关系，就又把之前学的东西都给还回去了。现在在新公司又要用 MyBatis，就再复习一下。

MyBatis 中使用 parameterType 向 SQL 语句传参，parameterType 后的类型可以是基本类型 int，String，HashMap 或 Java 自定义类型。
在 SQL 中引用这些参数的时候，可以使用两种方式：

  • #{parameterName}
  • ${parameterName}

#与$的都可以起到变量替换的作用，但是二者的使用效果却是截然不同的。

网上有关这两个符号的文章挺多的，但没有一致的说法，这里我就用代码来测试一下。

准备

环境：
Spring Boot 2.0.1.RELEASE
MySQL 8.0.11
mybatis-spring-boot-starter 1.3.2

pom.xml 添加以下依赖坐标，其中lombok是为了简化代码用的，不会用的自行 Google。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.2</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.11</version>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <scope>provided</scope>
</dependency>

application.yml 配置如下

1
2
3
4
5
6
7
8
9
10
11
12
13
mybatis:
  type-aliases-package: com.yibo.mybatis.model # Packages to search for type aliases.
  configuration:
    map-underscore-to-camel-case: true # 下划线命名转驼峰命名
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver # 驱动
    url: jdbc:mysql://localhost:3306/test1 # 8.0.11 默认用utf8了
    username: root # 数据库用户名
    password: root # 数据库密码
logging:
  level:
    com.yibo: debug # 开启 debug log 以便观察 SQL 语句

Spring Boot 会自动加载spring.datasource.*相关配置，数据源就会自动注入到sqlSessionFactory中，sqlSessionFactory又会自动注入到Mapper中，所以这一切你都不用管了，直接拿起来使用就行了。
com.mysql.cj.jdbc.Driver是新版的驱动，老版本的是com.mysql.jdbc.Driver。

DO 就是一个普通的 JavaBean，Gender类型是我为了测试枚举类型的转换而引入的，你可以忽略或者直接基本类型来代替，没有影响。

1
2
3
4
5
6
7
8
@AllArgsConstructor
@NoArgsConstructor
@Data
public class UserModel {
    private Integer id;
    private String userName;
    private Gender gender;
}

Mapper 是最关键的一部分，SQL 的生产都在这里，先贴个完整版的，之后的代码都会有所省略

1
2
3
4
5
6
7
8
9
10
11
public interface UserMapper {

    String cols = "id, user_name, gender";

    @Select("select " + cols + " from users where user_name=#{name}")
    @Results({
        @Result(property = "userName", column = "user_name"),
        @Result(property = "gender", column = "gender", javaType = Gender.class, typeHandler = GenderTypeHandler.class)
    })
    UserModel getOne(String name);
}

# 号

#{参数名} 【正常】

UserMapper 就用刚刚上边的那个

1
2
3
2018-04-20 17:00:32.344 DEBUG 51306 --- [           main] c.w.mybatis.mapper.UserMapper.getOne     : ==>  Preparing: select id, user_name, gender from users where user_name=?
2018-04-20 17:00:32.344 DEBUG 51306 --- [           main] c.w.mybatis.mapper.UserMapper.getOne     : ==> Parameters: Bob(String)
2018-04-20 17:00:32.347 DEBUG 51306 --- [           main] c.w.mybatis.mapper.UserMapper.getOne     : <==      Total: 1

运行结果：正常
生成语句：select id, user_name, gender from users where user_name=?

#{属性名} 【正常】

1
2
@Select("select " + cols + " from users where user_name=#{userName}")
UserModel getOne(UserModel user);
1
2
3
2018-04-20 17:35:19.694 DEBUG 51389 --- [           main] c.w.mybatis.mapper.UserMapper.getOne     : ==>  Preparing: select id, user_name, gender from users where user_name=?
2018-04-20 17:35:19.695 DEBUG 51389 --- [           main] c.w.mybatis.mapper.UserMapper.getOne     : ==> Parameters: Bob(String)
2018-04-20 17:35:19.697 DEBUG 51389 --- [           main] c.w.mybatis.mapper.UserMapper.getOne     : <==      Total: 1

运行结果：正常，会自动从 POJO 中抽取属性
生成语句：select id, user_name, gender from users where user_name=?

‘#{参数名}’ 【异常】

在#{}两边加上引号'

1
2
@Select("select " + cols + " from users where user_name='#{name}'")
UserModel getOne(String name);
1
2
3
2018-04-20 17:11:45.606 DEBUG 51328 --- [           main] c.w.mybatis.mapper.UserMapper.getOne     : ==>  Preparing: select id, user_name, gender from users where user_name='?'

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='name', mode=IN, javaType=class java.lang.String, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

运行结果：异常
生成语句：select id, user_name, gender from users where user_name='?'

$ 号

${参数名} 【异常】

1
2
@Select("select " + cols + " from users where user_name=${name}")
UserModel getOne(String name);
1
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'name' in 'class java.lang.String'

运行结果：异常
生成语句：无

${参数名}+@Param 【异常】

在方法参数前边加上@Param("name")

1
2
@Select("select " + cols + " from users where user_name=${name}")
UserModel getOne(@Param("name") String name);
1
2
3
4
5
6
7
8
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column 'Bob' in 'where clause'
### The error may exist in com/yibo/mybatis/mapper/UserMapper.java (best guess)
### The error may involve com.yibo.mybatis.mapper.UserMapper.getOne-Inline
### The error occurred while setting parameters
### SQL: select id, user_name, gender from users where user_name=Bob
### Cause: java.sql.SQLSyntaxErrorException: Unknown column 'Bob' in 'where clause'
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'Bob' in 'where clause'

运行结果：异常
生成语句：select id, user_name, gender from users where user_name=Bob

‘${参数名}‘+@Param 【正常】

在${}两边加上引号'并在方法参数前边加上@Param("name")

1
2
@Select("select " + cols + " from users where user_name='${name}'")
UserModel getOne(@Param("name") String name);
1
2
3
2018-04-20 17:05:57.104 DEBUG 51319 --- [           main] c.w.mybatis.mapper.UserMapper.getOne     : ==>  Preparing: select id, user_name, gender from users where user_name='Bob'
2018-04-20 17:05:57.104 DEBUG 51319 --- [           main] c.w.mybatis.mapper.UserMapper.getOne     : ==> Parameters:
2018-04-20 17:05:57.113 DEBUG 51319 --- [           main] c.w.mybatis.mapper.UserMapper.getOne     : <==      Total: 1

运行结果：正常
生成语句：select id, user_name, gender from users where user_name='Bob'

${属性名} 【异常】

1
2
@Select("select " + cols + " from users where user_name=${userName}")
UserModel getOne(UserModel user);
1
2
3
4
5
6
7
8
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column 'Bob' in 'where clause'
### The error may exist in com/yibo/mybatis/mapper/UserMapper.java (best guess)
### The error may involve com.yibo.mybatis.mapper.UserMapper.getOne-Inline
### The error occurred while setting parameters
### SQL: select id, user_name, gender from users where user_name=Bob
### Cause: java.sql.SQLSyntaxErrorException: Unknown column 'Bob' in 'where clause'
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'Bob' in 'where clause'

运行结果：异常
生成语句：select id, user_name, gender from users where user_name=Bob

‘${属性名}’ 【异常】

在${}两边加上引号'

1
2
3
4
5
6
@Select("select " + cols + " from users where user_name='#{userName}'")
@Results({
    @Result(property = "userName", column = "user_name"),
    @Result(property = "gender", column = "gender", javaType = Gender.class, typeHandler = GenderTypeHandler.class)
})
UserModel getOne(UserModel user);
1
2
2018-04-20 17:45:25.375 DEBUG 51401 --- [           main] c.w.mybatis.mapper.UserMapper.getOne     : ==>  Preparing: select id, user_name, gender from users where user_name='?'
org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.type.TypeException: Could not set parameters for mapping: ParameterMapping{property='userName', mode=IN, javaType=class java.lang.String, jdbcType=null, numericScale=null, resultMapId='null', jdbcTypeName='null', expression='null'}. Cause: org.apache.ibatis.type.TypeException: Error setting non null for parameter #1 with JdbcType null . Try setting a different JdbcType for this parameter or a different configuration property. Cause: java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).

运行结果：异常
生成语句：select id, user_name, gender from users where user_name='?'

总结

由上面各个测试结果可以得出以下结论：

  • #{}解析为一个 JDBC 预编译语句（Prepared Statement）的参数标记符?。
  • ${}在动态解析的时候，会将我们传入的参数当做 String 字符串直接填充到我们的语句中。
  • 两者都可以获取对象中的属性值。
  • 两者都不会自动地在 SQL 语句中添加引号'。
  • 使用${}存在 SQL 注入的风险，所以在能使用#{}的地方就用#{}。

典型误解

有些人认为#{}可以防止 SQL 注入，因此其使用的是 JDBC 编程中的 PrepareSatement。而${}不可以防止 SQL 注入，因此使用的是 Satement。

事实上，默认情况下，在 MyBatis 中，#{}和${}使用的都是 PrepareSatement。请回看前面测试打印出 SQL，前面都有一个 Preparing，这就是明显的提示。（详见org.apache.ibatis.logging.jdbc.ConnectionLogger#invoke）

事实上在Insert、Update、Delete、Select的时候都有一个statementType属性，类型为StatementType，取值范围为STATEMENT、PREPARED或 CALLABLE中的一个，这会让 MyBatis 分别使用Statement、PreparedStatement或CallableStatement。

statementType属性的默认值为PREPARED，因此默认的 SQL 语句都是通过PreparedStatement来执行的。

参考

mybatis-spring-boot-autoconfigure – MyBatis Sring-BootStarter | Reference Documentation
mybatis – MyBatis 3 | Mapper XML Files