Best way to create a query in Hibernate with programmatical conditions

Question

Let's say that I have a method responsible for building a query based on the parameters passed to it:

    public Query<User> buildQuery(String name, String city) {
        Session session = HibernateUtil.getSessionFactory().openSession();

        String queryString = "from Users where 1 = 1 ";
        if (name != null) queryString += "and name = :name ";
        if (city != null) queryString += "and city = :city ";

        Query<User> query = session.createQuery(queryString, User.class);

        if (name != null) query.setParameter("name", name);
        if (city != null) query.setParameter("city", city);

        return query;
    }

I don't like this because the logic is repeated two times (for adding the condition and for the setting of the parameter).

The only solution that came to my mind was to use a criteria query:

    public Query<User> buildQuery2(String name, String city) {
        Session session = HibernateUtil.getSessionFactory().openSession();

        CriteriaBuilder builder = session.getCriteriaBuilder();         
        CriteriaQuery<User> criteriaQuery = builder.createQuery(User.class);
        Root<User> root = criteriaQuery.from(User.class);
        Predicate predicate = builder.conjunction();
        if (name != null) {
            predicate = builder.and(predicate, builder.equal(root.get("name"), name));
        }
        if (city != null) {
            predicate = builder.and(predicate, builder.equal(root.get("city"), city));
        }
        criteriaQuery.select(root).where(predicate);            
        Query<User> query = session.createQuery(criteriaQuery);
        return query;
    }

Is there a better solution for this?

Answer 1

The JPA Criteria API approach is one way, but adding new filters will require changes in your business code which is not very desirable. Also, at some point it's just very painful to list all possible filter values as arguments to a method.

I would recommend you take a look at what Blaze-Persistence Entity-Views has to offer.

I created the library to allow easy mapping between JPA models and custom interface defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure the way you like and map attributes(getters) via JPQL expressions to the entity model. Since the attribute name is used as default mapping, you mostly don't need explicit mappings as 80% of the use cases is to have DTOs that are a subset of the entity model. The best thing about all that is, that you can define filters for your projections.

A mapping for your model could look as simple as the following

@EntityView(User.class)
interface UserView {
  @IdMapping
  Long getId();
  @AttributeFilter(EqualFilter.class)
  String getName();
  @AttributeFilter(EqualFilter.class)
  String getCity();
}

In your representation layer you build a EntityViewSetting object which you can pass to your service layer.

EntityViewSetting<UserView, CriteriaBuilder<UserView>> setting = EntityViewSetting.create(UserView.class);
if (nameFilter != null) setting.addAttributeFilter("name", nameFilter);
if (cityFilter != null) setting.addAttributeFilter("city", cityFilter);

Your service could then be pure and just about business logic, something like this

<T> T findAll(EntityViewSetting<T, CriteriaBuilder<T>> setting) {
    CriteriaBuilder<User> cb = criteriaBuilderFactory.create(entityManager, User.class);
    // Your business logic
    return entityViewManager.applySetting(setting, cb);
}