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).

How it all began

• Generation during build process (ant task), so all code referencing outdated structures won't compile

How it all began

Main Points (also see Features):

IDE Support

Expressions

• build SQL expressions with statically typed java objects and methods
• (named) parameter (prep.statement) integration
• dynamic extension of queries
• query(fragment) repositories

• TODO: Query Validation

Execution

• execute using a fluent language
• parameter support (either supplied with query or added before execution)
• Spring JDBC API Support
• Spring Extensions for additional Callbacks

MetaData Generation

• Table classes generated from Database Metadata (including comments, relationship-references)
• Generation during build process, so all code referencing outdated structures won't compile
• Documentation of Tables and Fields using JavaDoc from Database comments
• Custom enhancers for Table Metadata before file creation possible
  • e.g. include information from ResourceBundles (Translations)

Simple Exampe with Execution, Parameters and BeanRowMapper

(:source lang=java :)

    interface ArticleBean {
        int getArticleNo();
        String getName();
    }

    public void testParameterExample() {
        final Sql sql = Select(ARTICLE.NAME, ARTICLE.ARTICLE_NO)
                .from(ARTICLE)
                .where(ARTICLE.OID.in(named("article_oid"))).toSql();
        assertEquals("select ARTICLE.NAME, ARTICLE.ARTICLE_NO from ARTICLE where ARTICLE.OID in (:article_oid)", sql.toString());

        final Collection<String> articleDesc = sql.executeOn(dataSource)
                .withParams("article_oid", Arrays.asList(10, 11, 12))
                .mapBeans(new BeanRowMapper<ArticleBean, String>() {
                    public String mapBean(final ArticleBean bean) {
                        return bean.getArticleNo() + "/" + bean.getName();
                    }
                });
        assertEquals(1, articleDesc.size());
        assertEquals("12345/Foobar", articleDesc.iterator().next());
    }

(:sourcend:)

Frameworks Jequel uses and supports:

Simple Example:

Advantages (also see Features):

• JUnit the first java xUnit testing framework

• HSQLDB In memory database used for unit testing and examples

Frameworks Jequel uses and supports:

• Spring Framework the great framework integration layer and IOC container
• Schema Crawler allows you to retrieve all database metadata as convenient POJOs
• Apache Ant the first standard java build tool
• Apache iBatis separating SQL to XML is one step in the right direction, I used their JPetStore database schema as example

Advantages (also see Features):

Java Embedded QUEry Language

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

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.

Java Enabled QUEry Language

!! Java Enabled QUEry Language

!! Java Enabled QUEry Language

• Build Javadoc HTML API Documentation for generated Tables

• Documentation of Tables and Fields using JavaDoc from Database comments

• e.g. include information from ResourceBundles (Translations)

• Code Completion for Keywords, Tables and Fields
• Refactoring participation of Tables and Fields, through the whole codebase (single source)
• On the fly spell checking and error highlighting
• Quick documentation Lookup
• Javadoc Structures for generated Tables
• Table declaration classes generated from Database Metadata
  • During build process, so all code referencing outdated structures won't compile
  • Documentation of Tables and Fields using JavaDoc from Database domments
  • Custom enhancers for Table Metadata before file creation possible
    • e.g. include information from Resourcebundles
  • create database schema from manually written Table declarations
  • migrate database schemas

• statements form expression tree
  • custom expressions possible for encapsulating subqueries, custom code, even with parameters
  • own keywords possible

• rendering of syntax tree to SQL
  • TODO: other target languages possible (ejbql, hibernateql, jdoql)

JEQUEL

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-String waiting to be executed to get a live.

I couldn't bear it any longer. So 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.

Simple Example:

(:source lang=java :)

    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());
    }

(:sourcend:)

Advantages (by now):

• Syntax highlighting
• Code Completion
• Refactoring participation of Tables and Fields
• On the fly spell checking and error