Dynamic Data Access

While the DatabaseMetaData and ResultSetMetaData interfaces provide numerous methods to access a database without knowing its schema, other methods exist to support generic data access. These methods allow data to be accessed dynamically through generic Java objects. They are of the form getObject() and setObject() to retrieve and to set data, respectively, in a table or result set’s columns.

The usual methods to send and retrieve data allow you to map one or more SQL data types to specific Java types. The methods explained here allow you to use Java objects, regardless of what type SQL data is accessed. Precise mapping rules are used. Indeed, specific SQL data types must correspond to specific Java types, and specific Java types must correspond to specific SQL types.

Retrieving dynamically typed data

Dynamically typed data retrieval is performed using ResultSet.getObject() to access columns or CallableStatement.getObject() to access OUT parameters of stored procedures. Table 9-1 shows the default mapping from SQL types to Java types that are subtypes of Object. No mapping to Java streams is provided.

Table 9-1: DEFAULT MAPPING FROM SQL TYPES TO JAVA OBJECT TYPES

SQL Type	Java Object Type
CHAR	String
VARCHAR	String
LONGVARCHAR	String
NUMERIC	java.math.BigDecimal
DECIMAL	java.math.BigDecimal
BIT	Boolean
TINYINT	Integer
SQL Type	Java Object Type
SMALLINT	Integer
INTEGER	Integer
BIGINT	Long
REAL	Float
FLOAT	Double
DOUBLE	Double
BINARY	byte[ ]
VARBINARY	byte[ ]
LONGVARBINARY	byte[ ]
DATE	java.sql.Date
TIME	java.sql.Time
TIMESTAMP	java.sql.Timestamp
CLOB	Clob
BLOB	Blob
ARRAY	Array
DISTINCT	mapping of underlying type
STRUCT	Struct
REF	Ref
DATALINK	java.net.URL

The following methods apply to the ResultSet interface to retrieve dynamically typed data:

ResultSet Methods to Access Column Values as Objects

Object getObject(int columnIndex); Object getObject(String columnName);

The getObject() method is used like other getXXX() methods but returns a Java object whose type may be discovered using Table 9-1. This object corresponds to the SQL data type of the accessed result set column.

The following methods apply to the CallableStatement interface:

CallableStatement Methods to Access Parameters

void registerOutParameter(int parameterIndex, int sqlType); void registerOutParameter(int parameterIndex, int sqlType, int scale); Object getObject(int parameterIndex);

As shown earlier with callable statements, it is necessary to register OUT parameters before executing the call and accessing them. A Java object type corresponding to the SQL data type passed as parameter is returned.

Listing 9-6 provides a short example illustrating the use of the registerOutParameter() method when calling a stored procedure.

Listing 9-6: Accessing Parameters of CallableStatements

... ... // assume that this stored procedure provides one OUT parameter CallableStatement stmt = conn.prepareCall("{CALL getCurrentQuarterRevenue(?)}"); stmt.registerOutParameter(1, java.sql.Types.FLOAT); stmt.execute(); float revenue = stmt.getFloat(1); System.out.println("The revenue to date for the current quarter is: " + revenue); ... ...

Inserting or updating dynamically typed data

Dynamically typed data insertion or update may be performed only through IN parameters of stored procedures or prepared statements. Specific SQL data types may be explicitly targeted in accordance with Table 9-2.

Table 9-2: CONVERSIONS BETWEEN JAVA OBJECT TYPES AND TARGET SQL TYPES

	TINYINT	SMALLINT	INTEGER	BIGINT	REAL	FLOAT
String	J	J	J	J	J	J
BigDecimal	J	J	J	J	J	J
	TINYINT	SMALLINT	INTEGER	BIGINT	REAL	FLOAT
Boolean	J	J	J	J	J	J
Integer	J	J	J	J	J	J
Long	J	J	J	J	J	J
Float	J	J	J	J	J	J
Double	J	J	J	J	J	J
byte[ ]	-	-	-	-	-	-
java.sql. DateTime	-	-	-	-	-	-
java.sql. Time	-	-	-	-	-	-
java.sql. Timestamp	-	-	-	-	-	-
String	J	J	J	J	J	J
BigDecimal	J	J	J	J	J	J
Boolean	J	J	J	J	J	J
Integer	J	J	J	J	J	J
Long	J	J	J	J	J	J
Float	J	J	J	J	J	J
Double	J	J	J	J	J	J
byte[ ]	-	-	-	-	-	-
java.sql.Date	-	-	-	-	J	J
java.sql.Time	-	-	-	-	J	J
java.sql.Timestamp	-	-	-	-	J	J
	LONGVARCHAR	BINARY	VARBINARY	LONGVARBINARY
String	J	J	J	J
BigDecimal	J	-	-	-
Boolean	J	-	-	-
Integer	J	-	-	-
Long	J	-	-	-
Float	J	-	-	-
Double	J	-	-	-
byte[ ]	-	J	J	J
java.sql.Date	J	-	-	-
java.sql.Time	J	-	-	-
java.sql.Timestamp	J	-	-	-
	DATE	TIME	TIMESTAMP
String	J	J	J
BigDecimal	-	-	-
Boolean	-	-	-
Integer	-	-	-
Long	-	-	-
Float	-	-	-
Double	-	-	-
byte[ ]	-	-	-
java.sql.Date	J	J	-
java.sql.Time	-	J	-
java.sql.Timestamp	J	J	J

J means that the conversion can be done. No support for Java streams is provided.

The following methods apply to the PreparedStatement interface in order to set values for parameters:

CallableStatement or PreparedStatement: Setting Parameter Values

void setObject(int parameterIndex, Object x); void setObject(int parameterIndex, Object x, int targetSqlType); void setObject(int parameterIndex, Object x, int targetSqlType, int scale);

When no target SQL type is specified to the setObject() method, the Java object is directly converted according to the default mapping as shown in Table 9-3. If a target SQL type is provided, the Java object is first mapped to its default corresponding type and then converted to the specified SQL type (see Table 9-2).

Table 9-3: DEFAULT MAPPING FROM JAVA OBJECT TYPES TO SQL TYPES

Java Object Type	SQL Type
String	VARCHAR or LONGVARCHAR
java.math.BigDecimal	NUMERIC
Boolean	BIT
Integer	INTEGER
Long	BIGINT
Float	REAL
Double	DOUBLE
byte[ ]	VARBINARY or LONGVARBINARY
java.sql.Date	DATE
java.sql.Time	TIME
java.sql.Timestamp	TIMESTAMP