Using the JDBC Optional Package

The JDBC Optional Package (formerly known as the Standard Extension API) adds functionality that mostly applies if you are building distributed applications in which multiple clients are accessing remote databases. However, there is some functionality that applies generally to all JDBC development.

As mentioned before, the core JDBC API resides in the java.sql package. The optional package resides in the javax.sql package alone. The optional package includes four new high-level features:

• RowSets

• Connection pooling

• Distributed transactions

• Datasource

RowSets

A RowSet object is designed to be a container for a collection or set of rows. The RowSet is not provided by the driver implementation, but rather is implemented on top of a driver. This means it can be implemented by anyone who needs one, and the object can be implemented in many different ways depending on the needs of the application.

RowSet is a Java interface that extends the java.sql.ResultSet interface. Therefore, RowSet can perform all the functionality of the ResultSet interface like the getXXX and updateXXX methods . With the JDBC 2.0 Optional Package functionality, RowSet is a JavaBean This means that you can set and get the properties of the RowSet object as well as add listeners to the RowSet obect.

A RowSet object is not limited to where it gets the data to populate itself. It can come from a relational database or from a tabular data source. Once the RowSet has the data, it can be serialized over the network to a remote client. A client can deal with the RowSet object just like it was connected to the data source itself. Once a client has modified the data, the RowSet object can be sent back across the network. The RowSet can reconnect to the data source and upload the data. This makes it ideal for thin client implementations .

Connection Pooling

Connection pooling was mentioned briefly in the previous JDBC chapter. It's a mechanism to allow reuse of database connections. Because the time it takes to get a connection to the database is substantial, reusing connections can save time and speed up performance.

Usually, a connection pooling is created when the application is first started. Normally, you can specify how many connections to start with in the pool, the maximum number of connections the pool should grow to, and how many to increment each time more are needed. The logic for maintaining the pool of connections is not very complicated. When a client needs a connection, it is taken from the pool. Once a client is finished with the connection, it is placed back in. If a client never releases the connection or fails to release the connection within a certain amount of time, the client is usually reclaimed back into the pool forcibly .

This sounds like more work than it's worth, but the timesavings for not having to create connections when a client needs one is more than worth it in performance. Connection pooling can be done at various levels, and it also depends on what type of driver your application is using. Type III JDBC drivers most always have connection pooling within the middle tier . This is one reason that they can offer increased performance over Type I and II drivers.

Distributed Transactions

Distributed transactions really deal with more Enterprise technologies that are better covered in Special Edition Using Java 2, Enterprise Edition by Mark Wutka (Que, 2001). Although the first two features are also important in Enterprise applications, they do have context in general JDBC development.

Distributed transactions are used more often in Enterprise applications where data must be inserted or read from different heterogeneous persistent stores. These persistent stores may be relational databases, legacy databases, or even ERP systems. Dealing with a distributed transaction is usually very transparent to a developer. The main difference is the boundary of where the transaction begins and ends. In our previous examples of dealing with transactions, all the transaction statements were located in our code. This is because all the transaction operations were going to the same database. If we needed to operate on other databases as well and either commit the entire operation or roll it all back, we could solve this with a distributed transaction.

For example, let's say that we went out on the Internet to buy a book. During the ordering process, we select a book and then enter our credit card for payment. If the credit card authorization server crashes while the system is processing our credit card, the entire operation better roll back. The business does not want the book shipped to me if I did not pay for it. The system that processes the payment might be different from the fulfillment service, and the two need to participate in the same transaction or the business might not be very profitable.

DataSource

Throughout the examples in this and the previous JDBC chapter, a class has been used called DatabaseManager that was developed in the JDBC examples within this book. It abstracted the database connection for any client that needed a connection. Well, you can do better than having that level of abstraction by using a Datasource interface to acquire connections to a database.

A client can use a JNDI naming service to locate a Datasource object that it can then use to work with the database. The client stores enough information on how to locate the Datasource, and after it has the Datasource object, it can do whatever it needs to do against the database. The client does not need to know about driver names , URLs, or any other connection information.

The following code fragment gives you an idea of how you can get a Connection from JNDI:

 Context ctx = new InitialContext(); DataSource ds = (DataSource)ctx.lookup("jdbc/EmployeeDB"); Connection con = ds.getConnection("Scott", "tiger"); 

The first line acquires the initial context to the JNDI naming service. After it has the initial context, it can then get a reference to an object within the naming service. In this case, that object is a Datasource object. The jdbc/EmployeeDB parameter that you see in the lookup method gives kind of a unique name to each object within the naming service. There is sort of a hierarchical structure about the names. You can think of it as a tree or directory structure to get a mental picture of how it looks. To get more information on JNDI, check out this URL:

 http://java.sun.com/products/jndi/ 

After the client has located a Datasource object, it can then get connections as before by passing only minimal information, such as username and password. The Datasource hands the client a Connection instance that the client is free to access the database with. The main issue with this approach is the time that it takes to get the initial context to the JNDI naming service. The amount of time varies across JNDI vendors . The other real issue that some developers complain about is the complexity of learning another technology such as JNDI Of course, after you understand it, it becomes easier to use it.

One other advantage to using a Datasource is that the actual underlying data source can be anything from a relational database to a spreadsheet. Information about the underlying data source is stored within the JNDI Datasource object. This makes it very portable. A client does not need to worry about which machine a spreadsheet file is on, because the client can get that information from the Datasource once it has acquired a reference to it through JNDI.

Note

You may also want to read the database vendor's documentation. Some databases like Oracle 8i allow an application to obtain a Datasource without using JNDI. For example, you can instantiate an OracleDataSource() without JNDI and use it to access the database. Check the documentation for your database to see if it offers this feature.

You should be aware that these and many other features in general with Java are defined by Sun specifications, but are actually implemented by various third-party vendors. It's up to the vendor's implementation to adhere to the specification. Most of the time, a vendor will adhere to the specification, but sometimes you'll find a vendor who doesn't completely adhere to a specification. As we learned earlier, many vendors claim to have a JDBC driver that is 2.0 compliant, but in actuality they do not support all of the features.

Within Java, there are places where interfaces are defined like we found in the java.sql package. ResultSet is just a Java interface. It needs a concrete implementation for it to work. A vendor provides a set of classes that make up the driver and also provides implementations classes defined by the specification. It's up to the vendor how they implement some piece of functionality. The specification from Sun is a guideline for the vendor, but there is usually enough room for the vendor to be different from another vendor. You have to understand that all things being equal, implementations will vary according to a particular vendor.

Note

There are other features that are available, such as logging JDBC error messages. You can find more about these other features available in the Optional Package API at the following site:

 http://java.sun.com/products/jdbc/