Using Schema Annotations to Create and Execute SQL Queries

Kilo is an open-source framework for creating and consuming RESTful and REST-like web services in Java. Because many web services provide access to data stored in relational databases, Kilo includes support for programmatically constructing and executing SQL queries via the QueryBuilder class.

For example, given the following tables (adapted from the MySQL tutorial):

create table owner (
    name varchar(20),
    primary key (name)
);

create table pet (
    name varchar(20),
    owner varchar(20),
    species varchar(20),
    sex char(1),
    birth date,
    death date,
    primary key (name),
    foreign key (owner) references owner(name)
);

this code could be used to create a query that returns all rows associated with a particular owner:

var queryBuilder = new QueryBuilder();

queryBuilder.appendLine("select * from pet where owner = :owner order by name");

The colon character identifies “owner” as a parameter, or variable. Parameter values, or arguments, can be passed to QueryBuilder’s executeQuery() method as shown below:

try (var statement = queryBuilder.prepare(getConnection());
    var results = queryBuilder.executeQuery(statement, mapOf(
        entry("owner", owner)
    ))) {
    ...
}

The ResultSetAdapter type returned by executeQuery() provides access to the contents of a JDBC result set via the Iterable interface. Individual rows are represented by Map instances produced by the adapter’s iterator. The results could be coerced to a list of Pet instances and returned to the caller, or used as the data dictionary for a template document:

return results.stream().map(result -> BeanAdapter.coerce(result, Pet.class)).toList();

var templateEncoder = new TemplateEncoder(getClass().getResource("pets.html"), resourceBundle);

templateEncoder.write(results, response.getOutputStream());

Schema Annotations

Earlier Kilo versions supported query construction using “schema types”, enums that provided a SQL-like DSL in Java code. However, these ultimately proved too cumbersome for practical use and were abandoned in favor of “schema annotations”.

For example, given these type definitions:

@Table("owner")
public interface Owner {
    @Column("name")
    @PrimaryKey
    @Index
    String getName();
}

@Table("pet")
public interface Pet {
    @Column("name")
    @PrimaryKey
    @Index
    String getName();
    @Column("owner")
    @ForeignKey(Owner.class)
    String getOwner();
    @Column("species")
    String getSpecies();
    @Column("sex")
    String getSex();
    @Column("birth")
    LocalDate getBirth();
    @Column("death")
    LocalDate getDeath();
}

the preceding query could be written as follows:

var queryBuilder = QueryBuilder.select(Pet.class).filterByForeignKey(Owner.class, "owner").ordered(true);

The Table annotation associates an entity type with a database table. Similarly, the Column annotation associates a property with a column in the table. The PrimaryKey annotation indicates that a property represents the table’s primary key. The ForeignKey annotation indicates that a property represents a relationship to another table. Finally, the Index annotation indicates that a property is part of the default sort order for an entity.

While schema annotations may seem similar to JPA, the two serve different purposes. JPA is a heavyweight abstraction designed to hide the details of database access from the developer, whereas schema annotations are simply meant to help simplify the task of writing native SQL queries.

Insert, update, and delete operations are also supported. See the project README or the pet and catalog service examples for more information.