JDBC

Goals

Concepts

Library

Dependencies

Lesson

SQL is by far the most common language for accessing a database, and Java is an excellent language for programming database applications. For Java to access a database, there must be some way for Java to send SQL commands to the database to be executed. One brute-force approach would be for Java to issue shell commands to the database command interpreter, but that would be unwieldy and would tie the application to some particular database product.

In the early 1990s Microsoft created Open Database Connectivity (ODBC) API as a common way for programs to interact with a database. At one time ODBC was the most popular API for accessing databases, and it is still in wide use today. But ODBC is based on the C language, is difficult to learn, and does not yet provide access to later SQL features. Java instead provides the Java Database Connectivity (JDBC) API, which was modeled on ODBC but provides a pure-Java database connectivity solution.

The JDBC classes are found in two main packages, java.sql and javax.sql. It defines several concepts, presented in the API as Java interfaces. Here are the central ones:

connection
Encapsulates a session of interaction with the database.
statement
Provides a means to send an SQL command to the database, once a connection is established.
result set
Represents the result of an SQL command, with a means for iterating and/or updating individual rows in the result.

Drivers

JDBC thus provides a level of indirection allowing you to program to the JDBC API, rather than the using the database's proprietary protocol, in order to send the database SQL for execution. For this single API to work across database products, you must use a specific JDBC driver for each database product you access, which functions as an implementation of the API. Most of the time database vendors will provide a JDBC driver. These drivers are normally one of four types:

Type 1: JDBC-ODBC bridge
The JDBC driver talks to an existing ODBC driver for the database. This is mostly a stop-gap solution for a product that does not yet offer a JDBC driver.
Type 2: Java/native driver
Part of the JDBC driver is written in Java, but the part that speaks to the database is written in native code.
Type 3: Java/middleware driver
The driver is written in pure Java, but it doesn't talk directly to the database; instead, it talks to some “middleware” server, which in turn talks to the database.
Type 4: Pure Java driver
The driver is written in pure Java and talks directly with the database using its proprietary protocol.

Connecting to the Database

Before interacting with the database, you must first connect to it. The traditional approach is to ask java.sql.DriverManager for a connection using DriverManager.getConnection(String url, String user, String password). If you want to configure additional connection parameters, you can call DriverManager.getConnection(String url, Properties info) passing database-specific key/value pairs, usually including values for user and password.

The driver manager will determine the appropriate database driver to use based upon the connection URL. Once you have connected to the database, the driver returns a java.sql.Connection instance.

Connecting to a database using DriverManager.
final String CONNECTION_URL = "jdbc:postgresql://example.amazonaws.com:5432/warehouse";
final Properties properties = new Properties();
properties.setProperty("user", "jdoe"); //example only; do not include in source code!
properties.setProperty("password", "secret"); //example only; do not include in source code!
properties.setProperty("ssl", "true");
try(final Connection connection = DriverManager.getConnection(CONNECTION_URL, properties)) {
  //TODO talk to the database
}

Connection Pooling

TODO; see e.g. https://stackoverflow.com/q/2835090/421049, https://stackoverflow.com/q/2299469/421049, https://www.developer.com/java/data/understanding-jdbc-connection-pooling.html

Statements

After you are connected, you will be able to send SQL commands to the database. The java.sql.Statement interface encapsulates an SQL command or query.

DDL Statements

The connection acts as a statement factory, so you can ask for a statement using Connection.createStatement(). This provides a basic statement instance which might be used for data definition SQL commands, such as creating a table. These types of commands do not return results, so you can execute the literal SQL string using Statement.executeUpdate(String sql).

Creating a database schema using JDBC statements.
final String CREATE_SUPPLIER_TABLE_SQL = "CREATE TABLE Supplier ("
    + "  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,"
    + "  name VARCHAR(9999) NOT NULL UNIQUE,"
    + "  address VARCHAR(9999) NOT NULL"
    + ")";
final String CREATE_ITEM_TABLE_SQL = "CREATE TABLE Item ("
    + "  code VARCHAR(99) NOT NULL,"
    + "  name VARCHAR(9999) NOT NULL,"
    + "  stock INTEGER DEFAULT 0 NOT NULL,"
    + "  supplierId INTEGER NOT NULL,"
    + "  CONSTRAINT Item_pk PRIMARY KEY (code),"
    + "  CONSTRAINT Item_name_un UNIQUE (name),"
    + "  CONSTRAINT Item_supplierId_fk FOREIGN KEY (supplierId) REFERENCES Supplier(id)"
    + ")";
final String CREATE_CATEGORY_TABLE_SQL = "CREATE TABLE Category ("
    + "  id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY,"
    + "  name VARCHAR(9999) NOT NULL,"
    + "  CONSTRAINT Category_name_un UNIQUE (name)"
    + ")";
