finding latest entry by JPA

Question

I find this thread very helpful, but it looks like implementation in the advices are not quite efficient because of the nature of JPA.

I am looking for a solution to pickup latest entry of joining query with grouping, implemented by JPA, so it is not a simple job. My implementation is put "ORDER BY time DESC" in the end and pick up the first from return collection, instead of using MAX() function which have to introduce subquery as well, but I wonder is this a good alternative ?

This is the complex query I made by the example from the other example:

"SELECT oo FROM Order AS oo WHERE oo.id IN " +
    "(SELECT temp.id FROM " +
        "(SELECT t.order.id AS id, MAX(t.order.orderTime) AS ordTime FROM Transaction t " +
        "WHERE t.order.name= :name " +
        "GROUP BY t.order.name) AS temp" +
    ")";

This is the query which I think could be a good alternative but not sure:

String query = "SELECT t.order FROM Transaction AS t " + 
    " WHERE t.order.name= :name " +
    " ORDER BY t.order.orderTime DESC";

// and simply just pick up the 1st as the latest entry from result:
Order order =  em.createQueryquery , Order .class).getResultList().get(0);

@Denis Tulskiy Will do :) Do you think it is a good practice to find latest entry instead of put everything in a JPQL? I ponder the `ORDER BY DESC` should be reliable — Dreamer, Oct 07 '14 at 18:19

score 2 · Accepted Answer · edited May 23 '17 at 11:51

2

Problem with your solution is that it will load ALL entries in a list for you to select one. As per this answer, you need to limit result to 1 by calling query.setMaxResults(1). Other than that, it is a perfectly fine solution. You can also use query.getSingleResult() to get single entity, but beware of NoResultException if there are no such records.

edited May 23 '17 at 11:51

Community

1
1

answered Oct 07 '14 at 18:27

Denis Tulskiy

19,012
6
50
68

finding latest entry by JPA

1 Answers1