jpa - transforming jpql join query to criteria api

Question

I was trying to transform this JPQL query;

SELECT s FROM QuestionSet s JOIN s.questions q WHERE q.appointedRepetition.date  < :tomorrow

to its criteria api equivalent, here is what I have so far:

    DateTime tomorrow = DateTime.now().plusDays(1).withTime(0,0,0,0);

    CriteriaBuilder criteriaBuilder = JPA.em().getCriteriaBuilder();
    CriteriaQuery<QuestionSet> query = criteriaBuilder.createQuery(QuestionSet.class);
    Root<QuestionSet> root = query.from(QuestionSet.class);
    Join<QuestionSet, Question> questionJoin = root.join("questions");
    Predicate ownerCondition = criteriaBuilder.equal(root.get("owner"), owner);
    Predicate dateCondition = criteriaBuilder.lessThan(questionJoin.<DateTime>get("appointedRepetition.date"), tomorrow);

    query.where(criteriaBuilder.and(ownerCondition, dateCondition));


    List<QuestionSet> result = JPA.em().createQuery(query).getResultList();

    return result;

but I am getting

play.api.Application$$anon$1: Execution exception[[IllegalArgumentException: Unable to resolve attribute [appointedRepetition.date] against path [null]]]

Looking at How to convert a JPQL with subquery to Criteria API equivalent? I have almost the same code in criteria api part.

@Entity
@SequenceGenerator(name = "wordlist_seq", sequenceName = "wordlist_seq")
public class QuestionSet {
  @OneToMany(cascade = CascadeType.ALL)
  private List<Question> questions;
      ...
}


@Entity
@SequenceGenerator(name = "question_seq", sequenceName = "question_seq")
@Inheritance(strategy=InheritanceType.TABLE_PER_CLASS)
public abstract class Question{
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "question_seq")
    private Long id;
        ...
}


    @OneToOne(cascade = CascadeType.ALL)
    private AppointedRepetition appointedRepetition;

What is `appointedRepetition`? Perhaps it's an Entity with a ManyToOne relationship with `Question`? Many questions, one appointedRepetition? In that case you need another Join, I can show you — perissf, Jun 03 '13 at 14:03
I added snippets of the code presenting model, QuestionSet has `OneToMany` relation with `Question` instances and each `Questions` has `OneToOne` relation with `AppointedRepetition`, `AppointedRepetition` has no knowledge of `Question`, it is only one way relation. — Andna, Jun 03 '13 at 14:09

perissf · Accepted Answer · 2013-06-03T14:27:21.403

You need another join, but I can't assure it will work, because the entity definitions are either missing or incomplete, and not all relationships are defined as stated in your comment. Anyway, I would try this:

Join<Question, AppointedRepetition> repetition = questionJoin.join("appointedRepetition");
Predicate dateCondition = criteriaBuilder.lessThan(repetition.get("date"), tomorrow);

By the way, I see that you are using joda's DateTime. I have never used it with JPA CriteriaBuilder, so I can't assure it works.

jpa - transforming jpql join query to criteria api

1 Answers1

Linked