Java Database Connectivity (JDBC) is a package consisting of eight interfaces:
java.sql.Driver
java.sql.Connection
java.sql.Statement
java.sql.PreparedStatement
java.sql.CallableStatement
java.sql.ResultSet
java.sql.ResultSetMetaData
java.sql.DatabaseMetaData
The class diagram in shows the relationships between implementations of these interfaces:
The central class in JDBC is java.sql.DriverManager. It maintains a list of all available drivers. A driver knows how to create a connection to a particular database. Drivers can be a little hard to find. JDBC drivers for most well known databases are available for a price. If a database has an ODBC driver, then one can use a JDBC-ODBC bridge, which is provided free on Windows NT, but must be purchased for Sun platforms. Consult the javasoft web site for a list of drivers and bridges.
An application loads the driver manager class, then asks it for a database connection using the URL of the database. The driver manager searches its list of drivers, and, if it finds a matching driver, asks the driver to create the connection:
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:ODBC_Addresses";
String user = "smith";
String pswd = "foobar";
Connection myConn = DriverManager.getConnection(url, user, pswd);
A connection represents a single database session. It stores information about the session and provides statement (or prepared or callable statement) object to the application:
Statement myStmt = myConn.createStatement();
A statement represents a template for an SQL statement (UPDATE, DELETE, INSERT, or SELECT). It provides executeQuery() and executeUpdate() methods. The executeQuery() method returns a result set representing a list of rows:
String sql = "SELECT columns FROM table WHERE column = xxx;
ResultSet rs = stmt.executeQuery(sql);
We can collect these objects in a Database class encapsulating a connection and a statement. The constructor loads a driver manager, uses the driver manager to find a driver, uses the driver to establish a connection, then uses the connection to create a statement:
import java.sql.*;
class Database {
private Connection myConn;
private Statement myStmt;
public Database(String dm, String url, String user, String pswd) {
try {
Class.forName(dm); // loads a DriverManager class
myConn = DriverManager.getConnection(url, user, pswd);
myStmt = myConn.getStatement();
} //try
catch(SQLException sqlexp) {
Tools.error("connection failed: " + sqlexp);
}
catch (ClassNotFoundException cnfe) {
Tools.error("Failed to load driver; " + cnfe);
}
}
// etc.
} // Database
The principle method provided by the Database class is execute(). Given a standard SQL statement, represented as a String, it returns a result represented as a formatted string. The execute method uses private predicates to determine if its input is a select, update, or insert statement. In case it is an update or insert method, the treatment is the same: myStmt.executeUpdate(sql) is called and the number of rows affected is returned. In case the statement is a select statement, myStmt.executeQuery(sql) is called. A result set is returned. This is converted into a formatted string and returned:
class Database {
private Connection myConn;
private Statement myStmt;
public Database(String dm, String url, String user, String pswd) {
...
}
public String execute(String sql) {
try {
if (isSelect(sql)) {
ResultSet rs = myStmt.executeQuery(sql);
return formatResultSet(rs);
}
else if (isUpdate(sql) || isInsert(sql)) {
int rs = myStmt.executeUpdate(sql);
return "# of rows affected = " + rs;
}
else
Tools.error("unrecognized SQL: " + sql);
} // try
catch (SQLException sqle) {
Tools.error("Dbase access failure; " + sqle);
} // catch
catch(IOException ioe) {
Tools.error("Request read failure; " + ioe);
} // catch
} // execute
// etc.
private boolean isSelect(String stmt) {
return (stmt.substring(0, 6).equalsIgnoreCase("SELECT"));
}
private boolean isUpdate(String stmt) {
return stmt.substring(0, 6).equalsIgnoreCase("UPDATE");
}
private boolean isInsert(String stmt) {
return stmt.substring(0, 6).equals("INSERT");
}
} // Database
A result set represents a list of rows extracted from a table. We can iterate through these rows using the result set's next() method. Each row is a list of columns. Each column is a String.
A result set has an associated meta data object that tells us the number of columns and the name and type of each column. The Database class supplies a formatResultSet() method can be used to convert the result set into a table string suitable for stream input or tokenizing. The format of a table string is given by the EBNF:
TABLE ::= table: ROW ...
ROW ::= row: COL ...
COL ::= name: NAME type: TYPE value: VALUE
where NAME, TYPE, and VALUE are Strings:
class Database {
private Connection myConn;
private Statement myStmt;
public Database(String dm, String url, String user, String pswd) {
...
}
public String execute(String sql) { ... }
private String formatResultSet(ResultSet rs)
throws SQLException
{
ResultSetMetaData rsmd = rs.getMetaData();
int cc = rsmd.getColumnCount(); // = # of columns
String result = " table: ";
// iterate through each row
while(rs.next()) {
result = result + " row: ";
// iterate through each column
for(int i = 1; i <= cc; i++) {
String colName = " name: " + rsmd.getColumnName(i);
String colType = " type: " + rsmd.getColumnType(i);
String colVal = " value: " + rs.getString(i);
result = result + colName + colType + colVal;
} // for
} // while
return result;
} // formatResultSet
// etc.
} // Database
Assume an address book is represented by a table called People, which is in a database with URL: jdbc:odbc:ODBC_Addresses. Assume the fully qualified name of the driver manager class provided by Java is sun.jdbc.odbc.JdbcOdbcDriver. (This class manages a single JDBC-ODBC bridge.) Each row in the table represents one address book entry and consists of seven columns named:
FNAME LNAME ADDRESS CITY STATE ZIP PHONE
The client program finds and displays all entries with a phone number specified by a command line argument:
class DbaseClient {
public static void main(String[] args) {
String sql =
"SELECT FNAME, LNAME, ADDRESS, CITY, STATE, ZIP, PHONE " +
"FROM People " +
"WHERE PHONE = " +
"'" + args[0] + "'\n";
Database db =
new Database("sun.jdbc.odbc.JdbcOdbcDriver",
"jdbc:odbc:ODBC_Addresses",
"smith", // owner
"foobar"); // password
Tools.cout.println(db.execute(sql));
}
JDBC can also be used to create databases.