Java Database Connectivity (JDBC) is a standard database access interface with SQL. It provides programmers with a uniform interface to various relational database systems regardless of differences in implementations. JDBC is part of Java 2 (the java.sql package). Database vendors ship JDBC drivers that enable accessing their databases from Java program through JDBC. For example, DB2, IBM's RDBMS, provides a JDBC driver class, COM.ibm.db2.jdbc.app.DB2Driver, that translates JDBC calls into native DB2 calls and converts the DB2 results into a JDBC data structure. The class COM.ibm.db2.jdbc.net.DB2Driver is used for remote access of the database. Thus application programs that use JDBC can run with different RDBMSs without modification. A list of more than 150 JDBC drivers supplied by database vendors is maintained at Sun's Web site (http://industry.java.sun.com/products/jdbc/drivers). If you want to use Open Database Connectivity (ODBC), a standard API for accessing a database, to connect Java applications to Microsoft Access or Excel, you can use the JDBC-ODBC bridge. In this part of article, assume there is a sample database that contains a table named employee. The employee table has the schema shown in Table LOHIT1. The sample database and the employee table are included in DB2 as a sample, so you do not have to create them.
JDBC
Symbols such as CHARACTER and DATE show the data types of each column. For example, the data type CHARACTER(6)represents a six-letter character string, while VARCHAR(12) represents the data type of a character string that is at most 12 characters long. We use the data in this table as shown in Table LOHIT2, which is based on the schema in Table LOHIT1
Before creating a sample program that accesses the employee table by using JDBC, let's look at the functions of JDBC |
JDBC Package 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
Interfaces in the java.sql Package INTERFACE
- 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.
Exceptions in the java.sql Package EXCEPTION
- SQLException: An exception that provides information on a database access error.
- SQLWarning: An exception that provides information on a database access warning.
- BatchUpdateException: An exception that occurs during a batch update operation.
- DataTruncation: An exception that occurs when JDBC unexpectedly truncates data.
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");
...