SQL Zone is brought to you in partnership with:

Bernhard has posted 1 posts at DZone. You can read more from them at their website. View Full User Profile

Lightweight SQL Interfaces for Java

06.13.2008
| 16436 views |
  • submit to reddit

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!

Published at DZone with permission of its author, Bernhard Glomann.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Tags:

Comments

Catalin Strimbei replied on Fri, 2008/06/13 - 2:48am

What about the old projects of SQLJ (http://en.wikipedia.org/wiki/SQLJ) ?

Richard D. Jackson replied on Fri, 2008/06/13 - 3:59am

Personaly I really like DBUtils (http://commons.apache.org/dbutils/). I've been using it for a few years now. True like the other tools you have listed it does not do compile time checking of your SQL paremiters. Also I really much prefer to write my querys in SQL verses some DSL that tries to abstract SQL.

Thomas Mueller replied on Fri, 2008/06/13 - 4:17am

I recently started to work on 'my own' DLINQ for Java. Some sample code:

db = Db.open("jdbc:h2:mem:", "sa", "sa");
Product p = db.alias(Product.class);
List expensiveInStockProducts =
db.from(p).
where(p.unitsInStock).isBigger(0).
and(p.unitPrice).isBigger(3.0).
orderBy(p.productId).select();

public class Product implements Table {
public Integer productId;
public String productName;
public String category;
public Double unitPrice;
public Integer unitsInStock;

public void define() {
tableName("Product");
primaryKey(productId);
index(productName, category);
}
}

public static class CustOrder {
public String customerId;
public Integer orderId;
public BigDecimal total;
}

List orders =
db.from(c).
innerJoin(o).on(c.customerId).is(o.customerId).
where(o.total).isSmaller(new BigDecimal("500.00")).
select(CustOrder.class, new CustOrder() {{
customerId = c.customerId;
orderId = o.orderId;
total = o.total;
}});

This part of the code already works. My plan is to first include it as a tool in my H2 Database Engine, and if people are interested start a spin-off project.

Thomas Mueller replied on Fri, 2008/06/13 - 4:29am in response to: Thomas Mueller

Correction to my previous post: some <> tags where eaten. Correct is:

List<Product> expensiveInStockProducts =
List<CustOrder> orders = 

This requires JDK 1.5.

Regards,
Thomas

Christian Ullenboom replied on Fri, 2008/06/13 - 6:26am

Springs JdbcTemplate and SimpleJdbcTemplate is very nifty. I especially like the Generics and varargs (Apache Commons did'nt depend on Java 5). (Unfortunately) it is very tightly connected to Spring; it would be nice if this would be an extra package but the dependency to the Spring Framework is very high.

Christian

Harald Krake replied on Fri, 2008/06/13 - 12:30pm

Code generation is an alternative to get working code without the need to invent some new shiny abstraction layer or even extend the Java language. JDBC-code in particular is an excellent candidate for generative programming because of its "boilerplateness". Generating JDBC-code boils down to astonishingly simple models and few but straightforward generators. I'm doing that for years and get roughly 99% of my applications' database-layer generated. I suspect that most developers don't use GP because they fear the complexity of tools (MDD) and the modeling in general. However -- as always -- you can do that the sophisticated or the simple way. They way I'm doing it is terribly simple:

The trick is to embed the generators (I call them "wurblets") directly within the source code by means of a so-called wurblet-anchor. Basically, the anchor connects the wurblet with the model and the generated code. An extra ant-target invokes the wurblets and inserts (or modifies) the code right in place. Whenever you change your model, add or modify a wurblet-anchor or change a wurblet, the source code changes as well. There is no template engine or complex modeling. If you feel that you need a method selectByBlah, simply insert a wurblet anchor and the JDBC-code will be there.

As I said, it works pretty good for me and I think it's an alternative to abstraction layers and special languages encapsulating SQL.

Btw., dynamic language frameworks like Rails also implement their persistence layer via generative programming. Rails just does it at runtime "behind the scenes", but in principle it's the same.

Harald

 

For those who are interested in the code generator: http://www.wurbelizer.org

Honey Monster replied on Fri, 2008/06/13 - 2:31pm

A common misconception is that LINQ to SQL is a way to build or "inline" queries for a database (SQL Server). LINQ to SQL is actually a way to build a queryable model layer. This queryable model layer has a mapping specification just like Hibernate. When an entity collection is queried the model layer uses the mapping specification to generate the query. The returned objects are instances of model classes which can support identity mapping, object tracking, updating etc. In that sense LINQ to SQL is more comparable to Hibernate (albeit more elegant) than it is to query builders.

Bernhard Glomann replied on Sat, 2008/06/14 - 3:10pm in response to: Thomas Mueller

Thomas,

Your code example looks very promising. I just don't quite understand the last query in your example. I'm assuming that c and o are instances of classes representing customer and order tables (in the same way how the Product class apparently represents a product table), and you are doing a join to extract some information from both of these tables into a list of CustOrder instances. What I'm not sure about is this part:

 new CustOrder() {{
customerId = c.customerId;
orderId = o.orderId;
total = o.total;
}}

You are basically assigning column values from the two tables to fields of the CustOrder class. But wouldn't this have to be done for every row? As far as I know, the block in double braces is an instance initializer, which is only executed once during instance creation (it's not like a closure that can be invoked multiple times). Oh wait, maybe you are using reflection to create more instances of this anonymous class? I would certainly be interested in learning more about this tool.

Regards,

Bernhard

Thomas Mueller replied on Sun, 2008/06/15 - 11:28am

Your code example looks very promising. c and o are instances of classes representing customer and order tables. Maybe you are using reflection to create more instances of this anonymous class?

Thanks. Yes, c is defined by: Customer c = db.alias(Customer.class). And yes again, more instances of CustOrder are created using reflection. What I didn't like was the repetition of 'CustOrder' at select(CustOrder.class, new CustOrder() {{..}} - and just today I found a way to simplify it to select(new CustOrder() {{..}}. I will try to wrap up a first version and include it in the next release of H2 so it will be available in about one week.

Romen Law replied on Mon, 2008/06/16 - 2:06am

thanks for the good info. I quite like LINQ, but the problem with these SQL-like languages (MS Entity SQL, HQL, EJB QL, etc.) is that tools for executing these QLs are not as readily available as SQL. I prefer testing my queries before putting them into the code, I use tools like Toad, DB Visualizer or even inside the IDE for such tests.

cheers

romen

Timo Westkämper replied on Mon, 2010/01/11 - 2:57pm

Consider using Querydsl : http://source.mysema.com/display/querydsl/Querydsl

 Querydsl supports JPA/Hibernate, JDO, SQL and Collections.

Lukas Eder replied on Thu, 2010/12/23 - 1:44pm

I have recently published an article about my new DSL called jOOQ (for Java Object Oriented Querying). Here is the full article on dzone:
http://java.dzone.com/announcements/simple-and-intuitive-approach

As a teaser, please consider how you can express this SQL:

-- Select authors with books that are sold out
SELECT *
FROM T_AUTHOR
WHERE T_AUTHOR.ID IN (SELECT DISTINCT T_BOOK.AUTHOR_ID
FROM T_BOOK
WHERE T_BOOK.STATUS = 'SOLD OUT');

in jOOQ's fluent DSL:

create.select(T_AUTHOR)
.where(TAuthor.ID.in(create.selectDistinct(TBook.AUTHOR_ID) .from(T_BOOK) .where(TBook.STATUS.equal(TBookStatus.SOLD_OUT))));

jOOQ is specifically designed for a developer who:

  • interfaces Java with huge legacy databases.
  • knows SQL well and wants to use it extensively, but not with JDBC.
  • doesn't want to learn any new language (HQL, JPQL, etc)
  • doesn't want to spend one minute fine-tuning some sophisticated XML-configuration.
  • wants little abstraction over SQL, because his software is tightly coupled with his database. Something that I think the guys at Hibernate or JPA seem to have ignored.
  • needs a strong but light-weight library for database access. For instance to develop for mobile devices.

Read the full article on dzone:
http://java.dzone.com/announcements/simple-and-intuitive-approach

Yegor Bugayenko replied on Sun, 2012/05/20 - 9:19am

Take a look at JdbcSession from jcabi-jdbc (I'm a developer). It's a fluent wrapper around JDBC, simple and powerful.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.