Skip to content

User guide

Jump to bottom
Stephen Colebourne edited this page Jun 26, 2020 · 17 revisions

ElSql is a simple library, deliberately so. Many other solutions add far more techniques to support interoperation between Java and the SQL database. This solution simply separates the SQL, as an SQL-like DSL, into an external file. Basic tools then link back to Java. Variables may be passed in using the SqlParams interface. Optional integration with Spring is also available.

The SQL-like DSL contains tags and SQL. In fact it can contain any text outside the tags, as the SQL itself is not validated.

DSL overview

The DSL is deliberately simple. It is parsed in a line-by-line basis - no significant element to the parser may wrap over a line. The DSL is whitespace aware - two spaces are used to create blocks (instead of curly braces). In combination, this tends to result in readable, if slightly spread out, SQL.

The DSL consists of named snippets. A snippet may include other snippets allowing a degree of re-use. An application, wishing to perform a query or update, asks for a named snippet supplying the set of variables its going to send to JDBC. The ElSql classes lookup the snippet, resolve the SQL using the specified variables, and return it to the application that then uses Spring to resolve the variables themselves into the final SQL ready for the database call.

Variables

A variable consists of a colon followed by the variable name. For example, this is a variable named foo.

 :foo

From v1.2, there are two other formats for variables. These should only be used in special cases, such as the Apache Camel integration.

 :{foo}    -- variable name "foo"
 :${foo}   -- variable name "${foo}"

Tags

The tags cover the common awkward cases when building up SQL queries from Java. This includes dynamic generation of WHERE clauses, exact or wildcard matching and paging. Beyond this, there is little if any support - adding more tags complicates the DSL for everyone.

Variables are passed into the DSL using either the SqlParams interface or Spring's SqlParameterSource. Variables are always referred to using a colon prefix within the elsql file.

These are the tags:

@NAME(name)
The name tag creates a named block which can be referred to from the application or another part of the elsql file. The tag must be on a line by itself. This is the only permitted tag at the top level.

 @NAME(SearchByName)
   SELECT *
   FROM foo
   WHERE name = :name

When the ElSql bundle is queried for "SearchByName", the following is returned:

 SELECT * FROM foo WHERE name = :name

Note that the :name is not replaced, as it is intended to let Spring do that.

@INCLUDE(nameOrVariable)
The include tag includes the contents of a named block. If the parameter is a variable (prefixed by colon) then the name of the named block is looked up from the variable. The tag may be embedded in the middle of a line.

 @NAME(SearchByName)
   SELECT @INCLUDE(Rows)
   FROM foo
   WHERE name = :name

 @NAME(Rows)
   name, kind, value

When the ElSql bundle is queried for "SearchByName", the following is returned:

 SELECT name, kind, value FROM foo WHERE name = :name

@VALUE(variable)
The value tag simply outputs the value of the variable. The tag may be embedded in the middle of a line.

 @NAME(SearchByName)
   SELECT surname, forename
   FROM @VALUE(:table)
   WHERE name = :name

When the ElSql bundle is queried for "SearchByName" with the table variable bound to mytable, the following is returned:

 SELECT surname, forename FROM mytable WHERE name = :name

@WHERE
The where tag works together with the and/or tags to build dynamic searches. The tag will output an SQL WHERE, but only if there is at least some content output from the block. Normally, the where tag is not needed, as there is typically always one active where clause. The where tag must be on a line by itself.

@AND(expression), @OR(expression)
These tags are equivalent and output SQL AND or OR. The block that the tag contains is only output if the expression is true. The output SQL will avoid outputting the AND or OR if it immediately follows a WHERE. The and/or tag must be on a line by itself.

The expression is evaluated as follows. If the variable does not exist, then the result is false. Otherwise, if the expression is (:foo) and foo is a boolean, then the result is the boolean value. Otherwise, if the expression is (:foo) and foo is not a boolean, then the result is true. Otherwise, if the expression is (:foo = bar) then the result is true if the variable equals "bar" ignoring case.

 @NAME(Search)
   SELECT *
   FROM foo
   @WHERE
     @AND(:name)
       name = :name
     @AND(:kind)
       kind = :kind

