How to use JPA CriteriaBuilder selectCase() so that it can have Predicate as result?

Question

I needed to implement a criteria query which has if...then...else scenario. I found javax.persistence.criteria.CriteriaBuilder.selectCase() is suitable for this purpose.It returns an Expression as result.

To run criteria query I needed to use where() which accepts an array of Predicates. This is because I have multiple predicates to include in the query.

Now, since selectCase() is returning Expression, I am not able to integrate it with existing list of Predicates.

Type mismatch: cannot convert from Expression<Object> to Predicate

How do I use selectCase() so that I can have Predicate as result? Or any other better way of doing this?

Example:

To illustrate the problem, I have following implementation to "Get all users of particular age AND (from a particular country and city) else from India, by default"

if...then...else scenario

If from particular country

   if from particular city

else

 if from "India"

// Query implementation

@Override
    public List<User> findUsersByAgeCountryAndCity(int age, String country, String city) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<User> cq = cb.createQuery(User.class);

        Root<User> user = cq.from(User.class);
        List<Predicate> predicates = new ArrayList<>();

        predicates.add(age(user, age));

        predicates.add(country(user, country, city));

        cq.where(predicates.toArray(new Predicate[0]));

        return entityManager.createQuery(cq).getResultList();
    }

    private Predicate country(Root<User> user, String country, String city) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        return cb.selectCase() //
                .when(cb.equal(user.get("address").get("country"), country),
                        cb.equal(user.get("address").get("city"), city))
                .otherwise(cb.equal(user.get("address").get("country"), "India")); 
    }

    private Predicate age(Root<User> entity, int age) {
        return entityManager.getCriteriaBuilder().equal(entity.get("age"), age);
    }

// User.java

public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    private String name;
    private LocalDate creationDate;
    private LocalDate lastLoginDate;
    private boolean active;
    private int age;
    @Column(unique = true, nullable = false)
    private String email;
    private Integer status;
    @Embedded
    private Address address;
    @OneToMany
    List<Possession> possessionList;
...

// Address.java

@Embeddable
public class Address {

    private String city;
    private String country;
...

What native SQL query are you trying to reproduce via Criteria API? — Nikolai Shevchenko, Aug 01 '19 at 14:03
Actually I am trying to map an in-memory implementation into a JPA one. By in-memory I mean which is using maps and sets to store data and filters with predicates to query data. — Omkar Shetkar, Aug 01 '19 at 14:17
I mean that CriteriaAPI query finally gets rendered into some SQL query like `select * from XXX where YYY`. And I'm asking what exact SQL query you wish to run — Nikolai Shevchenko, Aug 01 '19 at 14:42

crizzis · Answer 1 · 2019-08-01T14:59:33.863

If I read your question correctly, you want the following logic:

IF is_user_from_country 
    RETURN is_user_from_city
ELSE
    RETURN is_user_from_india

Making it into a query is tricky, because predicates in SQL do not have an intrinsic boolean value you can return. In SQL terms, it will look something like:

CASE 
    WHEN user.country = :country THEN
        CASE WHEN user.city = :city THEN 1 ELSE 0 END
    WHEN user.country = 'India' THEN 1
    ELSE 0 
END

In Criteria API (note that I haven't tested it, there might be syntax errors):

cb.selectCase() //
    .when(cb.equal(user.get("address").get("country"), country),
            cb.selectCase()
            .when(cb.equal(user.get("address").get("city"), city), cb.literal(true))
            .otherwise(cb.literal(false))
    ))
    .when(cb.equal(user.get("address").get("country"), "India"), cb.literal(true))
    .otherwise(cb.literal(false));

I'm not entirely sure Criteria API supports nested CASE statements, though. If not, you can try making the logic more straightforward:

SELECT CASE
    WHEN user.country = :country AND user.city = :city THEN TRUE
    WHEN user.country = :country AND user.city <> :city THEN FALSE
    WHEN user.country = 'India' THEN TRUE
    ELSE FALSE
END

its in the similar lines as you explained. My main issue was to how to get Predicate from the method. With the above solution of nested case, I was getting Criteria Case as the result. Your second solution gave me a hint of cleaner solution. Explained below the answer. With this I am able to get Predicate as result. Please let me know if anything to be improved in the solution. Thanks — Omkar Shetkar, Aug 02 '19 at 00:46
private Predicate country(Root user, String country, String city) { CriteriaBuilder cb = entityManager.getCriteriaBuilder(); return cb.or( cb.and(cb.equal(user.get("address").get("country"), country), cb.equal(user.get("address").get("city"), city)), cb.and(cb.equal(user.get("address").get("country"), "India"))); } — Omkar Shetkar, Aug 02 '19 at 00:48

How to use JPA CriteriaBuilder selectCase() so that it can have Predicate as result?

if...then...else scenario

1 Answers1

Linked