Lightweight SQL Interfaces for Java

I previously blogged about the shortcomings of JDBC and its way of passing SQL statements as strings without any compile-time checking or type safety. The same also applies to other SQL-based database access libraries such as Microsoft’s ODBC, OLE DB and ADO.NET. None of these APIs provide proper integration of SQL with the host language. Of course you could argue that object-relational mapping (ORM) tools like Hibernate have eliminated the need to work directly with SQL, but I found that there are still situations where you want to control database operations more explicitly.

Microsoft has addressed this issue quite elegantly with the introduction of LINQ to SQL in the .NET Framework 3.5. Although there is nothing equivalent in Java, I recently came across some promising efforts to improve language integration by providing fluent interfaces or other lightweight wrappers around JDBC and SQL.

Standard JDBC Example

Before we dive into these newer approaches, consider the following example using traditional JDBC (which prints a list of robots that were “born” before 1980):

public void printClassicRobots() {
    Calendar dobThreshold =
        new GregorianCalendar(1980, 0, 1);
    PreparedStatement statement = null;
    try {
        Connection connection = getConnection();
        statement = connection.prepareStatement(
                "SELECT ID, Name" +
                " FROM Robots" +
                " WHERE DateOfBirth < ?");
        statement.setDate(1, new Date(
                dobThreshold.getTimeInMillis()));

        ResultSet rs = statement.executeQuery();
        while (rs.next()) {
            System.out.format("%08d: %s\n",
                    rs.getInt(1),
                    rs.getString(2));
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        cleanup(statement);
    }
}

JEQUEL

The JEQUEL (Java Embedded QUEry Language) project by Michael Hunger provides an internal DSL (domain specific language) for building SQL statements. Using JEQUEL, the above example can be rewritten as follows:

public static class Robots extends BaseTable<Robots>{

    public final Field<Integer> id = integer();
    public final Field<String> name = string();
    public final Field<Date> dateOfBirth = date();

    {
        initFields();
    }
}

public void printClassicRobots() {
    Calendar dobThreshold =
        new GregorianCalendar(1980, 0, 1);
    Robots robots = new Robots();
    Sql query =
        Select(robots.id, robots.name)
        .from(robots)
        .where(robots.dateOfBirth.lt(named("dob")))
        .toSql();
    query.executeOn(getDataSource())
        .withParams("dob", dobThreshold)
        .handleValues(new ValueRowHandler() {
            public void handleValue(int id,
                    String name) {
                System.out.format("%08d: %s\n",
                        id, name);
            }
    });
}

After defining a query, it is executed on a DataSource, and its result set is processed. One way (among others) of doing this is by providing a callback object with a handleValue method whose parameters correspond to the columns in the result set. Unfortunately you don’t find out until run time if the method signature doesn’t match the result set. Overall, I think JEQUEL looks like a very elegant solution, and I will be looking into it in more detail.

Quaere

The Quaere project by Anders Norås aims to provide query capabilities similar to LINQ in Java. Just like LINQ itself, Quaere is not limited to database access, but provides a general DSL for querying various types of data structures and data sources. The current implementation supports in-memory arrays and collections, as well as JPA entities (Java Persistence API). There is currently no support for SQL queries, so it doesn’t quite fit into the category of lightweight alternatives to ORM tools (since it requires one in the form of JPA). But it is certainly a project worth mentioning, and it should be possible to add support for SQL in the future.

EoD SQL

Early beta versions of JDK 6 contained an EoD (ease of development) feature as part of JDBC 4.0, using annotations to define SQL statements. This feature was later removed from JDK 6, and so far it has not come back (it is not included in JDK 7 as of build 28). However, the same API was reimplemented (with some additional features) in the EoD SQL project. Using this library, the above example can be rewritten as follows:

public static class Robot {
    public int id;
    public String name;
}

public static interface RobotDAI extends BaseQuery {

    @Select("SELECT ID, Name FROM Robots " +
            "WHERE DateOfBirth < ?{1}")
    public DataSet<Robot> getRobotsOlderThan(
            final Date date);
}

public void printClassicRobots() {
    Calendar dobThreshold =
        new GregorianCalendar(1980, 0, 1);
    try {
        RobotDAI query = QueryTool.getQuery(
                getConnection(), RobotDAI.class);
        DataSet<Robot> robots =
            query.getRobotsOlderThan(
                    dobThreshold.getTime());
        for (Robot robot : robots) {
            System.out.format("%08d: %s\n",
                    robot.id, robot.name);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

SQL statements are specified in annotations on the methods of a data access interface (RobotDAI above). An object implementing this interface is automatically generated by the library, and calling the annotated methods on that object causes the corresponding SQL statements to be executed. The query results are returned conveniently as custom data objects rather than just a ResultSet. Unfortunately, the SQL statements themselves still need to be specified as strings, without any compile-time checking or IDE support.

FEST-SQL?

In their article on InfoQ about internal DSLs in Java, Alex Ruiz (of FEST fame) and Jeff Bay showed an example of a DSL for building SQL statements and hinted that this would be released as an open source project. I’m not sure what they will call it, maybe “FEST-SQL” (although its usefulness should not be limited to testing)? Anyway, it certainly seems like a promising project.

Conclusion

Summing up, there is considerable interest and ongoing efforts to provide improvements over traditional JDBC for those situations where a full-blown ORM solution may not be the right tool for the job. I would definitely like to see these efforts continue and gain wider adoption. Thanks to all involved for providing these innovative tools!