Simple example

This page shows a small end-to-end example with Kaumei JDBC. It defines a table, maps rows to Java types, runs queries, and calls generated code. The examples are intentionally minimal and focus on structure rather than full application setup.

This page covers:

Set up a simple database table
Define the domain model
Simple SELECT queries
Simple INSERT queries
How to use it

Kaumei JDBC performs null checks in line with JSpecify. This example will use the following annotations:

@org.jspecify.annotations.Nullable: a value may be null
@org.jspecify.annotations.NonNull: a value must not be null

Create the SQL table

For this example we define a simple table to store a customer, with

auto generated columns
mandatory columns
optional columns
columns where the Java type differs from the JDBC type

CREATE TABLE db_customer (
    id     BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1000) PRIMARY KEY,
    name         VARCHAR(255) NOT NULL,
    email        VARCHAR(255) DEFAULT 'empty' NOT NULL,
    budge        INTEGER,
    pricing_plan VARCHAR(10) NOT NULL,
    created_at   TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP
);

Domain model

The domain model is used to write data to the database and read it back.

For the pricing_plan we will use a Java enum.

enum PricingPlan {
    FREE, BASIC, PRO, ENTERPRISE
}

We define a record to hold one row of the table. Kaumei JDBC maps Java names from camel case to lowercase SQL names with underscores, for example totalValue to total_value. Use @JdbcName only when the desired SQL name differs from that convention, for example when plan maps to pricing_plan.

record CustomerAsRecord(long id,
                        String name,
                        @Nullable Integer budge,
                        @JdbcName("pricing_plan") @NonNull PricingPlan plan,
                        @JdbcName("created_at") LocalDateTime created) {
}

The tool is not limited to records. The same row can also be represented as a plain Java class:

class CustomerAsClass {
    private final long id;
    private final String name;
    private final @Nullable Integer budge;
    private final @NonNull PricingPlan plan;
    private final LocalDateTime created;

    CustomerAsClass(long id,
                    String name,
                    @Nullable Integer budge,
                    @JdbcName("pricing_plan") @NonNull PricingPlan plan,
                    @JdbcName("created_at") LocalDateTime created) {
        this.id = id;
        this.name = name;
        this.budge = budge;
        this.plan = plan;
        this.created = created;
    }

    // ...
}

The plan property uses a Java enum. Kaumei JDBC maps enums out of the box by using the enum name as a string.

The created property uses LocalDateTime, which JDBC does not handle natively. Add a converter to translate between java.sql.Timestamp and java.time.LocalDateTime. You can use any name as converter method.

@JdbcToJava
static LocalDateTime fromDB(java.sql.Timestamp ts) {
    return ts.toLocalDateTime();
}

Simple SELECT query

This section shows basic queries and maps rows to domain types.

To return the table contents as a List or a Stream.

@JdbcSelect("SELECT id, name, budge, pricing_plan, created_at FROM db_customer ORDER BY name")
List<CustomerAsRecord> listCustomers();
@JdbcSelect("SELECT id, name, budge, pricing_plan, created_at FROM db_customer ORDER BY name")
Stream<CustomerAsClass> streamCustomers();
@JdbcSelect("SELECT id, name, budge, pricing_plan, created_at FROM db_customer WHERE id = :id")
CustomerAsRecord customerById(long id);

To return one row by ID:

@JdbcSelect("SELECT id, name, budge, pricing_plan, created_at FROM db_customer WHERE id = :id")
CustomerAsRecord customerById(long id);

You can also return a single scalar value:

@JdbcSelect("SELECT COUNT(*) FROM db_customer")
int countCustomers();

See mapping JDBC results to Java return types for more details.

Simple INSERT query

This section shows type-safe parameter binding for update methods.

If you do not need generated values, an update method can return void, int, or boolean. Use int for the JDBC update count, which is the number of affected rows. Use boolean when you only need to know whether at least one row was changed.

@JdbcUpdate("DELETE FROM db_customer")
int deleteCustomers();

The db_customers table defines auto generated columns. If you only need the generated ID and provide the other values yourself, return that ID directly.

@JdbcUpdate(value = """
        INSERT INTO db_customer (name, budge, pricing_plan, created_at)
                          VALUES (:name, :budge, :plan, CURRENT_TIMESTAMP)""",
        returnGeneratedColumns = {"id"})
long insertCustomerReturnId(String name, @Nullable Integer budge, @NonNull PricingPlan plan);

If you want several generated values, use a record to capture them. In this case the SQL and Java field names match, except for created_at, which is mapped with @JdbcName.

record CustomerGen(long id, @JdbcName("created_at") LocalDateTime createdDateTime) {
}

Next, we define the insert SQL.

@JdbcUpdate(value = """
        INSERT INTO db_customer (name, budge, pricing_plan)
                          VALUES (:name, :budge, :plan)""",
        returnGeneratedColumns = {"id", "created_at"})
CustomerGen insertCustomer(String name, @Nullable Integer budge, @NonNull PricingPlan plan);

The method parameters are bound to their placeholders, in this case :name and :budge.

See mapping Java parameters to JDBC for parameter binding details. See mapping JDBC results to Java return types for generated value mapping.

How to use it

The annotation processor generates the implementation SimpleExample$Jdbc alongside the interface. Create an instance and call the generated methods:

final AtomicReference<Connection> connectionRef = new AtomicReference<>();

