JDBC is provided by the java.sql package. Tables Here is the lists of classes, interfaces, and exceptions, respectively.

Classes in the java.sql Package

• Date: A subclass of java.util.Date and a thin wrapper of the SQL DATE value.

• DriverManager: A class that provides basic services for managing multiple JDBC drivers, such as loading drivers and logging on.

• DriverPropertyInfo:  A class for accessing the properties of loading drivers.

• SQLPermission: A class used when Security Manager inspects an applet that calls the setLogWriter() method.

• Time: A subclass of java.util.Date and a thin wrapper of the SQL TIME value.

• Timestamp: A subclass of java.util.Date and a thin wrapper of the SQL TIMESTAMP value.

• Types: A class that defines SQL data types such as INTEGER and VARCHAR.

• Array: An interface that represents the SQL ARRAY type.

• BLOB: An interface that represents the Binary Large Object (BLOB) type.

• CLOB: An Interface that represents the SQL Character Large Object (CLOB) type.

• CallableStatement: An interface used for executing a stored procedure of SQL.

• Connection: An interface that represents a session with a specific database. It is created by calling the Driver#connection() method.

• DatabaseMetaData: An interface used for accessing the properties of a database as a whole

• Driver: This is the main body of a JDBC driver. An implementation-specific driver must implement this interface.

• Ref: An interface used to access the SQL structured type.

• PreparedStatement: A subinterface of Statement that represents a compiled query for efficiently executing the same query many times.

• ResultSet: An interface that provides access to a table of data generated by executing an SQL query using a Statement object.

• ResultSetMetaData: An interface used for accessing the properties of query results, such as data types and column names.

• SQLData: An interface used to access an SQL user-defined type.

• SQLInput: An interface that represents an input stream to get an SQL user-defined type stored in database.

• SQLOutput: An interface that represents an output stream to write attributes of a user-defined data type to the database.

• Statement: An interface used for executing an SQL statement.

• Struct: An interface that represents an SQL structured type.

import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.SQLException;

Loading a JDBC Driver

Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");

In this case, we used the JDBC driver for DB2. The class name must be replaced when you use other database implementations. The coding style is something strange. What happens when the method is called?

The static method forName(), defined in the class Class, generates a class object of the specified class. How is the JDBC driver object registered with DriverManager? Any JDBC driver has to have a static initialization part that is executed when the class is loaded, as shown next. As soon as the class loader loads this class, the static initialization is executed, which automatically registers it as a JDBC driver with DriverManager.

public class DB2Driver {
public DB2Driver() {
...
}
static {
try  {
DriverManager.registerDriver(new DB2Driver());
return;
} catch(SQLException sqlexception)
}}

Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();

Connecting to a Database

With the driver in place, we next need to specify the data source we want to access. In JDBC, a data source is specified by a URL with a scheme jdbc.

Subprotocol represents the type of the data source, which is normally the name of the database system, such as db2 and oracle. Subname is used to specify information for the database. The contents and syntax of subname depend on the subprotocol.

For example, to access a table named sample stored in IBM's DB2 on a local machine, you need to create a URL as follows:

String url = "jdbc:db2:sample";

String url = "jdbc:db2:monet.trl.ibm.com/sample";

// Userid and password are specified as
// System properties
String userid = System.getProperty("chap11.userid");
String password =System.getProperty("chap11.password");
// Connects with default username and password
Connection con = DriverManager.getConnection(url,userid,password);

// Default username is used
Connection con = DriverManager.getConnection(url);

Submitting a Query

Statement stmt = con.createStatement();

String SQLquery = "SELECT * FROM EMPLOYEE";
// Gets result of the query
ResultSet rs = stmt.executeQuery(SQLquery);

The class ResultSet defines a number of methods for accessing the result. The result set is basically a sequence of rows, over which we can iterate using the next() method. The result set maintains a cursor to remember the current row in the result set. A call of the next() method advances this cursor to the next row until the end of data, where next() returns null. Within the cursor row, you can access the value of each column by specifying either the index number of the column or the name of the column. The getXX methods, where XX represents data types such as Int and String, can be used to access each column.

