Mabitis and $symbol differences and usage introduction

1. Introduction

In mybatis, the configuration in ES5en.xml is used for sql query, which often requires dynamic passing of 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 want the parameter "Jack" after name to be dynamically variable, that is, to query the user at different times according to different names. In Mapper. xml file, the following sql can be used to dynamically pass the parameter name:

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

Or:

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

2. $#

1. The difference between:

Dynamic SQL is one of the most powerful features of mybatis and one of the most important reasons why it is superior to other ORM frameworks. mybatis dynamically parses sql to 1 BoundSql object before precompiling the sql statement, and this is where dynamic SQL is processed. During the dynamic SQL parsing phase, #{} and ${} will behave differently.

#{} : Parameter marker resolved to 1 JDBC precompiled statement (prepared statement).

For example, the sql statement in ES46en.xml reads as follows:

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

Dynamic analysis is as follows:

select * from user where name = ?; 

1 #{} is resolved to 1 parameter placeholder ? .

The ${} is just a pure broken string replacement, which will be replaced during the dynamic SQL parsing phase.

For example, sql in Mapper is as follows:

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

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

select * from user where name = "Jack"; 

The pre-compiled SQL statement no longer contains variables and is completely constant data.

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

3. Usage

1. Use #{} where you can.

First, for performance reasons, the same precompiled sql can be reused. Second, ${} has been replaced by a variable prior to precompilation, causing sql injection issues. For example, sql as follows:

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

Suppose our parameter tableName is user; delete user; After the dynamic parsing phase of SQL, the pre-compiled sql will become:

select * from user; delete user; -- where name = ?; 
--  The statement that follows will act as a comment and will not work, so it should 1 A query is secretly included 1 Three delete table data  SQL . 

2. You must use ${} when the table name is a variable

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

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

The pre-compiled sql becomes:

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

0

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

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

1

The sql statement above is grammatically incorrect, and table names cannot be enclosed in single quotes '' (note that backquotes '' is ok).

4. sql precompiled

1. Definition:

sql precompilation refers to the database driver compiling sql statements before sending sql statements and parameters to DBMS so that DBMS does not need to recompile when it executes sql.

2. Why precompile

JDBC USES object PreparedStatement to abstract precompiled statements, using precompiled. The precompilation phase optimizes the execution of sql. sql after precompilation can be executed directly in most cases. DBMS does not need to be compiled again. The more complex sql, the more complex the compilation will be. Precompiled statement objects can be reused. Cache the PreparedStatement object generated by the pre-compiled sql. Next time for the same sql, you can directly use the cached PreparedState object. By default, all sql will be precompiled.