共计 18064 个字符，预计需要花费 46 分钟才能阅读完成。
序
本文次要钻研一下 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 为例
正文完