CODEWITHSUNDEEP
oracle-databasehibernatespring-bootspring-datajpql
DerM
DerM

Reputation: 1526

Spring Data LocalDate comparison fails (time is not ignored)

I got a Spring Boot app with an Oracle database. This is my repository and entity:

public interface CampaignRepository extends CrudRepository<Campaign, Long> {

    @Query("select k from Campaign k where k.validAfter <= :date")
    List<Campaign> findAllValidAfterDate(@Param("date") LocalDate date);
}

Entity:

@Entity
@OtherAnnotations
public class Campaign{
    id and stuff...

    private LocalDate validAfter;

}

Now I would assume that if you put in LocalDate.now() into the function it should return all campaigns that are valid today. However this is NOT the case if I run a test against the Oracle which contains entries that are valid today where the time is after "now". The validAfter column is of type DATE.

Somehow the time part is not ignored in the <= comparison. Did someone experience this behaviour? How can I ensure that the time is ignored and only the date is used in the comparison?

I already tried this solution but it has no effect: https://stackoverflow.com/a/40525940/1528880

Thanks!

Upvotes: 4

Views: 4413

Answers (2)

Yuriy Tsarkov
Yuriy Tsarkov

Reputation: 2548

There is no a straight way to persist a LocalDate in a JPA since the Java 8 LocalDate is younger then JPA and the JPA behavior in this case is unpredictable (actually is, but I think it's DB-depended behavior). You have to implement your own AttributeConverter to use LocalDate in entities. Here is an example:

@Converter(autoApply = true)    
public class LocalDateAttributeConverter implements AttributeConverter<LocalDate, Date> {   
        @Override
        public Date convertToDatabaseColumn(LocalDate locDate) {
            return (locDate == null ? null : Date.valueOf(locDate));
        }
        @Override
        public LocalDate convertToEntityAttribute(Date sqlDate) {
            return (sqlDate == null ? null : sqlDate.toLocalDate());
        }
    }

And here is a nice article: https://www.thoughts-on-java.org/persist-localdate-localdatetime-jpa/

Upvotes: 1

yvoytovych
yvoytovych

Reputation: 871

I would try to use a LocalDate.now().atStartOfDay() which will produce LocalDateTime formed at the time of midnight, 00:00, meaning the start of today's date. So that only date will be used in comparison.

Upvotes: 0

Related Questions