Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

java.time.LocalDate not supported in native queries by latest Spring Data/Hibernate?

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);
        }
    }
like image 605
wishihadabettername Avatar asked Oct 24 '17 21:10

wishihadabettername


2 Answers

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.

like image 190
Jens Schauder Avatar answered Nov 06 '22 15:11

Jens Schauder


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

like image 5
habelson Avatar answered Nov 06 '22 15:11

habelson