JPA Select latest instance for each item

by

In SQL the solution is very simple – join the table with a subquery, which gives you the most recent meeting for each attendee:

select * from Meeting ALL
join ( select max(meetingDate) as newest, attendee
from Meeting group by attendee ) LATEST
on ALL.meetingDate = LATEST.newest AND ALL.attendee = LATEST.attendee

This works, and works fast!

The problem with JPA is that it (or most implementations) won’t allow a subquery for a join. After spending several hours trying what will compile in the first place, and then, how slow it is, I decided that I hate JPA. Solutions like the ones above – like EXISTS (SELECT .. ) or IN ( SELECT .. ) – take ages to execute, orders of magnitude slower than they should.

Having a solution that works meant that I just needed to access that solution from JPA. There are two magic words in SQL that help you do just that:

CREATE VIEW

and the life becomes so much simpler… Just define such entity and use it.
Caution: it’s read-only.

Of course, any JPA purists will look down on you when you do that, so if anyone has a pure JPA solution, please let us both know!

More Related Contents:

  1. JPA Native Query select and cast object
  2. String variable in java to get and store the json array in table column
  3. JPA CascadeType.ALL does not delete orphans
  4. How to call a stored procedure from Java and JPA
  5. How can I validate two or more fields in combination?
  6. JPQL Create new Object In Select Statement – avoid or embrace?
  7. How to properly express JPQL “join fetch” with “where” clause as JPA 2 CriteriaQuery?
  8. Adding IN clause List to a JPA Query
  9. Hibernate – @ElementCollection – Strange delete/insert behavior
  10. JPA: unidirectional many-to-one and cascading delete
  11. How do I properly cascade save a one-to-one, bidirectional relationship on primary key in Hibernate 3.6
  12. How to use multiple JOIN FETCH in one JPQL query
  13. How to define unidirectional OneToMany relationship in JPA
  14. Hibernate generates negative id values when using a sequence
  15. How To Define a JPA Repository Query with a Join
  16. Using the JPA Criteria API, can you do a fetch join that results in only one join?
  17. Doing an “IN” query with Hibernate
  18. How to prevent SQL Injection with JPA and Hibernate?
  19. Setting a parameter as a list for an IN expression
  20. java.lang.IllegalStateException: Multiple representations of the same entity with @ManyToMany 3 entities
  21. How can I avoid the Warning “firstResult/maxResults specified with collection fetch; applying in memory!” when using Hibernate?
  22. IN-clause in HQL or Java Persistence Query Language
  23. Spring Data JPA and Exists query
  24. JPA 2 CriteriaQuery, using a limit
  25. JPQL Like Case Insensitive
  26. Rollback on every checked exception, whenever I say @Transactional
  27. Hibernate triggering constraint violations using orphanRemoval
  28. What’s the difference between the IN and MEMBER OF JPQL operators?
  29. Really dynamic JPA CriteriaBuilder
  30. Change Table Name of an Entity on runtime? [duplicate]

Leave a Comment