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.windmt.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.windmt: 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/windmt/mybatis/mapper/UserMapper.java (best guess)
### The error may involve com.windmt.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/windmt/mybatis/mapper/UserMapper.java (best guess)
### The error may involve com.windmt.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

事实上在InsertUpdateDeleteSelect的时候都有一个statementType属性,类型为StatementType,取值范围为STATEMENTPREPAREDCALLABLE中的一个,这会让 MyBatis 分别使用StatementPreparedStatementCallableStatement

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

参考

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