Explanation of the difference between and $$in dynamic sql under Mybatis

1. Introduction

In mybatis, the configuration in Mapper. xml is used for sql query, and it is often necessary to dynamically pass parameters. For example, when we need to filter users according to their names, sql is as follows:

select * from user where name = "Jack";

In the above sql, we hope that the parameter "Jack" after name is dynamically variable, that is, users are queried according to different names at different times. Using the following sql in the Mapper. xml file enables dynamic passing of the parameter name:

select * from user where name = #{name}; 

Or is it:

select * from user where name = ${name}; 

2. $and #

1. Differences:

Dynamic SQL is one of the powerful features of mybatis, and it is also an important reason why it is superior to other ORM frameworks. mybatis will dynamically parse sql into an BoundSql object before precompiling sql statement, where dynamic SQL is also processed. In the dynamic SQL parsing phase, # {} and ${} will behave differently.

# {}: Parses to a parameter marker for an JDBC precompiled statement (prepared statement).

For example, the following sql statement in Mapper. xml:

select * from user where name = #{name}; 

Dynamic parsing is:

select * from user where name = ?; 

1 # {} parsed to 1 parameter placeholder? .

While ${} is only a pure broken string substitution, variable substitution will be performed in the dynamic SQL parsing stage.

For example, the following sql in Mapper. xml:

select * from user where name = ${name}; 

When the parameter we pass is "Jack", the above sql resolves as:

select * from user where name = "Jack"; 

The SQL statement before pre-compilation no longer contains variables, and is completely constant data.

To sum up, the substitution phase of ${} variable is in the dynamic SQL parsing phase, while the substitution of # {} variable is in DBMS.

3. Usage

1. Use # {} where you can use # {}

First of all, this is for performance reasons, and the same precompiled sql can be reused. Second, ${} has been replaced by variables before pre-compilation, which presents an sql injection problem. For example, the following sql:

select * from ${tableName} where name = #{name} 

If our parameter tableName is user; delete user; --, then after the SQL dynamic parsing phase, the sql before precompilation will become:

select * from user; delete user; -- where name = ?; 

The following statement will be used as a comment and will not work, so the original query statement secretly contains an SQL that deletes table data.

2. When the table name is used as a variable, you must use ${}

This is because the table name is a string, and replacing the string with an sql placeholder with a single quotation mark ''causes an sql syntax error, such as:

select * from #{tableName} where name = #{name}; 

The precompiled sql becomes:

select * from user where name = #{name}; 

0

Assuming that we pass in parameters tableName = "user" and name = "Jack", the sql statement becomes:

select * from user where name = #{name}; 

1

The above sql statement is syntactically incorrect, and table names cannot be quoted in single quotation marks (note that reverse quotation marks are OK).

4. sql Precompile

1. Definition:

sql precompilation means that the database driver compiles the sql statement before sending the sql statement and parameters to DBMS, so that DBMS does not need to recompile when it executes sql.

2. Why do you need to precompile

JDBC uses the object PreparedStatement to abstract precompiled statements, using precompilation. The pre-compilation phase optimizes the execution of sql. After precompilation, sql can be directly executed in most cases, while DBMS does not need to be compiled again. The more complex sql is, the greater the complexity of compilation will be. In the precompilation stage, multiple operations can be combined into one operation. Precompiled statement objects can be reused. Cache the PreparedStatement object generated after precompiling an sql, and use the cached PreparedState object directly for the same sql next time. By default, mybatis precompiles all sql.

Summarize