When the ElSql bundle is queried for "Search" with the both the name and kind variables unbound, the following is returned:

 SELECT * FROM foo

When the ElSql bundle is queried for "Search" with the name variable bound and kind unbound, the following is returned:

 SELECT * FROM foo WHERE name = :name

When the ElSql bundle is queried for "Search" with the name and kind variables bound, the following is returned:

 SELECT * FROM foo WHERE name = :name AND kind = :kind

@IF(expression)
This tag performs a simple "if statement", as it might in Java. See above for how the expression is evaluated.

@LIKE sqlWithVariable
@LIKE(variable)
The like tag adds either an SQL = or an SQL LIKE based on the specified variable. If the tag has no variable in brackets, then the text between the like tag and the end of the line is parsed for a variable. This tag can differ by database, so the actual SQL is generated by the configuration class.

 @NAME(Search)
   SELECT *
   FROM foo
   WHERE name @LIKE :name
     AND UPPER(type) @LIKE UPPER(:type)

@ENDLIKE
The end-like tag is used on rare occasions to scope the end of the like tag. Normally, the SQL should be written such that the end of the like tag is the end of the line.

@PAGING(offsetVariable,fetchVariable)
The paging tag adds the SQL code to page the results of a search. These can differ by database, so the actual SQL is generated by the configuration class. The tag bases its actions on the specified integer variables which should begin with a colon. Instead of using variables, an integer literal can be used in v1.1. This replaces the OFFSETFETCH/FETCH tags in most situations as it enables window functions to be used where necessary.

 @NAME(Search)
   @PAGING(:rows_to_offset,:rows_to_fetch)
     SELECT *
     FROM foo
     WHERE name = 'bar'
     ORDER BY date

@OFFSETFETCH
@OFFSETFETCH(offsetVariable,fetchVariable)
The offset-fetch tag adds the SQL OFFSET and FETCH clauses for paging results. These can differ by database, so the actual SQL is generated by the configuration class. The tag bases its actions on the specified integer variables which should begin with a colon. Instead of using variables, an integer literal can be used in v1.1. The names "paging_offset" and "paging_fetch" are used if the variables are not specified.

 @NAME(Search)
   SELECT *
   FROM foo
   WHERE name = 'bar'
   ORDER BY date
   @OFFSETFETCH(:rows_to_offset,:rows_to_fetch)

@FETCH(fetchVariable)
@FETCH(numberOfRows)
The fetch tag adds the SQL FETCH clause. It works as per the offset-fetch tag, sometimes known as LIMIT.

 @NAME(SearchVariableRowCount)
   SELECT *
   FROM foo
   WHERE name = 'bar'
   ORDER BY date
   @FETCH(:num_rows)

 @NAME(SearchFixedRowCount)
   SELECT *
   FROM foo
   WHERE name = 'bar'
   ORDER BY date
   @FETCH(20)

@LOOP(sizeVariable)
@LOOPINDEX
@LOOPJOIN
In general, ElSql encourages compelx SQL to be coded in Java. However there can be cases where it can be beneficial to have a simple looping facility in ElSql itself. Bear in mind that it is not advisable to try and push the looping support too far!

The @LOOP tag introuces the loop and has a single variable defining the loop size, such as @LOOP(:size). The loop variable may also be a literal integer from v1.2, such as @LOOP(6). Within the loop, use @LOOPINDEX to output a zero-based number matching the index around the loop. At the end of the loop indented block, optionally provide an @LOOPJOIN followed by text to join each item in the loop. The @LOOPINDEX2 and @LOOPINDEX3 tags can be used for nested loops, although we do not recommend this level of complexity in elsql files. An example explains it well:

 @NAME(Test1)
   SELECT * FROM foo WHERE
   @LOOP(:size)
     (a = :a@LOOPINDEX AND b = :b@LOOPINDEX)
     @LOOPJOIN OR

could be used to output:

 SELECT * FROM foo WHERE (a = :a0 AND b = :b0) OR (a = :a1 AND b = :b1)

Home

Clone this wiki locally