CODEWITHSUNDEEP
postgresqlspring-boothibernatejava-8localdatetime
s5b
s5b

Reputation: 708

Using Hibernate to get the latest row with a LocalDateTime

I have a Java8 application using SpringBoot, which is pulling in Hibernate core 5.3.10 (connected to PostgreSQL 11). A simplified explanation: the application is maintaining a history of changes to a series of bespoke UserData records, containing two LocalDateTime columns for startDate and endDate, and a userId column (plus other columns).

The semantics of the application are that a history of changes to the UserData are maintained by the start_date and end_date contain no duplicates (respectively), and that any row where the endDate is null is the currently active record for a user. (Accordingly, a user may have no currently active record.)

It is a simple matter to retrieve all of the active rows, and this is working well.

However, I have a requirement to retrieve the latest row for all users irrespective of whether the row is active or not.

One way to achieve this outcome using SQL is with a query something like the following:

select ud1.* from user_data ud1
   where ud1.start_date = (select max(ud2.start_date) from user_data ud2 
                                            where ud2.user_id = ud1.user_id);

I have been attempting to write a CriteriaQuery to replicate the SQL, but I have run into a data type problem with the CriteriaBuilder.max method. The max method is complaining that it will only accept a type Number column. The code looks like this:

final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
final CriteriaQuery<UserDate> criteriaQuery = builder.createQuery(UserData.class);

final Root<UserData> ud1 = criteriaQuery.from(UserData.class);

final Subquery<LocalDateTime> maxUserDataStartDate = criteriaQuery.subquery(LocalDateTime.class);
final Root<UserData> ud2 = maxUserDataStartDate.from(UserData.class);
maxUserDataStartDate.select(builder.max(ud2.get("startDate"));
// ...

The problem is with the last line, where it complains that ud2.get("startDate") is not an extension of type Number - which is true, of course.

Does anybody know how to fix this situation? Especially, does anybody have an example they can share that does what I'm after?

Upvotes: 0

Views: 542

Answers (1)

Eklavya
Eklavya

Reputation: 18450

You can do order by start_date desc and get top 1

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<UserData> criteriaQuery = builder.createQuery(UserData.class);

Root<UserData> ud = criteriaQuery.from(UserData.class);
criteriaQuery.orderBy(builder.desc(ud.get("startDate")));
entityManager.createQuery(criteriaQuery).getFirstResult();

Upvotes: 2

Related Questions