jueves, abril 07, 2011

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.

10 comentarios:

Timo Westkämper dijo...

Hi Alex. Thanks for Querydsl promo.

Your example can be expressed in simpler form like this :

QEmployee qEmployee = QEmployee.employee;
List emps =
.list(new QBean(
qEmployee.id, qEmployee.name));

This will create a Employee for a each row and populate it via set Bean accessors.

Timo Westkämper dijo...
Este comentario ha sido eliminado por el autor.
Timo Westkämper dijo...

Here is a better formatting of the example :

Alex dijo...

Hi Timo, thank for your comment I agree with you that in this particular case it is easiest using QBean, but because JdbcTemplate query for domain objects, works with RowMappers, RowCallbackHandlers and ResultSetExtractors, I preferred using same approach.

Timo Westkämper dijo...

Querydsl doesn't have a Spring JDBC dependency, that's why you need much more code than with the QBean approach, to do simple things.

Look at the FactoryExpression interface and its implementations. They give you lots of options for transforming results.

I also found a little bug in your code.

In your QueryDSLJdbcTemplate method implementation you close the ResultSet and Connection twice, both in the Exception handling and in finally.

Also you can simplify getParameterName to return path.getMetadata().getExpression().toString()

Alex dijo...

Hi Timo, I will take a look of that class. I know it seems that there is a bug in code, releasing connection in exception and in finally but the bug will appear if in both cases would not appear realeasConnection. The Release Connection early, to avoid potential connection pool deadlock.

Mark Pollack dijo...

Hi Alex,

Thanks also for showing this in use with Spring JDBC. I work at SpringSource and we are looking to do integrate QueryDSL into many areas of the Spring Data project. Great stuff Timo! At the moment there is QueryDSL+Spring integration for MongoDB and JPA, but JDBC is also on our TODO list.

Would you perhaps be interested in contributing to Spring JDBC project that would join up JdbcTemplate and QueryDSL?

Mark Pollack

Thomas Risberg dijo...

Hi Alex,

I liked your idea so I borrowed some of your implementation and expanded it a bit. It's now in the Spring Data JDBC Extensions project - http://bit.ly/hBFIvy

Also added support for querying with a QBean but it seemed not being able to map column names with underscores to my Java properties. Maybe there is a config option -- Timo, any suggestions?

Anyway, like Mark said, let us know if you want to collaborate further on the Spring support.


Thomas Risberg dijo...

Just a quick follow-up. I upgraded to QueryDSL 2.1.2 which has better QBean support for column names with underscores, so it now works nicely.


Alex dijo...

Yes of course I would like to collaborate with Spring people. Actually I am collaborating with JBehave people, developing Spring namespace support for JBehave, but I could manage for collaborating with you too.