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.