BT

Facilitating the Spread of Knowledge and Innovation in Professional Software Development

Write for InfoQ

Topics

Choose your language

InfoQ Homepage News Stephen Colebourne/OpenGamma Release ElSql V 1.1

Stephen Colebourne/OpenGamma Release ElSql V 1.1

Bookmarks

Stephen Colebourne has released v1.1 of ElSql, (sponsored by OpenGamma), a library and DSL for managing SQL in external files. Colebourne is well known for his work as the spec lead of Java Time, a cornerstone of last year's Java 8 release, and for his creation of the ubiquitous Joda Time and Joda Money API's.

InfoQ spoke to Mr. Colebourne about ElSql (pronounced "else-Q-L") and the latest release.

Infoq: What is ElSql?

Colebourne: ElSql is a simple library that manages SQL queries in external files with a sprinkling of DSL magic. It is intended to be used when an ORM is not being used.

Infoq: What problem does it solve?

Colebourne: The main techniques for accessing data in a relational database are:

  • An Object Relational Mapper (ORM) framework, such as JPA or Hibernate.
  • Appending Strings, such as sql = "SELECT foo " + "FROM bar " + "WHERE ...".
  • Using a fluent API library, with methods like:
        select("foo").from("bar").where(..)
  • Reading in an external file, such as a properties file.

ElSql focusses on the last option, storing SQL in a file. It is based on the theory that SQL itself is not a particularly complex language, and that good developers should be comfortable writing it.

I've always found ORMs to be too much overhead for not enough value. And I shy away from hard coding the SQL queries in Java code (either using strings or a fluent DSL). Thus external SQL files appeal as the best way to structure and maintain the SQL in an application. It is also an effective format if your organisation has dedicated database administrators, as they can readily understand the ElSql files.

Infoq: What does the DSL cover?

Colebourne: This is best explained with an example file:

 -- a comment line describing the query
 @NAME(SelectBlogs)
  @PAGING(:paging_offset,:paging_fetch)
   SELECT @INCLUDE(CommonFields)
   FROM blogs
   WHERE id = :id
    @AND(:date)
     date > :date
    @AND(:active)
     active @LIKE :active
   ORDER BY title, author
  
 @NAME(CommonFields)
  title, author, content

As can be seen, the DSL adds very little to "raw" SQL. Anybody fluent in SQL will immediately have a good idea of what the query does. The DSL consists of "tags" starting with "@". Each tag performs a specific task, manipulating the "raw" SQL to make it more useful. The tags cover three key areas (all shown in the example above):

  • SQL 'LIKE' vs '=' for wildcards.
  • Dynamic construction of WHERE/AND clauses where only some things are being searched for.
  • Paging of results.

The @PAGING will add whatever code is necessary to perform paging on the configured database. The @AND block will only remain if the variable, such as :date actually exists. And the @LIKE handles different wildcard formats, changing 'LIKE' to an '=' sign if no wildcard is present. In the application, the SQL is simply obtained using the name referred to in @NAME:

String sql = bundle.getSql("SelectBlogs", args);

The result must then be processed using a tool that understands colon-prefixed variables, such as Spring, JDBI or fluent-jdbc.

Infoq: How does it handle different database vendors?

Colebourne: ElSql provides a level of abstraction above different databases. In many cases, the tags provide just enough of a hook that different SQL can be generated for each database. The library currently has rules for Postgres, MySQL, Oracle, HSQL, Vertica and SQLserver. For example, the command necessary to page results varies between databases, but using the @PAGING tag this is all transparent.

Should there be a need for specific SQL for a vendor, this is also permitted. The override only needs to exist for those @NAME tags that actually differ.

Infoq: When was the first release and what does the 1.1 release add?

Colebourne: The first public release was v0.8 in October 2013 with a v1.0 in December 2014. v1.1 adds some features requested through community feedback, including support for literals in the paging and offset/fetch tags.

Infoq: How is this different from Spring JDBC?

Colebourne: ElSql is designed to work well with Spring's JdbcTemplate. But it also works well with fluent-jdbc and JDBI. Note that ElSql has no code for parsing result sets - it is simply a tool for generating SQL.

Infoq: Your java.time work was a cornerstone of Java 8. Are you intending to have ElSql introduced to a future version of the Java core libraries?

Colebourne: While this could be added to the JDK (it is relatively small) I think that it is probably best remaining as an external library. Not everything has to be in the JDK. If anything, it would be more likely to go in Java EE, but even that seems unlikely.

Infoq: What is on the horizon for ElSql?

Colebourne: The project has already been used in production and meets its original goals. As such, the project has no real need to grow. Projects like JDBI and fluent-jdbc cover the rest of the tasks necessary to escape ORMs.

New releases will occur based on community feedback. For example, adding support for more database vendors.

Infoq: This seems a bit out of OpenGamma's core financial product line; what is their business model with relation to open source and EiSql?

Colebourne: OpenGamma is a company with open source at its heart. We've been producing open source software for the finance industry for five years now, and which has included ElSql for at least two years. And our next generation toolkit, Strata, is already open source. The company makes money from products built on the core open source codebase, such as our offering that calculates clearing house margins.

Infoq: Do you have any other interesting projects you'd like to share with our readers?

Colebourne: OpenGamma also supports my work on Joda-Beans and Joda-Convert, which bring C# style properties to Java. If you've ever cursed writing getters and setters, take a look!

 

Rate this Article

Adoption
Style

BT