What is a good design for a query "layer" for Java JPA

Question

In JPA the Entities are nice annotated Plain Old Java Objects. But I have not found a good way to interact with them and the database.

In my current app, my basic design is always to have a sequence based id as primary key so I usually have to look up entities by other properties than PK.

And for each Entity I have a stateless EJB of

@Stateless
public class MyEntApiBean implements MyEntApi {


@PersistenceContext(unitName = "xxx") @Inject EntityManager entityManager;

with query methods that all are some variation

/**
 * @return A List of all MyEnts that have some property
 * @param someProp some property
 */
public List<MyEnt> getAllMyEntsFromProp(final String someProp) {

 try {
  final Query query = entityManager.createQuery("select me from MyEnt me where me.someProp = :someProp");
  query.setParameter("someProp", someProp);
  return query.getResultList();
 } catch(final NoResultException nre) {
  log.warn("No MyEnts found");
 }
 return new ArrayList<MyEnt>();
}

So:

1. I really hate having these methods in an EJB because they seem to belong with the entities themselves, and the EJB local interfaces annoy the crap out of me.

2. I hate the duplication that I have in each method with "try, createQuery, getResultList,catch, log, return" (mostly a consequence of no closures or "with statement" or somesuch in Java).

Does anyone have a suggestion for a better way to interact with the Entities and Database that addresses one or both of my issues?

I am currently thinking of doing some base methods with generics and reflection to get some generic query methods to reduce the duplication (issue 2) (I will put a prototype up for review later).

Thanks, Anders

Answer 1

Try Seam. The Query Objects do most of the work for you, and they're easily extendable. Or, you could always implement a similar pattern.

In general, Seam does a lot of useful stuff to bridge the gap between JPA and you view and business layers. You don't have to use JSF for Seam to be useful.

Answer 2

If you do a lot of textual searches, maybe you should also consider some indexing framework like Compass.
I don't know if it suits your application, but if so it can both improve code design and performance.

Answer 3

I am actually using Seam. And the Query object suggestion lead me on to find Hibernates Criteria queries (Query By Example) functionality. That seems very close to what I was looking for.

Maybe in a base class, and with a dash of generics....?

Answer 4

You're being unnecessarily verbose. For one thing, getResultList() doesn't throw an exception when no rows are returned (at least not in Eclipse or Toplink--I can't imagine another provider being any different). getSingleResult() does, getResultList() doesn't. Also, you can use the builder pattern so:

@SuppressWarnings("unchecked")
public List<MyEnt> getAllMyEntsFromProp(final String someProp) {
  return entityManager.createQuery("select me from MyEnt me where me.someProp = :someProp")
    .setParameter("someProp", someProp);
    .getResultList();
}

should be sufficient to return a List of results if there are any or an empty List if there are none. Two things to note:

1. @SuppressWarnings("unchecked") is unnecessary but it gets rid of an otherwise unavoidable warning when casting the non-generic List result from getResultList() to a generic List; and

2. It's probably worth replacing the createQuery() call with a @NamedQuery on MyEnt (typically). For one thing, this will enable deploy-time validation and other useful things.

Its reasonably concise and complete.

Answer 5

Moin!

Here is my version for single results (I use it in my desktop JPA applications with TopLink essentials):

public class JPA {
  @SuppressWarnings ("unchecked")
  public static <T> T querySingle(
      EntityManager em, 
      Class<T> clazz, 
      String namedQuery, 
      Pair... params) 
  {
    Query q = em.createNamedQuery(namedQuery);
    for (Pair pair : params) {
      q.setParameter(pair.key, pair.value);      
    }
    List<T> result = q.getResultList();
    if ( result.size() == 0 ) {
      return null;
    }
    if ( result.size() == 1 ) {
      return result.get(0);
    }
    throw new 
      IllegalStateException(
        "To many result rows for query: " 
         + namedQuery 
         + " where " 
         + Arrays.toString(params));
  }

  public static class Pair {
    String key;
    Object value;

    public static Pair param (String key, Object value) {
      return new Pair (key, value);
    }

    public Pair (String key, Object value) {
      this.key = key;
      this.value = value;
    }

    @Override
    public String toString() {
      return key + "=" + value;
    }
  }
}

And the usage:

import static org.sepix.JPA.*;
...

String id = ...
Customer customer = querySingle (em, Customer.class, 
                      "Customer.findByID", Pair.param ("id", id));

or:

String inquiryID = ...
Boolean current = Boolean.TRUE;
Inquiry inq = querySingle (em, Inquiry.class, 
                      "Inquiry.findCurrent", 
                      Pair.param ("inquiry", inquiryID),
                      Pair.param ("current", current));

best regards, josh.

Answer 6

I prefer using Spring's JpaDaoSupport, which helps to deal with JPA. A good example is here http://github.com/rafalrusin/jpaqb/blob/master/src/test/java/jpaqb/CarDao.java.

A good separation of logic is to have a DAO class (Data Access Object) and DTO (Data Transfer Object). DAO typically contains all required queries and DTOs are entities with fields.