Java Database Connectivity (JDBC)

See Relational Databases for a review of the essential database concepts.

JDBC API is the collection of classes for connecting to a database, executing queries, and sifting through the results. Here are the principle classes in the API

Drivers

The driver is a program that mediates all communication between programs and a particular device. A JDBC driver mediates all communication between JDBC classes and a particular database management system (dbms). There are several types of drivers:

Example: A Database Browser

The most basic type of database application is a database browser. This application allows users to enter arbitrary SQL queries, then displays the results. Our browser is contained in three files:

Console.java

AppError.java

SQLBrowser.java

Read the javadocs to familiarize yourself with the browser's functionality.

Of course before the browser can work, a database management system must be running.

Design

Here's the design:

The Console is a standard console user interface (CUI) that perpetually reads a command, attempts to execute the command, displays the result if the execution is successful, and catches any AppError exceptions thrown if the execution is unsuccessful.

The SQLBrowser extends the Console and overrides the execute message. The browser assumes the command to be executed is an SQL statement: SELECT, UPDATE, DELETE, INSERT, etc. In the case of SELECT, the result will be a result set.

Implementation

Here is a high-altitude view of SQLBrowser's fields and methods:

package browser;
import java.sql.*;
public class SQLBrowser extends Console {
protected String driverName = "...";
protected String dbasePrefix = ...;
protected String username= "...";
protected String password= "...";
protected Connection connection;
protected Statement statement;
protected ResultSet result;

public SQLBrowser(String dbase) throws SQLException, ClassNotFoundException {...}

public void finalize() throws SQLException {...}
private String toString(ResultSet rs) throws SQLException {...}
public String execute(String sql) throws AppError {...}
static public void main(String[] args) {
try {
SQLBrowser browser = new SQLBrowser("aua");
browser.controlLoop();
}
catch (SQLException e) {
System.err.println("dbase error: " + e);
}
catch (Exception e) {
System.err.println("error: " + e);
}
}
}

Connecting

The constructor initializes the connection and statement fields:

public SQLBrowser(String dbase) throws SQLException, ClassNotFoundException {
String dbaseName = dbasePrefix + dbase;
try {
Class.forName(driverName);
connection = DriverManager.getConnection(dbaseName, username, password);
} catch (Exception e) { // dbase not found?
System.err.println("can't connect to " + dbaseName + " because " + e);
connection = DriverManager.getConnection(dbaseName + ";create=true");
}
statement = connection.createStatement();
}

Executing a Query

The execute method assumes its input is a well formed SQL statement and simply forwards it to the executeQuery method of the statement, then looks for the result in the statement's result set field:

public String execute(String sql) throws AppError {

String result = "???";
try {
if (sql.equals("commit")) {
connection.commit();
result = "done";
} else if (sql.equals("rollback")) {
connection.rollback();
result = "done";
} else if (statement.execute(sql)) {
result = toString(statement.getResultSet());
} else {
result = "done";
}
} catch (SQLException e) {
throw new AppError(e.toString());
}
return result;
}

Processing a Result Set

Converting the result set to a string doesn't work very well, but it is a good illustration of how result sets can be manipulated

private String toString (ResultSet rs) throws SQLException {
StringBuffer results = new StringBuffer();
ResultSetMetaData metaData = rs.getMetaData();
int numCols = metaData.getColumnCount();
for(int i = 1; i <= numCols; i++) { // get column names
results.append(metaData.getColumnName(i) + "\t");
}
results.append("\n");
while(rs.next()) { // get next row
for(int i = 1; i <= numCols; i++) {
results.append(rs.getObject(i) + "\t");
}
results.append("\n");
}
return results.toString();
}

The finalize method is called when the browser terminates:

Closing a Connection

public void finalize() throws SQLException {
statement.close();
connection.close();
}