final String CREATE_ITEM_CATEGORY_TABLE_SQL = "CREATE TABLE ItemCategory ("
    + "  itemCode VARCHAR(99) NOT NULL,"
    + "  categoryId INTEGER NOT NULL,"
    + "  CONSTRAINT ItemCategory_pk PRIMARY KEY (itemCode, categoryId),"
    + "  CONSTRAINT ItemCategory_itemCode_fk FOREIGN KEY (itemCode) REFERENCES Item(code),"
    + "  CONSTRAINT ItemCategory_categoryId_fk FOREIGN KEY (categoryId) REFERENCES Category(id)"
    + ")";
try(final Connection connection = DriverManager.getConnection(CONNECTION_URL, "jdoe", "secret")) {
  try(final Statement statement = connection.createStatement()) {
    statement.executeUpdate(CREATE_SUPPLIER_TABLE_SQL);
    statement.executeUpdate(CREATE_ITEM_TABLE_SQL);
    statement.executeUpdate(CREATE_CATEGORY_TABLE_SQL);
    statement.executeUpdate(CREATE_ITEM_CATEGORY_TABLE_SQL);
  }
}

DML Statements

SQL statements that perform CRUD operations, such as inserting or querying data, have a couple of special characteristics: essentially the same SQL statement may be called many times; and those statements may have parameters that vary slightly with each call. Manually constructing query strings is not only tedious, it can also be dangerous if the query parameters are not properly encoded. Moreover performance of repeated statements is not optimal if the database has to parse and process the statement afresh with each call.

For DML commands JDBC provides a special kind of statement object called a prepared statement. This type of statement “prepares” the SQL command by sending it to the database for analysis before actually being executed. A prepared statement can have variable placeholders, allowing a program to substitute specific values immediately before execution. When multiple commands are sent this can be more efficient because the database already knows the format of the statement. The prepared statement automatically encodes the replacement values as needed, preventing “injection attacks” in which malicious users provide raw SQL as entry values.

The java.sql.PreparedStatement interface extends the Statement interface. Instead of calling Connection.createStatment(), call Connection.prepareStatement(String sql) to acquire a prepared statement. If the SQL statements has information that could vary between calls, use the question mark ? character as a placeholder.

Before executing the update, call one of the prepared statement's setXXX() methods such as PreparedStatement.setInt(int parameterIndex, int x) or PreparedStatement.setString(int parameterIndex, String x) for the type you want to set. The parameterIndex indicates the one-based position of the parameter to replace. Once you have set all the parameters, call PreparedStatement.executeUpdate(). The method will return the number of rows updated.

Changing the address of a supplier by name using a prepared statement.
final String CHANGE_SUPPLIER_ADDRESS_BY_NAME_SQL = "UPDATE Supplier"
    + "  SET address = ?"
    + "  WHERE name = ?";
try(final Connection connection = DriverManager.getConnection(CONNECTION_URL, "jdoe", "secret")) {
  try(final PreparedStatement preparedStatement = connection.prepareStatement(CHANGE_SUPPLIER_ADDRESS_BY_NAME_SQL)) {
    preparedStatement.setString(2, "Acme Furniture Company");  //name
    preparedStatement.setString(1, "789 Last Lane");  //new address
    final int rowsUpdated = statement.executeUpdate();
    //TODO provide error handling if rowsUpdated != 1
  }
}

Prepared statements really shine when you need to perform several consecutive updates with different parameters. Consider adding the suppliers to the database for the first time, and assume that you have some sort of Supplier implementation class that holds suppliers. You can use a loop to add each of these suppliers to the database, using the same prepared statement.

Reusing a prepared statement to add multiple rows to the database.
final List<Supplier> suppliers = Arrays.asList(
  new Supplier("Acme Furniture Company", "123 Some Street"),
  new Supplier("Bozo Toy Company", "321 Other Street"),
  new Supplier("Top Toys Corporation", "456 Far Avenue")
);
final String INSERT_SUPPLIER_SQL = "INSERT INTO Supplier (name, address)"
    + "  VALUES (?, ?)";
try(final Connection connection = DriverManager.getConnection(CONNECTION_URL, "jdoe", "secret")) {
  try(final PreparedStatement preparedStatement = connection.prepareStatement(INSERT_SUPPLIER_SQL)) {
    for(final Supplier supplier : suppliers) {
      preparedStatement.setString(1, supplier.getName());  //name
      preparedStatement.setString(2, supplier.getAddress());  //new address
      final int rowsUpdated = statement.executeUpdate();
      //TODO provide error handling if rowsUpdated != 1
    }
  }
}

