Come On Let's Twist Again Like We Did Last Summer Yea, Let's Twist Again Like We Did Last Year (Chubby Checker - Let's Twist Again)

JPA 2 Criteria API allows criteria queries to be constructed in a strongly-typed manner, using meta-model objects to provide type safety. This is a useful feature because when a change occurs in database, for example a rename of a field, your queries will not compile, and you will see the problem in compilation time instead of running time.

Yes I have already talked about in my previous post http://alexsotob.blogspot.com/2011/01/deep-inside-you-cry-cry-cry-dont-let.html but there are an API that do the same as Criteria API, and is called QueryDSL. QueryDSL is a framework which enables the construction of type-safe SQL-like queries for multiple back-ends including JPA, JDO and SQL in Java. Working with QueryDSL and JPA is like working with HibernateMetamodel Generator because QueryDSL has an Annotation Processor that generates Meta-model information from JPA 2 annotated classes. So I suppose you are wondering, why I should use QueryDSL instead of JPA 2 Criteria API? In its site there are interesting posts about that http://source.mysema.com/forum/mvnforum/viewthread_thread,49.

What makes really different QueryDSL from JPA2 Criteria API, is that QueryDSL also works with JDBC applications. Yes you read it right, JDBC applications can take benefit from QueryDSL, and instead of creating queries as plain text, they can be constructed in a strongly-typed manner too.

Some of advantages of using QueryDSL in JDBC are:

• Code Completion in IDE.
• Almost no syntactically invalid queries allowed (type-safe on all levels).
• Domain types and properties can be referenced safely (no Strings involved!).
• Incremental query definition is easier.

If you visit its webpage you could see one example using QueryDSL with JPA, but also one for JDBC. What I am going to explain is how to use QueryDSL with Spring Jdbc Template. Most of us, when we develop a Spring application and want to use JDBC, we use JdbcTemplate class or one of its extensions.  For that reason I will explain how I use JdbcTemplate with QueryDSL.

First of all I implement an extension of JdbcTemplate class. I have called QueryDSLJdbcTemplate. This class has a simple method called queryForList(SQLQuery sqlQuery, RowMapper rowMapper, Expression... expressions) and has three parameters, the first one is a SQLQuery object, this is the main class where you define the query you want to execute (like JPA 2 Criteria), the next one is a RowMapper object as most of JdbcTemplate methods use, and the third one is an array of Expressions; these expressions represents each database fields we want to return as result, like id, name, age, ....

Implementation of this method:

At line 4, we are attaching a connection to given SQLQuery.

At line 6, the query is executed, and result set is returned.

At line 10, the result set is transformed to a list of required objects, using RowMapper.

Next step is creating a query. In this example I will use an Employee class that have two attributes, an id and a name. The query is finding an employee by name. So EmployeeDAO looks like:

Using JPA you specify which database engine is used injecting Database dialect. In our case it is a JDBC application so we should configure SQLQuery object with database engine used, so QueryDSL generates query syntax correctly. This is done with line 1 and 2.

Line 4 is an auto-generated class, similar to classes generated by Hibernate Annotation Processor. It contains all meta-information of an entity.

Line 5 is the query construction. See that we are not creating an SQL string (SELECT * FROM Employee as emp WHERE emp.name=?), but we are creating in a programmatic way. Look this line qEmployee.name.eq(name) because if we change field name to fullName, our code will not compile (qEmployee.name attribute does not exist now) meanwhile SQL string approach will compile, but crashing in runtime.

Line 7 is a simple RowMapper, and there the parameter name is also not specified as string but as an object, where native queries were rs.getString("name") and if name does not exists an SQLExpcetion was thrown, now a compilation error would be thrown too.

After RowMapper definition, we are executing the find method. The last two attributes are the fields we are looking for. Between SQL query and QueryDSL expression you can identify the FROM clause in both and the WHERE clause in both, but not the * of SELECT. This is the place where this information is passed.

Last method is for extracting real column name to result set.

Of course this is a basic implementation, for example Dialect should be injected rather than created each time, EmployeeRowMapper could not be an inner class, and getParameterName should be in a Util class, but because of clarity, all this code has been implemented in the same class.

And I suppose you are still wondering what is QEmployee class. Employee class is a simple DTO object. QEmployee is where all meta-information of Employee class is stored. In JPA usually JPA Providers have an Annotation Provider that read annotated model classes and generates Meta-model classes. In JDBC, there are no annotated model classes, but an alternative mechanism is provided for generating these classes. QueryDSL provides an ANT-task, a Maven-Task, and a Plain API. I will show you plain API configuration and same values should be provided for ANT and Maven tasks.

You must specify as input parameters, a connection to database and which schema contains business model. And as output parameters, which package should be generated meta-model classes, and root source code directory. The best approach for generating these classes is using Maven, so before compile goal is executed, all meta-data model is generated.

And Spring Application Context class looks as simple as:

Now you can take all benefits from using DSL for queries in JDBC applications.