Java DB採番テーブルで複数サーバー対応の連番生成を実装する

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DbAtomicCounterDemo {

    /** UPDATE 方式: 行を直接インクリメントして新しい値を取得 */
    public static long nextVal(Connection conn, String seqName)
            throws SQLException {
        var updateSql = "UPDATE seq_table SET current_val = current_val + 1 "
                      + "WHERE seq_name = ?";
        var selectSql = "SELECT current_val FROM seq_table WHERE seq_name = ?";

        try (var upd = conn.prepareStatement(updateSql);
             var sel = conn.prepareStatement(selectSql)) {

            upd.setString(1, seqName);
            if (upd.executeUpdate() == 0) {
                throw new SQLException("採番行が見つかりません: " + seqName);
            }
            sel.setString(1, seqName);
            try (var rs = sel.executeQuery()) {
                if (rs.next()) {
                    return rs.getLong("current_val");
                }
                throw new SQLException("採番値の取得に失敗: " + seqName);
            }
        }
    }

    /** SELECT FOR UPDATE 方式: 行ロック後に読み取り・更新 */
    public static long nextValWithLock(Connection conn, String seqName)
            throws SQLException {
        var selectForUpdate = """
                SELECT current_val FROM seq_table
                WHERE seq_name = ? FOR UPDATE
                """;
        var updateSql = """
                UPDATE seq_table SET current_val = ?
                WHERE seq_name = ?
                """;

        try (var sel = conn.prepareStatement(selectForUpdate);
             var upd = conn.prepareStatement(updateSql)) {

            sel.setString(1, seqName);
            long current;
            try (var rs = sel.executeQuery()) {
                if (!rs.next()) {
                    throw new SQLException("採番行が見つかりません: " + seqName);
                }
                current = rs.getLong("current_val");
            }
            var next = current + 1;
            upd.setLong(1, next);
            upd.setString(2, seqName);
            upd.executeUpdate();
            return next;
        }
    }

    /** トランザクションヘルパー */
    @FunctionalInterface
    interface TxWork<T> {
        T execute(Connection conn) throws SQLException;
    }

    public static <T> T runInTransaction(Connection conn, TxWork<T> work)
            throws SQLException {
        conn.setAutoCommit(false);
        try {
            T result = work.execute(conn);
            conn.commit();
            return result;
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        }
    }

    public static void main(String[] args) throws Exception {
        var url = "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1";

        try (var conn = DriverManager.getConnection(url, "sa", "")) {
            // テーブル準備
            try (var st = conn.createStatement()) {
                st.execute("""
                    CREATE TABLE IF NOT EXISTS seq_table (
                      seq_name    VARCHAR(64) PRIMARY KEY,
                      current_val BIGINT NOT NULL DEFAULT 0
                    )
                    """);
            }
            conn.setAutoCommit(false);
            try (var ps = conn.prepareStatement(
                    "INSERT INTO seq_table (seq_name, current_val) VALUES (?, ?)")) {
                ps.setString(1, "ORDER_SEQ");
                ps.setLong(2, 10000);
                ps.executeUpdate();
            }
            conn.commit();

            // 採番テーブル方式で5回採番
            System.out.println("=== UPDATE 方式 ===");
            for (var i = 0; i < 5; i++) {
                var val = runInTransaction(conn,
                    c -> nextVal(c, "ORDER_SEQ"));
                System.out.println("注文番号: ORD-" + val);
            }
        }
    }
}