A real database with JDBC

The DAO of the last two steps forgets everything when the program ends. A database keeps data safe on disk between runs, lets many programs use it at once, and finds rows fast even when there are millions of them. Most databases are used with SQL, a language of its own for asking for data (SELECT), adding it (INSERT), changing it (UPDATE) and removing it (DELETE).

Everything in this step runs on your own computer only. Java Arena has no database and no database library, so the Java in this step is code to read, and the challenges at the end use only the JDK.

JDBC and H2

JDBC (Java Database Connectivity) is the part of the JDK that talks to databases: the interfaces Connection, PreparedStatement and ResultSet and the class DriverManager, in the package java.sql. JDBC is the same for every database. What differs is the driver, a library from the database's makers that does the real work for their database.

H2 is a small database written in Java. Its driver and the whole database come in one jar, so it needs no installing: add its dependency to pom.xml (step 1), and a program can create a database in a file of its own.

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.2.224</version>
</dependency>

A DAO that uses a database

The repair queue's DAO, written with JDBC. It implements the same RepairJobDao as the DAO in memory:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class JdbcRepairJobDao implements RepairJobDao {
    private String url;

    public JdbcRepairJobDao(String url) {
        this.url = url;
        String sql = "CREATE TABLE IF NOT EXISTS RepairJob ("
                + "id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "
                + "bike VARCHAR(100), problem VARCHAR(200), ready BOOLEAN)";
        try (Connection connection = connect();
                PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.executeUpdate();
        } catch (SQLException e) {
            throw new IllegalStateException("Couldn't create the table", e);
        }
    }

    private Connection connect() throws SQLException {
        return DriverManager.getConnection(this.url, "sa", "");
    }

    @Override
    public List<RepairJob> list() {
        List<RepairJob> jobs = new ArrayList<>();
        String sql = "SELECT id, bike, problem, ready FROM RepairJob ORDER BY id";
        try (Connection connection = connect();
                PreparedStatement statement = connection.prepareStatement(sql);
                ResultSet rows = statement.executeQuery()) {
            while (rows.next()) {
                jobs.add(new RepairJob(rows.getInt("id"), rows.getString("bike"),
                        rows.getString("problem"), rows.getBoolean("ready")));
            }
        } catch (SQLException e) {
            throw new IllegalStateException("Couldn't read the jobs", e);
        }
        return jobs;
    }

    @Override
    public boolean remove(int id) {
        try (Connection connection = connect();
                PreparedStatement statement = connection.prepareStatement("DELETE FROM RepairJob WHERE id = ?")) {
            statement.setInt(1, id);
            return statement.executeUpdate() > 0;
        } catch (SQLException e) {
            throw new IllegalStateException("Couldn't remove job " + id, e);
        }
    }

    // add, findById and update follow the same pattern.
}

• The URL names the database: jdbc:h2:./repairs is an H2 database in the file repairs.mv.db in the folder where the program runs, made the first time. "sa" and "" are the user name and the password the database is created with.
• Connection is an open line to the database, from DriverManager.getConnection. JDBC finds H2's driver on the class path by itself.
• PreparedStatement is one SQL statement. Each ? in it is a parameter, filled in with setInt, setString or setBoolean, numbered from 1 (not 0, unlike almost everything else in Java). executeQuery() runs a SELECT and returns its rows; executeUpdate() runs the others and returns how many rows they changed, so remove returns true exactly when a row went away.
• ResultSet walks through the rows. It starts before the first row: each next() moves it one row on and returns false when there are no more, so while (rows.next()) visits every row once. getInt("id") and getString("bike") read a column of the current row.
• try-with-resources (modules 42 and 43) closes the result set, the statement and the connection at the end, in that order, even when an exception is thrown. A connection left open is a connection the database can't give to anyone else.
• SQLException is a checked exception, and RepairJobDao's methods don't declare it, so each method catches it and throws an unchecked one with the cause inside. The rest of the program doesn't change: the UI of step 3 works with this DAO as it is.

To add a job and get its id, the statement is made with connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS), and after executeUpdate(), statement.getGeneratedKeys() returns a ResultSet whose one row holds the id the database chose.

Never build SQL from strings

It's tempting to write "DELETE FROM RepairJob WHERE bike = '" + bike + "'". Then the user's text becomes part of the SQL itself. A bike named O'Neil's tandem breaks the statement, since its ' ends the quoted text early. Worse, someone can type text that is SQL:

DELETE FROM RepairJob WHERE bike = 'x' OR 'a'='a'

'a'='a' is true for every row, so this removes every job. Getting a program to run SQL of the attacker's choice like this is called SQL injection, and it has been behind many real data leaks. With a ? and setString, the text is sent to the database as a value, never as SQL: x' OR 'a'='a is then just an odd bike name that no row has. Always use parameters for values that come from outside the program.

On your own computer

1. Make a Maven project as in step 1, and add H2's dependency to pom.xml.
2. Put RepairJob, RepairJobDao, JdbcRepairJobDao (with the missing methods written), QueueUi and Main in src/main/java, each starting with the project's package line.
3. In main, create new JdbcRepairJobDao("jdbc:h2:./repairs") in place of the DAO in memory.
4. Run it with mvn compile exec:java -Dexec.mainClass=... (your Main's full name), add a few jobs and quit. Run it again: the jobs are still there, in repairs.mv.db.

The MOOC's section 14.4 has the database exercise, with a template whose pom.xml already has H2's dependency. For SQL itself, the MOOC points to a separate course on databases.