聊聊如何获取PreparedStatement的参数

序

本文主要研究一下如何获取PreparedStatement的参数

PreparedStatement

java/sql/PreparedStatement.java

public interface PreparedStatement extends Statement {

     void setNull(int parameterIndex, int sqlType) throws SQLException;

     void setBoolean(int parameterIndex, boolean x) throws SQLException;

     void setInt(int parameterIndex, int x) throws SQLException;

     void setLong(int parameterIndex, long x) throws SQLException;

     //......

    default void setObject(int parameterIndex, Object x, SQLType targetSqlType,
             int scaleOrLength) throws SQLException {
        throw new SQLFeatureNotSupportedException("setObject not implemented");
    }

    default void setObject(int parameterIndex, Object x, SQLType targetSqlType)
      throws SQLException {
        throw new SQLFeatureNotSupportedException("setObject not implemented");
    }

    /**
     * Retrieves the number, types and properties of this
     * <code>PreparedStatement</code> object's parameters.
     *
     * @return a <code>ParameterMetaData</code> object that contains information
     *         about the number, types and properties for each
     *  parameter marker of this <code>PreparedStatement</code> object
     * @exception SQLException if a database access error occurs or
     * this method is called on a closed <code>PreparedStatement</code>
     * @see ParameterMetaData
     * @since 1.4
     */
    ParameterMetaData getParameterMetaData() throws SQLException;
}

PreparedStatement继承了Statement接口，它主要是多定义了一系列的set方法，但是没有定义get方法，只是定义了getParameterMetaData方法返回ParameterMetaData

ParameterMetaData

java/sql/ParameterMetaData.java

public interface ParameterMetaData extends Wrapper {

    /**
     * Retrieves the number of parameters in the <code>PreparedStatement</code>
     * object for which this <code>ParameterMetaData</code> object contains
     * information.
     *
     * @return the number of parameters
     * @exception SQLException if a database access error occurs
     * @since 1.4
     */
    int getParameterCount() throws SQLException;

    /**
     * Retrieves whether null values are allowed in the designated parameter.
     *
     * @param param the first parameter is 1, the second is 2, ...
     * @return the nullability status of the given parameter; one of
     *        <code>ParameterMetaData.parameterNoNulls</code>,
     *        <code>ParameterMetaData.parameterNullable</code>, or
     *        <code>ParameterMetaData.parameterNullableUnknown</code>
     * @exception SQLException if a database access error occurs
     * @since 1.4
     */
    int isNullable(int param) throws SQLException;

    /**
     * The constant indicating that a
     * parameter will not allow <code>NULL</code> values.
     */
    int parameterNoNulls = 0;

    /**
     * The constant indicating that a
     * parameter will allow <code>NULL</code> values.
     */
    int parameterNullable = 1;

    /**
     * The constant indicating that the
     * nullability of a parameter is unknown.
     */
    int parameterNullableUnknown = 2;

    /**
     * Retrieves whether values for the designated parameter can be signed numbers.
     *
     * @param param the first parameter is 1, the second is 2, ...
     * @return <code>true</code> if so; <code>false</code> otherwise
     * @exception SQLException if a database access error occurs
     * @since 1.4
     */
    boolean isSigned(int param) throws SQLException;

    /**
     * Retrieves the designated parameter's specified column size.
     *
     * <P>The returned value represents the maximum column size for the given parameter.
     * For numeric data, this is the maximum precision.  For character data, this is the length in characters.
     * For datetime datatypes, this is the length in characters of the String representation (assuming the
     * maximum allowed precision of the fractional seconds component). For binary data, this is the length in bytes.  For the ROWID datatype,
     * this is the length in bytes. 0 is returned for data types where the
     * column size is not applicable.
     *
     * @param param the first parameter is 1, the second is 2, ...
     * @return precision
     * @exception SQLException if a database access error occurs
     * @since 1.4
     */
    int getPrecision(int param) throws SQLException;

    /**
     * Retrieves the designated parameter's number of digits to right of the decimal point.
     * 0 is returned for data types where the scale is not applicable.
     *
     * @param param the first parameter is 1, the second is 2, ...
     * @return scale
     * @exception SQLException if a database access error occurs
     * @since 1.4
     */
    int getScale(int param) throws SQLException;

