CODEWITHSUNDEEP
javajpaspring-data-jpacriteria
Omkar Shetkar
Omkar Shetkar

Reputation: 3636

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

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;
...

Upvotes: 4

Views: 8483

Answers (1)

crizzis
crizzis

Reputation: 10716

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

Upvotes: 4

Related Questions