Log in

ElSql - a Java Library for Managing SQL

15 October 2012 By

OpenGamma has just released a standalone Java library, ElSql (pronounced else-q-el). It provides a simple tool for managing SQL external to the application with a touch of DSL goodness. It is released under the Apache License v2 and intended for use with Spring SqlParameterSource.

ElSql - Managing SQL in Java

OpenGamma is an open source company, and as such we occasionally provide additional tools and libraries of general use to the Java developer community. Previously, we have released RouteMap, a JavaScript library for mapping URLs to methods and Fudge, a Java library for binary messaging. In this case, it's a Java library for managing SQL.

Managing SQL in Java can sometimes feel like a solved, or irrelevant, problem. The "cool" applications are all using NoSQL and avoiding SQL altogether (and facing different issues as a result). But, there is no doubt that a large body of applications still want and need the longevity and strengths of relational DBs.

The need to interact between Java and a relational SQL database has driven developers to create many solutions. The most commonly used include JPA, Hibernate, Spring templates and raw JDBC. Chief amongst the interesting ones are the Object Relational Mappers. These try to bridge the difference between two models - object-based and set-based. In doing so, most end up adding in an additional complex abstraction. The mismatch between the two worlds has most famously been described as the Vietnam of Computer Science.

At OpenGamma, we found that for most cases, the ORM added more complexity than it took away. As such, we chose to use Spring's JdbcTemplate to make calls using real SQL. SQL is a powerful language and it really needs to be used directly to get the most out of a relational database.

But there is then the problem of managing the SQL, especially as we are a platform capable of running on multiple underlying databases. There are three basic approaches to consider:

  • concatenate strings - "SELECT foo " + "FROM bar " + "WHERE ..."
  • use a fluent API library - methods like select("foo").from("bar").where(..)
  • read SQL from an external file, such as a properties file

Externalizing SQL has key benefits for the administrators (DBAs) whose primary language is SQL, not Java. Having SQL in a form close to SQL allows them to understand what each query is doing and suggest changes more easily if necessary. Storing the SQL in strings, or fluent libraries, locks the SQL away from the DBAs making it harder to have discussions and more difficult to change.

Thus, the ElSql library is an implementation providing support for externalizing SQL from the application. It is intended to work with Spring, especially theSqlParameterSource.

Abstracting common problems in querying a database - DSL tags to the rescue

But ElSql does just a little bit more than allow you to load SQL files from the class path. It also provides a small set of DSL tags that abstract some common problems in querying a database. The key problem areas it tackles are:

  • SQL LIKE vs = for wildcards
  • dynamic construction of WHERE/AND clauses where only some things are being searched for
  • paging of results

Here is an example elsql file:

 -- an example comment
 @NAME(SelectBlogs)
   @PAGING(:paging_offset,:paging_fetch)
     SELECT @INCLUDE(CommonFields)
     FROM blogs
     WHERE id = :id
       @AND(:date)
         date > :date
       @AND(:active)
         active = :active
     ORDER BY title, author
 @NAME(CommonFields)
   title, author, content

The idea is to be a simple DSL that adds just enough tags to solve the most common problems, not all problems. Note that all tags apart from @NAME are optional, so it can be used for straight SQL.

The file is structured into named blocks using the @NAME tag and significant whitespace indentation. The application, when it is ready to use some SQL, will load the named block and pass it, together with the Spring parameter structure, to the Spring JdbcTemplate.

As well as supporting the limited set of tags, there is also the ability to specify the type of database being used. A plugin configuration class allows the generated SQL to differ slightly by database. For example, paging on SQL server needs a different mechanism to that on Postgres, or indeed older MySql versions.

Thus, the @PAGING tag will wrap the block it refers to (significant whitespace indentation) in whatever the most appropriate paging technique is for the database, using the pageing_offset and paging_fetch variables passed in from the caller to choose the required page.

However, at OpenGamma we knew that this minor amount of control per database wouldn't necessarily be enough for our needs, where we might want to truly customize the SQL per database. As such, when an elsql file is loaded, the library also checks for an override file for the configured database type. Any named block in the override file overrides the named block with the same name in the standard file. Thus, we can (and do) have dedicated SQL for the more unusual databases, yet re-use as much as we can via the tags.

The final tag in the example is @AND. This only includes the block it refers to if the variable specified is present. So, if the parameters passed by the caller do not contain "date", then the whole date AND block will not be included.

These three problems - wildcards, WHERE/AND and paging - were the three big issues we faced when building SQL dynamically. By abstracting them out into simple tags along with the SQL itself, we greatly simplified the platform. And by extracting the SQL from the application we now have a great file we can use to talk to DBAs.

Summary

ElSql is a library that allows SQL to be stored and managed external to the Java application. In addition it provides a deliberately limited DSL that can optionally be used within the external SQL to support some of the most common problems in writing cross-platform database code.

See the project page on GitHub for more details and to fork it. Or download from maven com.opengamma/elsql. Let us know if you find it useful!

This is the developer blog of OpenGamma. For more posts by the OpenGamma team, check out our main blog.

About the Author

Stephen Colebourne

Stephen Colebourne is Engineering Lead, Platform at OpenGamma.

Follow us on Twitter