Query on Date only with Spring Boot Data JPA / Java 8 Instant?

By : Busata
Source: Stackoverflow.com

I have a Spring boot 1.4.x application that uses the starter jpa in combination with H2/Postgresql. I have an entity that stores a date as an Instant (with Instant.now(), based on Java 8: What's the difference between Instant and LocalDateTime? answer), and the associated table stores this field as a timestamp.

For the storing to work I had to setup a converter that converts Instant's to sql.Timestamps & vice versa, which seems to work (Timestamp.from(instant) and timestamp.toInstant())

My question is if there is a straightforward way to query by Date only using this instant using JPA repository, eg.

List<Orders> findBySaleTime(.... day)

Or am I forced to find a way to convert the day to two instants & do an in between query?

By : Busata

public List<SaleOrder> findByDate(String date) {
    Instant startOfDay = Instant.parse(date).truncatedTo(ChronoUnit.DAYS);
    Instant endOfDay = startOfDay.plus(Duration.ofDays(1));

    return saleOrderRepository.findBySaleTimeAfterAndSaleTimeBefore(startOfDay, endOfDay);

Where the date is an UTC date passed from the client, seems to be the most straightforward to do this with java 8 dates. (Credits to Liste & Yawkat on freenode #java for help/pointers)

By : Busata

There are two approaches:

1st one:

List<Orders> findBySaleTimeBetween(DateTime start, DateTime end);

2nd one:

List<Orders> findBySaleTime(DateTime date);

It's worth trying to save dates rounded as much as possible, so if you only need day, set hours and minutes to certain values for all the entities.

By : xenteros

You can use a WHERE clause for this. Convert your INSERT VALUES to an INSERT SELECT and add a WHERE clause.

For example,

INSERT INTO fields (field_name, control_type_id, needs_approval)
'Array Photos', 3, 0
WHERE Condition;

If Condition is true, it will insert the row. If Condition is false, the SELECT will return zero rows and thus the INSERT will insert zero rows.

This video can help you solving your question :)
By: admin