JEQUEL
Java Embedded QUEry Language
A Domain Specific Language for the Structured Query Language (SQL) embedded in Java
How it all began
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());
}
Simple Exampe with Execution, Parameters and BeanRowMapper
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());
} Main Points (also see Features):
IDE Support
- Syntax highlighting
- 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
Expressions
- build SQL expressions with statically typed java objects and methods
- (named) parameter (prep.statement) integration
- dynamic extension of queries
- query(fragment) repositories
- rendering of syntax tree to SQL
- 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 (ant task), 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)
Frameworks Jequel uses and supports:
- JUnit the first java xUnit testing framework
- Spring Framework the great framework integration layer and IOC container
- Schema Crawler allows you to retrieve all database metadata as convenient POJOs
- HSQLDB In memory database used for unit testing and examples
- 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