ElSql is not an ORM or anything similar. Instead, it is a simple tool for managing SQL external to the application. Plus a touch of DSL goodness.
The development model that ElSql is targetted at is Spring-based, using
JdbcTemplate. You need to be comfortable writing SQL, as well as writing the code to convert data objects to and from the form needed for the database.
So what does ElSql do?
Primarily, ElSql defines a DSL file format and two public classes - a manager and a configuration class. The aim of the DSL elements in the file format is to provide some basic tags to handle common difficulties encountered when building up SQL dynamically across a variety of databases. This external file-based approach has the major benefit of allowing DBAs to discuss the SQL more easily with the development team.
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 aim is that at first glance it should be relatively clear what the file represents and how it works. And yes, that is significant whitespace indentation being used for defining the blocks.
The colon prefixes are variables, as defined by the Spring
The tags, starting with @, cover three key areas:
- SQL LIKE vs = for wildcards
- dynamic construction of WHERE/AND clauses where only some things are being searched for
- paging of results
The second and third of these are shown in the example above. 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
The named block, @NAME, is how the application refers to the SQL:
String sql = bundle.getSql("SelectBlogs", args);
args is the
SqlParameterSource that has to be created and passed to
NamedParameterJdbcOperations anyway. (Passing in the arguments allows tags like @AND to work, by querying to see if the variable is present or not)
These three problems - wildcards, WHERE/AND and paging - have always been the three biggest issues faced when building SQL dynamically, and the tags neatly solve the problems. And of course there is now a great file we can use to talk to DBAs.
ElSql is a library I've wanted to write for a long time and now it finally exists. Its not for everyone - you need to be able to write SQL for starters! The DSL elements are deliberately simple and limited, providing just enough to tackle the common cases hit when dynamically building SQL.