Spring Data JPA - Conversion failed when converting date and/or time from character string

Question

I am using Spring Boot and Spring JPA. I have a repository class with custom @Query methods.

public interface MarketForceRepository extends CrudRepository<MarketForceComment, Integer> {

    @Query("SELECT c FROM MarketForceComment c WHERE c.property.id = ?1 and c.commentDate >= '?1' AND  c.commentDate <= '?2'")
    List<MarketForceComment> getByPropAndDate(Integer propID, LocalDate start, LocalDate end);

    @Query("SELECT c FROM MarketForceComment c WHERE c.property.id IN ?1 and c.commentDate >= '?2' AND c.commentDate <= '?3'")
    List<MarketForceComment> getByPropsAndDates(List<Integer> propIDs, LocalDate start, LocalDate end);
}

The MarketForceComment class follows (in part):

@Table(name = "MarketForceComment", schema = "dmb")
@Entity
public class MarketForceComment {
    @ManyToOne
    @JoinColumn(name = "PropertyID")
    private Property property;

    @Column(name = "CommentDate")
    private LocalDate commentDate;

    @Column(name = "Level")
    private int level;

    @Column(name = "Subject")
    private String subject;

    @Column(name = "Details", columnDefinition = "text")
    private String details;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID")
    private Integer id;  

    .... 
}

Whenever I attempt to run my query method:

@Override
public List<CommentDTO> getCommentsByStore(int storeID, LocalDate date) {
    List<CommentDTO> dtoList = new ArrayList<>();
    marketRepo.getByPropAndDate(storeID, date.plusMonths(1), date)
            .forEach(c -> dtoList.add(mapper.map(c, CommentDTO.class)));
    guestRepo.getByPropAndDate(storeID, date.plusMonths(1).atStartOfDay(), date.atStartOfDay())
            .forEach(c -> dtoList.add(mapper.map(c, CommentDTO.class)));
    return dtoList;
}

I receive the following error: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.

I am a bit perplexed. It appears to be a format issue (the DBMS wants a 'yyyy-MM-dd' or similar format), yet I thought Java's LocalDate would be expressed/converted into a format that could be parsed by database system.

Any advice would be appreciated. Thanks.

EDIT:

For reference, the underlying fault was the order of the parameters (?1, ?2, ?3):

public interface MarketForceRepository extends CrudRepository<MarketForceComment, Integer> {

    @Query("SELECT c FROM MarketForceComment c WHERE c.property.id = ?1 and c.commentDate >= ?2 AND  c.commentDate <= ?3")
    List<MarketForceComment> getByPropAndDate(Integer propID, LocalDate start, LocalDate end);

    @Query("SELECT c FROM MarketForceComment c WHERE c.property.id IN ?1 and c.commentDate >= ?2 AND c.commentDate <= ?3")
    List<MarketForceComment> getByPropsAndDates(List<Integer> propIDs, LocalDate start, LocalDate end);
}

Answer 1

If you are using JPA version lower than 2.2, it doesn't directly support Java8 LocalDate & LocalTime conversion from SqlDate, as it was released before Java8. You will have to write your custom date converter by implementing JPA AttributeConverter interface. Then either you can autoApply it for all entity classes or you can selectively annotate it to LocalDate entity field.

import java.sql.Date;
import java.time.LocalDate;
import javax.persistence.AttributeConverter;
import javax.persistence.Converter;

@Converter(autoApply = true)
public class LocalDateConverter implements AttributeConverter<LocalDate, Date> {

    @Override
    public Date convertToDatabaseColumn(LocalDate date) {        
        return Date.valueOf(date);
    }

    @Override
    public LocalDate convertToEntityAttribute(Date value) {
        return value.toLocalDate();
    }
}

With autoApply = true JPA will automatically associate this converter whereaever Entity fields are LocalDate.

Or if you choose autoApply = false, you will have to associate converter class via annotation with the field.

@Column(name = "CommentDate")
@Convert(converter = LocalDateConverter.class)
private LocalDate commentDate;

Refer JPA 2.2 release spec

Answer 2

Please have a look at: How to persist JSR-310 types with Spring Data JPA?

Depending on the framework versions you use, you have to add additional configuration to your project to use JSR-310 types like LocalDate in your entities.

Answer 3

Either use converter or column definition

for example

@Column(name = "local_time", columnDefinition = "TIME")
private LocalTime localTime;

@Column(name = "local_date", columnDefinition = "DATE")
private LocalDate localDate;

@Column(name = "local_date_time", columnDefinition = "TIMESTAMP")
private LocalDateTime localDateTime;

using converter

@Convert(converter = LocalDateConverter.class)
@Column(name = "createdDate")
private LocalDate createdDate;