// create the service with a connection provider
final SimpleExample service = new SimpleExample$Jdbc(connectionRef::get);
try (Connection con = dataSource().getConnection()) {
    connectionRef.set(con); // simulate a currently open connection

    // query the empty table
    assertThat(service.listCustomers()).isEmpty();
    try (var stream = service.streamCustomers()) {
        assertThat(stream).isEmpty();
    }
    assertThat(service.countCustomers()).isEqualTo(0);

    // insert one customer and return only the generated id
    assertThat(service.insertCustomerReturnId("Alpha", null, FREE)).isPositive();

    // insert another customer and return several generated values
    var bravoGenerated = service.insertCustomer("Bravo", 100_000, ENTERPRISE);

    // select one customer by id
    var bravo = service.customerById(bravoGenerated.id());
    assertThat(bravo).isEqualTo(new CustomerAsRecord(
            bravoGenerated.id(),
            "Bravo",
            100_000,
            ENTERPRISE,
            bravoGenerated.createdDateTime()
    ));

    // count the customers
    assertThat(service.countCustomers()).isEqualTo(2);

    // list all customers and check values
    assertThat(service.listCustomers())
            .extracting(CustomerAsRecord::name)
            .containsExactly("Alpha", bravo.name());

    // stream all customers and check values
    try (var stream = service.streamCustomers()) {
        assertThat(stream.toList())
                .extracting(CustomerAsClass::getName)
                .containsExactly("Alpha", bravo.name());
    }

    // delete all customers and return the number of affected rows
    assertThat(service.deleteCustomers()).isEqualTo(2);
}

The example uses a minimal JdbcConnectionProvider. To integrate with frameworks such as Spring, Micronaut, or Quarkus, see the integration overview.

The full interface code

Combine all components into a single Java interface:

public interface SimpleExample {

    enum PricingPlan {
        FREE, BASIC, PRO, ENTERPRISE
    }

    /**
     * A record which will hold one row of the table.
     */
    record CustomerAsRecord(long id,
                            String name,
                            @Nullable Integer budge,
                            @JdbcName("pricing_plan") @NonNull PricingPlan plan,
                            @JdbcName("created_at") LocalDateTime created) {
    }


    /**
     * A class which can also hold one row of the table.
     */
    class CustomerAsClass {
        private final long id;
        private final String name;
        private final @Nullable Integer budge;
        private final @NonNull PricingPlan plan;
        private final LocalDateTime created;

        CustomerAsClass(long id,
                        String name,
                        @Nullable Integer budge,
                        @JdbcName("pricing_plan") @NonNull PricingPlan plan,
                        @JdbcName("created_at") LocalDateTime created) {
            this.id = id;
            this.name = name;
            this.budge = budge;
            this.plan = plan;
            this.created = created;
        }

        // ...
        public long getId() {
            return this.id;
        }

        public String getName() {
            return this.name;
        }

        public @Nullable Integer getBudge() {
            return this.budge;
        }

        public @NonNull PricingPlan getPlan() {
            return this.plan;
        }

        public LocalDateTime getCreated() {
            return this.created;
        }
    }

    @JdbcToJava
    static LocalDateTime fromDB(java.sql.Timestamp ts) {
        return ts.toLocalDateTime();
    }

    /*
     * @return a list of all rows of the table.
     */
    @JdbcSelect("SELECT id, name, budge, pricing_plan, created_at FROM db_customer ORDER BY name")
    List<CustomerAsRecord> listCustomers();

    /*
     * @return a stream of all rows of the table.
     */
    @JdbcSelect("SELECT id, name, budge, pricing_plan, created_at FROM db_customer ORDER BY name")
    Stream<CustomerAsClass> streamCustomers();

    @JdbcSelect("SELECT id, name, budge, pricing_plan, created_at FROM db_customer WHERE id = :id")
    CustomerAsRecord customerById(long id);

    /*
     * @return the count(*) of all customers
     */
    @JdbcSelect("SELECT COUNT(*) FROM db_customer")
    int countCustomers();

    /*
     * @return the number of delete rows (return value of executeUpdate)
     */
    @JdbcUpdate("DELETE FROM db_customer")
    int deleteCustomers();

    @JdbcUpdate(value = """
            INSERT INTO db_customer (name, budge, pricing_plan, created_at)
                              VALUES (:name, :budge, :plan, CURRENT_TIMESTAMP)""",
            returnGeneratedColumns = {"id"})
    long insertCustomerReturnId(String name, @Nullable Integer budge, @NonNull PricingPlan plan);

    @JdbcUpdate(value = """
            INSERT INTO db_customer (name, budge, pricing_plan, created_at)
                              VALUES (:name, :budge, :plan, CURRENT_TIMESTAMP)""",
            returnGeneratedValues = JdbcUpdate.GeneratedValues.DEFAULT)
    long insertCustomerReturnId_old(String name, @Nullable Integer budge, @NonNull PricingPlan plan);


    /**
     * The database generated values.
     */
    record CustomerGen(long id, @JdbcName("created_at") LocalDateTime createdDateTime) {
    }

    /*
     * Define a SQL update to insert a row into the table.
     * The values of the 'id' and 'created' columns will be generated by the database.
     * To get those values back we define a record CustomerGen and add the returnGeneratedValues property
     */
    @JdbcUpdate(value = """
            INSERT INTO db_customer (name, budge, pricing_plan)
                              VALUES (:name, :budge, :plan)""",
            returnGeneratedColumns = {"id", "created_at"})
    CustomerGen insertCustomer(String name, @Nullable Integer budge, @NonNull PricingPlan plan);

    @JdbcUpdate(value = """
            INSERT INTO db_customer (name, budge, pricing_plan)
                              VALUES (:name, :budge, :plan)""",
            returnGeneratedValues = JdbcUpdate.GeneratedValues.DEFAULT)
    CustomerGen insertCustomer_old(String name, @Nullable Integer budge, @NonNull PricingPlan plan);

}