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.

Advertisements

7 Responses to JaQu — Another Lightweight SQL Interface for Java

  1. FYI there is now a new ‘project page’ for JaQu:
    http://www.h2database.com/html/jaqu.html
    At some point, JaQu will probably have its own home page.

  2. Bernhard Glomann says:

    Thanks Thomas. I added a link to the page. Keep up the great work!

  3. Now Date, Time, and Timestamp are now supported.
    Also, the newest version of JaQu uses prepared statements.

    • Lukas Eder says:

      Hi Thomas,

      After going live with jOOQ and gaining my first contributor Espen, jOOQ is right on track with many new features. Espen has implemented H2 support for jOOQ, which will ship in the next release some time in December. I was wondering, whether you guys at JaQu would be interested in a cooperation? I have seen that there has not been a lot of activity at JaQu during the last two years. At the same time, JaQu has a lot of similar concepts like jOOQ.

      What do you think?

      Cheers
      Lukas

      https://sourceforge.net/apps/trac/jooq/wiki/Examples

  4. Thierry Forgeard says:

    I like JaQu. It looks simple and natural.

  5. Josef Garvi says:

    Interesting project. Think it can handle the awkward Ms Access sql syntax? (brackets for quoting fields for instance)

  6. Lukas Eder says:

    Thanks for the interesting article. I had recently implemented a similar utility that does not operate on Strings but keeps an object model of the query. This allows for abstraction of SQL and variable binding. Also, no schema-related code needs to be written, as the schema is generated.

    I’ll happily review jaqu as it is today.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: