Problem: Native queries with Spring Data returning dates return java.sql.Date not java.time.LocalDate, despite the setup.
Context: A new project with Spring Boot 2.0.0.M5 (the latest), Hibernate 5.2.11, Hibernate-Java8 5.2.12 (which gives support for JSR310 classes as long as it's on the classpath).
Anonymized example below (the app is not really about birthdays):
public interface BirthdayRepository<T, ID extends Serializable> extends Repository<T, ID> {
@Query(value = "select day from birthdays", nativeQuery = true)
Iterable<java.sql.Date> getBirthdays(); //the return type should ideally be java.time.LocalDate
}
In the database (SQL Server), the day field is DATE and values are like 2017-10-24.
The problem is that at runtime, the Spring Data repository (whose implementation I cannot control, or is there a way?) returns java.sql.Date
not java.time.LocalDate
(Clarification: the return type appears to be decided by Spring Data and remains java.sql.Date
even if I change the return type to be java.time.LocalDate
, which is how I started to).
Isn't there a way to get LocalDate
directly? I can convert it later, but (1) that's inefficient and (2) the repository methods have to return the old date/time classes, which is something I'd like to avoid. I read the Spring Data documentation, but there's nothing about this.
EDIT: for anyone having the same question, below is the solution, the converter suggested by Jens.
public class LocalDateTypeConverter {
@Converter(autoApply = true)
public static class LocalDateConverter implements AttributeConverter<LocalDate, Date> {
@Nullable
@Override
public Date convertToDatabaseColumn(LocalDate date) {
return date == null ? null : new Date(LocalDateToDateConverter.INSTANCE.convert(date).getTime());
}
@Nullable
@Override
public LocalDate convertToEntityAttribute(Date date) {
return date == null ? null : DateToLocalDateConverter.INSTANCE.convert(date);
}
}
It looks like you found a gap in the converters. Spring Data converts out of the box between java.util.Date
and java.time.LocalDate
but not between java.time.LocalDate
and java.sql.Date
and other date and time-related types in the java.sql
package.
You can create your own converter to do that. You can use Jsr310JpaConverters as a template.
Also, you might want to create a feature request and if you build a converter for your use, you might even submit a pull request.
I know this is an older question, but my solution to this problem does not require a custom converter.
public interface BirthdayRepository<T, ID extends Serializable> extends Repository<T, ID> {
@Query(value = "select cast(day as date) from birthdays", nativeQuery = true)
Iterable<java.time.LocalDate> getBirthdays();
}
The CAST
tells JPQL to use available java date\time types rather than java.sql.Date
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With