聊聊mysql jdbc的prepareStatement

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