JaQu — Another Lightweight SQL Interface for Java

Following up on my earlier post, I would like to mention a new tool by the name of JaQu (Java Query) that Thomas Mueller announced in a comment to my repost on Javalobby. Meanwhile, it has been released as part of the H2 Database Engine and I’ve had a chance to try it.

Even though JaQu comes bundled with H2, it is not specific to that database. I have tested the following code example successfully with an Apache Derby database.

JaQu provides a fluent interface (or internal DSL) for building SQL queries in Java. Before you start writing queries, you need to create a class for each table, with a public field for each column. This is how such a class looks for my Robots table:

public class Robot implements Table {

    public Integer id;
    public String name;
    public Date dateOfBirth;

    @Override
    public void define() {
        tableName("Robots");
        primaryKey(id);
    }
}

Using this table class, my example query can be expressed as follows:

public void printClassicRobots() {
    QuotedTimestamp dobThreshold =
        QuotedTimestamp.from(
                new GregorianCalendar(1980, 0, 1));
    Db db = Db.open(Database.URL, Database.USER,
            Database.PASSWORD);
    Robot r = new Robot();
    List<Robot> robots =
        db.from(r).
        where(r.dateOfBirth).smaller(dobThreshold).
        select();
    for (Robot robot : robots) {
        System.out.format("%08d: %s\n",
                robot.id, robot.name);
    }
}

In order to get the date inserted correctly into the query, I had to define the following class, which encloses the string representation of the time stamp in single quotes:

public class QuotedTimestamp extends Timestamp {

    public static QuotedTimestamp from(
            Calendar calendar) {
        return new QuotedTimestamp(
                calendar.getTimeInMillis());
    }

    public QuotedTimestamp(long time) {
        super(time);
    }

    @Override
    public String toString() {
        return "'" + super.toString() + "'";
    }
}

It is obviously still early days for JaQu, and I’m sure these kinds of issues will be resolved in the future, maybe by using prepared statements internally rather than converting everything to pure SQL strings. I definitely like the powerful API provided by JaQu, which supports much more complex queries than the one shown above. Please take a look at this page for more code examples, including joins, grouping etc.