Hello I working on a scenario, where I need the latest timestamp data based on Currency using Java 8.
Suppose I'm requesting for USD currency then I should get all USD data with Least timestamp for all date.
class Currency{
private Integer id;
private String name;
private LocalDateTime lastReceived;
}
Record In Database As below.
ID NAME LAST_RECEIVED
-- ---- -------------
1 USD 18-MAY-22 09.04.01.545899000 AM
2 USD 18-MAY-22 08.04.01.545899000 AM
3 USD 19-MAY-22 08.04.01.545899000 AM
4 USD 20-MAY-22 08.04.01.545899000 AM
5 USD 20-MAY-22 11.04.01.545899000 AM
6 BUSD 18-MAY-22 08.04.01.545899000 AM
Expected:
ID NAME LAST_RECEIVED
-- ---- -------------
1 USD 18-MAY-22 09.04.01.545899000 AM
3 USD 19-MAY-22 08.04.01.545899000 AM
5 USD 20-MAY-22 11.04.01.545899000 AM
I tried, Suppose I requested for USD Data,
List<Currency> listCurrency = repo.getAllDataBasedOnCurrency("USD");
listCurrency.stream().sorted(Comparator.comparing(Currency::getLastReceived).reversed()).collect(Collectors.toList());
// But here I want latest received Data for a single date.
CodePudding user response:
It appears that you should group by currency AND date and then select by maximal (most recent) timestamp, this results in a map, and then you can use the map's values()
to build the list.
So, a list of currency name and the date can be used as the key in the map, and Collectors.collectingAndThen
is needed to convert the Optional
result of Collectors.maxBy
into the currency instance:
List<Currency> lastByDate = new ArrayList<>(data
.stream() // Stream<Currency>
.collect(Collectors.groupingBy(
// key = List<Serializable & Comparable<? extends Comparable<?>>>
curr -> Arrays.asList(curr.getName(), curr.getLastReceived().toLocalDate()),
Collectors.collectingAndThen(
Collectors.maxBy(Comparator.comparing(Currency::getLastReceived)),
Optional::get // Optional<Currency> -> Currency
)
)) // Map<List<...>, Currency>
.values()
);
If a sorted list is required, it may be retrieved by sorting the stream of values
:
List<Currency> sortedByDateAndCurr = data
.stream()
.collect(Collectors.groupingBy(
curr -> Arrays.asList(curr.getName(), curr.getLastReceived().toLocalDate()),
Collectors.collectingAndThen(
Collectors.maxBy(Comparator.comparing(Currency::getLastReceived)),
Optional::get
)
))
.values()
.stream()
.sorted(
Comparator.comparing(Currency::getLastReceived)
.thenComparing(Currency::getName)
)
.collect(Collectors.toList());
or just by sorting the unsorted list:
lastByDate.sort(
Comparator.comparing(Currency::getLastReceived)
.thenComparing(Currency::getName)
);
For a specific currency (e.g. USD
) the implementation should be changed slightly to include filtering by the currency name then a simpler key should be created in the map:
List<Currency> lastUSDByDate = new ArrayList<>(data
.stream()
.filter(curr -> "USD".equalsIgnoreCase(curr.getName()))
.collect(Collectors.groupingBy(
curr -> curr.getLastReceived().toLocalDate(),
Collectors.collectingAndThen(
Collectors.maxBy(Comparator.comparing(Currency::getLastReceived)),
Optional::get
)
))
.values()
);
lastUSDByDate.sort(Comparator.comparing(Currency::getLastReceived));
Similar result may be achieved by building a native SQL query using a window function to get the most recent value per date. JPA does not seem to support yet the window functions.
An example for PostgreSQL
// some JPA CurrencyRepository
@Query(nativeQuery = true, value = """
SELECT id, name, last_received
FROM (
SELECT c.*,
ROW_NUMBER() OVER (
PARTITION BY name, to_char(last_received, 'yyyy-MM-dd')
ORDER BY last_received DESC
) AS rr
FROM Currency c
WHERE c.name = :currName
) tbl
WHERE rr = 1
ORDER BY last_received
""")
List<Currency> findLastByDateByCurrencyName(@Param("currName") currName);