mybatis in-depth understanding of (1) the difference between # and $ and sql precompilation

Time:2022-8-6

When using sqlMap for sql query in mybatis, it is often necessary to dynamically pass parameters. For example, when we need to filter users according to their names, the sql is as follows:

select * from user where name = "ruhua";

In the above sql, we hope that the parameter "ruhua" after the name is dynamically variable, that is, users are queried according to different names at different times. Use the following sql in the xml file of sqlMap to dynamically pass the parameter name:

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

or

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

For the query case above, using #{ } and ${ } yields the same result, but in some cases we can only use either.

'#' and '$'

the difference

Dynamic SQLIt is one of the powerful features of mybatis and an important reason why it is superior to other ORM frameworks. Before precompiling the sql statement, mybatis will dynamically parse the sql and parse it into a BoundSql object, which is also where the dynamic SQL is processed.

During dynamic SQL parsing, #{ } and ${ } behave differently:

#{ } resolves to a parameter marker for a JDBC 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 just a pure string replacement, variable replacement will be performed during dynamic SQL parsing

For example, the following sql in sqlMap

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

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

select * from user where name = "ruhua";

The precompiled SQL statement no longer contains the variable name.

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

Usage tips

1. Use #{ } wherever #{ } can be used

First of all this is for performance reasons, the same precompiled sql can be reused.

Second,${ } has been replaced by variables before pre-compilation, which will cause sql injection problems. For example, the following sql,

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

Suppose, our parameter tableName isuser; delete user; --, then after the SQL dynamic parsing stage, the precompiled sql 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 a SQL to delete 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 single quotes are used when replacing the string with the sql placeholder'', which results in sql syntax errors, such as:

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

The precompiled sql becomes:

select * from ? where name = ?;

Assuming that the parameters we pass in are tableName = "user" , name = "ruhua", then after the variable substitution of the placeholder, the sql statement becomes

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

The above sql statement has a syntax error, and the table name cannot be added with single quotation marks''(Note that backticks “ are ok).

sql precompile

definition

SQL precompilation means that 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.

Why do you need to precompile

The object PreparedStatement is used in JDBC to abstract the prepared statement and use the precompiled

  1. The precompile phase can optimize the execution of sql
    The precompiled SQL can be directly executed in most cases, and the DBMS does not need to be compiled again. The more complex the SQL, the more complicated the compilation will be. The precompiled phase can combine multiple operations into one operation.

  2. Prepared statement objects can be reused
    Cache the PreparedStatement object generated after precompiling a SQL, and use the cached PreparedState object directly for the same SQL next time.

By default mybatis will precompile all sql.

MySQL precompiled source code analysis

The precompiled source code of mysql is incom.mysql.jdbc.ConnectionImplclass, as follows:

public synchronized java.sql.PreparedStatement prepareStatement(String sql,
            int resultSetType, int resultSetConcurrency) throws SQLException {
        checkClosed();

        //
        // FIXME: Create warnings if can't create results of the given
        // type or concurrency
        //
        PreparedStatement pStmt = null;
        
        boolean canServerPrepare = true;
        
        // Different database systems perform syntax conversion on sql
        String nativeSql = getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql): sql;
        
        // Determine if server-side precompilation is possible
        if (this.useServerPreparedStmts && getEmulateUnsupportedPstmts()) {
            canServerPrepare = canHandleAsServerPreparedStatement(nativeSql);
        }
        
        // if server-side precompilation is possible
        if (this.useServerPreparedStmts && canServerPrepare) {

            // Whether the PreparedStatement object is cached
            if (this.getCachePreparedStatements()) {
                synchronized (this.serverSideStatementCache) {
                    
                    // Get the cached PreparedStatement object from the cache
                    pStmt = (com.mysql.jdbc.ServerPreparedStatement)this.serverSideStatementCache.remove(sql);
                    
                    if (pStmt != null) {
                        // When there is an object in the cache, clear the parameters of the original sqlStatement, etc.
                        ((com.mysql.jdbc.ServerPreparedStatement)pStmt).setClosed(false);
                        pStmt.clearParameters();
                    }

                    if (pStmt == null) {
                        try {
                            // If it does not exist in the cache, call the server side (database) to precompile
                            pStmt = ServerPreparedStatement.getInstance(getLoadBalanceSafeProxy(), nativeSql,
                                    this.database, resultSetType, resultSetConcurrency);
                            if (sql.length() < getPreparedStatementCacheSqlLimit()) {
                                ((com.mysql.jdbc.ServerPreparedStatement)pStmt).isCached = true;
                            }
                            
                            // Set return type and concurrency type
                            pStmt.setResultSetType(resultSetType);
                            pStmt.setResultSetConcurrency(resultSetConcurrency);
                        } catch (SQLException sqlEx) {
                            // Punt, if necessary
                            if (getEmulateUnsupportedPstmts()) {
                                pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
                                
                                if (sql.length() < getPreparedStatementCacheSqlLimit()) {
                                    this.serverSideStatementCheckCache.put(sql, Boolean.FALSE);
                                }
                            } else {
                                throw sqlEx;
                            }
                        }
                    }
                }
            } else {

                // When the cache is not enabled, directly call the server side to precompile
                try {
                    pStmt = ServerPreparedStatement.getInstance(getLoadBalanceSafeProxy(), nativeSql,
                            this.database, resultSetType, resultSetConcurrency);
                    
                    pStmt.setResultSetType(resultSetType);
                    pStmt.setResultSetConcurrency(resultSetConcurrency);
                } catch (SQLException sqlEx) {
                    // Punt, if necessary
                    if (getEmulateUnsupportedPstmts()) {
                        pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
                    } else {
                        throw sqlEx;
                    }
                }
            }
        } else {
            // Client-side pre-compilation is called when server-side pre-compilation is not supported (database connection is not required)
            pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);
        }
        
        return pStmt;
    }

The flow chart is as follows:

mybatis in-depth understanding of (1) the difference between # and $ and sql precompilation

Mybatis's sql dynamic parsing and precompiled source code

mybatis sql dynamic parsing

Before calling connection to precompile sql, mybatis will dynamically parse the sql statement. The dynamic parsing mainly includes the following functions:

  • Handling of placeholders

  • Processing of dynamic sql

  • parameter type check

The specific implementation of the powerful dynamic SQL function of mybatis is here. Dynamic parsing involves too many things to discuss later.

Summarize

This article mainly explores the different ways mybatis handles #{ } and ${ }, and learns about sql precompilation.