- When we write mybatis in the development process, most people may use #{}, but we also have another symbol ${}. What is the difference between them?
When applying variables, we use #{} by default, as follows:
<select id=getUserById resultType="org.hyf.mybatis.model.User"> select * from user where id = #{id}; </select>
<select id=getUserById resultType="org.hyf.mybatis.model.User"> select * from user where id = ${id}; </select>
From the above example, I don't seem to see any difference between them
1, Let's take a look at the differences between them through the log:
Add log dependency:
<dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>1.7.5</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>1.7.5</version> </dependency>
In the resources directory, add log4j properties
log4j.rootLogger=DEBUG,stdout log4j.logger.org.mybatis=DEBUG log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%5p %d %C: %m%n
First look at the log running through ${}:
As can be seen from the above log, ${} is a pure string replacement, and variable replacement will be carried out in the dynamic sql parsing stage
Just splice the sql directly, and the Parameters are empty
See #{} running log
Parameter markers that resolve to a JDBC precompiled statement
#{} is a placeholder to solve the problem of parameter passing. The replacement of variables is in the DBMS, and the replacement will automatically add single quotation marks'
${} is a parameter splicing problem. Variable replacement is in the dynamic SQL parsing stage, but there may be SQL injection in parameter splicing
2, Usage:
1. Use #{} where you can use #{} because sql injection in ${} is very unsafe. Maybe a query statement can become a table deletion operation
For example:
select * from ${tableName} where name=#{name} This time we pass tableName by user;delete user; -- sql After precompiling, it becomes: select * from user; delete user; -- where name = ?;
2. Indicates that ${} must be used as a variable
You know why? This is because if we use #{} placeholders to pass, we will automatically add single quotation marks' ', but ${} will not automatically add single quotation marks
select * from #{tableName} where name = #{name};
Become after precompiling
select * from ? where name = ?;
Suppose we pass in tableName=user name="zhangsan"
select * from 'User' where name='zhangsan';
The above sql statement is wrong, and the table name cannot be enclosed in single quotation marks
3, What is sql precompiling?
In the above introduction, sql precompiling is mentioned many times. sql precompiling is to compile the sql statement before the database driver sends the sql statement and parameters to the DBMS, so that the DBMS does not need to recompile when executing the sql statement