LocalTime with spring-data-jpa and mssql

Question

We have issue with query which is working with LocalTime on MSSQL DB

Our entity has field

@Column(name = "receipt_time", nullable = false)
private LocalTime receiptTime;

Everything works fine except when querying i.e. using spring-data-jpa query

boolean existsByReceiptTime(LocalTime time);

Returns The data types time and datetime are incompatible in the equal to operator.

I tried to resolve with sendTimeAsDateTime, but it didn't work. The url string was not accepted. Then I tried with some AttributeConverters to no avail. Any other possible advices? NOTE: I really like to stay with LocalTime type.

UPDATE: query generated is

Hibernate: select TOP(?) receipt0_.id as col_0_0_ from receipt receipt0_ where receipt0_.receipt_time=?' is the query.

http://stackoverflow.com/questions/29517508/how-to-persist-jsr-310-types-with-spring-data-jpa — Alan Hay, May 02 '17 at 08:19
I actually wrote my own converter, it didn't help. Will try with the one mentioned in link above — Zveratko, May 02 '17 at 08:33
define what "existsByReceiptTime" is generating in terms of JPQL. Also it is your JPA provider handling the query not Spring, so the stack trace from whichever JPA provider is used — Neil Stockton, May 02 '17 at 08:48
Here we are over 4 years later and I still have the same problem with spring boot 2.6.3, LocalTime columns cause errors in queries. Any new solution to this aside from writing a converter? — Martin, Feb 01 '22 at 23:56

score 2 · Accepted Answer · answered May 02 '17 at 14:29

The solution is to write custom attribute converter. It is probably Microsoft SQL server specific solution.

@Converter(autoApply = true)
public class LocalTimeConverter implements AttributeConverter<LocalTime, String> {

    @Override
    public String convertToDatabaseColumn(LocalTime time) {
       return time.toString();
    }

    @Override
    public LocalTime convertToEntityAttribute(String time) {
       return LocalTime.parse(time);
    }
}

LocalTime with spring-data-jpa and mssql

1 Answers1