    /**
     * Retrieves the designated parameter's SQL type.
     *
     * @param param the first parameter is 1, the second is 2, ...
     * @return SQL type from <code>java.sql.Types</code>
     * @exception SQLException if a database access error occurs
     * @since 1.4
     * @see Types
     */
    int getParameterType(int param) throws SQLException;

    /**
     * Retrieves the designated parameter's database-specific type name.
     *
     * @param param the first parameter is 1, the second is 2, ...
     * @return type the name used by the database. If the parameter type is
     * a user-defined type, then a fully-qualified type name is returned.
     * @exception SQLException if a database access error occurs
     * @since 1.4
     */
    String getParameterTypeName(int param) throws SQLException;


    /**
     * Retrieves the fully-qualified name of the Java class whose instances
     * should be passed to the method <code>PreparedStatement.setObject</code>.
     *
     * @param param the first parameter is 1, the second is 2, ...
     * @return the fully-qualified name of the class in the Java programming
     *         language that would be used by the method
     *         <code>PreparedStatement.setObject</code> to set the value
     *         in the specified parameter. This is the class name used
     *         for custom mapping.
     * @exception SQLException if a database access error occurs
     * @since 1.4
     */
    String getParameterClassName(int param) throws SQLException;

    /**
     * The constant indicating that the mode of the parameter is unknown.
     */
    int parameterModeUnknown = 0;

    /**
     * The constant indicating that the parameter's mode is IN.
     */
    int parameterModeIn = 1;

    /**
     * The constant indicating that the parameter's mode is INOUT.
     */
    int parameterModeInOut = 2;

    /**
     * The constant indicating that the parameter's mode is  OUT.
     */
    int parameterModeOut = 4;

    /**
     * Retrieves the designated parameter's mode.
     *
     * @param param the first parameter is 1, the second is 2, ...
     * @return mode of the parameter; one of
     *        <code>ParameterMetaData.parameterModeIn</code>,
     *        <code>ParameterMetaData.parameterModeOut</code>, or
     *        <code>ParameterMetaData.parameterModeInOut</code>
     *        <code>ParameterMetaData.parameterModeUnknown</code>.
     * @exception SQLException if a database access error occurs
     * @since 1.4
     */
    int getParameterMode(int param) throws SQLException;
}

ParameterMetaDatat提供了getParameterCount、getParameterType、getParameterTypeName、getParameterClassName、getParameterMode

com.mysql.jdbc.PreparedStatement

com/mysql/jdbc/PreparedStatement.java

public class PreparedStatement extends com.mysql.jdbc.StatementImpl implements
        java.sql.PreparedStatement {

    //......

    protected int parameterCount;

    protected MysqlParameterMetadata parameterMetaData;

    private InputStream[] parameterStreams = null;

    private byte[][] parameterValues = null;

    /**
     * Only used by statement interceptors at the moment to
     * provide introspection of bound values
     */
    protected int[] parameterTypes = null;

    public ParameterBindings getParameterBindings() throws SQLException {
        synchronized (checkClosed()) {
            return new EmulatedPreparedStatementBindings();
        }
    }

    //......
}        

mysql的PreparedStatement实现定义了parameterCount、parameterMetaData、parameterStreams、parameterValues、parameterTypes属性，提供了getParameterBindings方法，返回的是EmulatedPreparedStatementBindings

ParameterBindings

com/mysql/jdbc/ParameterBindings.java

public interface ParameterBindings {

    public abstract Array getArray(int parameterIndex) throws SQLException;

    public abstract InputStream getAsciiStream(int parameterIndex) throws SQLException;

    public abstract BigDecimal getBigDecimal(int parameterIndex) throws SQLException;

    public abstract InputStream getBinaryStream(int parameterIndex) throws SQLException;

    public abstract java.sql.Blob getBlob(int parameterIndex) throws SQLException;

    public abstract boolean getBoolean(int parameterIndex) throws SQLException;
    
