• 聊聊如何获取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
     * PreparedStatement object's parameters.
     *
     * @return a ParameterMetaData object that contains information
     *         about the number, types and properties for each
     *  parameter marker of this PreparedStatement object
     * @exception SQLException if a database access error occurs or
     * this method is called on a closed PreparedStatement
     * @see ParameterMetaData
     * @since 1.4
     */
    ParameterMetaData getParameterMetaData() throws SQLException;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36

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

    ParameterMetaData

    java/sql/ParameterMetaData.java

    public interface ParameterMetaData extends Wrapper {

    /**
     * Retrieves the number of parameters in the PreparedStatement
     * object for which this ParameterMetaData 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
     *        ParameterMetaData.parameterNoNulls,
     *        ParameterMetaData.parameterNullable, or
     *        ParameterMetaData.parameterNullableUnknown
     * @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 NULL values.
     */
    int parameterNoNulls = 0;

    /**
     * The constant indicating that a
     * parameter will allow NULL 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 true if so; false 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.
     *
     * 
    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 java.sql.Types
     * @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 PreparedStatement.setObject.
     *
     * @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
     *         PreparedStatement.setObject 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
     *        ParameterMetaData.parameterModeIn,
     *        ParameterMetaData.parameterModeOut, or
     *        ParameterMetaData.parameterModeInOut
     *        ParameterMetaData.parameterModeUnknown.
     * @exception SQLException if a database access error occurs
     * @since 1.4
     */
    int getParameterMode(int param) throws SQLException;
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154

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

	//......
}		

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52

    ParameterBindings定义了一系列的get方法

    EmulatedPreparedStatementBindings

    	class EmulatedPreparedStatementBindings implements ParameterBindings {

		private ResultSetImpl bindingsAsRs;
		private boolean[] parameterIsNull;
		
		EmulatedPreparedStatementBindings() throws SQLException {
			List rows = new ArrayList();
			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();
		}

		//......
	}		

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57

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

    小结

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

  • 相关阅读:
    SpringBoot基于javaweb的养老院敬老院管理系统
    Android 签名机制
    数字化转型入门
    0基础学习PyFlink——用户自定义函数之UDAF
    定制chromium中window对象添加函数
    使用 @antfu/eslint-config 配置 eslint (包含兼容uniapp方法)
    一线大厂Redis高并发缓存架构实战与性能优化
    Mybatis和MybatisPlus：数据库操作工具的对比
    使用 Ruby 的 Nokogiri 库来解析
    通过canvas获取图片的颜色值
  • 原文地址：https://blog.csdn.net/hello_ejb3/article/details/132707768