Answer a question

I am using:

• Hibernate 4.3.5
• Spring JPA 1.6.0
• Javax Persistence API 2.1

The "refcodemailing" column is defined as an array of int: int[]

My entity object:

@Entity
@Table
public class CalendarEvent implements Serializable {

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

  @Convert(converter = IntegerArrayConverter.class)
  @Column(name = "refcodemailing")
  private final List<Integer> mailingCodes = new ArrayList<>();

  // ....

}

I am trying to filter the column array with the following JPA Specification method:

private final List<MailingCode> mailingCodes = new ArrayList<>();

@Override
public Predicate toPredicate(Root<CalendarEvent> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

  // Mailing codes
  if(!mailingCodes.isEmpty()){
    List<Predicate> mailingCodePred = new ArrayList<>();

    for(MailingCode mailingCode: mailingCodes){
      restrictions.add(cb.isMember(mailingCode.getId(), root.<List<Integer>>get("mailingCodes")));
    }

    restrictions.add(cb.and(cb.isNotNull(root.<List<Integer>>get("mailingCodes")),       cb.or(mailingCodePred.toArray(new Predicate[]{}))));
  }
}

But the following exception is thrown:

java.lang.IllegalArgumentException: unknown collection expression type [org.hibernate.jpa.criteria.path.SingularAttributePath]
    at org.hibernate.jpa.criteria.CriteriaBuilderImpl.isMember(CriteriaBuilderImpl.java:1332)
    at com.agenda.CalendarEventQuery.toPredicate(CalendarEventQuery.java:100)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.applySpecificationToCriteria(SimpleJpaRepository.java:521)
    at org.springframework.data.jpa.repository.support.SimpleJpaRepository.getQuery(SimpleJpaRepository.java:472)

Is there a way to do it?

Answers

According to JPA 2.0 specs:

Expressions that evaluate to embeddable types are not supported in collection member expressions. Support for use of embeddables in collection member expressions may be added in a future release of this specification.

However, I built a working example on GitHub using Hibernate.

Assuming we have this CalendarEvent entity and the MailingCode DTO object:

@Entity(name = "CalendarEvent")
@Table
public static class CalendarEvent implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @ElementCollection
    private final List<Integer> mailingCodes = new ArrayList<>();

}

public static class MailingCode {
    private Integer id;

    public MailingCode(Integer id) {
        this.id = id;
    }

    public Integer getId() {
        return id;
    }
}

You can write the Criteria API code as follows:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<CalendarEvent> criteria = builder.createQuery(CalendarEvent.class);
Root<CalendarEvent> root = criteria.from(CalendarEvent.class);

List<MailingCode> mailingCodes = Arrays.asList(
    new MailingCode(1),
    new MailingCode(2),
    new MailingCode(3)
);

Expression<List<Integer>> mailingCodesPath = root.get("mailingCodes");

Predicate predicate = builder.conjunction();

for(MailingCode mailingCode: mailingCodes){
    predicate = builder.and(predicate, builder.isMember(mailingCode.getId(), mailingCodesPath));
}

criteria.where(predicate);
List<CalendarEvent> events = entityManager.createQuery(criteria).getResultList();

However, an IN query is a much better choice since the SQL query above is suboptimal.