PreparedStatement 的 setString(int,String) 学习

前因: 今天一个sql在走jdbc 的时候 由于使用 PreparedStatement.setString(int,String) 的方法 把 原本的 sql中需要作为数字处理的 2 作为字符串处理了导致报错并查找了半小时(各种狂躁) 再加上 心情不好就打算研究一下。

 

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

首先是注释:

　　一个有参或无参的sql语句被存放在 PreparedStatement对象中预编译， 该对象能高效、可重复执行。

　　<B>NOTE</B> 这个对象是最佳的持有 带有一个或多个参数的 SQL statement，如果驱动支持与便那它能够高速的通过。在这个块(或者说实例？)中，

在 PreparedStatement 显式的调用 executed 方法之前 不会把 statement 发送到 数据库。只要任何方法抛出了 SQLException 的异常 就不会直接影响用户

　　<p>

　　　　　　由于 Mysql 并不支持 statement 预处理 所以 他们把 预处理放在了驱动中。(--! 好吧，mysql不支持预处理。。。。。。)

　　</p>

　　param　　　　sql

　　　　　　　　　　　　　　sql中存在一个或多个 ? 占位符的语句

　　return　　　　

　　　　　　　　　　　　　　返回一个 新的(注意！新的！)包含了预编译 statement 对象

　　exception　　SQLException 

　　　　　　　　　　　　　　如果 数据库存储 出现错误 则 抛出 sql异常

　　

　　public java.sql.PreparedStatement prepareStatement(String sql) throws SQLException {
        return prepareStatement(sql, DEFAULT_RESULT_SET_TYPE, DEFAULT_RESULT_SET_CONCURRENCY);
    }

　　DEFAULT_RESULT_SET_TYPE 与 DEFAULT_RESULT_SET_CONCURRENCY 详见 java.sql.ResultSet

 

  ----------------------------   我是分割线  -----------------------------------------------------------------

　　/**
     * 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 java.sql.PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        synchronized (getConnectionMutex()) {
            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(getMultiHostSafeProxy(), 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(getMultiHostSafeProxy(), 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;
        }
    }

　注释说明

　　在 JDBC 2.0 之前 PreparedStatement 没什么变化，只是之前 PreparedStatement 的 结果集类型 和结果集并发性类型 可以被重写

　　param　　　　　　sql

　　　　　　　　　　　　包含占位符的sql查询语句【question: 为什么是 query 不是 option? 】

　　　　　　　　　　　resultSetType

　　　　　　　　　　　　结果集类型 详见 　　ResultSet

　　　　　　　　　　　resultSetConcurrency

　　　　　　　　　　　　并发类型　详见　　ResultSet

　　return

　　　　　　　　　　　　返回一个新的 包含 预编译sql声明的PreparedStatement 类对象

 

　　　　　　/**

　　　　　　*  　　需要了解的变量 与方法

　　　　　　　　private InvocationHandler realProxy = null;

　　　　　　　　public Object getConnectionMutex() {
       　　　　　　　　 return (this.realProxy != null) ? this.realProxy : getProxy();
    　　　　　　　}

　　　　　　*/

　　　　　　可以知道的是这里的操作有 

　　　　　　　　1.连接的可用性做了验证

　　　　　　　　2.sql语句进行了 预处理

　　　　　　　　3.各种强转 。。。。。。

　　　　　　由于目的是 查看 setString() 的实现 所以 connection 就到这里了。

 

------------------------  我是分割线  以下是 PreparedStatement 的实现---------------------

 

首先来看一下 继承 与实现 的关系

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 {
                String jdbc4ClassName = Util.isJdbc42() ? "com.mysql.jdbc.JDBC42PreparedStatement" : "com.mysql.jdbc.JDBC4PreparedStatement";
                JDBC_4_PSTMT_2_ARG_CTOR = Class.forName(jdbc4ClassName).getConstructor(new Class[] { MySQLConnection.class, String.class });
                JDBC_4_PSTMT_3_ARG_CTOR = Class.forName(jdbc4ClassName).getConstructor(new Class[] { MySQLConnection.class, String.class, String.class });
                JDBC_4_PSTMT_4_ARG_CTOR = Class.forName(jdbc4ClassName)
                        .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;
        }
    }

　　声明了3个 构造器泛型

  通过反射拿到 Connection 的 3种构造器　　

　好吧大致想想就该知道 通过Connection 的构造器能干很多坏事了，唔。。。。。比如创建自己的实例对象。。。。。。

 

------------------------------ 我是分割线  直接查找 setString()方法 及其重写----------------------

