JPA 2 - Get last element of a list in a @OneToMany relationship

Question

I have a entity with a entity called Deadline with a @OneToMany relationship to a entity called DeadlineDate.

Deadline entity:

@Entity
@Table(name="deadline")
@Cache(alwaysRefresh=true)
public class Deadline implements Serializable { 
    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    @Column(name = "id", nullable = false)
    private Long id;


    @OneToMany(mappedBy = "deadline", cascade = PERSIST)
    @OrderBy("id ASC")
    private List<DeadlineDate> datesList = new ArrayList<DeadlineDate>();

    //...
}

DeadlineDate entity:

@Entity
@Table(name="deadlinedate")
public class DeadlineDate implements Serializable {     
    @Id
    @GeneratedValue(strategy = IDENTITY)
    @Column(name = "id")
    private Long id;

    @Basic
    @Column(name = "date", nullable = false)
    @Temporal(DATE)
    private Date date;

    @ManyToOne
    @JoinColumn(nullable = false, name = "deadline_id")
    private Deadline deadline;
    //...
}

The getAllDeadlines method:

private List<Deadline> getAllDeadlines(){
        CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
        CriteriaQuery<Deadline> query = criteriaBuilder.createQuery(Deadline.class);
        Root<Deadline> root = query.from(Deadline.class);

        Join<Deadline, DeadlineDate> joinDeadDate = root.join("datesList");

        List<Predicate> predicateList = new ArrayList<Predicate>();             

        Date date = new Date();

        //other unrelated predicates

        //i'm stuck here - begin
        predicateList.add( criteriaBuilder.lessThan(joinDeadDate.<predicateArray>get("date"), date));       
        //i'm stuck here - end

        Predicate[] predicateArray = new Predicate[predicateList.size()];
        predicateList.toArray(predicateArray);
        query.where(predicateArray);        

        TypedQuery<Deadline> typedQuery = em.createQuery(query);
        return typedQuery.getResultList();
    }

I want to make a query of deadlines that have passed the current system date. The deadlines have a history that stores changes made by the user (expiry dates can be modified forward or backward). Current deadline date is considered the largest id on the deadlinedate table. I'm trying to use the Criteria api, but do not know how to make it return only the last value of the datesList to use in the lessThan method of the criteriaBuilder. Using the current code, i get all records of deadline dates of a particular deadline.

Thanks in advance!

score 2 · Accepted Answer · edited May 23 '17 at 12:08

2

If you need to use only the DeadlineDate with max id for each Deadline, you need a subquery (not tested):

Subquery<Long> sq = query.subquery(Long.class);
Root<DeadlineDate> deadlineDate = query.from(DeadlineDate.class);
Root<DeadlineDate> deadlineDateSQ = sq.from(DeadlineDate.class);
Path<Deadline> deadline = deadlineDate.get("deadline");
Path<Deadline> deadlineSQ = deadlineDateSQ.get("deadline");
sq.select(criteriaBuilder.max(deadlineSQ.<Long>get("id")));
sq.groupBy(deadlineSQ);
... //create the list of other predicates
sq.where(predicateListSQ);
query.select(deadline);
Predicate predicate1 = criteriaBuilder.in(deadlineDate.get("id")).value(sq);
Predicate predicate2 = criteriaBuilder.lessThan(deadlineDate.<Date>get("date"), date);
query.where(criteriaBuilder.and(predicate1, predicate2));

See also:

edited May 23 '17 at 12:08

Community

1
1

answered Mar 24 '14 at 22:37

perissf

15,979
14
80
117

Another thing... with this solution, I get a list of all Deadlines with DeadlineDate that match the criteria of the current date(getting duplicate Deadlines in the result), however how can I restrict to just pick DeadlineDate that has the highest id of each of the Deadlines(getting distinct Deadlines)? This is because I need to get the list of all the Deadlines by comparing only the latest DeadlineDate of the list (the DeadlineDate with largest id). Thanks. – hlucasfranca Mar 25 '14 at 07:45
Ok, now I see what you are looking for. Be careful, if the dates can be edited, comparing them by id will lead to errors, are you sure? I would compare them by date instead. – perissf Mar 26 '14 at 06:36
Yes, I'm sure. because there must be the ability for the user to change the date to a closer or farther date. but I have validations which prevent to set a past date. – hlucasfranca Mar 27 '14 at 07:44

score -1 · Answer 2 · answered Mar 24 '14 at 04:16

-1

Use setMaxResults to limit the number of records returned. Sort the output so the record you want is the first one.

typedQuery.setMaxResults(1);

answered Mar 24 '14 at 04:16

David A.

343
2
8

I tried your solution but still getting all the records. – hlucasfranca Mar 24 '14 at 07:22

JPA 2 - Get last element of a list in a @OneToMany relationship

2 Answers2