Simple example

This page shows typical database access patterns with Kaumei JDBC. Learn how to define models, run queries, and integrate generated code. They are intentionally minimal and focus on structure rather than full application setup.

We will address the following things

Setup a simple database table
Define the domain model
Simple SELECT query
Simple INSERT INTO query
How to use it

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

@org.jspecify.annotations.Nullable: variable may contain nulls
@org.jspecify.annotations.NonNull: variable must not contain nulls

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 which which Java type will differ from the JDBC type

CREATE TABLE db_customers (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name         VARCHAR(255) NOT NULL,
    budge        INTEGER,
    pricing_plan VARCHAR(10) NOT NULL,
    created_at   TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP
);

Domain model

The domain model will be used to get data into the database or from 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. If your Java names do not match the SQL names, use the @JdbcName annotation to map them to SQL names.

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, so we define prallel the same as a plain old 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, for which we use the Kaumei JDBC default mapping, which is the name of the enum as 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

Demonstrates retrieving multiple rows and streaming results. Now we see two basic examples of executing a query and mapping rows to domain types.

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

@JdbcSelect("SELECT * FROM db_customers ORDER BY name")
List<CustomerAsRecord> listCustomers();
@JdbcSelect("SELECT * FROM db_customers ORDER BY name")
Stream<CustomerAsClass> streamCustomers();

You can also return a single scalar value:

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

See the select guide for more details.

Simple INSERT INTO query

Demonstrates type-safe parameter binding and reuse of prepared statements.

The table defines two auto generated columns that we want to be returned from each insert.

First we have to create a record to capture those generated values. In this case the SQL and Java field names will match, to keep the example simple.

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

Next, we define the insert SQL.

@JdbcUpdate("INSERT INTO db_customers (name, budge, pricing_plan) VALUES (:name, :budge, :plan)")
@JdbcReturnGeneratedValues
CustomerGen insertCustomer(String name, @Nullable Integer budge, @NonNull PricingPlan plan);

The method parameters are bind to there placeholders in this case to :name and :budge.

See the JDBC update section of the spec for further details. See the Parameter Binding section of the spec for further details.

How to use it

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

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

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

    // insert two customers and return the generated values
    var alphaGen = dba.insertCustomer("Alpha", null, FREE);
    var bravoGen = dba.insertCustomer("Bravo", 100_000, ENTERPRISE);

    var alphaRecord = new CustomerAsRecord(alphaGen.id(), "Alpha", null, FREE, alphaGen.createdDateTime());
    var bravoRecord = new CustomerAsRecord(bravoGen.id(), "Bravo", 100_000, ENTERPRISE, bravoGen.createdDateTime());
    var alphaClass = new CustomerAsClass(alphaGen.id(), "Alpha", null, FREE, alphaGen.createdDateTime());
    var bravoClass = new CustomerAsClass(bravoGen.id(), "Bravo", 100_000, ENTERPRISE, bravoGen.createdDateTime());

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

    // list all customers and check values
    assertThat(service.listCustomers())
            .containsExactly(alphaRecord, bravoRecord);

    // stream all customers and check values
    try (var stream = service.streamCustomers()) {
        assertThat(stream.toList())
                .usingRecursiveFieldByFieldElementComparator()
                .containsExactly(alphaClass, bravoClass);
    }
}

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 * FROM db_customers ORDER BY name")
    List<CustomerAsRecord> listCustomers();

    /*
     * @return a stream of all rows of the table.
     */
    @JdbcSelect("SELECT * FROM db_customers ORDER BY name")
    Stream<CustomerAsClass> streamCustomers();

    /**
     * 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 @JdbcReturnGeneratedValues annotation
     */
    @JdbcUpdate("INSERT INTO db_customers (name, budge, pricing_plan) VALUES (:name, :budge, :plan)")
    @JdbcReturnGeneratedValues
    CustomerGen insertCustomer(String name, @Nullable Integer budge, @NonNull PricingPlan plan);

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

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