|
|
|
|
|
The JDBC API is Java's way of accessing any tabular data source,
typically a relational database. The API presents an
object-oriented interface to the data and is made up of classes and
interfaces in the java.sql package, with standard
extensions in javax.sql. Since JDBC 1.0, a lot of
functionality has been added to the API. JDBC 2.0 introduced
scrollable ResultSets, updateable
ResultSets, batch update capability and support for new
SQL3 data types. The new JDBC 3.0 specification, in its proposed
final draft form at this point, is promising to add more robust
transactions by way of savepoints, a number of resource pooling
enhancements, retrieval of auto-generated keys and a lot more.
Through all of the additional functionality, the API has remained
very straightforward and very easy to work with. This article
discusses a number of these new areas of functionality.
The four basic steps involved with using JDBC to connect to a database are:
Loading the driver is accomplished either by setting the
jdbc.drivers system property or by dynamically loading the
appropriate driver class with a call to
Class.forName(). The following command line launches
the com.ociweb.jdbc.MyApplicationName application and
loads the sun.jdbc.odbc.JdbcOdbcDriver driver. This
particular driver is used to connect to ODBC data sources, but the
procedure is the same for all JDBC drivers.
java -Djdbc.drivers=sun.jdbc.odbc.JdbcOdbcDriver com.ociweb.jdbc.MyApplicationName
The other approach is to load the driver by calling
Class.forName().
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
} catch (ClassNotFoundException exc) {
}
The appropriate driver must be loaded before a connection to the
database can be established. Once the driver is loaded, a
connection to the database can be established with a call to
DriverManager.getConnection(). There are several
versions of the getConnection() method that accept
different parameters. The simplest version accepts a single
String argument that is a URL to a database. The format
of the URL is:
jdbc:sub-protocol:sub-name
The sub-protocol portion of the URL is used by the
DriverManager class to locate the appropriate driver.
The sub-name portion of the URL is used by the driver to identify
which database to connect to. The specifics of the sub-name vary
from vendor to vendor so refer to the documentation for your
specific JDBC driver. For example, a database URL to an ODBC data
source might look like jdbc:odbc:HR. In this particular case, the
sub-name is "HR" and this should be the name of an ODBC data source.
// Load the JDBC-ODBC bridge driver used
// for connecting to an ODBC data source
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
// declare a URL to an ODBC data source named HR
String dbUrl = "jdbc:odbc:HR";
// Retrieve a connection to the database
Connection connection = DriverManager.getConnection(dbUrl);
Once a connection to the database has been established, SQL can be
sent to the database through a Statement object. The
Connection interface defines a
createStatement() method, which returns a
Statement that may be used to issue SQL commands on
that Connection. There are two methods in the
Statement interface for sending SQL,
executeQuery() and executeUpdate(). Each
of these methods accepts a String argument. The
executeQuery() method expects an SQL "select" statement
as an argument and will return a ResultSet containing
the results of the query.
The ResultSet interface defines the next()
method, which is used to iterate over the results. The
ResultSet interface also defines numerous "get" methods
for retrieving individual columns out of the results.
Statement statement = connection.createStatement();
String sql = "select first_name, last_name from users";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()) {
// the integer arguments to the getString() method here correspond
// with the columns specified in the select statement
// note that the integer arguments are NOT zero based
// the first column is index 1
String firstName = resultSet.getString(1);
String lastName = resultSet.getString(2);
// do something with the data
}
The executeUpdate() method in the
Statement interface expects an
SQLstatement that is updating the database, not
selecting data from the database.
Statement statement = connnection.createStatement();
String sql = "update users set eligible_flag = 'Y' where age > 66";
// the return value of executeUpdate() indicates how many rows were affected
// by the update
int n = statement.executeUpdate(sql);
System.out.println(n + " rows were updated.");
Those are the very basics of using JDBC to issue simple selects and updates to the database. The JDBC API provides much more functionality.
Starting with JDBC 2.0, a number of new features were added that
relate to the flexibility of ResultSets. One of the new
features added in JDBC 2.0 is the scrollability of
ResultSets. JDBC 1.0 ResultSets are
limited in such a way that each row in the ResultSet
may only be visited once and the ResultSet has to be
traversed from front to end.
ResultSet resultSet = statement.executeQuery(sql);
// visit each item once
while (resultSet.next()) {
// process the resultSet
}
JDBC 2.0 ResultSets may be one of three different types
with respect to scrollability. ResultSets of type
TYPE_FORWARD_ONLY are similar to JDBC 1.0
ResultSets. The two new types of
ResultSets are TYPE_SCROLL_INSENSITIVE and
TYPE_SCROLL_SENSITIVE. Both of these
ResultSet types are scrollable.
TYPE_SCROLL_INSENSITIVE ResultSets are
scrollable and are not sensitive to changes made by other
transactions. TYPE_SCROLL_SENSITIVE
ResultSets are also scrollable, but they are sensitive
to changes made by other transactions. If a ResultSet
is one of the scrollable types, then there are a number of methods
that will scroll the ResultSet cursor around in more
flexible ways than simply calling next() to visit each
item sequentially.
// retrieve resultSet from the database
ResultSet resultSet = statement.executeQuery(sql);
// moves the cursor to the third row
resultSet.absolute(3);
// moves the cursor to the fifth row from the end
resultSet.absolute(-5);
// moves the cursor backwards one row
resultSet.previous();
// moves the cursor forward 4 rows
resultSet.relative(4);
// moves the cursor backward 2 rows
resultSet.relative(-2);
// moves the cursor to the first row in the ResultSet
resultSet.first();
// moves the cursor to the last row in the ResultSet
resultSet.last();
All of these methods return a boolean that indicates whether or not
the cursor is actually on a row in the ResultSet. For
example, if the result contains 10 rows and code tries to move the
cursor to the 12th row, then the cursor is left in a
position that is after the last row. The methods
afterLast() and beforeFirst() can be used
to query this condition also.
ResultSet resultSet = statement.executeQuery(sql);
// assume that resultSet contains 10 rows of information
// this will work fine and returns true
resultSet.absolute(5);
// this will move the cursor off the end of resultSet and will return false
resultSet.relative(7);
// this will also return true now
boolean isAfterLastRow = resultSet.afterLast();
None of this works with ResultSets of type
TYPE_FORWARD_ONLY. The Statement object
that returned the ResultSet determines the
ResultSet's type. There is an overloaded version of
createStatement() in the Connection
interface that accepts two integer arguments. The first integer is a
ResultSet scrollability type and the second is a
ResultSet concurrency type (concurrency will be
discussed shortly).
// ResultSet objects returned from this Statement should be scrollable,
// should not be sensitive to changes made by other transactions and should
// be read only
Statement statement = connnection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
Note that the arguments being passed to the
createStatement() method here are only requests for
ResultSets to be of the specified type. Not all JDBC
drivers support all of these ResultSet types. The
ResultSet methods getType() and
getConcurrency() will return the
ResultSet's actual type and concurrency, respectively.
ResultSets have one of two concurrency types. A read
only ResultSet is of type CONCUR_READ_ONLY
and an updatable ResultSet is of type
CONCUR_UPDATABLE. JDBC 1.0 ResultSets are
all of type CONCUR_READ_ONLY. Updatable
ResultSets allow changes to be made to the data using
Java code instead of using SQL statements. This approach may be much
more natural for Java developers.
The ResultSet interface defines a number of
updateXXX() methods for updating the contents of a
ResultSet. There are two forms of each of these update
methods.
updateInt(int columnIndex, int i);
updateInt(String columnName, int i);
updateString(int columnIndex, String s);
updateString(String columnName, String s);
...
The first argument is either the column index that is being updated or a string that represents the name of the column being updated. The index corresponds to the order that the column names were specified in the select statement.
String sql = "select part_no, description, qty_needed from stock where "
+ "qty_on_hand < 10";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
// update the qty_needed column with the value 500
resultSet.updateInt(3, 500);
// send the change to the database
resultSet.updateRow();
}
After modifying values in a row of a ResultSet, a call
should be made to either updateRow() to send the update
to the database or cancelRowUpdates() to effectively
undo the update.
Version 2.0 of the JDBC specification introduced the idea of batch updates. Batch updates provide the ability to send a group of operations to the database instead of sending each operation independently. Sending the collection of operations as a group has the potential to greatly improve performance by cutting down on the communication back and forth between the application and the database. In JDBC 1.0, every update is sent to the database independently.
// each time executeUpdate is called, an insert is sent to the database
statement.executeUpdate("insert into users (first_name, last_name) "
+ "values('Jeff', 'Brown')");
statement.executeUpdate("insert into users (first_name, last_name) "
+ "values('Betsy', 'Brown')");
statement.executeUpdate("insert into users (first_name, last_name) "
+ "values('Zack', 'Brown')");
statement.executeUpdate("insert into users (first_name, last_name) "
+ "values('Jake', 'Brown')");
The batch update approach accumulates a group of updates on the
client and sends them all to the database at once. After adding a
group of updates to a batch, the items are sent to the database with
a call to executeBatch() or the batch is cleared out
with a call to clearBatch().
statement.addBatch("insert into users (first_name, last_name) "
+ "values('Jeff', 'Brown')");
statement.addBatch("insert into users (first_name, last_name) "
+ "values('Betsy', 'Brown')");
statement.addBatch("insert into users (first_name, last_name) "
+ "values('Zack', 'Brown')");
statement.addBatch("insert into users (first_name, last_name) "
+ "values('Jake', 'Brown')");
// send all of the updates to the database
statement.executeBatch();
A JDBC 2.0 driver may or may not support batch updates. The
supportsBatchUpdates() method in the
DatabaseMetaData class may be called to discover if a
particular driver supports batch updates.
The JDBC 3.0 specification is currently in a proposed final draft form. The current draft proposes a number of enhancements including transaction savepoints and retrieval of auto-generated keys.
To use transactions, a JDBC Connection object must not
be in auto-commit mode. The default behavior is for a connection to
be in auto-commit mode. To use transactions, a call must be made to
setAutoCommit() with an argument of false.
// retrieve a connection to the database
Connection connection = DriverManager.getConnection(dbUrl);
// turn off auto-commit mode
connection.setAutoCommit(false);
Once a Connection is out of auto-commit mode,
transactions must be explicitly committed to the database, or rolled
back by making calls to commit() or
rollback() on the Connection. Calling the
commit() method commits the changes to the database and
implicitly begins a new transaction. Calling the
rollback() method rolls the changes back as if they had
never happened and also implicitly begins a new transaction.
connection.setAutoCommit(false);
Statement statement = connection.createStatement();
// send 4 separate updates to the database
statement.executeUpdate(updateString1);
statement.executeUpdate(updateString2);
statement.executeUpdate(updateString3);
statement.executeUpdate(updateString4);
// commit the updates
connection.commit();
Before JDBC 3.0, all four of these updates would have to be committed as a group or rolled back as a group. There would be no way of committing a subset of the updates once they were added to the transaction. With JDBC 3.0, savepoints will offer finer-grained control of this behavior. During a transaction a named savepoint may be inserted between operations. This named savepoint acts as a marker in the transaction and the transaction may be rolled back to that marker, effectively removing all of the operations after the marker but leaving all of the operations before the marker in place.
connection.setAutoCommit(false);
Statement statement = connection.createStatement();
// send 2 updates to the database
statement.executeUpdate(updateString1);
statement.executeUpdate(updateString2);
// create a savepoint
Savepoint savePoint1 = connection.setSavepoint("SP1");
// send 2 more updates to the database
statement.executeUpdate(updateString3);
statement.executeUpdate(updateString4);
// rollback to SP1, effectively removing the last 2 updates from the
// transaction
connection.rollback(savePoint1);
// commit the updates
connection.commit();
Some databases allow for certain columns to be given automatically
generated key values. In this case, an insert statement would not be
responsible for supplying a value for the column. The database would
generate a unique value for the column and insert the value. This is
often used for generating unique primary keys. A problem with this
approach is that it may be difficult to get the value after the
insert is executed. The JDBC 3.0 specification proposes a more
functional Statement interface that provides access to
these values after an insert.
Assume a table called USERS with 3 columns. The FIRST_NAME column and LAST_NAME column are varchars. The USER_ID column is an auto generated column and should contain a unique identifier for each user in the table.
Statement statement = connection.createStatement();
// insert a new user into the database
// notice that the USER_ID is not accounted for here
statement.executeUpdate("insert into users (first_name, last_name) "
+ "values('Jeff', 'Brown')");
// Retrieve a ResultSet containing all of the auto-generated keys from the
// last update issued on this statement
// the specific details of the format of this ResultSet are not clearly
// specified yet
ResultSet resultSet = statement.getGeneratedKeys();
The JDBC API has matured a lot over the last few years. The API continues to get more flexible and powerful while remaining very simple and straightforward. The Java 2 Standard Edition (J2SE) version 1.4 will contain version 3.0 of JDBC. It may take some time before most driver vendors support all of the new functionality.
A related technology is Java Data Objects (JDO). JDO promises to hide more of the database specific code from the developer. Some benefits of JDO will be the addition of more compile time checking and more manipulation of data through the API instead of through SQL statements. JDO is intended to complement JDBC.
Object Computing, Inc (OCI) has been providing educational services to clients, industries and universities since 1993. We offer one of the most comprehensive distributed Object Oriented training curricula in the country. These curricula focus on the fundamentals of OO technology; with close to 40 workshops in OOAD, Java, XML, C++/CORBA and Unix/Linux.
For further information regarding OCI's Educational Services programs, please visit our Educational Services section on the web or contact us at training.
|
|