Is it possible to implement an exists query based on @ElementCollection using Jpa criteria query?

Question

I'm learning Jpa criteria query and have no idea of how to implement this:

I have an entity called Restaurant:

@Entity
@Table(name = "t_f2g_restaurant", 
    uniqueConstraints = @UniqueConstraint(columnNames = { "NAME" }))
public class Restaurant {
    @EmbeddedId
    @AttributeOverrides(
        { @AttributeOverride(name = "value", column = @Column(name = "ID")) })
    private RestaurantIdentity id;

    @Column(name = "name")
    private String name;

    @ElementCollection
    @CollectionTable(name = "t_f2g_restaurant_srv_area", 
        joinColumns = @JoinColumn(name = "RESTAURANT_ID"))
    @Column(name = "zip_code")
    private List<String> serviceAreas = new ArrayList<String>();
}

Now I want to count restaurants filtering by serviceAreas, below is my native sql implentation:

private boolean delegateToNativeQuery(Address deliveryAddress {
    String sqlString = "select count(*) from t_f2g_restaurant r "
        + "where exists (select * from t_f2g_restaurant_srv_area sa "
        + "where sa.zip_code = ? and r.id = sa.restaurant_id) ";

    Query query = entityManager.createNativeQuery(sqlString);
    query.setParameter(1, deliveryAddress.getZipCode());
    return ((Number) query.getSingleResult()).longValue() > 0;
}

But how do I implement it with criteria query?

Below is my attempt:

private boolean delegateToCriteriaQuery(Address deliveryAddress) {
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> query = criteriaBuilder.createQuery(Long.class);
    Root<Restaurant> restaurant = query.from(Restaurant.class);

    Subquery<Long> serviceAreaSubquery = query.subquery(Long.class);

    /* This causes an extra t_f2g_restaurant, t_f2g_restaurant_srv_area join 
    Root<Restaurant> serviceAreaRoot = serviceAreaSubquery
        .from(Restaurant.class);

    ListJoin<Restaurant, String> serviceAreas = serviceAreaRoot
        .joinList("serviceAreas");
    serviceAreaSubquery.select(criteriaBuilder.count(serviceAreaRoot))
        .where(criteriaBuilder.
            //serviceArea is represented by String, so what's the attribute name?
            equal(serviceAreas.get("serviceArea"), deliveryAddress.getZipcode()));

    query.select(criteriaBuilder.count(restaurant)).
        where(criteriaBuilder.exists(serviceAreaSubquery));

    return entityManager.createQuery(query).getSingleResult() > 0;
}

score 1 · Answer 1 · answered Aug 18 '13 at 13:55

I made it just now. Hope the answer helps someone.

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Restaurant> query = criteriaBuilder
    .createQuery(Restaurant.class);

Root<Restaurant> restaurants = query.from(Restaurant.class);

Subquery<Restaurant> serviceAreasFiltering = query
    .subquery(Restaurant.class);

ListJoin<Object, Object> serviceAreas = serviceAreasFiltering
    .correlate(restaurants).joinList("serviceAreas");
//correlate joins t_f2g_restaurant and t_f2g_restaurant_srv_area

serviceAreasFiltering.where(criteriaBuilder.or(
    criteriaBuilder.equal(serviceAreas, deliveryAddress.getStreet1()),
    //equal against serviceAreas for I use List<String> for serviceAreas
    criteriaBuilder.equal(serviceAreas, deliveryAddress.getStreet2())));

query.where(criteriaBuilder.exists(serviceAreasFiltering));

return entityManager.createQuery(query).getResultList();

Frankly speaking, I don't think the criteria api is easy to read in this case. I probably won't switch to criteria api unless I need to return a list of restaurants(I need to write a result mapper if using native sql query).

Is it possible to implement an exists query based on @ElementCollection using Jpa criteria query?

1 Answers1