Mybatis中#{}与${}的区别

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