14 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.
14.1 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.
14.2 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.
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
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:
14.2.2 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 .
14.2.3 CallableStatement.getObject
Before calling CallableStatement.getObject you must 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.
Contents | Prev | Next
jdbc@wombat.eng.sun.com
or
jdbc-odbc@wombat.eng.sun.com
Copyright © 1996, 1997 Sun Microsystems, Inc. All rights reserved.