Contents | Prev | Next | JDBCTM Guide: Getting Started |
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.
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;
}
// 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]);
}
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.
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.
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
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.
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.