　　/**
     * Set a parameter to a Java String value. The driver converts this to a SQL
     * VARCHAR or LONGVARCHAR value (depending on the arguments size relative to
     * the driver's limits on VARCHARs) when it sends it to the database.
     *
     * @param parameterIndex
     *            the first parameter is 1...
     * @param x
     *            the parameter value
     *
     * @exception SQLException
     *                if a database access error occurs
     */
    public void setString(int parameterIndex, String x) throws SQLException {
        synchronized (checkClosed().getConnectionMutex()) {
            // if the passed string is null, then set this column to null
            if (x == null) {
                setNull(parameterIndex, Types.CHAR);
            } else {
                checkClosed();

                int stringLength = x.length();

                if (this.connection.isNoBackslashEscapesSet()) {
                    // Scan for any nasty chars

                    boolean needsHexEscape = isEscapeNeededForString(x, stringLength);

                    if (!needsHexEscape) {
                        byte[] parameterAsBytes = null;

                        StringBuilder quotedString = new StringBuilder(x.length() + 2);
                        quotedString.append('\'');
                        quotedString.append(x);
                        quotedString.append('\'');

                        if (!this.isLoadDataQuery) {
                            parameterAsBytes = StringUtils.getBytes(quotedString.toString(), this.charConverter, this.charEncoding,
                                    this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor());
                        } else {
                            // Send with platform character encoding
                            parameterAsBytes = StringUtils.getBytes(quotedString.toString());
                        }

                        setInternal(parameterIndex, parameterAsBytes);
                    } else {
                        byte[] parameterAsBytes = null;

                        if (!this.isLoadDataQuery) {
                            parameterAsBytes = StringUtils.getBytes(x, this.charConverter, this.charEncoding, this.connection.getServerCharset(),
                                    this.connection.parserKnowsUnicode(), getExceptionInterceptor());
                        } else {
                            // Send with platform character encoding
                            parameterAsBytes = StringUtils.getBytes(x);
                        }

                        setBytes(parameterIndex, parameterAsBytes);
                    }

                    return;
                }

                String parameterAsString = x;
                boolean needsQuoted = true;

                if (this.isLoadDataQuery || isEscapeNeededForString(x, stringLength)) {
                    needsQuoted = false; // saves an allocation later

                    StringBuilder buf = new StringBuilder((int) (x.length() * 1.1));

                    buf.append('\'');

                    //
                    // Note: buf.append(char) is _faster_ than appending in blocks, because the block append requires a System.arraycopy().... go figure...
                    //

                    for (int i = 0; i < stringLength; ++i) {
                        char c = x.charAt(i);

                        switch (c) {
                            case 0: /* Must be escaped for 'mysql' */
                                buf.append('\\');
                                buf.append('0');

                                break;

                            case '\n': /* Must be escaped for logs */
                                buf.append('\\');
                                buf.append('n');

                                break;

                            case '\r':
                                buf.append('\\');
                                buf.append('r');

                                break;

                            case '\\':
                                buf.append('\\');
                                buf.append('\\');

                                break;

                            case '\'':
                                buf.append('\\');
                                buf.append('\'');

                                break;

                            case '"': /* Better safe than sorry */
                                if (this.usingAnsiMode) {
                                    buf.append('\\');
                                }

                                buf.append('"');

                                break;

                            case '\032': /* This gives problems on Win32 */
                                buf.append('\\');
                                buf.append('Z');

                                break;

                            case '\u00a5':
                            case '\u20a9':
                                // escape characters interpreted as backslash by mysql
                                if (this.charsetEncoder != null) {
                                    CharBuffer cbuf = CharBuffer.allocate(1);
                                    ByteBuffer bbuf = ByteBuffer.allocate(1);
                                    cbuf.put(c);
                                    cbuf.position(0);
                                    this.charsetEncoder.encode(cbuf, bbuf, true);
                                    if (bbuf.get(0) == '\\') {
                                        buf.append('\\');
                                    }
                                }
                                buf.append(c);
                                break;

                            default:
                                buf.append(c);
                        }
                    }

                    buf.append('\'');

                    parameterAsString = buf.toString();
                }

                byte[] parameterAsBytes = null;

                if (!this.isLoadDataQuery) {
                    if (needsQuoted) {
                        parameterAsBytes = StringUtils.getBytesWrapped(parameterAsString, '\'', '\'', this.charConverter, this.charEncoding,
                                this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor());
                    } else {
                        parameterAsBytes = StringUtils.getBytes(parameterAsString, this.charConverter, this.charEncoding, this.connection.getServerCharset(),
                                this.connection.parserKnowsUnicode(), getExceptionInterceptor());
                    }
                } else {
                    // Send with platform character encoding
                    parameterAsBytes = StringUtils.getBytes(parameterAsString);
                }

                setInternal(parameterIndex, parameterAsBytes);

                this.parameterTypes[parameterIndex - 1 + getParameterIndexOffset()] = Types.VARCHAR;
            }
        }
    }

 

　　写在在分析代码之前：

　　　　　　虽然之前在编辑器上 看过了 setString 的说明，但是在用 limit 之前 int 、date 我通过setString  保存成功过 就没特别注意这个方法应该是与数据库的特性相关联的，如果数据库的逻辑特别严谨的话，应该还是会报错的，

　　　之前 int、date 存入了mysql 应该说感谢mysql 的强大的自转换功能吧。今后的代码还是应该越严谨越好，不能为了抽象而抽象。

　　

　　设置参数为 JAVA String 类型值。 驱动使这个转变为 sql 的 varchar 或者 longvarchar 类型的值(取决于 驱动对于 varchars 的大小限制 和 参数本身的大小) 当 它发送给数据库的时候。

　　

　　param　　　　　　　　

　　　　　　　　　　　int　　　parameterIndex　　　　　　下标从 1 开始

　　　　　　　　　　　String　x　　　　　　　　　　　　　　参数值

　　获取参数长度

　　检查是否有反斜杠转义