Mybatis中#和$的区别及sql预编译

动态SQL是Mybatis的强大特性之一。在使用Mybatis进行开发的过程中，经常需要动态传入参数。假如我们需要根据用户名称name来筛选用户，需要在映射文件中这样写：

select * from user where name = #{name};
或者
select * from user where name = ${name};

在一般情况下， #{} 与 ${} 达到的效果是一致的，但是在某些特殊场景下，二者有很大的区别，我们只能择其一。

#{} 与 ${}的区别

为了直观展现二者的区别，我们先来看一个示例：
mybatis 在对 sql 语句进行预编译之前，会对 sql 进行动态解析，解析为一个 BoundSql 对象，也是在此处对动态 SQL 进行处理的。

SELECT *
FROM machine
WHERE id = #{id} AND ip = #{ip}

-->(1,"1")

com.mysql.jdbc.JDBC4PreparedStatement@fa12711: 
SELECT *
FROM machine
WHERE id = 1 AND ip = '1'

[DEBUG] ...DAO.test - ==>  Preparing: SELECT * FROM machine where id = ? and ip = ?
[DEBUG] ...DAO.test - ==> Parameters: 1(Integer), 1(String)
[DEBUG] ...DAO.test - <==      Total: 1

-----------------------------------------------
SELECT *
FROM machine
ORDER BY id #{order}

-->("ASC")

com.mysql.jdbc.JDBC4PreparedStatement@8a592b9: 
SELECT *
FROM machine
ORDER BY id 'ASC'

[DEBUG] ...DAO.test - ==>  Preparing: SELECT * FROM machine ORDER BY id ?
[DEBUG] ...DAO.test - ==> Parameters: ASC(String)
[ERROR] merge sql error, dbType mysql, sql : SELECT * FROM machine ORDER BY id ?
com.alibaba.druid.sql.parser.ParserException: syntax error, error in :'R BY id ?',expect QUES, actual QUES id
druid.sql.Statement - {conn-10020, pstmt-20001} execute error. SELECT * FROM machine ORDER BY id ?

-----------------------------------------------
SELECT *
FROM machine
ORDER BY id ${order}

-->("ASC")

com.mysql.jdbc.JDBC4PreparedStatement@7f0610f5: 
SELECT *
FROM machine
ORDER BY id ASC

[DEBUG] ...DAO.test - ==>  Preparing: SELECT * FROM machine ORDER BY id ASC
[DEBUG] ...DAO.test - ==> Parameters:
[DEBUG] ...DAO.test - <==      Total: 3

-----------------------------------------------
SELECT *
FROM machine
WHERE id != #{id}
ORDER BY id ${order}

-->(4,"ASC")

com.mysql.jdbc.JDBC4PreparedStatement@662af804: 
SELECT *
FROM machine
WHERE id != 4
ORDER BY id ASC

[DEBUG] ...DAO.test - ==>  Preparing: SELECT * FROM machine WHERE id != ? ORDER BY id ASC
[DEBUG] ...DAO.test - ==> Parameters: 4(Integer)
[DEBUG] ...DAO.test - <==      Total: 3

从上面的示例中，我们可以看出：

1. 使用#时，Mybatis在动态解析阶段会将参数解析为一个参数占位符？，而使用$时，直接进行了参数替换。
2. 在某些情况下，使用#{}会造成sql预编译错误

这是因为，在JDBC中，主要有两种执行过程，一种是Statement直接执行原生的SQL，还有一种是支持预编译的PrepareStatement，可以通过设置占位符的方式来支持sql语句的参数化。
Mybatis隐藏了一些底层的细节，在开发的过程中，我们通过使用#和$来告诉Mybatis，是使用原生的SQL，还是使用PrepareStatement。

SQL预编译

通常一条SQL在数据库接收到执行完毕返回，会经历以下三个过程：

1. 词法和语义解析
2. 优化sql语句，制定执行计划
3. 执行并返回结果

在很多情况下，一条SQL语句可能需要反复执行，或者每次执行的时候只有个别的值不同（比如query的where子句值不同，update的set子句值不同，insert的values值不同）。
如果每次都需要经过上面的词法语义解析、语句优化、制定执行计划等，则效率就明显不行了。

所谓预编译语句就是将这类语句中的值用占位符替代，可以视为将sql语句模板化或者说参数化。
预编译语句的优势在于：

1. 提高效率：一次编译、多次运行，省去了解析优化等过程；
2. 提高安全性：防止sql注入。

Statement的执行机制

Statement的excute方法直接将SQL语句作为参数传入并提交给数据库执行 -> 数据库引擎对SQL语句进行编译得到数据库可执行代码 -> 执行SQL语句。
这就表示，每次提交SQL都需要经过编译再执行。

PreparedStatement的预编译机制

通过 PreparedStatement Connection.prepareStatement(String sql);方法可以得到PreparedStatement SQL语句对象。可以看到，在创建对象时，就需要传入SQL语句，此时会将该SQL语句直接提交给数据库进行编译，得到的PreparedStatement句柄其实是一个预编译好的SQL语句。
之后调用excute方法会直接将该预编译好的语句提交给数据库运行，提高了效率。

预编译如何提高SQL安全性

Statement不支持预编译，因此，当SQL语句中包含可变内容时，需要进行字符串拼接，在这个过程中，很容易发生拼接错误，甚至会埋下SQL注入的风险。
举个例子，如果需要从数据表中查询某个用户的数据，我们会这样拼接SQL：

select * from user where name = '" + user_name+"'";

如果用户在输入框中填入' or true，那么SQL就会被拼接为：

select * from user where name = '' or true ;

Statement将上述拼接好的SQL直接提交到数据库，查出了所有的用户信息，这样，黑客通过SQL注入成功提权。
但是如果进行了SQL预编译，那么词法分析、语义分析等过程都已经执行完毕，这就说明关键字、执行逻辑等都不会再变化，编译后注入的部分无法再改变执行逻辑，被当成字符串处理，从而达到了防止SQL注入的目的。
上述语句使用预编译后，传入的内容都被当中字符串，单引号进行了转义：

select * from user where name = '\' or true';

预编译的局限性

由于预编译时数据库会进行词法和语义的解析、生成执行计划，因此占位符只能占位SQL语句中的普通值，而表名、列名、关键字等影响编译的部分是不可以使用占位符的。
因此，示例1中排序的参数ASC只能使用${}传参。

#与$的使用场景

1. 能使用#{ }的地方就使用#{ }

这样不仅可以提高SQL执行的效率，还可以防止SQL注入。

2. 表名、列名、关键字等必须使用${}

参考文档

1. mybatis深入理解(一)之 # 与 $ 区别以及 sql 预编译
2. [疯狂Java]JDBC：PreparedStatement预编译执行SQL语句