Implementing Auto-Incrementing IDs in H2 Database: Best Practices

In database design, auto-incrementing IDs are essential for ensuring data uniqueness and simplifying insertion operations. H2 database, as a lightweight relational database, offers multiple implementation methods. This article will analyze these approaches in detail and illustrate their applications through code examples.

BIGINT AUTO_INCREMENT Syntax

H2 database supports creating auto-incrementing ID columns using the BIGINT AUTO_INCREMENT syntax. Here is a basic example:

DROP TABLE test;
CREATE TABLE test(id BIGINT AUTO_INCREMENT, name VARCHAR(255));
INSERT INTO test(name) VALUES('hello');
INSERT INTO test(name) VALUES('world');
SELECT * FROM test;

After executing this code, the query results will show IDs auto-incremented to 1 and 2. This approach allows omitting the ID field during data insertion, with the database management system automatically generating it.

IDENTITY Syntax

H2 also supports the IDENTITY type, a simplified syntax based on the SQL:2003 standard. The following example demonstrates table creation with IDENTITY:

CREATE TABLE event_ (
    pkey_ IDENTITY NOT NULL PRIMARY KEY,
    name_ VARCHAR NOT NULL,
    start_ TIMESTAMP WITH TIME ZONE NOT NULL,
    duration_ VARCHAR NOT NULL
);

When inserting data, no value is required for the pkey_ column:

INSERT INTO event_ (name_, start_, duration_) VALUES (?, ?, ?);

Retrieving Generated Keys

In Java applications, the JDBC API can be used to retrieve auto-generated key values. First, specify the Statement.RETURN_GENERATED_KEYS flag when creating a PreparedStatement:

PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

After executing the insert operation, call the getGeneratedKeys() method to obtain a ResultSet:

pstmt.executeUpdate();
try (ResultSet rs = pstmt.getGeneratedKeys()) {
    while (rs.next()) {
        int id = rs.getInt(1);
        System.out.println("generated key: " + id);
    }
}

Complete Example Application

Below is a full Java example demonstrating the use of H2's IDENTITY type with JDBC:

package work.basil.example;

import org.h2.jdbcx.JdbcDataSource;
import java.sql.*;
import java.time.*;
import java.util.Objects;

public class H2ExampleIdentity {
    public static void main(String[] args) {
        H2ExampleIdentity app = new H2ExampleIdentity();
        app.doIt();
    }

    private void doIt() {
        JdbcDataSource dataSource = Objects.requireNonNull(new JdbcDataSource());
        dataSource.setURL("jdbc:h2:mem:h2_identity_example_db;DB_CLOSE_DELAY=-1");
        dataSource.setUser("scott");
        dataSource.setPassword("tiger");

        try (Connection conn = dataSource.getConnection()) {
            String sql = """
                CREATE TABLE event_ (
                    id_ IDENTITY NOT NULL PRIMARY KEY,
                    title_ VARCHAR NOT NULL,
                    start_ TIMESTAMP WITHOUT TIME ZONE NOT NULL,
                    duration_ VARCHAR NOT NULL
                );
                """;
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(sql);
            }

            sql = """
                INSERT INTO event_ (title_, start_, duration_) VALUES (?, ?, ?);
                """;
            try (PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
                ZoneId z = ZoneId.of("America/Montreal");
                ZonedDateTime start = ZonedDateTime.of(2021, 1, 23, 19, 0, 0, 0, z);
                Duration duration = Duration.ofHours(2);

                pstmt.setString(1, "Java User Group");
                pstmt.setObject(2, start.toOffsetDateTime());
                pstmt.setString(3, duration.toString());

                pstmt.executeUpdate();
                try (ResultSet rs = pstmt.getGeneratedKeys()) {
                    while (rs.next()) {
                        int id = rs.getInt(1);
                        System.out.println("generated key: " + id);
                    }
                }
            }

            sql = "SELECT * FROM event_;";
            try (Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)) {
                while (rs.next()) {
                    int id = rs.getInt("id_");
                    String title = rs.getString("title_");
                    OffsetDateTime odt = rs.getObject("start_", OffsetDateTime.class);
                    Duration duration = Duration.parse(rs.getString("duration_"));
                    System.out.println("id_" + id + " | start_: " + odt + " | duration: " + duration);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Timestamp Handling Considerations

When working with timestamp data, H2 supports TIMESTAMP WITH TIME ZONE and TIMESTAMP WITHOUT TIME ZONE types. In Java, OffsetDateTime or ZonedDateTime can be used for mapping. For instance, a stored OffsetDateTime object may display different offsets based on the JVM's default time zone but represents the same point in time. In practical applications, appropriate data types should be chosen based on requirements, such as using LocalDateTime with separate time zone storage for handling future appointments.

In summary, H2 database offers flexible and standard-compliant implementations for auto-incrementing IDs. Developers can choose between BIGINT AUTO_INCREMENT or IDENTITY syntax based on project needs and efficiently handle generated keys with the JDBC API.