Connections and DataSource

There are two ways to get a Connection. DriverManager.getConnection(url, user, password) opens a new physical connection on every call. javax.sql.DataSource is an interface with a getConnection() method, and what sits behind it is a configuration choice: a plain vendor class that also opens a fresh connection each time, or a pool that lends out one it already has.

Why services take a DataSource

Write repositories and services against DataSource and pass it in through the constructor. The query code then does not change when you move from a simple data source in a script to HikariCP in production, or to a container database in tests. It also keeps the URL and credentials in one place, built once at start-up from configuration, instead of scattered through the code.

A connection is not thread-safe

A Connection carries a socket, session settings and the current transaction. Two threads sharing one interleave their statements and commit each other's work. The usual mistake is opening a connection in a constructor and keeping it in a field. It works in a demo, breaks under concurrent requests, and dies for good the first time the database restarts or a firewall drops the idle socket.

Get a connection when a unit of work starts and close it when the work ends. With a pool, close() hands it back instead of disconnecting, so this is cheap.

Timeouts

Driver defaults are generous. The PostgreSQL driver's connectTimeout is 10 seconds and its socketTimeout is 0, meaning a read can wait forever if the server stops answering. Set a socket timeout, or setQueryTimeout(seconds) on individual statements, so a stuck query fails one request instead of holding a thread and a connection indefinitely.

Credentials

Read the URL, user and password from environment variables or a mounted secret, never from source code. The database user a web service connects as should have only the rights the service needs: no superuser, and no permission to drop tables.

Checking the connection

connection.isValid(seconds) asks the driver to confirm the connection is still alive, usually with a tiny query. A readiness probe should call it through the same DataSource the requests use, so the probe fails exactly when the application cannot reach its database.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.postgresql.ds.PGSimpleDataSource;

// Built once at start-up and passed to every repository.
final class Database {
    private Database() {}

    static DataSource fromEnvironment() {
        PGSimpleDataSource ds = new PGSimpleDataSource();
        ds.setURL(System.getenv("DB_URL"));            // jdbc:postgresql://db:5432/shop
        ds.setUser(System.getenv("DB_USER"));
        ds.setPassword(System.getenv("DB_PASSWORD"));
        ds.setApplicationName("orders-service");        // visible in pg_stat_activity
        ds.setSocketTimeout(30);                       // seconds; 0 would wait forever
        return ds;
    }
}

final class OrderRepository {
    private final DataSource dataSource;               // a DataSource, not a Connection

    OrderRepository(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    long countOpen() throws SQLException {
        String sql = "SELECT count(*) FROM orders WHERE status = 'OPEN'";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            rs.next();
            return rs.getLong(1);                     // count(*) is bigint in PostgreSQL
        }
    }
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

final class ScriptConnection {
    static Connection open() throws SQLException {
        Properties props = new Properties();
        props.setProperty("user", System.getenv("DB_USER"));
        props.setProperty("password", System.getenv("DB_PASSWORD"));
        props.setProperty("connectTimeout", "5");     // PostgreSQL driver option, in seconds
        return DriverManager.getConnection(System.getenv("DB_URL"), props);
    }
}

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;

public final class DatabaseHealth {
    private final DataSource dataSource;

    public DatabaseHealth(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public boolean isUp() {
        try (Connection conn = dataSource.getConnection()) {
            return conn.isValid(2);                   // wait at most 2 seconds
        } catch (SQLException e) {
            return false;                             // log e, but do not expose it to the caller
        }
    }
}

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

// Do not do this.
public class UserService {
    private final Connection conn;                    // shared by every request thread

    public UserService(String url, String user, String password) throws SQLException {
        this.conn = DriverManager.getConnection(url, user, password);
    }

    // Two requests running this at once share one transaction and one socket.
    // After a database restart, every call fails until the process restarts.
}