The following code fragment shows how to retrieve the first column (EMPNO) and second column (FIRSTNAME) of the result set.

while (rs.next()) {
String firstColumn  = rs.getString(1);
String secondColumn = rs.getString(2);
System.out.print(firstColumn);
System.out.print(" " + secondColumn);
System.out.print("\n");
}

package appendixD;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.SQLException;
class JDBCSample {
static {
try {
// Register the driver with DriverManager
Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String argv[]) {
try {
// URL is jdbc:db2:dbname
String url = "jdbc:db2:sample";
// Userid and password are specified as
// System properties
String userid = System.getProperty("appendixD.userid")
String password = System.getProperty("appendixD.password");
// Connects with default username and password
Connection con = DriverManager.getConnection(url,userid,password);
// Creates statement
Statement stmt = con.createStatement();
String SQLquery = "SELECT * FROM EMPLOYEE";
// Gets result of the query
ResultSet rs = stmt.executeQuery(SQLquery);
// Displays Result
while (rs.next()) {
String firstColumn  = rs.getString(1);
String secondColumn = rs.getString(2);
System.out.print(firstColumn);
System.out.print(" " + secondColumn);
System.out.print("\n");
}
rs.close();
stmt.close();
} catch(SQLException e) {
e.printStackTrace();
}}}

Executing this program generates the following output.

This section of article introduced a program using JDBC. This program is quite simple but shows a typical pattern appearing in large applications. The next section describes other functions of JDBC by using more complex programs.

Using a Connection Pool

We showed how to connect to a database in the previous section. However, connecting to a database is expensive work for most Web applications. Therefore, application servers provide various ways for managing database connections and reducing the overhead for the connections. However, the implementation is vendor-specific, and it is an obstacle to developing vendor-independent systems.

To resolve this issue, JDBC supports the javax.sql.DataSource and javax.sql.ConnectionPoolDataSource classes, which abstract low-level connection information. These classes are distributed separately as an optional package of JDBC .

By using the connection pool, you do not need to specify the database URI in a program. Instead, you use the Java Naming and Directory Interface (JNDI) to bind a declared name to a database. It makes a program more robust for changing database settings. The connection pool also provides a way to control the number of connections. We recommend that you use the connection pool if it is available in your middleware, such as an application server.

However, the detailed setting of connection information still depends on implementations of JDBC drivers. The function is now supported mainly by J2EE-compliant application servers, so it may be available when you are developing applications on an application server. In the other sections in this appendix, we use sample programs that connect to database without using the connection pool, as shown in Listing LOHIT1.

import javax.naming.Context;
import javax.naming.InitialContext;
import COM.ibm.db2.jdbc.DB2DataSource;
// Connects with default username and password
DataSource ds = (DataSource)Class.forName(
"COM.ibm.db2.jdbc.DB2DataSource").newInstance();
// Sets database-specific info. It depends on JDBC driver
// implementations
(DB2DataSource)ds).setDatabaseName("sample");
Context ctx = new InitialContext();
// Binds JNDI name and DataSource instance
ctx.bind("jdbc/SampleDataSource", ds);
...

The Context interface is used to specify a name context. A JNDI name is managed by the context. The Context object is initiated by calling a constructor of the InitContext class. An implementation of JNDI is provided as a provider. There are some implementations of the JNDI provider based on Lightweight Directory Access Protocol (LDAP), an ordinal file system, and so on.

The class COM.ibm.db2.jdbc.DB2DataSource is an implementation of DataSource and provides some (implementation-specific) methods to set database information. After setting the information, you register a JNDI name by using the bind() method. To learn the details of the connection pool, please consult the vendor-specific APIs for the DataSource implementation, or consult the manuals for the application server you are using.

import javax.naming.Context;           // JNDI
import javax.naming.InitialContext;
import java.sql.Conncection;
import javax.sql.DataSource;           // JDBC 2.0 Optional
import COM.ibm.db2.jdbc.DB2DataSource; // DB2 specific
...
// Connects with default username and password
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("jdbc/SampleDataSource");
Connection con = ds.getConnection("db2admin", "db2admin");
...