MyBatis in-depth understanding of # and $ differences and SQL precompilation

When using Sqlmap for SQL queries in MyBatis, it is often necessary to dynamically pass parameters, such as when we need to filter the user based on the user's name, SQL is as follows:

select * from user where name = "ruhua";

In the above SQL, we want the parameter "Ruhua" after name to be dynamically variable, that is, different moments to query the user based on different names. The following SQL can be used in the Sqlmap XML file to implement the dynamic pass parameter name:

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

Or

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

The result of using #{} and ${} is the same for this type of query, but in some cases we can only use either.

The difference between ' # ' and ' $ '

Dynamic SQL is one of the powerful features of MyBatis and an important reason for its superiority over other ORM frameworks. MyBatis SQL statements are dynamically parsed, parsed into a Boundsql object, and dynamic SQL is processed here before the SQL statement is precompiled.

In the dynamic SQL parsing phase, #{} and ${} will behave differently:

#{} resolves to a parameter marker for a JDBC precompiled statement (prepared statement).

For example, the following SQL statement in Sqlmap

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

resolves to:

select * from user where name = ?;

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

and

${} is replaced only by a pure, broken string, and variable substitution is performed during the dynamic SQL parsing phase

For example, the following SQL in Sqlmap

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

When we pass a parameter of "Ruhua", the above SQL parsing is:

select * from user where name = "ruhua";

Pre-compiled SQL statements already do not contain the variable name.

In summary, the replacement phase of the variable for ${} is in the dynamic SQL parsing phase, while the replacement of the #{} variable is in the DBMS.

Usage Tips

1, can use #{} place is used #{}

First, this is for performance reasons, and the same precompiled SQL can be reused.

Second, ${} has been replaced by variables before precompiling, and there is a SQL injection problem. For example, the following SQL,

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

Assuming that our parameters are TableName user; delete user; -- , then the SQL Dynamic parsing phase will be preceded by the pre-compiled SQL into

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

--The statements that follow will not work as comments, so the original query statement secretly contains a sql! that deletes the table data.

2, table name as a variable, you must use ${}

This is because the table name is a string, and a single quotation mark is used when replacing the string with the SQL placeholder, ‘‘ which results in a SQL syntax error, such as:

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

The pre-compiled SQL becomes:

select * from ? where name = ?;

Assuming that the parameter we passed in is TableName = "User", name = "Ruhua", then after the placeholder is replaced by the variable, the SQL statement becomes

select * from ‘user‘ where name=‘ruhua‘;

There is a syntax error in the above SQL statement, and the table name cannot be enclosed in single quotation marks ‘‘ (note that the anti-quote ' is possible).

SQL pre-compilation definition

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

Why pre-compiling is required

Use Object PreparedStatement in JDBC to abstract precompiled statements using precompiled

1. The pre-compilation phase optimizes SQL execution.

   Pre-compiled SQL can be executed in most cases, the DBMS does not need to be compiled again, the more complex the SQL, the greater the complexity of the compilation, the pre-compilation phase can be combined multiple operations as an operation.

2. Precompiled statement objects can be reused.

   Cache the PreparedStatement object generated after a SQL precompiled, and the next time for the same SQL, you can use the cached Preparedstate object directly.

MyBatis by default, all SQL is precompiled.

MySQL pre-compiled source code parsing

MySQL precompiled source code in the com.mysql.jdbc.ConnectionImpl class, as follows:

Public synchronized java.sql.PreparedStatement preparestatement (String sql, int resultsettype, int resultsetcon Currency) throws SQLException {checkclosed ();////Fixme:create warnings if can ' t Create results of the givenType or concurrencyPreparedStatement pstmt =Null Boolean canserverprepare =Truesyntax conversion for SQL by different database systems String NativeSql = Getprocessescapecodesforprepstmts ()? NativeSql (SQL): SQL;Determine if server-side precompilation is possibleif (This.useserverpreparedstmts && Getemulateunsupportedpstmts ()) {Canserverprepare = Canhandleasserverpreparedstatement (NativeSql); }If server-side precompilation is possibleif (This.useserverpreparedstmts && canserverprepare) {Whether the PreparedStatement object is cachedif (This.getcachepreparedstatements ()) {synchronized (This.serversidestatementcache) {Gets the cached PreparedStatement object from the cache pstmt = (com.mysql.jdbc.ServerPreparedStatement)This.serverSideStatementCache.remove (SQL);if (pstmt! =NULL) {When an object exists in the cache, the original sqlstatement is emptied of parameters ((com.mysql.jdbc.ServerPreparedStatement) pstmt). setclosed (FALSE); Pstmt.clearparameters (); }if (pstmt = =NULL) {try {If the cache does not exist, then the server-side (database) is called to precompile pstmt = Serverpreparedstatement.getinstance (Getloadbalancesafeproxy (), NativeSql,This.database, ResultsetType, resultsetconcurrency);if (Sql.length () < Getpreparedstatementcachesqllimit ()) {((com.mysql.jdbc.ServerPreparedStatement) pstmt). IsCached =True }Set the return type and concurrency type Pstmt.setresultsettype (resultsettype); Pstmt.setresultsetconcurrency (resultsetconcurrency); }catch (SQLException Sqlex) {Punt, if necessaryif (Getemulateunsupportedpstmts ()) {pstmt = (PreparedStatement) clientpreparestatement (NativeSql, ResultSetType, resultSetConcurrency,FALSE);if (Sql.length () < Getpreparedstatementcachesqllimit ()) {This.serverSideStatementCheckCache.put (SQL,Boolean.false); } }else {Throw Sqlex; } } } } }else {Directly invoke server-side precompilation when caching is not enabledtry {pstmt = Serverpreparedstatement.getinstance (Getloadbalancesafeproxy (), NativeSql,this.database, ResultsetType, resultsetconcurrency); Pstmt.setresultsettype (ResultsetType); Pstmt.setresultsetconcurrency (resultsetconcurrency); } catch (SQLException Sqlex) { //Punt, if necessary if (Getemulateunsupportedpstmts ()) {pstmt = (preparedsta tement) clientpreparestatement (NativeSql, ResultsetType, resultSetConcurrency, false);} else { throw Sqlex;}}} } else { //does not support server-side precompilation when invoking client precompilation (database connection not required) pstmt = (PreparedStatement) clientpreparestatement (NativeSql , ResultsetType, resultSetConcurrency, false); } return pstmt;}          

The flowchart is as follows:

MyBatis SQL Dynamic parsing and pre-compiled source MyBatis SQL Dynamic parsing

MyBatis SQL statements are parsed dynamically before calling connection for SQL precompilation, and dynamic parsing includes the following features:

• Handling of placeholders

• Processing of dynamic SQL

• Parameter type check

This is where the mybatis powerful dynamic SQL features are implemented. Dynamic parsing involves too many things to discuss later.

Summarize

This article focuses on the different approaches of MyBatis to #{} and ${}, and understands SQL precompilation.

MyBatis in-depth understanding of # and $ differences and SQL precompilation