Section 4.2. JDBC

4.2. JDBC

JDBC has been around nearly as long as Java itself. The JDBC 1.0 API was released with JDK 1.1. This is the java.sql package. JDBC 2.0 was released with JDK 1.2. It included both the Core package and what was called the Optional Package (javax.sql). The optional package brought with it better enterprise support for database connections, including connection pools and distributed transactions. JDBC 3.0 is the latest release, included with JDK 1.4.

If you've written JDBC code since the good old days, you're probably familiar with using the DriverManager to get a database connection, as in Example 4-1.

Example 4-1. Example of the JDBC DriverManager

     static final String DB_DRIVER_CLASS = "com.mysql.jdbc.Driver";     static final String DB_URL =                  "mysql://localhost:3306/JBossatWorkDB?autoReconnect=true";     Connection connection = null;     try {         // Load the Driver.         Class.forName(DB_DRIVER_CLASS).newInstance(  );         // Connect to the database.         connection = DriverManager.getConnection(DB_URL);     } catch (SQLException se) {         ...     } catch (...) {         ...     } 

While this code certainly works, it has several shortcomings:

• Every time you connect and disconnect from the database, you incur the overhead of creating and destroying a physical database connection.

• You have to manage the database transaction yourself.

• You have a local transaction that's concerned only with database activity. What if you deal with other resources such as JMS Destinations (Queues and Topics)? If there's a problem and you need to roll back database updates, there's no automated way to roll back the work done with these other resources.

One of the main benefits of living in an application server is having the server take care of these sorts of plumbing issues. JBoss, like all other J2EE application servers, deals with the issues listed above on your behalf. However, to facilitate this, we need to slightly change the way you obtain your database connections.

Rather than using a java.sql.DriverManager, we need to use a javax.sql.DataSource to allow JBoss to manage the details in Example 4-2.

Example 4-2. Example of the JDBC DataSource

         static final String DATA_SOURCE=                      "java:comp/env/jdbc/JBossAtWorkDS";         DataSource   dataSource = null;         Connection conn = null;         try {             // Load the Driver.             dataSource = ServiceLocator.getDataSource(DATA_SOURCE);             // Connect to the database.             conn = dataSource.getConnection(  );         } catch (SQLException se) {             ...         } catch (ServiceLocatorException sle) {             ...         } 

A DataSource provides the following advantages:

• When you obtain a database connection using a DataSource, you're not creating a new connection. At startup, JBoss creates a database Connection Pool managed by a DataSource. When you get a database connection from a DataSource, you access an already existing connection from the pool. When you "close" the connection, you just return it to the pool so someone else can use it.

• When you use a Container-Managed DataSource, all database access for a particular Transaction Context commits or rolls back automatically. You don't have to manage the transaction yourself anymore.

• If you use Container-Managed Transactions (CMT) and your DBMS supports two-phase commit (the XA protocol), then your database transaction can participate in a global transaction. Suppose you have a unit of work that requires database activity and sends JMS messages: if something goes wrong, the JBoss Transaction Manager rolls back everything.

OK, we admit it. We pulled a bit of a fast one on you. Using DataSources brings great power to the table, but it also brings along some added complexity. We should look at a few more moving parts in greater detail.