Spring Data JPA - 从字符串转换日期和/或时间时转换失败 [英] Spring Data JPA - Conversion failed when converting date and/or time from character string

问题描述

我正在使用 Spring Boot 和 Spring JPA.我有一个带有自定义 @Query 方法的存储库类.

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

MarketForceComment 类如下(部分):

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

我收到以下错误:<代码>com.microsoft.sqlserver.jdbc.SQLServerException:从字符串转换日期和/或时间时转换失败.

我有点困惑.这似乎是一个格式问题(DBMS 需要yyyy-MM-dd"或类似格式)，但我认为 Java 的 LocalDate 将被表达/转换为可由数据库系统解析的格式.

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.

作为参考，底层错误是参数(?1，?2，?3)的顺序:

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

推荐答案

如果您使用的 JPA 版本低于 2.2，它不直接支持 Java8 LocalDate &SqlDate 的 LocalTime 转换，因为它是在 Java8 之前发布的.您必须通过实现 JPA AttributeConverter 接口来编写自定义日期转换器.然后，您可以为所有实体类autoApply 它，或者您可以有选择地将其注释到 LocalDate 实体字段.

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 将自动关联此转换器，无论实体字段为 LocalDate.

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

或者如果您选择autoApply = false，您将必须通过注释将转换器类与字段相关联.

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;

参考 JPA 2.2 发布规范

这篇关于Spring Data JPA - 从字符串转换日期和/或时间时转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！