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);
    }
}
&#91;/sourcecode&#93;
<h3>JEQUEL</h3>
The <a href="http://www.jequel.de/" target="_blank">JEQUEL</a> (Java Embedded QUEry Language) project by <a href="http://www.jexp.de/blog/archives/4-On-Jequel.html" target="_blank">Michael Hunger</a> provides an <a href="http://martinfowler.com/bliki/DomainSpecificLanguage.html" target="_blank">internal DSL</a> (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) throws SQLException;
}

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!

Advertisements

8 Responses to Lightweight SQL Interfaces for Java

  1. You forgot to include sql orm! :-) It is still work in progress http://sqlorm.sourceforge.net/ but there is a cool article on automatic refactoring of db schema AND code at http://sqlorm.sourceforge.net/database_refactoring_made_easy.html

  2. cease says:

    what about spring jdbc ? If you count spring as a whole, it may not be lightweight anymore, but should still be considered a lightweight solution.

  3. Jason says:

    In EoD SQL you could also return:

    List
    Set
    Robot[]
    DataIterator (one of EoD SQL’s really special features)
    or just a Robot (for if there was only one row… ie: a select-by-id)

    Also… in your code example, you don’t need to cacth(SQLException), since you didn’t declare one, so EoD SQL will wrap it for you ;)

    Thanks for the comparison, you’ve put my EoD SQL annotation-processor-tool back on my priority list!

  4. Lars Hoss says:

    I’d like to add iBatis. Though it might not be a “lightweight” framework it allows you to write custom SQL and specify how to map the results into Java objects. With iBatis it’s easier to make use of views and stored procedures than most other full-blown ORM solutions.

  5. Harald says:

    May I introduce the Tentackle framework, which — among other things — addresses this issue. It provides a solution somewhat beyond the beaten path but nevertheless a very efficient one:
    The ORM is handled by a lightweight layer on top of JDBC. The “configuration” is achieved by overriding methods (hence not POJO).
    However, you don’t need to override manually, because the JDBC-code is generated from a model.
    There is also a tutorial:
    http://www.tentackle.org/html/en/tutorial.html

  6. Bernhard Glomann says:

    Thank you all for your comments.

    @Kasper, @Harald:
    Thanks for pointing to these interesting projects. I’ll be sure to check them out.

    @Jason:
    Thanks for the hints. You can tell I haven’t yet explored the full potential of EoD SQL. It’s great that the developer can choose whether checked or unchecked exceptions should be used (since both approaches have many supporters). I just changed my
    example to declare SQLException, because I actually like checked exceptions.

    @Cease, @Lars:
    I agree, Spring JDBC and iBatis would have been worth mentioning in this context. But I wanted to focus on recent and lesser known projects, and I am particularly interested in internal DSLs (like JEQUEL) that allow building SQL statements with compile-time checking and IDE support, rather than writing strings that get interpreted at runtime. Of course, EoD SQL doesn’t really fit this description, but at least it keeps the SQL strings neatly separated from the Java code that uses them, and it should be possible to write a custom annotation processor that would check the SQL syntax at compile time.

  7. […] — 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 […]

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: