• 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