✅ Simple example

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

This page covers:

Setup a simple database table
Define the domain model
Simple SELECT queries
Simple INSERT INTO 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: 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 where the Java type differs 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 the same row 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. 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

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();
@JdbcSelect("SELECT * FROM db_customers WHERE id = :id")
CustomerAsRecord customerById(long id);

To return one row by ID:

@JdbcSelect("SELECT * FROM db_customers WHERE id = :id")
CustomerAsRecord customerById(long id);

You can also return a single scalar value:

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

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

Simple INSERT INTO query

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

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_customers")
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_customers (name, budge, pricing_plan, created_at)
                          VALUES (:name, :budge, :plan, CURRENT_TIMESTAMP)""",
        returnGeneratedValues = JdbcUpdate.GeneratedValues.DEFAULT)
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_customers (name, budge, pricing_plan)
                          VALUES (:name, :budge, :plan)""",
        returnGeneratedValues = JdbcUpdate.GeneratedValues.DEFAULT)
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 SimpleExampleJdbc 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 SimpleExampleJdbc(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 * 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();

    @JdbcSelect("SELECT * FROM db_customers WHERE id = :id")
    CustomerAsRecord customerById(long id);

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

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

    @JdbcUpdate(value = """
            INSERT INTO db_customers (name, budge, pricing_plan, created_at)
                              VALUES (:name, :budge, :plan, CURRENT_TIMESTAMP)""",
            returnGeneratedValues = JdbcUpdate.GeneratedValues.DEFAULT)
    long insertCustomerReturnId(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_customers (name, budge, pricing_plan)
                              VALUES (:name, :budge, :plan)""",
            returnGeneratedValues = JdbcUpdate.GeneratedValues.DEFAULT)
    CustomerGen insertCustomer(String name, @Nullable Integer budge, @NonNull PricingPlan plan);
}