Mybatis中#{}与${}的区别
根据一个需求来实验一下：
可以在前台拼接sql例如：
很多条件可以一直and、and、and
一开始我使用的是#{}进行传值，后台出错了
测试接口如下：
@Autowired EmploymentMessageService employmentMessageService; @Test public void getData(){ List<EmploymentMessage> employmentMessageData = employmentMessageService.getEmploymentMessageData("AND COMPANY_HIGHERNUM=150"); System.out.println(employmentMessageData.size()); }
测试SQL:
<select id="getEmploymentMessageData" resultType="cn.bigdata.entity.EmploymentMessage"> select * from employment_message WHERE 1=1 #{sql} </select>
报错如下：
org.springframework.jdbc.BadSqlGrammarException: ### Error querying database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''AND COMPANY_HIGHERNUM=150'' at line 1 ### The error may exist in file [H:\Programme\IdeaProject\bigdata-boot\target\classes\cn\bigdata\mapper\EmploymentMapper.xml] ### The error may involve defaultParameterMap ### The error occurred while setting parameters ### SQL: select * from employment_message WHERE 1=1 ? ### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''AND COMPANY_HIGHERNUM=150'' at line 1 ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''AND COMPANY_HIGHERNUM=150'' at line 1
解决办法：
xml里的sql中的接收参数改为如下：
<select id="getEmploymentMessageData" resultType="cn.bigdata.entity.EmploymentMessage"> select * from employment_message WHERE 1=1 #{sql} </select>
返回：
2019-08-12 22:32:23.864 INFO 16800 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2019-08-12 22:32:24.056 INFO 16800 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. 2019-08-12 22:32:24.064 DEBUG 16800 --- [ main] c.b.d.E.getEmploymentMessageData : ==> Preparing: select * from employment_message WHERE 1=1 AND COMPANY_HIGHERNUM=150 2019-08-12 22:32:24.093 DEBUG 16800 --- [ main] c.b.d.E.getEmploymentMessageData : ==> Parameters: 2019-08-12 22:32:25.902 DEBUG 16800 --- [ main] c.b.d.E.getEmploymentMessageData : <== Total: 56515 56515
总结：
也就是说使用#{}时，mybatis会自动加上引号。如果不想让加那么就使用${}
如果传递一个：AND COMPANY_HIGHERNUM=150
使用#{sql}会自动译为：’‘AND COMPANY_HIGHERNUM=150’’
运行sql则变成如下所示的代码，所以会出错：
select * from employment_message WHERE 1=1 ''AND COMPANY_HIGHERNUM=150''
使用${}会自动译为：AND COMPANY_HIGHERNUM=150
运行sql则：
select * from employment_message WHERE 1=1 AND COMPANY_HIGHERNUM=150
