A database with JDBC

While posts live in memory, they disappear after a server restart. It is time to save them in a database. Javalin has no database layer of its own: you use any Java library. We take plain JDBC, familiar from the tutorial, and the embedded H2 database — it runs inside the program and keeps data in a file, nothing to install.

The dependency

Add the H2 driver to pom.xml:

<!-- pom.xml -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.5.252</version>
</dependency>

Connection and table

A DataSource is an object that hands out database connections. The Database class creates one together with the posts table:

// src/main/java/com/example/blog/Database.java
package com.example.blog;
 
import org.h2.jdbcx.JdbcDataSource;
 
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
 
public class Database {
    public static DataSource create(String url) {
        JdbcDataSource ds = new JdbcDataSource();
        ds.setURL(url);
        ds.setUser("sa");
        ds.setPassword("");
        createTables(ds);
        return ds;
    }
 
    private static void createTables(DataSource ds) {
        try (Connection conn = ds.getConnection();
             Statement st = conn.createStatement()) {
            st.execute("""
                    CREATE TABLE IF NOT EXISTS posts (
                        id     BIGINT AUTO_INCREMENT PRIMARY KEY,
                        title  VARCHAR(200) NOT NULL,
                        body   VARCHAR(5000) NOT NULL,
                        author VARCHAR(100) NOT NULL
                    )""");
        } catch (SQLException e) {
            throw new RuntimeException("Cannot create tables", e);
        }
    }
}

The repository

A repository is a class that hides SQL behind clear methods. It has the same methods as PostStore, so the controller barely changes. Here are two methods, the others work the same way:

// src/main/java/com/example/blog/PostRepository.java
public class PostRepository {
    private final DataSource ds;
 
    public PostRepository(DataSource ds) {
        this.ds = ds;
    }
 
    public Optional<Post> findById(long id) {
        String sql = "SELECT id, title, body, author FROM posts WHERE id = ?";
        try (Connection conn = ds.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setLong(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? Optional.of(toPost(rs)) : Optional.empty();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
 
    public Post create(NewPost data) {
        String sql = "INSERT INTO posts (title, body, author) VALUES (?, ?, ?)";
        try (Connection conn = ds.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, data.title());
            ps.setString(2, data.body());
            ps.setString(3, data.author());
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                keys.next();
                return new Post(keys.getLong(1), data.title(), data.body(), data.author());
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
 
    private Post toPost(ResultSet rs) throws SQLException {
        return new Post(rs.getLong("id"), rs.getString("title"),
                rs.getString("body"), rs.getString("author"));
    }
 
    // findAll() — SELECT ... ORDER BY id, delete(id) — DELETE FROM posts WHERE id = ?
}

SQLException is wrapped in a RuntimeException, and the exception handler from the errors lesson catches it.

Wiring it into the controller

In PostController change the type of the field and the constructor parameter from PostStore to PostRepository (you may rename the store field to repo). In Main create the repository:

// src/main/java/com/example/blog/Main.java
var repo = new PostRepository(Database.create("jdbc:h2:./blog"));
var posts = new PostController(repo);

The address jdbc:h2:./blog means the file blog.mv.db in the folder the server was started from. The PostStore class is no longer needed — delete it.

Create a post, restart the server and request the list:

curl -X POST http://localhost:8080/posts \
     -d '{"title":"Hello, H2","body":"Posts now live in a database.","author":"Oleg"}'
curl http://localhost:8080/posts

[{"id":1,"title":"Hello, H2","body":"Posts now live in a database.","author":"Oleg"}]

The post survived the restart. Add *.db to .gitignore so the database file does not end up in Git.

Common mistake

The in-memory database jdbc:h2:mem:blog is handy for tests, but H2 deletes it when the last connection closes. The table created in Database.create disappears right away, and any later query to it fails, for example SELECT * FROM posts:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "POSTS" not found (this database is empty); SQL statement:
SELECT * FROM posts [42104-252]

For an in-memory database add a parameter: jdbc:h2:mem:blog;DB_CLOSE_DELAY=-1 — then it lives as long as the program runs.

Thanks for reading!