Processing Results

Queries by definition have a distinction that sets them apart from other DML statement: they potentially return results. For such statements JDBC provides the PreparedStatement.executeQuery() method, which provides a java.sql.ResultSet for accessing the result of the query. The Statement interface provides a similar Statement.executeQuery(String sql) method, although most of the time it is recommended to use PrepareStatement instead for queries.

In the relational algebra, the result of an operation is always a relation. In SQL the result of a query is table, which may have duplicate rows. A ResultSet provides access to the rows a query produces by maintaining a cursor indicating the current row being accessed. It is similar to java.util.Iterator<E> in that it provides a way to repeatedly retrieve a “next” row until there no more rows remaining. Unlike the Iterator<E> interface, there is no “has next” functionality; the method to advance to the next row, ResultSet.next(), will simply return false if there is no next row. ResultSet.next() does not return the next row; rather the ResultSet instance itself provides access to data in the row pointed to by the cursor.

A ResultSet is initialized with its cursor pointing before the first row of the result. This means that ResultSet.next() must be called before information can retrieved from the row. If ResultSet.next() returns true, use the getXXX() method for the appropriate type to retrieve a value from a column. These methods such as ResultSet.getInt(String columnLabel) and ResultSet.getString(String columnLabel) are analogous to the setXXX() methods of PreparedStatement and support the same types. The columnLabel identifies the name of the result column from which to retrieve the value for the current row.

Listing all the stocks of toys.
final String LIST_ITEMS_FOR_CATEGORY_SQL = "SELECT Item.name AS itemName, Item.stock AS itemStock"
    + "  FROM Item"
    + "    JOIN ItemCategory ON ItemCategory.itemCode = Item.code"
    + "    JOIN Category ON ItemCategory.categoryId = Category.id"
    + "  WHERE Category.name = ?";
try(final Connection connection = DriverManager.getConnection(CONNECTION_URL, "jdoe", "secret")) {
  try(final PreparedStatement preparedStatement = connection.prepareStatement(LIST_SUPPLIERS_SQL)) {
      preparedStatement.setString(1, "toy");  //set category name to filter on
      try(final ResultSet resultSet = statement.executeQuery()) {
        while(resultSet.next()) {
          System.out.println(String.format("%s: %d", resultSet.getString("itemName"), resultSet.getInt("itemStock")));
        }
      }
    }
  }
}

Scrollable, Updatable Result Sets

A scrollable result set has the ability to move its cursor both forward and backwards, and an updatable result set allows you to change the underlying data. By default a result set can only iterate forward, and its contents are read-only. If you want a scrollable and/or an updatable result set, you must specify this when you create the statement using Connection.createStatement(int resultSetType, int resultSetConcurrency). The scrolling types and concurrency types are defined in the ResultSet interface.

Result set scrollability values.
ResultSet.TYPE_FORWARD_ONLY
The result set cursor can only move forward. This is the default for Connection.createStatement().
ResultSet.TYPE_SCROLL_INSENSITIVE
The result set is scrollable, but will not reflect changes made to the database.
ResultSet.TYPE_SCROLL_SENSITIVE
The result set is scrollable, and changes to the underlying data will be visible.
Result set concurrency values.
ResultSet.CONCUR_READ_ONLY
The result set does not allow updating the database. This is the default for Connection.createStatement().
ResultSet.CONCUR_UPDATABLE
The result set allows updating the underlying data.
Scrolling

A scrollable result set can move forward as can a non-scrollable result set, but can also move backward using ResultSet.previous(). Remember that a the result set's cursor starts out before the first row. You can later return to this position using ResultSet.beforeFirst(). You can also move the cursor to after the last row by using ResultSet.afterLast() if you wish to work backwards. ResultSet comes with several other methods for scrolling backward and forward absolute and relative amounts; see the API documentation for details.

Updating

If you requested an updatable result set, you can use the appropriate updateXXX() method for the appropriate type to update a value in a column for the current row. These methods such as ResultSet.updateInt(String columnLabel, int x) and ResultSet.updateString(String columnLabel, String x) correspond to the getXXX() methods for retrieving values. There is also a ResultSet.updateNull(String columnLabel) method for setting a column to NULL. The columnLabel identifies the name of the result column for which to set the value for the current row. Once you update columns for a particular row, you must call ResultSet.updateRow() to save the updated values to the database; otherwise, your changes will be lost when you move to a different row.

Review

Summary

TODO table of JDBC Java/SQL mappings from specification

Gotchas

In the Real World

Think About It

Self Evaluation

Task

Implement a JdbcPublicationRepository that loads and stores publications in your database.

See Also

References

Resources

Acknowledgments