    public abstract byte getByte(int parameterIndex) throws SQLException;

    public abstract byte[] getBytes(int parameterIndex) throws SQLException;

    public abstract Reader getCharacterStream(int parameterIndex) throws SQLException;

    public abstract Clob getClob(int parameterIndex) throws SQLException;
    
    public abstract Date getDate(int parameterIndex) throws SQLException;
    
    public abstract double getDouble(int parameterIndex) throws SQLException;

    public abstract float getFloat(int parameterIndex) throws SQLException;

    public abstract int getInt(int parameterIndex) throws SQLException;
    
    public abstract long getLong(int parameterIndex) throws SQLException;

    public abstract Reader getNCharacterStream(int parameterIndex) throws SQLException;
    
    public abstract Reader getNClob(int parameterIndex) throws SQLException;
    
    public abstract Object getObject(int parameterIndex) throws SQLException;

    public abstract Ref getRef(int parameterIndex) throws SQLException;

    public abstract short getShort(int parameterIndex) throws SQLException;

    public abstract String getString(int parameterIndex) throws SQLException;

    public abstract Time getTime(int parameterIndex) throws SQLException;

    public abstract Timestamp getTimestamp(int parameterIndex) throws SQLException;

    public abstract URL getURL(int parameterIndex) throws SQLException;

    public abstract boolean isNull(int parameterIndex) throws SQLException;
}

ParameterBindings定义了一系列的get方法

EmulatedPreparedStatementBindings

    class EmulatedPreparedStatementBindings implements ParameterBindings {

        private ResultSetImpl bindingsAsRs;
        private boolean[] parameterIsNull;
        
        EmulatedPreparedStatementBindings() throws SQLException {
            List<ResultSetRow> rows = new ArrayList<ResultSetRow>();
            parameterIsNull = new boolean[parameterCount];
            System
                    .arraycopy(isNull, 0, this.parameterIsNull, 0,
                            parameterCount);
            byte[][] rowData = new byte[parameterCount][];
            Field[] typeMetadata = new Field[parameterCount];

            for (int i = 0; i < parameterCount; i++) {
                if (batchCommandIndex == -1)
                    rowData[i] = getBytesRepresentation(i);
                else
                    rowData[i] = getBytesRepresentationForBatch(i, batchCommandIndex);

                int charsetIndex = 0;

                if (parameterTypes[i] == Types.BINARY
                        || parameterTypes[i] == Types.BLOB) {
                    charsetIndex = 63;
                } else {
                    try {
                        String mysqlEncodingName = CharsetMapping
                                .getMysqlEncodingForJavaEncoding(connection
                                        .getEncoding(), connection);
                        charsetIndex = CharsetMapping
                                .getCharsetIndexForMysqlEncodingName(mysqlEncodingName);
                    } catch (SQLException ex) {
                        throw ex;
                    } catch (RuntimeException ex) {
                        SQLException sqlEx = SQLError.createSQLException(ex.toString(), SQLError.SQL_STATE_ILLEGAL_ARGUMENT, null);
                        sqlEx.initCause(ex);
                        throw sqlEx;
                    }
                }

                Field parameterMetadata = new Field(null, "parameter_"
                        + (i + 1), charsetIndex, parameterTypes[i],
                        rowData[i].length);
                parameterMetadata.setConnection(connection);
                typeMetadata[i] = parameterMetadata;
            }

            rows.add(new ByteArrayRow(rowData, getExceptionInterceptor()));

            this.bindingsAsRs = new ResultSetImpl(connection.getCatalog(),
                    typeMetadata, new RowDataStatic(rows), connection, null);
            this.bindingsAsRs.next();
        }

        //......
    }        

EmulatedPreparedStatementBindings实现了ParameterBindings接口，它主要是把参数组装到rowData，然后创建了RowDataStatic，构造ResultSetImpl这个对象来实现

小结

jdbc的PreparedStatement并未提供相应的get参数的方法，只能从driver的实现类去找，比如mysql的PreparedStatement实现提供了getParameterBindings方法，返回的是EmulatedPreparedStatementBindings，可以获取参数