Example

SQL table

Create a simple customer table in your database.

customer.sql

CREATE TABLE db_customers (
    id      BIGSERIAL PRIMARY KEY,
    name    VARCHAR(255) NOT NULL,
    budge   INTEGER,
    created TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP
);

Define the row data

First we define a record, which will hold one row of the table. If your Java names do not match the SQL names, you can define the SQL name with the @JdbcName annotation.

record Customer(long id,
                @JdbcName("name") String firstName,
                @Nullable Integer budge,
                LocalDateTime created) {
}

We have the component created with the Java type LocalDateTime which is not a native JDBc type. Therefore we need to define a converter to convert a java.sql.Timestamp into a java.time.LocalDateTime.

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

Define the select statements

You can return the content of the table as list or stream (more details here).

@JdbcSelect("SELECT * FROM db_customers order by name")
List<Customer> listCustomers();
@JdbcSelect("SELECT * FROM db_customers order by name")
Stream<Customer> streamCustomers();

We can even just get one value back from an SQL:

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

Insert data

The table defines two auto generated columns, which we want to return for every insert. Therefore we need a record to hold those generated values. In this case we use the same SQL names in Java.

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

Next we can define the insert SQL. (more details here).

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

The full example

Now we put all components into one Java interface.

SimpleExample.java

public interface SimpleExample {

    /**
     * A record which will hold one row of the table.
     */
    record Customer(long id,
                    @JdbcName("name") String firstName,
                    @Nullable Integer budge,
                    LocalDateTime 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<Customer> listCustomers();

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

    /**
     * The database generated values.
     */
    record CustomerGen(long id, @JdbcName("created") 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) VALUES (:name, :budge)")
    @JdbcReturnGeneratedValues
    CustomerGen insertCustomer(String name, @Nullable Integer budge);

    /*
     * @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();
}

How to use it

The annotation processor will create all the implementation of the interface SimpleExample with name SimpleExampleJdbc in the same package. Now we can use it:

SimpleExampleTest.java

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 alpha = dba.insertCustomer("Alpha", null);
    var bravo = dba.insertCustomer("Bravo", 100_000);

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

    // list all customers and check values
    assertThat(service.listCustomers()).containsExactly(
            new SimpleExample.Customer(alpha.id(), "Alpha", null, alpha.createdDateTime()),
            new SimpleExample.Customer(bravo.id(), "Bravo", 100_000, bravo.createdDateTime())
    );

    // stream all customers and check values
    try (var stream = service.streamCustomers()) {
        assertThat(stream.toList()).containsExactly(
                new SimpleExample.Customer(alpha.id(), "Alpha", null, alpha.createdDateTime()),
                new SimpleExample.Customer(bravo.id(), "Bravo", 100_000, bravo.createdDateTime())
        );
    }
}

We use a very simple JdbcConnectionProvider for this example. To find out more about how you can integrate with common technologies, look at the integration section.