JDK 5 Documentation v1.2.2, Java 2 SDK 英文文档

JDBC^TM Guide: Getting Started

Appendix A: Rejected design choices

A.1 Use Holder types rather than get/set methods.

In earlier drafts of JDBC we used a mechanism of "Holder" types to pass parameters and to obtain results. This mechanism was an attempt to provide a close analogue to the use of pointers to variables in ODBC. However as we tried to write test examples we found the need to create and bind Holder types fairly irksome, particularly when processing simple row results.

We therefore came up with the alternative design using the getXXX and setXXX methods that is described in Sections 7.2 and 7.1. After comparing various example programs we decided that the getXXX/setXXX mechanism seemed to be simpler for programmers to use. It also removed the need to define a dozen or so Holder types as part of the JDBC API. So we decided to use the getXXX/setXXX mechanism and not to use Holders.

A.1.1 Using Holder types to pass parameters

As part of the java.sql API, we define a set of Holder types to hold parameters to SQL statements. There is an abstract base class Holder, and then specific subtypes for different Java types that may be used with SQL. For example, there is a StringHolder to hold a String parameter and a ByteHolder to hold a byte parameter.

To allow parameters to be passed to SQL statements, the java.sql.Statement class allows you to associate Holder objects with particular parameters. When the statement is executed any IN or INOUT parameter values will be read from the corresponding Holder objects, and when the statement completes, then any OUT or INOUT parameters will get written back to the corresponding Holder objects.

An example of IN parameters using Holders:

java.sql.Statement stmt = conn.createStatement();

// We pass two parameters. One varies each time around

// the for loop, the other remains constant.

IntHolder ih = new IntHolder();

stmt.bindParameter(1, ih);

StringHolder sh = new StringHolder();

stmt.bindParameter(2, sh);

sh.value ="Hi"

