Kiwiproc

Use declarative interfaces for Java database queries.

Quick Start

Build Tool Configuration

Maven users should refer to Maven Plugin for dependency and compiler plugin configuration.

Gradle

Add the Gradle plugin. By default, it will:

Set up dependencies for the annotation processor and runtime.
Run an embedded Postgres database during Gradle build and test.
Run a Liquibase changeset against the database.
Export a configuration file that is used by the annotation processor.
Export a properties file that works for Micronaut and Spring to access the embedded database.

Groovy

plugins {
    id 'org.ethelred.kiwiproc' version '1.0'
}

dependencies {
    implementation("jakarta.inject:jakarta.inject-api:2.0.1")
}

kiwiProc {
    // uncomment for Spring:
    // dependencyInjectionStyle = DependencyInjectionStyle.SPRING
}

Kotlin

plugins {
    id("org.ethelred.kiwiproc").version("1.0")
}

dependencies {
    implementation("jakarta.inject:jakarta.inject-api:2.0.1")
}

kiwiProc {
    // uncomment for Spring:
    // dependencyInjectionStyle = DependencyInjectionStyle.SPRING
}

Define a DAO interface

@DAO (1)
public interface CountryCityDao {
    @SqlQuery("""
            SELECT id, name, code
            FROM country
            WHERE code = :code
            """) (2)
    @Nullable
    Country findCountryByCode(String code);

    @SqlUpdate("""
            INSERT INTO city(name, country_id)
            VALUES (:name, :country_id)
            """)
    boolean addCity(String name, int countryId);

}

1	Declare an interface as being a DAO.
2	Define a query. The SQL statement goes inline with the code. Parameters are inserted with ':'.

Inject

Use your favourite dependency injection framework to inject an instance of your DAO.

public class CountryService {
    private final CountryCityDao dao;

    @Inject
    public CountryService(CountryCityDao dao) {
        this.dao = dao;
    }

    public @Nullable Country getCountryByCode(String code) {
        return dao.findCountryByCode(code);
    }
}

Transactions

@DAO
public interface CountryCityDao extends TransactionalDAO<CountryCityDao> { (1)

1	Make the DAO interface extend `TransactionalDAO`

