Using Schema Types To Create SQL Queries in Java

Kilo (formerly known as HTTP-RPC) is an open-source framework for creating and consuming RESTful and REST-like web services in Java. It is extremely lightweight and requires only a Java runtime environment and a servlet container. The entire framework is less than 150KB in size, making it an ideal choice for applications where a minimal footprint is desired.

This article begins with an introduction to two fundamental Kilo classes, QueryBuilder and ResultSetAdapter. It then discusses Kilo’s support for “schema types,” which allow SQL queries to be expressed via Java language constructs rather than plain text. In other words, they provide a “domain-specific language,” or DSL, for queries.

Getting Kilo

Kilo is distributed via Maven Central:

• org.httprpc:kilo-client – includes support for consuming web services, interacting with relational databases, and working with common file formats (Java 17 or later required)
• org.httprpc:kilo-server – depends on client; includes support for creating web services (Jakarta Servlet specification 5.0 or later required)

QueryBuilder and ResultSetAdapter

Kilo’s QueryBuilder class provides support for programmatically constructing and executing SQL queries. For example, given the following table from the MySQL sample database:

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

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

var queryBuilder = new QueryBuilder();

queryBuilder.append("select * from pet where owner = :owner");

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 = new ResultSetAdapter(queryBuilder.executeQuery(statement, mapOf(
        entry("owner", owner)
    )))) {
    ...
}

Kilo’s ResultSetAdapter class 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 serialized to JSON or CSV, or used as the data dictionary for a template document. Alternatively, they could be mapped to a list of Petinstances and returned to the caller:

public interface Pet {
    String getName();
    String getOwner();
    String getSpecies();
    String getSex();
    Date getBirth();
    Date getDeath();
}

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

Schema Types

QueryBuilder also supports a more structured approach to query construction using “schema types.” For example, the preceding query could be written as follows using a schema type:

public interface Pet {
    @Table("pet")
    enum Schema implements SchemaElement {
        @Column("name")
        NAME,
        @Column("owner")
        OWNER,
        @Column("species")
        SPECIES,
        @Column("sex")
        SEX,
        @Column("birth")
        BIRTH,
        @Column("death")
        DEATH
    }

    ...
}

var queryBuilder = QueryBuilder.selectAll()
    .from(Pet.Schema.class)
    .where(OWNER.eq("owner"));

Schema types are enums that implement the SchemaElement interface. They are associated with table and column names using the Table and Column annotations, respectively. They are often preferable to string literals as they promote code reuse and are more resilient to refactoring. They can also help improve readability, particularly when working with complex queries. In the example above, the OWNER constant has been statically imported to reduce verbosity.

Insert, update, and delete operations are also supported. For example, given the following type (representing an item in a hypothetical product catalog):

@Description("Represents an item in the catalog.")
public interface Item {
    @Table("item")
    enum Schema implements SchemaElement {
        @Column("id")
        ID,
        @Column("description")
        DESCRIPTION,
        @Column("price")
        PRICE
    }

    @Key("id")
    @Description("The item's ID.")
    Integer getID();
    void setID(Integer id);

    @Description("The item's description.")
    @Required
    String getDescription();
    void setDescription(String description);

    @Description("The item's price.")
    @Required
    Double getPrice();
    void setPrice(Double price);
}

This service method could be used to add a row to the item table:

@RequestMethod("POST")
@ResourcePath("items")
@Description("Adds an item to the catalog.")
@Creates
public Item addItem(
    @Description("The item to add.") Item item
) throws SQLException {
    var queryBuilder = QueryBuilder.insertInto(Item.Schema.class, DESCRIPTION, PRICE)
        .values("description", "price");

    try (var statement = queryBuilder.prepare(getConnection())) {
        queryBuilder.executeUpdate(statement, new BeanAdapter(item));
    }

    return getItem(BeanAdapter.coerce(queryBuilder.getGeneratedKeys().get(0), Integer.class));
}

Similarly, the following two methods could be used to update or delete an existing item:

@RequestMethod("PUT")
@ResourcePath("items/?")
@Description("Updates an item.")
public void updateItem(
    @Description("The item ID.") Integer itemID,
    @Description("The updated item.") Item item
) throws SQLException {
    item.setID(itemID);

    var queryBuilder = QueryBuilder.update(Item.Schema.class, DESCRIPTION, PRICE)
        .set("description", "price")
        .where(ID.eq("id"));

    try (var statement = queryBuilder.prepare(getConnection())) {
        queryBuilder.executeUpdate(statement, new BeanAdapter(item));
    }
}

@RequestMethod("DELETE")
@ResourcePath("items/?")
@Description("Deletes an item.")
public void deleteItem(
    @Description("The item ID.") Integer itemID
) throws SQLException {
    var queryBuilder = QueryBuilder.deleteFrom(Item.Schema.class).where(ID.eq("id"));

    try (var statement = queryBuilder.prepare(getConnection())) {
        queryBuilder.executeUpdate(statement, mapOf(
            entry("id", itemID)
        ));
    }
}

See the project README for more information.