Kiwiproc

Use declarative interfaces for Java database queries.

Quick Start

Gradle Configuration

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("0.6.8")
}

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

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

Kotlin

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

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.

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.

For Spring, the @Repository and @Qualifier annotations are used instead.

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

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.

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

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

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.

Properties shown with their default values

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 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).

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.

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 = "..."
        }
    }
}

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.

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. It contains lines of the form:

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

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

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

This is natively supported by Micronaut, and can be used with Spring with a small extra configuration. Other code can read it 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
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
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.
MK	R \| S
MV	CV \| C
M<MK, MV>	A Map.

Notation

Description

void

int or a compatible type

A primitive or boxed primitive

Object type. Specifically, one of: String, BigInteger, BigDecimal, LocalDate, LocalTime, OffsetTime, LocalDateTime, OffsetDateTime

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.

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	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
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.)