Complex Search Queries JPA

Question

In my Wicket+JPA/Hibernate+Spring project, much of the functionality is based around the Inbox page where, using many filtering options (not all of them have to be used), users can restrict the set of objects they want to work with. I was wondering what the best strategy to implement this filtering is? In the old version of this application, the search query was built concatenating strings containing SQL conditions. Recently I read about the new Criteria API JPA provides - would you recommend this over working with the search string? And how does this combine with the DAO layer - isn't building the search query using Criteria API in the business layer a breach in separation of layers?

Answer 1

For filtering queries like you describe I definitely recommend using the Hibernate or JPA criteria API because of the support for conditional queries. I usually just put the criteria construction code in my DAO's and pass all the required (possibly null) arguments there.

Here's an example DAO method from an example car-rental application using the Hibernate criteria API:

public List<VehicleRentalContract> list(Long contractID,
            String customerNameOrID, Date date,
            String vehicleDescriptionOrRegistration) {
        Criteria criteria = getSession().createCriteria(
                VehicleRentalContract.class);
        // contractID filter
        if (contractID != null && contractID != 0) {
            criteria.add(Restrictions.eq("id", contractID));
        }
        // customerNameOrID filter
        if (customerNameOrID != null && customerNameOrID.length() > 0) {
            try {
                Long customerID = Long.parseLong(customerNameOrID);
                criteria.add(Restrictions.eq("customer.id", customerID));
            } catch (NumberFormatException e) {
                // assume we have a customer name
                String customerNameQuery = "%" + customerNameOrID.trim() + "%";
                criteria.createAlias("customer", "customer").add(
                        Restrictions.or(Restrictions.like("customer.firstName",
                                customerNameQuery), Restrictions.like(
                                "customer.lastName", customerNameQuery)));
            }
        }
        // date filter
        if (date != null) {
            criteria.add(Restrictions.and(
                    Restrictions.le("rentalPeriod.startDate", date),
                    Restrictions.ge("rentalPeriod.endDate", date)));
        }

        // vehicleDescriptionOrRegistration filter
        if (vehicleDescriptionOrRegistration != null
                && vehicleDescriptionOrRegistration.length() > 0) {
            String registrationQuery = "%"
                    + Vehicle
                            .normalizeRegistration(vehicleDescriptionOrRegistration)
                    + "%";
            String descriptionQuery = "%"
                    + vehicleDescriptionOrRegistration.trim() + "%";

            criteria.createAlias("vehicle", "vehicle").add(
                    Restrictions.or(Restrictions.like("vehicle.registration",
                            registrationQuery), Restrictions.like(
                            "vehicle.description", descriptionQuery)));
        }

        List<VehicleRentalContract> contracts = criteria.list();
        return contracts;
}

The createAlias call can be used where you would need a join in SQL.

Answer 2

even i will prefer using Criteria over HQL and SQL, for me reason will be modularity and also performance, because when the project comes into production, the major problem that we face is performance, neither HQL nor SQL can compete Criteria over performance.

Adding to above The DAO layer is created for accessing the data, and this layer should be as clear as glass without any complex coding or business logic, but in case of criteria, one has to write a logic(create criteria) to arrive at a better and tuned way to access object, So in my view there is no breach in putting this much logic in DAO layer.

Answer 3

Two approaches:

1.. Depending on what kind of filtering you need you may be able to acheive this by searching e.g. index all the objects with Lucene and then use search queries to perform the filtering. e.g build up a query like:

title:"The Right Way" & mod_date:[20020101 TO 20030101]

See: http://lucene.apache.org/java/2_4_0/queryparsersyntax.html

---

2.. Or using criteria...

I'd use the new type-safe criteria api from hibernate:

http://relation.to/12805.lace

Rather than one method that builds up a very large criteria, I'd try to seperate out all the logic using detached criteria -

http://docs.jboss.org/hibernate/core/3.5/reference/en/html/querycriteria.html#querycriteria-detachedqueries

With a combination of these two you would be able to build up criteria easily.

One other place to look for inspiration is the grails dynamic finders. This is essentially what you are trying to achieve in a static way.

http://www.grails.org/doc/1.0.x/guide/single.html#5.4.1 Dynamic Finders

If you really want complete separation of layers you could implement a simple grammar. Then parse this to create the relevant criteria. This would allow for changing of the underlying criteria implementations. Whether this is appropriate depends on how crucial this abstraction is to you.