序
本文次要钻研一下mysql jdbc的prepareStatement
prepareStatement
java/sql/Connection.java
/** * Creates a <code>PreparedStatement</code> object for sending * parameterized SQL statements to the database. * <P> * A SQL statement with or without IN parameters can be * pre-compiled and stored in a <code>PreparedStatement</code> object. This * object can then be used to efficiently execute this statement * multiple times. * * <P><B>Note:</B> This method is optimized for handling * parametric SQL statements that benefit from precompilation. If * the driver supports precompilation, * the method <code>prepareStatement</code> will send * the statement to the database for precompilation. Some drivers * may not support precompilation. In this case, the statement may * not be sent to the database until the <code>PreparedStatement</code> * object is executed. This has no direct effect on users; however, it does * affect which methods throw certain <code>SQLException</code> objects. * <P> * Result sets created using the returned <code>PreparedStatement</code> * object will by default be type <code>TYPE_FORWARD_ONLY</code> * and have a concurrency level of <code>CONCUR_READ_ONLY</code>. * The holdability of the created result sets can be determined by * calling {@link #getHoldability}. * * @param sql an SQL statement that may contain one or more '?' IN * parameter placeholders * @return a new default <code>PreparedStatement</code> object containing the * pre-compiled SQL statement * @exception SQLException if a database access error occurs * or this method is called on a closed connection */ PreparedStatement prepareStatement(String sql) throws SQLException;java.sql.Connection定义了prepareStatement办法，依据sql创立PreparedStatement
ConnectionImpl
mysql-connector-java-5.1.21-sources.jar!/com/mysql/jdbc/ConnectionImpl.java
/** * A SQL statement with or without IN parameters can be pre-compiled and * stored in a PreparedStatement object. This object can then be used to * efficiently execute this statement multiple times. * <p> * <B>Note:</B> This method is optimized for handling parametric SQL * statements that benefit from precompilation if the driver supports * precompilation. In this case, the statement is not sent to the database * until the PreparedStatement is executed. This has no direct effect on * users; however it does affect which method throws certain * java.sql.SQLExceptions * </p> * <p> * MySQL does not support precompilation of statements, so they are handled * by the driver. * </p> * * @param sql * a SQL statement that may contain one or more '?' IN parameter * placeholders * @return a new PreparedStatement object containing the pre-compiled * statement. * @exception SQLException * if a database access error occurs. */ public java.sql.PreparedStatement prepareStatement(String sql) throws SQLException { return prepareStatement(sql, DEFAULT_RESULT_SET_TYPE, DEFAULT_RESULT_SET_CONCURRENCY); }mysql jdbc的ConnectionImpl实现了prepareStatement办法，依据正文，预编译次要是driver来解决
prepareStatement
mysql-connector-java-5.1.21-sources.jar!/com/mysql/jdbc/ConnectionImpl.java
/** * JDBC 2.0 Same as prepareStatement() above, but allows the default result * set type and result set concurrency type to be overridden. * * @param sql * the SQL query containing place holders * @param resultSetType * a result set type, see ResultSet.TYPE_XXX * @param resultSetConcurrency * a concurrency type, see ResultSet.CONCUR_XXX * @return a new PreparedStatement object containing the pre-compiled SQL * statement * @exception SQLException * if a database-access error occurs. */ 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; String nativeSql = getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql): sql; if (this.useServerPreparedStmts && getEmulateUnsupportedPstmts()) { canServerPrepare = canHandleAsServerPreparedStatement(nativeSql); } if (this.useServerPreparedStmts && canServerPrepare) { if (this.getCachePreparedStatements()) { synchronized (this.serverSideStatementCache) { pStmt = (com.mysql.jdbc.ServerPreparedStatement)this.serverSideStatementCache.remove(sql); if (pStmt != null) { ((com.mysql.jdbc.ServerPreparedStatement)pStmt).setClosed(false); pStmt.clearParameters(); } if (pStmt == null) { try { pStmt = ServerPreparedStatement.getInstance(getLoadBalanceSafeProxy(), nativeSql, this.database, resultSetType, resultSetConcurrency); if (sql.length() < getPreparedStatementCacheSqlLimit()) { ((com.mysql.jdbc.ServerPreparedStatement)pStmt).isCached = true; } 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 { 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 { pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false); } return pStmt; }prepareStatement首先依据useServerPreparedStmts以及getEmulateUnsupportedPstmts来判断是否要通过canHandleAsServerPreparedStatement判断canServerPrepare；之后在useServerPreparedStmts及canServerPrepare为true时，依据cachePreparedStatements做ServerPreparedStatement的解决；如果不开启serverPrepare则执行clientPrepareStatement
canHandleAsServerPreparedStatement
mysql-connector-java-5.1.21-sources.jar!/com/mysql/jdbc/ConnectionImpl.java
private boolean canHandleAsServerPreparedStatement(String sql) throws SQLException { if (sql == null || sql.length() == 0) { return true; } if (!this.useServerPreparedStmts) { return false; } if (getCachePreparedStatements()) { synchronized (this.serverSideStatementCheckCache) { Boolean flag = (Boolean)this.serverSideStatementCheckCache.get(sql); if (flag != null) { return flag.booleanValue(); } boolean canHandle = canHandleAsServerPreparedStatementNoCache(sql); if (sql.length() < getPreparedStatementCacheSqlLimit()) { this.serverSideStatementCheckCache.put(sql, canHandle ? Boolean.TRUE : Boolean.FALSE); } return canHandle; } } return canHandleAsServerPreparedStatementNoCache(sql); }canHandleAsServerPreparedStatement首先判断useServerPreparedStmts，之后若cachePreparedStatements为true则做serverSideStatementCheckCache判断，最初都会通过canHandleAsServerPreparedStatementNoCache进行判断
canHandleAsServerPreparedStatementNoCache
mysql-connector-java-5.1.21-sources.jar!/com/mysql/jdbc/ConnectionImpl.java
private boolean canHandleAsServerPreparedStatementNoCache(String sql) throws SQLException { // Can't use server-side prepare for CALL if (StringUtils.startsWithIgnoreCaseAndNonAlphaNumeric(sql, "CALL")) { return false; } boolean canHandleAsStatement = true; if (!versionMeetsMinimum(5, 0, 7) && (StringUtils.startsWithIgnoreCaseAndNonAlphaNumeric(sql, "SELECT") || StringUtils.startsWithIgnoreCaseAndNonAlphaNumeric(sql, "DELETE") || StringUtils.startsWithIgnoreCaseAndNonAlphaNumeric(sql, "INSERT") || StringUtils.startsWithIgnoreCaseAndNonAlphaNumeric(sql, "UPDATE") || StringUtils.startsWithIgnoreCaseAndNonAlphaNumeric(sql, "REPLACE"))) { // check for limit ?[,?] /* * The grammar for this (from the server) is: ULONG_NUM | ULONG_NUM * ',' ULONG_NUM | ULONG_NUM OFFSET_SYM ULONG_NUM */ int currentPos = 0; int statementLength = sql.length(); int lastPosToLook = statementLength - 7; // "LIMIT ".length() boolean allowBackslashEscapes = !this.noBackslashEscapes; char quoteChar = this.useAnsiQuotes ? '"' : '\''; boolean foundLimitWithPlaceholder = false; while (currentPos < lastPosToLook) { int limitStart = StringUtils.indexOfIgnoreCaseRespectQuotes( currentPos, sql, "LIMIT ", quoteChar, allowBackslashEscapes); if (limitStart == -1) { break; } currentPos = limitStart + 7; while (currentPos < statementLength) { char c = sql.charAt(currentPos); // // Have we reached the end // of what can be in a LIMIT clause? // if (!Character.isDigit(c) && !Character.isWhitespace(c) && c != ',' && c != '?') { break; } if (c == '?') { foundLimitWithPlaceholder = true; break; } currentPos++; } } canHandleAsStatement = !foundLimitWithPlaceholder; } else if (StringUtils.startsWithIgnoreCaseAndWs(sql, "CREATE TABLE")) { canHandleAsStatement = false; } else if (StringUtils.startsWithIgnoreCaseAndWs(sql, "DO")) { canHandleAsStatement = false; } else if (StringUtils.startsWithIgnoreCaseAndWs(sql, "SET")) { canHandleAsStatement = false; } return canHandleAsStatement; }canHandleAsServerPreparedStatementNoCache办法针对call、create table、do、set返回false，其余的针对小于5.0.7版本的做非凡判断，其余的默认返回true
clientPrepareStatement
mysql-connector-java-5.1.21-sources.jar!/com/mysql/jdbc/ConnectionImpl.java
/** A cache of SQL to parsed prepared statement parameters. */ private CacheAdapter<String, ParseInfo> cachedPreparedStatementParams; public java.sql.PreparedStatement clientPrepareStatement(String sql, int resultSetType, int resultSetConcurrency, boolean processEscapeCodesIfNeeded) throws SQLException { checkClosed(); String nativeSql = processEscapeCodesIfNeeded && getProcessEscapeCodesForPrepStmts() ? nativeSQL(sql): sql; PreparedStatement pStmt = null; if (getCachePreparedStatements()) { PreparedStatement.ParseInfo pStmtInfo = this.cachedPreparedStatementParams.get(nativeSql); if (pStmtInfo == null) { pStmt = com.mysql.jdbc.PreparedStatement.getInstance(getLoadBalanceSafeProxy(), nativeSql, this.database); this.cachedPreparedStatementParams.put(nativeSql, pStmt .getParseInfo()); } else { pStmt = new com.mysql.jdbc.PreparedStatement(getLoadBalanceSafeProxy(), nativeSql, this.database, pStmtInfo); } } else { pStmt = com.mysql.jdbc.PreparedStatement.getInstance(getLoadBalanceSafeProxy(), nativeSql, this.database); } pStmt.setResultSetType(resultSetType); pStmt.setResultSetConcurrency(resultSetConcurrency); return pStmt; }clientPrepareStatement在cachePreparedStatements为true时会从cachedPreparedStatementParams(缓存的key为nativeSql，value为ParseInfo)去获取ParseInfo，获取不到则执行com.mysql.jdbc.PreparedStatement.getInstance再放入缓存，获取到ParseInfo则通过com.mysql.jdbc.PreparedStatement(getLoadBalanceSafeProxy(), nativeSql,this.database, pStmtInfo)创立PreparedStatement；如果为false则间接通过com.mysql.jdbc.PreparedStatement.getInstance来创立PreparedStatement.getInstance
mysql-connector-java-5.1.21-sources.jar!/com/mysql/jdbc/PreparedStatement.java
/** * Creates a prepared statement instance -- We need to provide factory-style * methods so we can support both JDBC3 (and older) and JDBC4 runtimes, * otherwise the class verifier complains when it tries to load JDBC4-only * interface classes that are present in JDBC4 method signatures. */ protected static PreparedStatement getInstance(MySQLConnection conn, String sql, String catalog) throws SQLException { if (!Util.isJdbc4()) { return new PreparedStatement(conn, sql, catalog); } return (PreparedStatement) Util.handleNewInstance( JDBC_4_PSTMT_3_ARG_CTOR, new Object[] { conn, sql, catalog }, conn.getExceptionInterceptor()); }getInstance办法对于非jdbc4的间接new一个PreparedStatement，若应用了jdbc4则通过Util.handleNewInstance应用JDBC_4_PSTMT_3_ARG_CTOR的结构器反射创立
JDBC_4_PSTMT_3_ARG_CTOR
mysql-connector-java-5.1.21-sources.jar!/com/mysql/jdbc/PreparedStatement.java
public class PreparedStatement extends com.mysql.jdbc.StatementImpl implements java.sql.PreparedStatement { private static final Constructor<?> JDBC_4_PSTMT_2_ARG_CTOR; private static final Constructor<?> JDBC_4_PSTMT_3_ARG_CTOR; private static final Constructor<?> JDBC_4_PSTMT_4_ARG_CTOR; static { if (Util.isJdbc4()) { try { JDBC_4_PSTMT_2_ARG_CTOR = Class.forName( "com.mysql.jdbc.JDBC4PreparedStatement") .getConstructor( new Class[] { MySQLConnection.class, String.class }); JDBC_4_PSTMT_3_ARG_CTOR = Class.forName( "com.mysql.jdbc.JDBC4PreparedStatement") .getConstructor( new Class[] { MySQLConnection.class, String.class, String.class }); JDBC_4_PSTMT_4_ARG_CTOR = Class.forName( "com.mysql.jdbc.JDBC4PreparedStatement") .getConstructor( new Class[] { MySQLConnection.class, String.class, String.class, ParseInfo.class }); } catch (SecurityException e) { throw new RuntimeException(e); } catch (NoSuchMethodException e) { throw new RuntimeException(e); } catch (ClassNotFoundException e) { throw new RuntimeException(e); } } else { JDBC_4_PSTMT_2_ARG_CTOR = null; JDBC_4_PSTMT_3_ARG_CTOR = null; JDBC_4_PSTMT_4_ARG_CTOR = null; } } //......} com.mysql.jdbc.PreparedStatement在static办法初始化了JDBC_4_PSTMT_3_ARG_CTOR，其结构器有三个参数，别离是MySQLConnection.class, String.class,String.class，应用的类是com.mysql.jdbc.JDBC4PreparedStatement
JDBC4PreparedStatement
mysql-connector-java-5.1.21-sources.jar!/com/mysql/jdbc/JDBC4PreparedStatement.java
public class JDBC4PreparedStatement extends PreparedStatement { public JDBC4PreparedStatement(MySQLConnection conn, String catalog) throws SQLException { super(conn, catalog); } public JDBC4PreparedStatement(MySQLConnection conn, String sql, String catalog) throws SQLException { super(conn, sql, catalog); } public JDBC4PreparedStatement(MySQLConnection conn, String sql, String catalog, ParseInfo cachedParseInfo) throws SQLException { super(conn, sql, catalog, cachedParseInfo); } public void setRowId(int parameterIndex, RowId x) throws SQLException { JDBC4PreparedStatementHelper.setRowId(this, parameterIndex, x); } /** * JDBC 4.0 Set a NCLOB parameter. * * @param i * the first parameter is 1, the second is 2, ... * @param x * an object representing a NCLOB * * @throws SQLException * if a database error occurs */ public void setNClob(int parameterIndex, NClob value) throws SQLException { JDBC4PreparedStatementHelper.setNClob(this, parameterIndex, value); } public void setSQLXML(int parameterIndex, SQLXML xmlObject) throws SQLException { JDBC4PreparedStatementHelper.setSQLXML(this, parameterIndex, xmlObject); }} JDBC4PreparedStatement的三个参数结构器次要是调用了父类PreparedStatement的对应的结构器；JDBC4PreparedStatement次要是反对了setNClob、setSQLXML
new PreparedStatement
mysql-connector-java-5.1.21-sources.jar!/com/mysql/jdbc/PreparedStatement.java
/** * Constructor for the PreparedStatement class. * * @param conn * the connection creating this statement * @param sql * the SQL for this statement * @param catalog * the catalog/database this statement should be issued against * * @throws SQLException * if a database error occurs. */ public PreparedStatement(MySQLConnection conn, String sql, String catalog) throws SQLException { super(conn, catalog); if (sql == null) { throw SQLError.createSQLException(Messages.getString("PreparedStatement.0"), //$NON-NLS-1$ SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); } detectFractionalSecondsSupport(); this.originalSql = sql; if (this.originalSql.startsWith(PING_MARKER)) { this.doPingInstead = true; } else { this.doPingInstead = false; } this.dbmd = this.connection.getMetaData(); this.useTrueBoolean = this.connection.versionMeetsMinimum(3, 21, 23); this.parseInfo = new ParseInfo(sql, this.connection, this.dbmd, this.charEncoding, this.charConverter); initializeFromParseInfo(); this.compensateForOnDuplicateKeyUpdate = this.connection.getCompensateOnDuplicateKeyUpdateCounts(); if (conn.getRequiresEscapingEncoder()) charsetEncoder = Charset.forName(conn.getEncoding()).newEncoder(); }这里次要是用了connection的metaData、以及结构ParseInfo
StatementImpl
mysql-connector-java-5.1.21-sources.jar!/com/mysql/jdbc/StatementImpl.java
public StatementImpl(MySQLConnection c, String catalog) throws SQLException { if ((c == null) || c.isClosed()) { throw SQLError.createSQLException( Messages.getString("Statement.0"), //$NON-NLS-1$ SQLError.SQL_STATE_CONNECTION_NOT_OPEN, null); //$NON-NLS-1$ //$NON-NLS-2$ } this.connection = c; this.connectionId = this.connection.getId(); this.exceptionInterceptor = this.connection .getExceptionInterceptor(); this.currentCatalog = catalog; this.pedantic = this.connection.getPedantic(); this.continueBatchOnError = this.connection.getContinueBatchOnError(); this.useLegacyDatetimeCode = this.connection.getUseLegacyDatetimeCode(); if (!this.connection.getDontTrackOpenResources()) { this.connection.registerStatement(this); } // // Adjust, if we know it // if (this.connection != null) { this.maxFieldSize = this.connection.getMaxAllowedPacket(); int defaultFetchSize = this.connection.getDefaultFetchSize(); if (defaultFetchSize != 0) { setFetchSize(defaultFetchSize); } if (this.connection.getUseUnicode()) { this.charEncoding = this.connection.getEncoding(); this.charConverter = this.connection.getCharsetConverter(this.charEncoding); } boolean profiling = this.connection.getProfileSql() || this.connection.getUseUsageAdvisor() || this.connection.getLogSlowQueries(); if (this.connection.getAutoGenerateTestcaseScript() || profiling) { this.statementId = statementCounter++; } if (profiling) { this.pointOfOrigin = new Throwable(); this.profileSQL = this.connection.getProfileSql(); this.useUsageAdvisor = this.connection.getUseUsageAdvisor(); this.eventSink = ProfilerEventHandlerFactory.getInstance(this.connection); } int maxRowsConn = this.connection.getMaxRows(); if (maxRowsConn != -1) { setMaxRows(maxRowsConn); } this.holdResultsOpenOverClose = this.connection.getHoldResultsOpenOverStatementClose(); } version5013OrNewer = this.connection.versionMeetsMinimum(5, 0, 13); }这里会获取connection的一系列配置，同时对于须要trackOpenResources的会执行registerStatement(这个在realClose的时候会unregister)参数值
isJdbc4
com/mysql/jdbc/Util.java
private static boolean isJdbc4 = false; try { Class.forName("java.sql.NClob"); isJdbc4 = true; } catch (Throwable t) { isJdbc4 = false; }isJdbc4默认为false，在检测到java.sql.NClob类的时候为true；jdk8版本反对jdbc4
useServerPreparedStmts
com/mysql/jdbc/ConnectionPropertiesImpl.java
private BooleanConnectionProperty detectServerPreparedStmts = new BooleanConnectionProperty( "useServerPrepStmts", //$NON-NLS-1$ false, Messages.getString("ConnectionProperties.useServerPrepStmts"), //$NON-NLS-1$ "3.1.0", MISC_CATEGORY, Integer.MIN_VALUE); //$NON-NLS-1$useServerPreparedStmts默认为false
cachePrepStmts
com/mysql/jdbc/ConnectionPropertiesImpl.java
private BooleanConnectionProperty cachePreparedStatements = new BooleanConnectionProperty( "cachePrepStmts", //$NON-NLS-1$ false, Messages.getString("ConnectionProperties.cachePrepStmts"), //$NON-NLS-1$ "3.0.10", PERFORMANCE_CATEGORY, Integer.MIN_VALUE); //$NON-NLS-1$cachePrepStmts默认为false
小结
- mysql的jdbc driver的prepareStatement首先依据useServerPreparedStmts以及getEmulateUnsupportedPstmts来判断是否要通过canHandleAsServerPreparedStatement判断canServerPrepare；之后在useServerPreparedStmts及canServerPrepare为true时，依据cachePreparedStatements做ServerPreparedStatement的解决；如果不开启serverPrepare则执行clientPrepareStatement(
useServerPreparedStmts及cachePrepStmts参数默认为false) - clientPrepareStatement在cachePreparedStatements为true时会从cachedPreparedStatementParams(
缓存的key为nativeSql，value为ParseInfo)去获取ParseInfo，获取不到则执行com.mysql.jdbc.PreparedStatement.getInstance再放入缓存，获取到ParseInfo则通过com.mysql.jdbc.PreparedStatement(getLoadBalanceSafeProxy(), nativeSql,this.database, pStmtInfo)创立PreparedStatement；如果为false则间接通过com.mysql.jdbc.PreparedStatement.getInstance来创立 - useServerPreparedStmts为true时，创立的是ServerPreparedStatement(
创立的时候会触发prepare操作，往mysql服务端发送COM_PREPARE指令)，本地通过serverSideStatementCache类来缓存ServerPreparedStatement，key为sql
doc
- 预编译语句(Prepared Statements)介绍，以MySQL为例