Wednesday, August 1, 2007

JPA NamedQueries and JDBC 4.0

In one project doing a migration from EJB 2.0 to EJB 3, I found this:
@Entity(name = "Action")
@NamedQuery(name = "Action.findAll", query = "SELECT o FROM Action o"),
@NamedQuery(name = "Action.findByExtCode", query = "SELECT o FROM Action o WHERE o.externalCode = ?1"),
@NamedQuery(name = "Action.findByDescription", query = "SELECT o FROM Action o WHERE o.description = ?1"),
@NamedQuery(name = "Action.findManualActions", query = "SELECT o FROM Action o WHERE o.manual=?1"),
@NamedQuery(name = "Action.findSelectedActions", query = "SELECT o FROM Action o WHERE <> 3"),
@NamedQuery(name = "Action.findFlowActions", query = "SELECT o FROM Action o WHERE"),
@NamedQuery(name = "Action.findByActionFlowId", query = "SELECT o FROM Action o JOIN o.actionFlows af WHERE = ?1")
The code looks like this, because when doing named queries in JPA the name need to be unique for the WHOLE persistence unit. So, we agreed about the naming convention "[entity name].[finder name]" for the name of the query.
It is actually quite nicer an more manageable than EJBQL in xml files, but still there is quite a bunch of copy/paste, String that are not constants, and the usage of named queries is here more problematic than EJB 2.0 home interfaces.
The usage looks like:
Query namedQuery = em.createNamedQuery("Action.findByExtCode");
namedQuery.setParameter(1, "001");
ActionBean actionBean = (ActionBean) namedQuery.getSingleResult();
And this is for only one parameter...

The possible code errors (due to lack of static typing) we get here are:
  1. Errors on the string name for the namedQuery
  2. Errors on the parameter position (the named queries annotation is in the model not close to the business logic executing queries)
  3. Errors in Casting
So, when looking at this, I thought about JDBC 4.0 (jsr 221 chapter 20 of the spec) and finally managed to create a nice dynamic proxy doing the work for JPA.
It is quite clear looking at the code above, a JPA named query can be defined as an interface method. It has:
  • a name (entityName + methodName),
  • a list of parameters (ordered or named),
  • and a result (list or single).
So, with the dynamic proxy the usage code looks like:
ActionQuery actionQuery = NamedQueriesFactory.getQueryProxy(ActionQuery.class, em);
ActionBean action = actionQuery.findByExtCode("001");
And the Queries interface:
@JpaQueriesInterface(prefix = "Action")
public interface ActionQuery {
public Collection<ActionBean> findAll();
public ActionBean findByExtCode(String extCode);
public ActionBean findByDescription(@JpaParamName("description")String description);
public Collection<ActionBean> findManualActions(boolean manual);
public Collection<ActionBean> findSelectedActions();
public Collection<ActionBean> findFlowActions();
public Collection<ActionBean> findByFlowActionId(long flowActionId);
Which gets all the advantage of strong Java typing.
So, the code of my small running example is here: and it's using maven of course...
Now, the next step is to use the Annotated Query Interface has NamedQuery provider so it will really look like JDBC 4.0.


Anonymous said...

I haven't finished reading through yet, but point #2 on the parameters can be avoided by having named parameters in the query e.g.

SELECT o FROM Action o WHERE o.externalCode = :externalCode

and then query.setParameter("externalCode", "001");

Anonymous said...

What I normally do is to add public static Query createQueryWhereXXX(Entity Manager em, String xxxValue, ...); to the entity itself. That way the producer and consumer of NamedQueries remains within the same class.

Noah Campbell said...

Small suggestion.

In your proxy, you might want to cache your work since the definition of your interface isn't likely to change during the running of your code.

Frederic Simon said...

@Anonymous: About static method in Bean.
I really like this solution too, and maintenance and mangement is quite nice and easy. Like you say, what's important is to make the SQL query close to the java code activating it.
The issues I have with it are:
- Static is less OO than interfaces (but that really kind of annoying academic argument)
- You still have 3 times the same name of the query, and you cannot inforce the naming convention [entity name].[query name]
- Does not remove the copy paste of execution of the name query.

Anonymous said...

Concerning "Errors on the string name for the namedQuery": Why not use constants? E.g.

@NamedQuery(name = Action.Q_FIND_BY_EXT_CODE, query = "SELECT o FROM Action o")
public class Action{
public static final String Q_FIND_BY_EXT_CODE = "Action.findAll";

Ollie said...

Hey Fred,

just rediscovered this blog post via twitter :). You told me about your approach o the Roundup already. Just wanted to leave a note that the OpenSource Hades library (we were chatting about this at the Roundup, too) is still growing that approaches the same issues by introducing a generic DAO interface. This allows defining and executing queries in a very sophisticated manner. See an explaining DAO interface from the Hades sample app here

The actual project home is here:

Hope to see you at one of the upcoming conferences or at least at the Roundup next year :).