Spring Data JPA -从字符串转换日期和/或时间时转换失败
我使用的是Spring Boot和Spring JPA。我有一个带有自定义@Query方法的存储库类。
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;
....
}每当我尝试运行query方法时：
@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: Conversion failed when converting date and/or time from character string.
我有点困惑。这似乎是一个格式问题(数据库管理系统想要一个'yyyy-MM-dd‘或类似的格式)，但我认为Java的LocalDate会被表达/转换成数据库系统可以解析的格式。
任何建议都将不胜感激。谢谢。
编辑：
作为参考，基本故障是参数(?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);
}回答 2
Stack Overflow用户
发布于 2018-06-19 03:56:49
如果您使用的JPA版本低于2.2，它不直接支持从SqlDate转换Java8 LocalDate & LocalTime，因为它是在Java8之前发布的。您必须通过实现JPA AttributeConverter接口来编写自定义日期转换器。然后，您可以为所有实体类对其进行autoApply，也可以有选择地将其注释为LocalDate实体字段。
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();
}
}在任何实体字段为LocalDate的情况下，autoApply = true都会自动关联这个转换器。
或者，如果选择autoApply = false，则必须通过注释将转换器类与字段相关联。
@Column(name = "CommentDate")
@Convert(converter = LocalDateConverter.class)
private LocalDate commentDate;Stack Overflow用户
发布于 2018-06-19 03:55:30
请看一下：How to persist JSR-310 types with Spring Data JPA?
根据您使用的框架版本，您必须向您的项目添加额外的配置，以便在您的实体中使用LocalDate等JSR-310类型。
https://stackoverflow.com/questions/50916404
复制相似问题
腾讯云开发者
