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.