The JSON annotation

[gk-brown]

Kilo's QueryBuilder class provides support for programmatically constructing and executing SQL queries. For example, this code could be used to create a query that returns all pets associated with a particular owner:

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

The query could then be executed as follows:

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

The JSON annotation can be used to indicate that a table column contains JSON data. When this annotation is present, QueryBuilder will automatically translate the associated property value to and from JSON as needed. For example:

@Table("employee_details")
public interface EmployeeDetail extends Employee {
    @Column("salaries")
    @JSON
    List<Salary> getSalaries();
}

@Table("salaries")
public record Salary(
    @Column("emp_no")
    @PrimaryKey
    @ForeignKey(Employee.class)
    Integer employeeNumber,

    @Column("salary")
    Integer salary,

    @Column("from_date")
    LocalDate fromDate,

    @Column("to_date")
    LocalDate toDate
) {
}

The data could be stored in a text field, or it could be the result of a SQL query like this:

create view employee_details as
select employees.*, (select json_arrayagg(json_object(
  'salary', salary,
  'fromDate', from_date,
  'toDate', to_date
)) from salaries where salaries.emp_no = employees.emp_no) as salaries
from employees;

Either way, the serialization is handled entirely by QueryBuilder – no application involvement is needed.

QueryBuilder will also automatically translate properties of type org.w3c.dom.Document to and from XML. For more information, see the README.