Detailed explanation of the difference and usage of # and $ symbols in Mabitis

This article mainly introduces the difference between # and $ symbols in Mabitis. Friends who need it can refer to it

1. Introduction

Mybatis uses the configuration in Mapper.xml to perform sql queries. It is often necessary to dynamically pass parameters. For example, when we need to filter users based on their names, the 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 can be queried based on different names at different times. Use the following sql in the Mapper.xml file to dynamically pass parameter name:

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

or:

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

2. $ and

#1. Difference:

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

#{ }: Parsed into a parameter marker of a JDBC prepared statement.

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

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

is dynamically resolved to:

select * from user where name = ?;

A #{ } is parsed as a parameter placeholder?.

And ${ } is just a pure string replacement, variable replacement will be performed during the dynamic SQL parsing phase.

For example, the following sql in Mapper.xml:

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

When the parameter we pass is "Jack", the parsing of the above sql For:

select * from user where name = "Jack";

The SQL statement before precompilation no longer contains variables, it is completely constant data.

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

3. Usage

1. Where #{ } can be used, use #{ }

First of all, this is for performance reasons. The same precompiled sql can be reused. Secondly, ${ } has been replaced by variables before precompilation, which will cause SQL injection problems. 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 stage, before pre-compilation The sql will become:

select * from user; delete user; -- where name = ?; 
-- 之后的语句将作为注释，不起作用，因此本来的一条查询语句偷偷的包含了一个删除表数据的 SQL。

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

must be used This is because the table name is a string. When using SQL placeholder to replace the string, single quotes '' will be included, which will cause SQL syntax errors, such as:

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

The precompiled sql becomes:

select * from ? where name = ?;

Assume that the parameters we pass in are tableName = "user", name = "Jack" , then after the placeholder is replaced with variables, the sql statement becomes:

select * from 'user' where name='Jack';

The above sql statement has a syntax error, and the table name cannot be enclosed in single quotes. '' (note that backticks `` are allowed).

4. SQL pre-compilation

1. Definition:

sql pre-compilation refers to The database driver compiles the SQL statement before sending the SQL statement and parameters to the DBMS, so that when the DBMS executes the SQL, it does not need to be recompiled.

2. Why precompilation is needed

The object PreparedStatement is used in JDBC to abstract precompiled statements and use precompilation. The precompilation phase can optimize the execution of sql. In most cases, the precompiled SQL can be executed directly, and the DBMS does not need to be compiled again. The more complex the SQL, the greater the compilation complexity. The precompilation stage can combine multiple operations into one operation. Prepared statement objects can be reused. Cache the PreparedStatement object generated after a sql is precompiled. Next time for the same sql, you can directly use this cached PreparedState object. By default, mybatis will precompile all sql.

The above is the detailed content of Detailed explanation of the difference and usage of # and $ symbols in Mabitis. For more information, please follow other related articles on the PHP Chinese website!