14

What is the best way how to integrate Java 8 Date Time api in jpa?

I have added converters:

@Converter(autoApply = true)
public class LocalDatePersistenceConverter implements AttributeConverter<LocalDate, Date> {

    @Override
    public Date convertToDatabaseColumn(LocalDate localDate) {
        return Date.valueOf(localDate);
    }

    @Override
    public LocalDate convertToEntityAttribute(Date date) {
        return date.toLocalDate();
    }
}

and

@Converter(autoApply = true)
public class LocalDateTimePersistenceConverter implements AttributeConverter<LocalDateTime, Timestamp> {
    @Override
    public Timestamp convertToDatabaseColumn(LocalDateTime entityValue) {
        return Timestamp.valueOf(entityValue);
    }

    @Override
    public LocalDateTime convertToEntityAttribute(Timestamp databaseValue) {
        return databaseValue.toLocalDateTime();
    }
}

Everything seems fine, but how should I use JPQL for querying? I am using Spring JPARepository, and goal is to select all entities where date is the same as date given, only difference is that it is saved in entity as LocalDateTime.

So:

public class Entity  {

    private LocalDateTime dateTime;

    ...
}

And:

@Query("select case when (count(e) > 0) then true else false end from Entity e where e.dateTime = :date")
public boolean check(@Param("date") LocalDate date);

When executing it just gives me exception, which is correct.

Caused by: java.lang.IllegalArgumentException: Parameter value [2014-01-01] did not match expected type [java.time.LocalDateTime (n/a)]

I have tried many ways, but it seems that none is working, is that even possible?

xenoterracide
  • 16,274
  • 24
  • 118
  • 243
sandris
  • 1,478
  • 2
  • 18
  • 34
  • Your field is of type LocalDateTime. Your parameter is of type LocalDate. It should be of type LocalDateTime. What happens when you execute the query? – JB Nizet Sep 14 '14 at 16:19
  • Yes I understand that, I need to save datetime but query by date. – sandris Sep 14 '14 at 16:21
  • 1
    Then why not use `date.atStartOfDay()` to convert your LocalDate to a LocalDateTime? The error message tells you precisely that the parameter is not of the right type, and should be of type LocalDateTime. – JB Nizet Sep 14 '14 at 16:23
  • The "best way" depends on your JPA implementation. The JPA impl I use (DataNucleus JPA) allows it to work out of the box with no special code. What are you using? –  Sep 14 '14 at 17:10
  • I am using spring-jpa and hibernate. One more time, I know why there is exception, I am asking how to make it work by doing another way. But looks like I will need to think about something else. – sandris Sep 14 '14 at 18:36
  • if something is stored in a DB as Timestamp (containing Time), then you need to make use of datastore functions to extra the day, month, year out and compare those with input values. – Neil Stockton Sep 15 '14 at 07:10
  • Well I can use yaer(stored_date) and other function. But is there any possibility to extract or cast passed parameter? Unless it is possible, which I dont believe, I need to pass seperately year, month etc. I am starting to think that it would be easier to store strings in database :| – sandris Sep 15 '14 at 07:39

3 Answers3

7

Hibernate has an extension library, hibernate-java8 I believe, which natively supports many of the time types.

You should use it before writing converters.

in hibernate 5.2 you won't need this additional library, it is part of core.

xenoterracide
  • 16,274
  • 24
  • 118
  • 243
  • +1 for mentioning hibernate 5.2 having it as default. I am using JPA2.1 with hibernate-core 5.2 and not facing this issue. – The Coder Jun 28 '18 at 18:21
1

To query temporal fields you should use the @Temporal Anotation in the temporal fields, add the converters to persistence.xml and also be sure you are using the java.sql.Date,java.sql.Time or java.sql.Timestamp in the converters. (Sometimes i imported from the wrong package)

for example thats works for me:

@Temporal(TemporalType.TIMESTAMP)
@Convert(converter = InstantPersistenceConverter.class)
private Instant StartInstant;
@Temporal(TemporalType.TIME)
@Convert(converter = LocalTimePersistenceConverter.class)
private LocalTime StartTime;

and my Instant converter:

@Converter(autoApply = true)
public class InstantPersistenceConverter implements   AttributeConverter <Instant,java.sql.Timestamp>{
@Override
public java.sql.Timestamp convertToDatabaseColumn(Instant entityValue) {
    return java.sql.Timestamp.from(entityValue);
}

@Override
public Instant convertToEntityAttribute(java.sql.Timestamp databaseValue) {
    return databaseValue.toInstant();
}

}

Ismael_Diaz
  • 191
  • 7
0

Did you add LocalDatePersistenceConverter and LocalDateTimePersistenceConverter in persistence.xml placed in 'class' element ?

nsfreak
  • 186
  • 1
  • 6