Spring data JPA Specifications - @OneToMany dependency

Question

i have a problem with getting List from entity Person using Spring data JPA specifications (because of pagination). I need to get all notes by person but dependency between these two entities is on Person side. I don't know how to create my Predicate because Note doesn't contain any attribute related to Person.

I simply can get List with Persons getter but i can't use this way because i need returned data paginated.

@Entity
public class Person implements Serializable {

    @Id
    private Long personId;

    @OneToMany
    @JoinColumn(name = "personId")
    private List<Note> notes;

}

@Entity
public class Note implements Serializable {

    @Id
    private Long noteId;
}

Normally, I would write something like this, but i don't have an attribute person in Note and database can't be remapped at this stage.

public static Specification<Note> notesByPerson(final Long personId) {
        return new Specification<Note>() {
            @Override
            public Predicate toPredicate(final Root<Note> root, final CriteriaQuery<?> query,
                    final CriteriaBuilder builder) {

                final Path<Person> per = root.<Person> get("person");

                return builder.equal(per.<Long> get("personId"), personId);

            }
        };
    }

Thank you, Zdend

Solution 1

Solved..

public static Specification<Note> notesByPerson(final Long personId) {
        return new Specification<Note>() {

            @Override
            public Predicate toPredicate(final Root<Note> noteRoot, final CriteriaQuery<?> query,
                    final CriteriaBuilder cb) {

                final Subquery<Long> personQuery = query.subquery(Long.class);
                final Root<Person> person = personQuery.from(Person.class);
                final Join<Person, Note> notes = person.join("notes");
                personQuery.select(notes.<Long> get("noteId"));
                personQuery.where(cb.equal(person.<Long> get("personId"), personId));

                return cb.in(noteRoot.get("noteId")).value(personQuery);
            }
        };
    }

Solution 2

I am not sure how to do that with Predicates, as I usually dont use them, but in JPQL (or HQL, which is similar), you can do something like this:

SELECT Note n FROM Person.notes WHERE XXXX

It is basically the same thing as doing this in SQL

SELECT n.noteId FROM person as p JOIN persons_notes pn ON pn.person=p.personId JOIN notes as n ON n.noteId=pn.noteId

I would venture a guess that the Predicate method has similar abilities as described above.

Comments

[Zdend]

That's the problem.. I can't use query like this, because I need it to be paginated and ours implementation can handles only specifications as query argument. Also, I'm new with Predicates so I don't know what they are able to.

[CodeChimp]

For pagination, you would take the TypedQuery that is generated, either using the above method or through CriteriaBuilder (read Predicates), then call setFistResult() to page*returnCount, and setMaxResults() to returnCount. If you need a total, I believe most people simply call the same query, once to get the data, another to do a "count()".

[levtatarov]

can you further explain the solution? (elaborate on the steps)

[rwyland]

There has to be a better way than complicating it with that in clause.... You should consider adding the @ManyToOne Person person; in your Note class so then you can do a simple Join.

[bytor99999]

I know this is old years ago. But first explanation of their answer. Create a Subquery to the side that has the association mapped. That being Person, and the Subquery is of type Long because it is only getting the pk of notes rows so that it returns the list of ids to use in the Notes root main query as an IN clause. 2) Regarding the comment above mine adding the ManyToOne mapping and I agree, however be careful because ManyToOne are defaulted to be eager fetched such that it will always be returned even if another use case doesn't need that data.