for (int i = 0; i < 10; i++) {

	ih.value = i;

	stmt.executeUpdate("UPDATE Table2 set a = ? WHERE b = ?");

An example of OUT parameters using Holders:

java.sql.Statement stmt = conn.createStatement();

IntHolder ih = new IntHolder();

stmt.bindParameter(1, ih);

StringHolder sh = new StringHolder();

stmt.bindParameter(2, sh);

for (int i = 0; i < 10; i++) {

	stmt.executeUpdate("{CALL testProcedure(?, ?)}");

	byte x = ih.value;

	String s = sh.value;

A.1.2 Getting row results using Holder objects

Before executing a statement, we can allow the application programmers to bind Holder objects to particular columns. After the statement has executed, the application program can iterate over the ResultSet using ResultSet.next() to move to successive rows. As the application moves to each row, the Holder objects will be populated with the values in that row. This is similar to the SQLBindColumn mechanism used in ODBC.

Here's a simple example:

// We're going to execute a SQL statement that will return a

// collection of rows, with column 1 as an int, column 2 as

// a String, and column 3 as an array of bytes.

java.sql.Statement stmt = conn.createStatement();

IntHolder ih = new IntHolder();

stmt.bindHolder(1, ih);

StringHolder sh = new StringHolder();

stmt.bindHolder(2, sh);

BytesHolder bh = new BytesHolder();

stmt.bindHolder(3, bh);

ResultSet r = stmt.executeQuery("SELECT a, b, c FROM Table7");

while (r.next()) {

	// print the values for the current row.

	int i = ih.value;

	String s = sh.value;

	byte b[] = bh.value;

	System.out.println("ROW = " + i + " " + s + " " + b[0]);

A.2 Design Alternative: Don't use types such as fooHolder, instead use foo[]

At some point in the future we would probably like to add support for some form of column-wise binding, so that a bunch of rows can be read at once. When we were using the Holder design, we considered the following design alternative that would allow for column-wise binding.

Holder objects are capable of holding single instances of various Java types. However an array of a single element could instead be used as a holder. This approach has several disadvantages, but one major advantage.

The first disadvantage is that people may be confused if they read "foo f[] = new foo[1];". The corresponding holder declaration "fooHolder f = new fooHolder();" gives a better clue as to what f is and why we are allocating it.

The second disadvantage is that we would have to replace the single method Statement.bindColumn with a distinct method for each array type. This is because all our Holder types inherit from java.sql.Holder and can therefore be passed as arguments to a generic method that takes a java.sql.Holder argument. (On the other hand at least we avoid defining the dozen or so holder classes.)

The last disadvantage is that using foo[] only gives us the raw Java type information. By defining a specific set of holder types for use with SQL, we can define extra fields and/or semantics, e.g. for the CurrencyHolder type.

The corresponding major advantage is that if we use foo[1] as the container for a parameter then it is very natural to allow foo[x] as a way of binding multiple rows of a table in column- wise binding. This would let us add support for column-wise binding without having to remodel the interface.

If we use arrays instead of Holders, them the bindColumn mechanism makes it easier to scale up to column-wise binding.

A.3 Support for retrieving multiple rows at once

Currently we provide methods for retrieving individual columns within individual rows, a field at a time. We anticipate that drivers will normally prefetch rows in larger chunks so as to reduce the number of interactions with the target database. However, it might also be useful to allow programmers to retrieve data in larger chunks through the JDBC API.

The easiest mechanism to support in Java would probably be to support some form of column- wise binding where a programmer can specify a set of arrays to hold (say) the next 20 values in each of the columns, and then read all 20 rows at once.

However we do not propose to provide such a mechanism in the first version of JDBC. We do recommend that drivers should normally prefetch rows in suitable chunks.

A.4 Columns numbers in ResultSet.get methods

In an earlier version of the JDBC spec, the various "get" methods took no arguments, but merely returned the next column value in left-to-right order. We (re)introduced a column number argument because we were unsatisfied with the readability of the resulting example code. We frequently found ourselves having to count through the various "get" calls in order to match them up with the columns specified in the SELECT statement.

A.5 Method overloading for set methods

In an earlier version of the design we used method overloading so that rather than having methods with different names such as setByte, setBoolean, etc., all these methods were simply called setParameter, and were distinguished only by their different argument types. While this is a legal thing to do in Java, several reviewers commented that it was confusing and was likely to lead to error, particularly in cases where the mapping between SQL types and Java types is ambiguous. On reflection we agreed with them.

A.6 That wretched registerOutParameter method

We dislike the need for a registerOutParameter method. During the development of JDBC we made a determined attempt to avoid it and instead proposed that the drivers should use database metadata to determine the OUT parameter types. However reviewer input convinced us that for performance reasons it was more appropriate to require the use of registerOutParameter to specify OUT parameter types.

A.7 Support for large OUT parameters.

We don't currently support very large OUT parameters. If we were to provide a mechanism for very large OUT parameters, it would probably consist of allowing programmers to register java.io.OutputStreams into which the JDBC runtimes could send the OUT parameter's data when the statement executes. However, this seems to be harder to explain than it is worth, given that there is a already a mechanism for handling large results as part of ResultSet.

A.8 Support for GetObject versus getXXX

Because of the overlap between the various get/set methods and the generic getObject/setObject methods we looked at discarding our get/set methods and simply using getObject/setObject. However for the simple common cases where a programmer know the SQL types, the resulting casts and extracts are extremely tedious:

int i = ((Integer)r.getObject(1, java.sql.Types.INTEGER)).intValue()

We therefore decided to bend our minimalist principles a little in this case and retain the various get/set methods as the preferred interface for the majority of applications programmers, while also adding the getObject/setObject methods for tool builders and sophisticated applications

A.9 isNull versus wasNull

We had some difficulty in determining a good way of handling SQL NULLs. However by JDBC 0.50 we had designed a ResultSet.isNull method that seemed fairly pleasant to use. The isNull method could be called on any column to check for NULL before (or after) reading the column.

if (!ResultSet(isNull(3)) {

	count += ResultSet.getInt(3);

Unfortunately, harsh reality intervened and it emerged that "isNull" could not be implemented reliably on all databases. Some databases have no separate means for determining if a column is null other than reading the column and they would only permit a given column to be read once. We looked at reading the column value and "remembering" it for later use, but this caused problems when data conversions were required.

After examining a number of different solutions we reluctantly decided to replace the isNull method with the wasNull method. The wasNull method merely reports whether the last value read from the given ResultSet (or CallableStatement) was SQL NULL.

A.10 Use of Java type names v SQL type names.

In an earlier version of the spec we used getXXX and setXXX method for retrieving results and accessing parameters, where the XXX was a SQL type name. In revision 0.70 of JDBC we changed to use getXXX and setXXX methods where the XXX was a Java type name.

Thus for example, getChar was replaced by getString and setSmallInt by setShort.

The new methods have essentially the same semantics as the methods that they replace. However the use of Java type names makes the meaning of each of the methods clearer to Java programmers.

Contents | Prev | Next

jdbc@wombat.eng.sun.com or jdbc-odbc@wombat.eng.sun.com