CODEWITHSUNDEEP
hibernatehibernate-criteria
Thololo
Thololo

Reputation: 3

Hibernate Criteria to get maximum ID of the child of one-to-many for each parent entity

I have two classes:

public class DocumentRecord {  

    @Column(name = "id")
    private Long id;

    @OneToMany(...)
    @JoinColumn(name = "document_id")
    private final List<DocumentHistRecord> history = new ArrayList<>();

    ... other fields
}

and 

public class DocumentHistRecord {

    @Id
    @Column(name = "id")
    private Long id;

    ... other fields
}

What I want is to have the maximum id of the DocumentHistRecord attached to every document. Every Document has at least one DocumentHistRecord in its history.

How to achieve that in Hibernate 4.3.7 with the Hibernate Criteria API?

Here is an example of the tables and the result I want:

Table DocumentRecord:      Table DocumentHistRecord
id                         id  |  documentId
--                         -----------------
1                          1   |  1
2                          2   |  1
3                          3   |  1
4                          4   |  2
                           5   |  3
                           6   |  3
                           7   |  4
                           8   |  4

The result would be:

id (from DocumentHistRecord table)
--
3
4
6
8

Thanks in advance for your answers.

Upvotes: 0

Views: 596

Answers (1)

jpprade
jpprade

Reputation: 3664

It would look like this :

final Criteria criteria = session.createCriteria(DocumentRecord.class);
criteria.createCriteria("history" ,"h");


ProjectionList pl = Projections.projectionList();
pl.add(Projections.groupProperty("id"));
pl.add(Projections.max("h.id"));
criteria.setProjection(pl);
List res = criteria.list();

Upvotes: 1

Related Questions