CODEWITHSUNDEEP
javaspring-data-jpaspring-data
VIOO66
VIOO66

Reputation: 67

SpringData findAll entries that were last inserted

suppose I have the following entries in the DB:

id entityId entityPart lastModifiedDate
1 entity1 part1 28.12.2020
2 entity2 part1 28.12.2020
3 entity3 part2 28.12.2020
4 entity1 part1 30.12.2020
5 entity3 part2 31.12.2020

Now, if I want to use spring data to find the entry with the newer lastModifiedData field, I can find it by defining the following method

findFirstByEntityIdAndEntityPartOrderByLastModifiedDateTimeDesc(String entityId, String entityPart)

Is there any way to return a list of all entries that will contain only the the entries that have the newer lastModifiedDate, so in our case it should return the list containing only the entries with ids 2, 4, 5, as for these entries, they have the newer lastModifiedData.

EDIT 1 As a solution I used a sql query like follows, also defined a fix for pagination

@Query(value = "SELECT * FROM orders AS or WHERE LAST_MODIFIED_DATE =\n" +
        "      (SELECT MAX(LAST_MODIFIED_DATE)\n" +
        "       FROM orders \n" +
        "       WHERE (ENTITY_ID = or.ENTITY_ID AND ENTITY_PART = or.ENTITY_PART))"
       countQuery = "SELECT COUNT(*) FROM (\n" +
        "         SELECT MAX(LAST_MODIFIED_DATE) as or\n" +
        "         FROM orders \n" +
        "         GROUP BY ENTITY_ID , ENTITY_PART) AS order")
       nativeQuery = true)

Upvotes: 0

Views: 130

Answers (2)

ville
ville

Reputation: 57

Because you use date without time there will be danger that you going to have duplicate date on entries. If you can modify database, just add boolean value it to identify latest entry. Then you can use List findByBoolean(Boolean true);

  • you should consider to use epoch time for date to avoid later complications.

Upvotes: 0

csenga
csenga

Reputation: 4114

In this case, the After keyword should be used:
(I kept the same ordering as in your example)

List<YourEntityType> findByLastModifiedDateAfterOrderByLastModifiedDateTimeDesc(Date lastModifiedDateAfter)

but, it will be not enough. First, you need to find the last one(with the query you've provided), and then query those entities that are with the value. This can't be done with one repository query, but you can combine multiple ones by defining a default method. Please note the @Transactional annotation, the queries should run in one transaction otherwise the result can be inconsistent.

example:

public interface ExampleRepository extends CrudRepository<YourEntityType, Long> {

YourEntityType findFirstByEntityIdAndEntityPartOrderByLastModifiedDateTimeDesc(String entityId, String entityPart);

List<YourEntityType> findByLastModifiedDateAfterOrderByLastModifiedDateTimeDesc(Date lastModifiedDateAfter);

@Transactional
default List<YourEntityType> findByLastModifiedDateOrderByLastModifiedDateTimeDesc(String entityId, String entityPart) {
    YourEntityType last = findFirstByEntityIdAndEntityPartOrderByLastModifiedDateTimeDesc(entityId, entityPart);
    return findByLastModifiedDateAfterOrderByLastModifiedDateTimeDesc(last.getLastModifiedDateAfter());
}

}

ref: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation

Upvotes: 1

Related Questions