8

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);
        }
    }
wishihadabettername
  • 14,231
  • 21
  • 68
  • 85
  • I'm confused. You specified in your interface that you want `Iterable` why do you expect anything else? Why do "the repository methods have to return the old date/time classes?" – Jens Schauder Oct 25 '17 at 05:47
  • 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. – wishihadabettername Oct 25 '17 at 11:20

2 Answers2

8

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.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • 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. – wishihadabettername Oct 25 '17 at 15:51
  • 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. – Jens Schauder Oct 25 '17 at 18:04
  • 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](https://stackoverflow.com/questions/24650186/choosing-between-java-util-date-or-java-sql-date) so the converter for the latter should/could activate, but it doesn't. I'll work on the converter then. – wishihadabettername Oct 25 '17 at 18:45
  • And the converter worked, so I'll accept the answer fully. – wishihadabettername Oct 25 '17 at 19:00
5

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

habelson
  • 71
  • 1
  • 6