Dynamic Database Access

We expect most JDBC programmers will be programming with knowledge of their target database's schema. They can therefore use the strongly typed JDBC interfaces described in Section 7 for data access. However there is also another extremely important class of database access where an application (or an application builder) dynamically discovers the database schema information and uses that information to perform appropriate dynamic data access. This section describes the JDBC support for dynamic access.

    Metadata information

JDBC provides access to a number of different kinds of metadata, describing row results, statement parameters, database properties, etc., etc. We originally attempted to provide this information via extra methods on the core JDBC classes such as java.sql.Connection and java.sql.ResultSet. However, because of the complexity of the metadata methods and because they are likely to be used by only a small subset of JDBC programmers, we decided to split the metadata methods off into two separate Java interfaces.

In general, for each piece of metadata information we have attempted to provide a separate JDBC method that takes appropriate arguments and provides an appropriate Java result type. However, when a method such as Connection.getProcedures() returns a collection of values, we have chosen to use a java.sql.ResultSet to contain the results. The application programmer can then use normal ResultSet methods to iterate over the results.

We considered defining a set of enumeration types for retrieving collections of metadata results, but this seemed to add additional weight to the interface with little real value. JDBC programmers will already be familiar with using ResultSets, so using them for metadata results should not be too onerous.

A number of metadata methods take String search patterns as arguments. These search patterns are the same as for ODBC, where a `_' iimplies a match of any single character and a `%' implies a match of zero or more characters. For catalog and schema values, a Java empty string matches an `unnamed' value; and a Java null String causes that search criteria to be ignored.

The java.sql.ResultSetMetaData type provides a number of methods for discovering the types and properties of the columns of a particular java.sql.ResultSet object.

The java.sql.DatabaseMetaData interface provides methods for retrieving various metadata associated with a database. This includes enumerating the stored procedures in the database, the tables in the database, the schemas in the database, the valid table types, the valid catalogs, finding information on the columns in tables, access rights on columns, access rights on tables, minimal row identification, and so on.

    Dynamically typed data access

In Section 8 we described the normal mapping between SQL types and Java types. For example, a SQL INTEGER is normally mapped to a Java int. This supports a simple interface for reading and writing SQL values as simple Java types.

However, in order to support generic data access, we also provide methods that allow data to be retrieved as generic Java objects. Thus there is a ResultSet.getObject method, a PreparedStatement.setObject method, and a CallableStatement.getObject method. Note that for each of the two getObject methods you will need to narrow the resulting java.lang.Object object to a specific data type before you can retrieve a value.

Since the Java built-in types such as boolean and int are not subtypes of Object, we need to use a slightly different mapping from SQL types to Java object types for the getObject/setObject methods. This mapping is shown in Table 4.

SQL type	Java Object Type
CHAR	String
VARCHAR	String
LONGVARCHAR	String
NUMERIC	java.math.BigDecimal
DECIMAL	java.math.BigDecimal
BIT	Boolean
TINYINT	Integer
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

The corresponding default mapping from Java Object types to SQL types are shown below:

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

Note that it is not possible to send or receive Java input streams using the getObject or setObject methods. You must explicitly use PreparedStatement.setXXXStream or ResultSet.getXXX Stream to transfer a value as a stream.

ResultSet.getObject

ResultSet.getObject returns a Java object whose type correspond to the SQL type of the ResultSet column, using the mapping specified in Table 4.

So for example, if you have a ResultSet where the "a" column has SQL type CHAR, and the "b" column has SQL type SMALLINT, here are the types returned by some getObject calls:

ResultSet rs = stmt.executeQuery("SELECT a, b FROM foo");
while (rs.next()) {
 Object x = rs.getObject("a");        // gets a String
 Object y = rs.getObject("b");        // gets an Integer
}

PreparedStatement.setObject

For PreparedStatement.setObject you can optionally specify a target SQL type. In this case the argument Java Object will first be mapped to its default SQL type (as specified in Table 5), then converted to the specified SQL type (see Table 6), and then sent to the database.

Alternatively you can omit the target SQL type, in which case the given Java Object will simply get mapped to its default SQL type (using Table 5) and then be sent to the database .

CallableStatement.getObject

Before calling CallableStatement.getObject first have specified the parameter's SQL type using CallableStatement.registerOutParameter. When you call CallableStatement.getObject the Driver will return a Java Object type corresponding to that SQL type, using the mapping specified Table 4.