InfoCenter Home >
4: Developing applications >
4.2: Building Web applications >
4.2.4: Putting it all together (Web applications) >
4.2.4.2: Obtaining and using database connections >
4.2.4.2.1: Accessing data with the JDBC 2.0 Optional Package APIs

4.2.4.2.1: Accessing data with the JDBC 2.0 Optional Package APIs

In JDBC 1.0 and the JDBC 2.0 Core API, the DriverManager class is used exclusively for obtaining a connection to a database. The database URL, user ID, and password are used in the getConnection() call. In the JDBC 2.0 Optional Package API, the DataSource object provides a means for obtaining connections to a database. The benefit of using datasources is that the creation and management of the connection factory is centralized. Applications do not need to have specific information like the database name, user ID, or password in order to obtain a connection to the database.

The steps for obtaining and using a connection with the JDBC 2.0 Optional Package API differ slightly from those in the JDBC 2.0 Core API example. Using the extensions, you access a relational database as follows:

  1. Retrieve a DataSource object from the JNDI naming service
  2. Obtain a Connection object from the datasource
  3. Send SQL queries or updates to the database management system
  4. Process the results

The connection obtained from the datasource is a pooled connection. This means that the Connection object is obtained from a pool of connections managed by IBM WebSphere Application Server. The following code fragment shows how to obtain and use a connection with the JDBC 2.0 Optional Package API:

try {
// Retrieve a DataSource through the JNDI Naming Service

java.util.Properties parms = new java.util.Properties();
parms.setProperty(Context.INITIAL_CONTEXT_FACTORY,
"com.ibm.ejs.ns.jndi.CNInitialContextFactory");

// Create the Initial Naming Context
javax.naming.Context ctx = new javax.naming.InitialContext(parms);

// Lookup through the naming service to retrieve a DataSource object
javax.sql.DataSource ds =
(javax.sql.DataSource)ctx.lookup("jdbc/SampleDB");

// Obtain a Connection from the DataSource
java.sql.Connection conn = ds.getConnection();

// query the database
java.sql.Statement stmt = conn.createStatement();
java.sql.ResultSet rs =
stmt.executeQuery("SELECT EMPNO, FIRSTNME, LASTNAME FROM SAMPLE");

// process the results
while (rs.next()) {
String empno = rs.getString("EMPNO");
String firstnme = rs.getString("FIRSTNME");
String lastname = rs.getString("LASTNAME");
// work with results
}
}
catch (java.sql.SQLException sqle) {
// handle SQLException
}
finally {
try {
if (rs != null) rs.close();
}
catch (java.sql.SQLException sqle) {
// can ignore
}
try {
if (stmt != null) stmt.close();
}
catch (java.sql.SQLException sqle) {
// can ignore
}
try {
if (conn != null) conn.close();
}
catch (SQLException sqle) {
// can ignore
}
} // end finally

In the previous example, the first action is to retrieve a DataSource object from the JNDI namespace. This is done by creating a Properties object of parameters used to set up an InitialContext object. After a context is obtained, a lookup on the context is performed to find the specific datasource necessary, in this case, SampleDB.

(In this example, it is assumed the datasource has already been created and bound into JNDI by the WebSphere administrator. For information about doing this in application code, see the Related information.)

After a DataSource object is obtained, the application code calls getConnection() on the datasource to get a Connection object. After the connection is acquired, the querying and processing steps are the same as for the JDBC 1.0 example.

Go to previous article: Obtaining and using database connections Go to next article: Creating datasources with the WebSphere connection pooling API

 

 
Go to previous article: Obtaining and using database connections Go to next article: Creating datasources with the WebSphere connection pooling API