    public boolean addCityInCountry(String cityName, String countryCode) {
        return dao.call(d -> { (1)
           Country country = d.findCountryByCode(countryCode);
           if (country != null) {
               return d.addCity(cityName, country.id());
           }
           return false;
        });
    }

1	Use the `call` or `run` methods, which are passed an instance of "dao" in a transaction.

If the callback completes normally the transaction is committed. If the callback throws a SQLException (or an UncheckedSQLException from a nested DAO call), the transaction is rolled back and the exception is re-thrown wrapped in an UncheckedSQLException.

Streaming

A @SqlQuery method may return java.util.stream.Stream<T> to stream rows lazily from the database rather than buffering them into a List.

@SqlQuery("SELECT id, name FROM types ORDER BY id")
Stream<PetType> streamPetTypes();

Unlike List-returning methods, stream-returning methods are not wrapped in a call() transaction. Each streaming method acquires its own JDBC connection with autoCommit=false (required for PostgreSQL server-side cursors).

The caller is responsible for closing the stream, which commits the transaction and closes the connection. Use try-with-resources:

try (var stream = petClinicDAOProvider.streamPetTypes()) {
    stream.forEach(pet -> process(pet));
}

Use @SqlQuery(fetchSize = N) to enable PostgreSQL server-side cursoring (fetching rows in batches rather than all at once):

@SqlQuery(value = "SELECT id, name FROM types ORDER BY id", fetchSize = 100)
Stream<PetType> streamPetTypes();

Failing to close the stream will leak the database connection. Always use try-with-resources or call stream.close() explicitly.

Framework Support

Kiwiproc uses only the Jakarta annotations @Singleton and @Named, so should work with any Dependency Injection framework that supports those. It expects a DataSource to be injected, with a name matching that specified on the @DAO annotation.

Micronaut test cases are in the "test-micronaut" subproject.
Spring test cases are in the "test-spring" subproject.

Spring Boot

Set dependencyInjectionStyle = DependencyInjectionStyle.SPRING in the Gradle plugin configuration. This changes the generated code to use @Repository and @Qualifier instead of @Singleton and @Named.

The Gradle plugin will automatically add the kiwiproc-spring-autoconfigure module as a dependency. This module provides a Spring Boot auto-configuration (KiwiProcAutoConfiguration) that registers the primary DataSource bean under the "default" qualifier, so no manual DataSource bean definition is needed.

A typical Spring Boot project only needs:

spring.datasource.url=jdbc:postgresql://localhost:5432/mydb

The Gradle plugin generates this property automatically for embedded test databases when dependencyInjectionStyle = DependencyInjectionStyle.SPRING is configured. See Test Properties for details.

The spring-autoconfigure module uses @ConditionalOnSingleCandidate(DataSource.class), so the automatic "default" alias is only created when there is exactly one DataSource bean. For projects with multiple datasources, define the "default" (or named) DataSource bean explicitly.

Database Support

PostgreSQL

PostgreSQL is fully supported with all features:

All standard SQL types
SQL arrays
RETURNING clause in INSERT/UPDATE/DELETE statements
Full parameter type checking at compile time
Embedded database via io.zonky.test embedded-postgres — no Docker required

MySQL

MySQL is supported with some limitations. See MySQL Limitations below.

Embedded MySQL uses Testcontainers at build time, which requires Docker to be available on the build machine.

To select embedded MySQL, set driverClassName to com.mysql.cj.jdbc.Driver in the datasource configuration:

kiwiProc {
    dataSources {
        register("default") {
            driverClassName = "com.mysql.cj.jdbc.Driver" (1)
            liquibaseChangelog = file("$projectDir/src/main/resources/changelog.xml")
        }
    }
}

1	Setting `driverClassName` to the MySQL driver selects embedded MySQL via Testcontainers.

H2

H2 is supported as a lightweight alternative to embedded PostgreSQL for build-time schema validation. No Docker is required — H2 is a pure-Java database that runs in-memory within the Gradle build.

To select embedded H2, set driverClassName to org.h2.Driver in the datasource configuration:

kiwiProc {
    dataSources {
        register("default") {
            driverClassName = "org.h2.Driver" (1)
            liquibaseChangelog = file("$projectDir/src/main/resources/changelog.xml")
        }
    }
}

1	Setting `driverClassName` to the H2 driver selects embedded H2 — no Docker required.

H2 is started in PostgreSQL compatibility mode (MODE=PostgreSQL) to maximise SQL compatibility.

H2 Limitations

Parameter type checking may be weaker — H2’s JDBC driver does not always provide reliable parameter metadata; types fall back to UNKNOWN when unavailable.
SQL arrays are not supported — array type introspection is not implemented for H2.
The RETURNING clause is not supported — use a separate SELECT query after an INSERT if you need generated values.
PostgreSQL-specific types are not available — types such as jsonb, timetz, and timestamptz are not supported in H2.

MySQL Limitations

Parameter type checking is weaker — the MySQL JDBC driver does not reliably provide parameter metadata, so parameter types are not validated at compile time.
SQL arrays are not supported — MySQL has no native array type.
The RETURNING clause is not supported — use a separate SELECT query after an INSERT if you need generated values.
Embedded MySQL requires Docker — Testcontainers is used at build time to start a MySQL container.

Gradle Plugin

As noted in Quick Start, add the plugin with

Groovy

plugins {
    id 'org.ethelred.kiwiproc' version '1.0'
}

Kotlin

plugins {
    id("org.ethelred.kiwiproc").version("1.0")
}

With no additional configuration, this will assume:

A single datasource named "default".
A Liquibase changelog in "src/main/resources/changelog.xml".
Use jakarta.inject annotations in generated code.

All available properties with their default values:

Groovy

import org.ethelred.kiwiproc.processorconfig.DependencyInjectionStyle

kiwiProc {
    dependencyInjectionStyle = DependencyInjectionStyle.JAKARTA (1)
    debug = false (2)
    addDependencies = true (3)
    liquibaseChangelog = file("$projectDir/src/main/resources/changelog.xml") (4)
}

Kotlin

import org.ethelred.kiwiproc.processorconfig.DependencyInjectionStyle

kiwiProc {
    dependencyInjectionStyle = DependencyInjectionStyle.JAKARTA (1)
    debug = false (2)
    addDependencies = true (3)
    liquibaseChangelog = file("$projectDir/src/main/resources/changelog.xml") (4)
}

1	Change to `DependencyInjectionStyle.SPRING` for use with Spring.
2	Enabling debug will make the annotation processor print more details during compilation.
3	Disabling "add dependencies" will prevent the plugin adding kiwiproc dependencies to the build. This is primarily for testing.
4	Change the path for liquibase for the "default" datasource. It is not valid when using multiple datasources - see below.

Liquibase

When using an embedded database, a schema must be defined. Liquibase is the supported way to do this. Therefore, a Liquibase changelog file is required, except when using an external database.

Embedded H2

To use embedded H2 instead of PostgreSQL, set driverClassName to the H2 driver class name. H2 is a pure-Java in-memory database — no Docker required.

Groovy

kiwiProc {
    dataSources {
        register("default") {
            driverClassName = "org.h2.Driver" (1)
            liquibaseChangelog = file("$projectDir/src/main/resources/changelog.xml")
        }
    }
}

Kotlin

kiwiProc {
    dataSources {
        register("default") {
            driverClassName = "org.h2.Driver" (1)
            liquibaseChangelog = file("$projectDir/src/main/resources/changelog.xml")
        }
    }
}

1	Setting `driverClassName` to the H2 driver selects embedded H2.

See H2 Limitations for a list of features not available with H2.

Embedded MySQL

To use embedded MySQL instead of PostgreSQL, set driverClassName to the MySQL driver class name. This requires Docker to be available on the build machine (Testcontainers is used internally).

Groovy

kiwiProc {
    dataSources {
        register("default") {
            driverClassName = "com.mysql.cj.jdbc.Driver" (1)
            liquibaseChangelog = file("$projectDir/src/main/resources/changelog.xml")
        }
    }
}

Kotlin

kiwiProc {
    dataSources {
        register("default") {
            driverClassName = "com.mysql.cj.jdbc.Driver" (1)
            liquibaseChangelog = file("$projectDir/src/main/resources/changelog.xml")
        }
    }
}

1	Setting `driverClassName` to the MySQL driver selects embedded MySQL via Testcontainers/Docker.

See MySQL Limitations for a list of features not available with MySQL.

Multiple and External Datasources

KiwiProc supports using more than one datasource in a project.

@DAO(dataSourceName = "firstDatabase") (1)
interface FirstDatabaseDAO {
    //...
}

1	Specify the datasource name in an interface.

Groovy

kiwiProc {
    dataSources {
        register("firstDatabase") { (1)
            liquibaseChangelog = file("$projectDir/src/main/resources/first/changelog.xml")
        }
        register("secondDatabase") {
            liquibaseChangelog = file("$projectDir/src/main/resources/somepath/changelog.xml")
        }
        register("externalDatabase") { (2)
            jdbcUrl = "jdbc:postgresql://db.example.com/database"
            // optional
            database = "..."
            username = "..."
            password = "..." (3)
        }
        register("externalMySQL") { (4)
            jdbcUrl = "jdbc:mysql://db.example.com/database"
            username = "..."
            password = "..."
        }
        register("externalH2") { (5)
            jdbcUrl = "jdbc:h2:file:/path/to/database"
        }
    }
}

Kotlin

kiwiProc {
    dataSources {
        register("firstDatabase") { (1)
            liquibaseChangelog = file("$projectDir/src/main/resources/first/changelog.xml")
        }
        register("secondDatabase") {
            liquibaseChangelog = file("$projectDir/src/main/resources/somepath/changelog.xml")
        }
        register("externalDatabase") { (2)
            jdbcUrl = "jdbc:postgresql://db.example.com/database"
            // optional
            database = "..."
            username = "..."
            password = "..." (3)
        }
        register("externalMySQL") { (4)
            jdbcUrl = "jdbc:mysql://db.example.com/database"
            username = "..."
            password = "..."
        }
        register("externalH2") { (5)
            jdbcUrl = "jdbc:h2:file:/path/to/database"
        }
    }
}

1	Register one or more datasources.
2	An external PostgreSQL datasource is specified by giving a JDBC URL. Liquibase changelog is optional for an external datasource.
3	How to manage secret values for Gradle builds, is out of scope for this document.
4	An external MySQL datasource uses a `jdbc:mysql://` URL.
5	An external H2 datasource uses a `jdbc:h2:` URL.

Groovy

kiwiProc {
    dataSources {
        register("default") { (1)
            jdbcUrl = "jdbc:postgresql://db.example.com/database"
        }
    }
}

Kotlin

kiwiProc {
    dataSources {
        register("default") { (1)
            jdbcUrl = "jdbc:postgresql://db.example.com/database"
        }
    }
}

1	To use an external database for the "default" datasource, configure it in the dataSources block.

Test properties

A file named application-test.properties will be written to the test resources path, so that automated tests can use the same database.

The property key depends on the dependencyInjectionStyle:

For JAKARTA (the default) and Micronaut:

datasources.default.url=jdbc\:postgresql\://localhost\:42581/rgtnmpgbihhy?user\=postgres

For SPRING:

spring.datasource.url=jdbc\:postgresql\://localhost\:42581/rgtnmpgbihhy?user\=postgres

This is the standard Spring Boot property for datasource auto-configuration. Combined with the kiwiproc-spring-autoconfigure module (added automatically by the plugin), no manual DataSource bean definition is needed in tests.

For MySQL, credentials are embedded in the URL as query parameters:

datasources.default.url=jdbc\:mysql\://localhost\:3306/test?user\=root&password\=secret

Other code can read the properties file with java.util.Properties.

Maven Plugin

kiwiproc-maven-plugin mirrors the Gradle plugin’s support for an embedded build-time database: it manages an embedded PostgreSQL or H2 instance, applies your Liquibase changelog, generates the annotation processor’s config.json, and writes a properties file for use in tests.

Dependencies

Add the following to your pom.xml:

<dependencies>
    <!-- kiwiproc annotations -->
    <dependency>
        <groupId>org.ethelred.kiwiproc</groupId>
        <artifactId>shared</artifactId>
        <version>1.0</version>
    </dependency>
    <!-- kiwiproc runtime (required at runtime) -->
    <dependency>
        <groupId>org.ethelred.kiwiproc</groupId>
        <artifactId>runtime</artifactId>
        <version>1.0</version>
    </dependency>
    <!-- Jakarta inject API -->
    <dependency>
        <groupId>jakarta.inject</groupId>
        <artifactId>jakarta.inject-api</artifactId>
        <version>2.0.1</version>
    </dependency>
</dependencies>

For Spring, add spring-autoconfigure instead and set dependencyInjectionStyle to SPRING in the plugin configuration below — see Framework Support.

Adding the Plugin

Add the plugin to your pom.xml and bind the generate goal. generate is bound to the generate-sources phase by default, so an empty execution declaring just the goal is enough:

<plugin>
    <groupId>org.ethelred.kiwiproc</groupId>
    <artifactId>kiwiproc-maven-plugin</artifactId>
    <version>1.0</version>
    <executions>
        <execution>
            <goals>
                <goal>generate</goal>
            </goals>
        </execution>
    </executions>
</plugin>

With no <configuration>, this will assume:

A single datasource named "default".
Embedded PostgreSQL.
A Liquibase changelog at src/main/resources/changelog.xml.
JAKARTA dependency injection style in generated code.

Liquibase

When using an embedded database, a schema must be defined. Liquibase is the supported way to do this. Therefore, a Liquibase changelog file is required, except when using an external database.

All available properties

<plugin>
    <groupId>org.ethelred.kiwiproc</groupId>
    <artifactId>kiwiproc-maven-plugin</artifactId>
    <version>1.0</version>
    <executions>
        <execution>
            <goals>
                <goal>generate</goal>
            </goals>
        </execution>
    </executions>
    <configuration>
        <dependencyInjectionStyle>JAKARTA</dependencyInjectionStyle> (1)
        <debug>false</debug> (2)
        <liquibaseChangelog>${project.basedir}/src/main/resources/changelog.xml</liquibaseChangelog> (3)
        <dataSources> (4)
            <!-- omit this entirely to use <liquibaseChangelog> above for the "default" datasource -->
        </dataSources>
    </configuration>
</plugin>

1	Change to `SPRING` for use with Spring.
2	Enabling debug will make the annotation processor print more details during compilation.
3	Path to the Liquibase changelog used for the "default" datasource, when `dataSources` is empty or omitted.
4	A list of `<dataSource>` entries — see below. If non-empty, this takes precedence and `liquibaseChangelog` above is ignored entirely.

Each <dataSource> entry supports:

Property Description

Property	Description
`name`	The datasource name. Defaults to `"default"`.
`liquibaseChangelog`	Path to a Liquibase changelog. Required for embedded databases; optional for external databases (if present, it is applied to the external database too).
`jdbcUrl`	JDBC URL of an external database. If set, the datasource is treated as external and no embedded database is started.
`database`	Database name, used when connecting to an external PostgreSQL datasource.
`username`	Username for an external datasource.
`password`	Password for an external datasource.
`driverClassName`	JDBC driver class name. Set to `org.h2.Driver` or `com.mysql.cj.jdbc.Driver` to select embedded H2 or MySQL instead of PostgreSQL (for embedded datasources), or to describe the driver of an external datasource.

name

The datasource name. Defaults to "default".

liquibaseChangelog

Path to a Liquibase changelog. Required for embedded databases; optional for external databases (if present, it is applied to the external database too).

jdbcUrl

JDBC URL of an external database. If set, the datasource is treated as external and no embedded database is started.

database

Database name, used when connecting to an external PostgreSQL datasource.

username

Username for an external datasource.

password

Password for an external datasource.

driverClassName

JDBC driver class name. Set to org.h2.Driver or com.mysql.cj.jdbc.Driver to select embedded H2 or MySQL instead of PostgreSQL (for embedded datasources), or to describe the driver of an external datasource.

Embedded H2

To use embedded H2 instead of PostgreSQL, set driverClassName to the H2 driver class name. H2 is a pure-Java in-memory database — no Docker required.

<configuration>
    <dataSources>
        <dataSource>
            <name>default</name>
            <driverClassName>org.h2.Driver</driverClassName> (1)
            <liquibaseChangelog>${project.basedir}/src/main/resources/changelog.xml</liquibaseChangelog>
        </dataSource>
    </dataSources>
</configuration>

1	Setting `driverClassName` to the H2 driver selects embedded H2.

See H2 Limitations for a list of features not available with H2.

Multiple and External Datasources

KiwiProc supports using more than one datasource in a project.

@DAO(dataSourceName = "firstDatabase") (1)
interface FirstDatabaseDAO {
    //...
}

1	Specify the datasource name in an interface.

<configuration>
    <dataSources>
        <dataSource> (1)
            <name>default</name>
            <liquibaseChangelog>${project.basedir}/src/main/resources/changelog.xml</liquibaseChangelog>
        </dataSource>
        <dataSource> (2)
            <name>externalDatabase</name>
            <jdbcUrl>jdbc:postgresql://db.example.com/database</jdbcUrl>
            <!-- optional -->
            <database>...</database>
            <username>...</username>
            <password>...</password> (3)
        </dataSource>
        <dataSource> (4)
            <name>externalH2</name>
            <jdbcUrl>jdbc:h2:tcp://db.example.com/database</jdbcUrl>
        </dataSource>
    </dataSources>
</configuration>

1	An embedded "default" datasource, as in the default configuration.
2	An external PostgreSQL datasource is specified by giving a JDBC URL. Liquibase changelog is optional for an external datasource.
3	How to manage secret values for Maven builds is out of scope for this document.
4	An external H2 datasource uses a `jdbc:h2:` URL.

Embedded MySQL

To use embedded MySQL instead of PostgreSQL, set driverClassName to the MySQL driver class name. This requires Docker to be available on the build machine (Testcontainers is used internally).

<configuration>
    <dataSources>
        <dataSource>
            <name>default</name>
            <driverClassName>com.mysql.cj.jdbc.Driver</driverClassName> (1)
            <liquibaseChangelog>${project.basedir}/src/main/resources/changelog.xml</liquibaseChangelog>
        </dataSource>
    </dataSources>
</configuration>

1	Setting `driverClassName` to the MySQL driver selects embedded MySQL via Testcontainers/Docker.

See MySQL Limitations for a list of features not available with MySQL.

Compiler plugin configuration

kiwiproc-maven-plugin automatically writes ${project.build.directory}/kiwiproc/config.json during the generate-sources phase, describing the datasource(s) it just prepared. Configure maven-compiler-plugin to point the kiwiproc annotation processor at this generated file:

<plugin>
    <groupId>org.apache.maven.plugins</groupId>
    <artifactId>maven-compiler-plugin</artifactId>
    <configuration>
        <annotationProcessorPaths>
            <path>
                <groupId>org.ethelred.kiwiproc</groupId>
                <artifactId>processor</artifactId>
                <version>1.0</version>
            </path>
        </annotationProcessorPaths>
        <compilerArgs>
            <arg>-Aorg.ethelred.kiwiproc.configuration=${project.build.directory}/kiwiproc/config.json</arg>
        </compilerArgs>
    </configuration>
</plugin>

config.json is (re)generated automatically on every build, so there is nothing to edit by hand.

Test properties

A file named application-test.properties is written to ${project.build.directory}/generated-test-resources/kiwiproc, which is registered as a test resource directory so it is available on the test classpath.

The property key depends on dependencyInjectionStyle, following the same rules as the Gradle plugin (see Test properties):

For JAKARTA (the default) and Micronaut:

datasources.default.url=jdbc\:postgresql\://localhost\:42581/rgtnmpgbihhy?user\=postgres

For SPRING, the "default" datasource uses the standard Spring Boot property:

spring.datasource.url=jdbc\:postgresql\://localhost\:42581/rgtnmpgbihhy?user\=postgres

Combined with the kiwiproc-spring-autoconfigure module, no manual DataSource bean definition is needed in tests. Other code can read the properties file with java.util.Properties.

API Reference

Appendix A: Types and Validation

Kiwiproc performs validations during build, based on the method signature and database metadata.

Type Hierarchy: Do the types in the method signature fit Kiwiproc conventions.
Exhaustiveness: Are all input parameters matched. Are all result columns matched.
Compatibility: Does Kiwiproc have supported conversions between the Java and SQL types.

Types

A semi-formal attempt to describe how Kiwiproc uses types. These are generalized types, not Java or SQL types.

Notation Description

Notation	Description
V	void
I	int or a compatible type
P	A primitive or boxed primitive
O	Object type. Specifically, one of: String, BigInteger, BigDecimal, LocalDate, LocalTime, OffsetTime, LocalDateTime, OffsetDateTime, UUID, or any Java `enum`
S	P \| O
?S	@Nullable (specifically org.jspecify.annotations.Nullable), or Optional<S>. A boxed primitive is also treated as nullable when it is not a type parameter of a container. Optional is only accepted as the return type of a query method.
SC<S>	A Collection or array, only in a context where it is mapped to or from a SQL array.
RC	S \| ?S \| SC
RCS	RC \| RCS RC
R(RCS)	A Java record.
CV	S \| SC \| R
C<CV>	A Collection, array or Iterable.
St<CV>	A `Stream`. See Streaming for lifecycle details.
MK	R \| S
MV	CV \| C
M<MK, MV>	A Map.

void

int or a compatible type

A primitive or boxed primitive

Object type. Specifically, one of: String, BigInteger, BigDecimal, LocalDate, LocalTime, OffsetTime, LocalDateTime, OffsetDateTime, UUID, or any Java enum

P | O

@Nullable (specifically org.jspecify.annotations.Nullable), or Optional<S>. A boxed primitive is also treated as nullable when it is not a type parameter of a container. Optional is only accepted as the return type of a query method.

SC<S>

A Collection or array, only in a context where it is mapped to or from a SQL array.

S | ?S | SC

RCS

RC | RCS RC

R(RCS)

A Java record.

S | SC | R

C<CV>

A Collection, array or Iterable.

St<CV>

A Stream. See Streaming for lifecycle details.

R | S

CV | C

M<MK, MV>

A Map.

Unresolved Types

Used internally, not by end users. Documented here for developers working on Kiwiproc itself.

Notation	Description
UC<S>	SC<S> \| C<S>
UM	Map, where the key and/or value are not Record, and we need to know the column names.

Notation

Description

UC<S>

SC<S> | C<S>

Map, where the key and/or value are not Record, and we need to know the column names.

Nullability

In the current version of the library, nullability handling is neither as strict or as consistent as I would like it to be.

Java values are treated as non-null, except as described here.

Elements of SC, C, M must not be nullable. The current implementation allows null values, but skips over adding them to the collection.
A boxed primitive is treated as nullable, except where it is an element of SC, C, M.
An object type annotated with org.jspecify.annotations.Nullable. This does not include boxed primitives.
The element of an Optional<>, OptionalInt, OptionalDouble, OptionalLong.

JDBC drivers may return 'unknown' for nullability. In practice, the Postgres driver always returns 'unknown' for parameters. (As do a couple of other drivers I checked for comparison.) The way that Kiwiproc deals with unknown nullability could do with more design work.

SQL result columns are often reported as nullable even when they cannot practically be null at runtime — for example, count(*) returns a BIGINT that PostgreSQL metadata marks as nullable. When the declared Java return type is a non-nullable primitive (e.g. int), Kiwiproc permits the narrowing conversion and emits a compile-time warning about the lossy cast, rather than rejecting it as an error. If the SQL value is ever actually null at runtime a NullPointerException will occur.

Exhaustiveness

For each Java method parameter, there must be a corresponding parameter in the SQL statement. When the method parameter is a record, at least one of its components must correspond to a parameter in the SQL statement.
For each parameter in the SQL statement, there must be a corresponding method parameter, or component of a record that is a method parameter.
Every column in the SQL result must be used in the return type of the method.
Every value or component in the return type of the method must correspond to a column in the SQL result.

Compatibility

Kiwiproc has a set of type conversions, for any supported types where it makes sense. "Compatibility" means that there is a type conversion between the matching Java and SQL elements.

Table 1. Conversions
Source	Target	Warning
BigDecimal	BigInteger	possible lossy conversion from BigDecimal to BigInteger
BigDecimal	byte	possible lossy conversion from BigDecimal to byte
BigDecimal	double	possible lossy conversion from BigDecimal to double
BigDecimal	float	possible lossy conversion from BigDecimal to float
BigDecimal	int	possible lossy conversion from BigDecimal to int
BigDecimal	long	possible lossy conversion from BigDecimal to long
BigDecimal	short	possible lossy conversion from BigDecimal to short
BigInteger	BigDecimal
BigInteger	boolean
BigInteger	byte	possible lossy conversion from BigInteger to byte
BigInteger	double	possible lossy conversion from BigInteger to double
BigInteger	float	possible lossy conversion from BigInteger to float
BigInteger	int	possible lossy conversion from BigInteger to int
BigInteger	long	possible lossy conversion from BigInteger to long
BigInteger	short	possible lossy conversion from BigInteger to short
LocalDate	LocalDateTime
LocalDate	OffsetDateTime
LocalDate	long	uses system default ZoneId
LocalDateTime	LocalDate
LocalDateTime	LocalTime
LocalDateTime	long	uses system default ZoneId
OffsetDateTime	LocalDate
OffsetDateTime	LocalDateTime
OffsetDateTime	OffsetTime
OffsetDateTime	long
OffsetTime	LocalTime
String	BigDecimal	possible NumberFormatException parsing String to BigDecimal
String	BigInteger	possible NumberFormatException parsing String to BigInteger
String	Byte	possible NumberFormatException parsing String to byte
String	Character	possible NumberFormatException parsing String to char
String	Double	possible NumberFormatException parsing String to double
String	Float	possible NumberFormatException parsing String to float
String	Integer	possible NumberFormatException parsing String to int
String	LocalDate	possible DateTimeParseException parsing String to LocalDate
String	LocalDateTime	possible DateTimeParseException parsing String to LocalDateTime
String	LocalTime	possible DateTimeParseException parsing String to LocalTime
String	Long	possible NumberFormatException parsing String to long
String	OffsetDateTime	possible DateTimeParseException parsing String to OffsetDateTime
String	OffsetTime	possible DateTimeParseException parsing String to OffsetTime
String	Short	possible NumberFormatException parsing String to short
String	UUID	possible IllegalArgumentException parsing String to UUID
String	boolean
String	byte	possible NumberFormatException parsing String to byte
String	char	possible NumberFormatException parsing String to char
String	double	possible NumberFormatException parsing String to double
String	float	possible NumberFormatException parsing String to float
String	int	possible NumberFormatException parsing String to int
String	long	possible NumberFormatException parsing String to long
String	short	possible NumberFormatException parsing String to short
UUID	String
boolean	BigInteger
boolean	byte
boolean	char
boolean	int
boolean	long
boolean	short
byte	BigDecimal
byte	BigInteger
byte	boolean
byte	byte
byte	char	possible lossy conversion from byte to char
byte	double
byte	float
byte	int
byte	long
byte	short
char	boolean
char	byte	possible lossy conversion from char to byte
char	char
char	double
char	float
char	int
char	long
char	short	possible lossy conversion from char to short
double	BigDecimal
double	BigInteger
double	byte	possible lossy conversion from double to byte
double	char	possible lossy conversion from double to char
double	float	possible lossy conversion from double to float
double	int	possible lossy conversion from double to int
double	long	possible lossy conversion from double to long
double	short	possible lossy conversion from double to short
float	BigDecimal
float	BigInteger
float	byte	possible lossy conversion from float to byte
float	char	possible lossy conversion from float to char
float	double
float	float
float	int	possible lossy conversion from float to int
float	long	possible lossy conversion from float to long
float	short	possible lossy conversion from float to short
int	BigDecimal
int	BigInteger
int	boolean
int	byte	possible lossy conversion from int to byte
int	char	possible lossy conversion from int to char
int	double
int	float
int	int
int	long
int	short	possible lossy conversion from int to short
long	BigDecimal
long	BigInteger
long	LocalDate	uses system default ZoneId
long	LocalDateTime	uses system default ZoneId
long	LocalTime	uses system default ZoneId
long	OffsetDateTime	uses system default ZoneId
long	OffsetTime	uses system default ZoneId
long	boolean
long	byte	possible lossy conversion from long to byte
long	char	possible lossy conversion from long to char
long	double
long	float
long	int	possible lossy conversion from long to int
long	long
long	short	possible lossy conversion from long to short
short	BigDecimal
short	BigInteger
short	boolean
short	byte	possible lossy conversion from short to byte
short	char	possible lossy conversion from short to char
short	double
short	float
short	int
short	long
short	short

(Any type can be converted to String.)

Enum conversions

Java enum types are converted to and from SQL using the constant name:

SQL → Java: MyEnum.valueOf(stringValue) — mapped from a VARCHAR or PostgreSQL native enum column. A compile-time warning is emitted because an unrecognised DB value will throw IllegalArgumentException at runtime.
Java → SQL: myEnum.name() — the string name is bound as a parameter using setObject with Types.OTHER, which PostgreSQL accepts for both VARCHAR and native enum columns.

PostgreSQL native enum columns (CREATE TYPE … AS ENUM (…)) are supported transparently: the JDBC driver returns their values as String, so no special handling is required on the read path.