14 Dynamic Database Access

14.1     Metadata information

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.

14.2     Dynamically typed data access

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.

Table 4: 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
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 is show in Table 5.

Table 5: Mapping from Java Object types to SQL types.

Note that the mapping for String will normally be VARCHAR but will turn into LONGVARCHAR if the given value exceeds the drivers limit on VARCHAR values. The situation is similar for byte[] and VARBINARY and LONGVARBINARY.

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.

14.2.1 ResultSet.getObject

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

}

14.2.2 PreparedStatement.setObject

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 .

14.2.3 CallableStatement.getObject