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

Question

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);
        }
    }

Elman

I'm confused. You specified in your interface that you want Iterable<java.sql.Date> why do you expect anything else? Why do "the repository methods have to return the old date/time classes?" – Irita

That's what it gets returned by Spring Data. If I put LocalDate (which I did initially), it fails at runtime with a ClassCastException when I retrieve the item from the iterator. That's why I put that comment, that the type should be. I'll add a clarification. – Elman

Answer 1

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.

Irita

Thanks, Jen. Could you please clarify, if known, why Spring Data (or is it the driver) chooses java.sql.Date as opposed to, say, java.util.Date or java.time.Date? The field type in the database is date (which, for SQL Server, is what it says, just YYYY-MM-DD). Out of the 3 posibilities, how is that particular one selected? Also, given that the query is native, how to tell Spring Data to use it for the return type? There's nothing to annotate, like in the case of JPA entities and columns. Thank you. – Elman

Without looking in the code at any detail: Spring Data probably just takes whatever the driver provides as a default and then tries to convert that to the required type with its converters. But in this case a suitable converter is not available. – Irita

I suspect that converter matching logic looks at exactly the same class and not also at subclasses. java.sql.Date is a subclass of java.util.Date (a useful discussion here so the converter for the latter should/could activate, but it doesn't. I'll work on the converter then. – Elman

And the converter worked, so I'll accept the answer fully. – Elman

Answer 2

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

Centrist

Cool! Works very well! – Unmeet