聊聊mysql jdbc的prepareStatement

序

本文主要研究一下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为例

	/**
	* 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;

	/**
	* 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);
	}

	/**
	* 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;
	}

	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);
	}

	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;
	}

	/** 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;
	}

	/**
	* 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());
	}

	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;
	}
	}

	//......
	}

	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);
	}
	}

	/**
	* 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();
	}

	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);
	}

	private static boolean isJdbc4 = false;

	try {
	Class.forName("java.sql.NClob");
	isJdbc4 = true;
	} catch (Throwable t) {
	isJdbc4 = false;
	}

	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$

	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$