Neo JEQUEL

Java Embedded QUEry Language

A Domain Specific Language for the Structured Query Language (SQL) embedded in Java

Originally by Michael Hunger. Maintained and reviewed with Maven as build tool by Marian Zaplatynski.

Versioning

The old JEQUEL had a Subversion revisions only. Since Maven was introduced we need proper versioning. So the r120 from 2007 is turned into 1.2.0. So the first version of this Git repository is 1.2.1-SNAPSHOT.

Maven modules and dependencies

Coming soon. There are no Maven Central artifacts yet.

How it all began

(By Michael Hunger)

After dealing with DSL theoretically for a while, this is my second experiment with implementing an embedded DSL in Java.

When looking at the code after a big refactoring at work I could still see lots of SQL contained in Java Strings. Although already using Spring NamedParameterJdbcTemplate and so getting very readable prepared statements, containing them in String form is very stupid.

You won't get IDE support in writing it, you have no code completion, error reporting or refactoring. There is no documentation to quickly look up while writing it. It is not possible to catch syntax errors until the code is executed against the database (hopefully in an unit test).

The SQL statements are just dumb, meaningless Java Strings waiting to be executed to get a live.

I couldn't bear it any longer. So after discussing the idea with my co-workers when leaving I used the spare spare time during my day of this week (after my family went to bed) to start writing an embedded DSL in Java to support the creation of sql query expression tree objects which can be rendered (e.g.) to SQL. The next day I got enthusiastic and excited support and was urged to publish it. So, here it is. (Oct. 2007).

Simple Example:

public void testSimpleSql() {
     final SqlString sql =
             select(ARTICLE.OID)
                  .from(ARTICLE, ARTICLE_COLOR)
                  .where(ARTICLE.OID.eq(ARTICLE_COLOR.ARTICLE_OID)
                         .and(ARTICLE.ARTICLE_NO.is_not(NULL)));

     assertEquals("select ARTICLE.OID" +
                  " from ARTICLE, ARTICLE_COLOR" +
                  " where ARTICLE.OID = ARTICLE_COLOR.ARTICLE_OID" +
                  " and ARTICLE.ARTICLE_NO is not NULL", sql.toString());
}

Feature

Writing SQL

Syntax highlighting
Code completion for tables, fields and keywords and operations
Spell and error checking for tables, fields and keywords
Refactoring participation of tables and fields, through the whole codebase (single source)
On the fly spell checking and error highlighting
easy navigation to tables and fields
directly visible foreign key relations
deprecate tables and columns

Keywords by now

select, from, where, group by, having, order by
numeric, boolean, list expressions
Value, Prepared Statement, NamedParameterJdbcTemplate parameters
raw sql possible
min, max, avg, count
not, and, or
!=,=,<,<=,>,>=, like, between
is null, is not null
subselect, in, exists
+,-,*,/
aliases (table,field,expressions)

Use - Execution

Support for Prepared Statements, Springs NamedParameterJdbcTemplate
supply default parameters typesafe within statement
fluent language for statement execution
additional POJO or Method param based RowHandlers/Mappers SpringExtensions
Rendering to SQL 92 (TODO: Formatters for different target languages)

Build Process

Generate Java source file for schema meta data (tables, fields, relations,comments) from target database schema
Enhance Schema Metadata with Documentation from resourcebundles, glossaries, database comment columns, analyse table relationships
Code that does not comply with the generated Java code will not compile (database changes will appear as build errors)
Ant task for generation and enhancement
Single and Multi-File Schema generation

Reuse

keep often used sql fragments (whole selects or combinations of from, where, ...) in named and documented java classes
extract variable, method or constant refactorings for reused sql parts

Testing

write unit tests for SQL fragments
provide test parameters and expected results for fragments

Documentation

Quick documentation lookup for alle tables and fields when writing statements (^Q in IDEA)
Generate Javadoc from Schema Meta Data File(s)
Find (all) usages of tables and fields within the codebase using your IDE (shift+ctrl+g in eclipse and alt-f7 in idea)
deprecate tables and columns
use descriptive table and column names that are mapped to (inflexible?) existing database schema

How to build from sources

Apache Maven is used to build the project.

mvn clean install

Prerequisites:

Java 8
Maven 3.5+

Ordiel/neo-jequel