java - char 列中的 Spring Boot 存储库日期

Question

我正在使用旧系统，其中日期以 dd.MM.yyyy 格式存储在 char(10) 列中。是否可以在 String 和 LocalDate/Date 之间透明地转换以编写像这样的查询

Iterable<User> findByRegistrationDateBefore(LocalDate deadline)

我尝试将自定义 @Query 与 JPQL 结合使用，但我总是使用依赖于数据库的函数，这不太好。

@Query("select u from User u where DATE(u.registrationDate) < DATE(:deadline)")
Iterable<User> customFind(@Param("deadline") LocalDate deadline);

这应该适用于 MySQL，但由于我使用 H2，所以它不适用于两者。有没有办法/解决方法来存档这个？我想在数据库中按日期搜索，但无法加载所有条目并在 java 中搜索。

import java.util.Date;

import javax.persistence.*;

import org.springframework.format.annotation.DateTimeFormat;

import com.example.datecharcolumn.DateFormatConverter;

import lombok.NoArgsConstructor;
import lombok.ToString;

@NoArgsConstructor
@Entity
@Table(name = "user")
@ToString
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "ID", nullable = false, precision = 5)
    private Integer userId;

    @Convert(converter = DateFormatConverter.class)
    @Column(name ="REGDAT", nullable = false, length = 10, columnDefinition = "char(10)")
    private Date registrationDate;
}

@Repository
public interface UserRepository extends CrudRepository<User, Integer> {
    Iterable<User> findByRegistrationDateAfter(Date d);
}

@SpringBootApplication
public class DateCharcolumnApplication {

    public static void main(String[] args) {
        SpringApplication.run(DateCharcolumnApplication.class, args);
    }

}

@Converter
public class DateFormatConverter implements AttributeConverter<Date, String> {

    private SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd.MM.yyyy");

    @Override
    public String convertToDatabaseColumn(Date attribute) {
        final String format = simpleDateFormat.format(attribute);
        return format;
    }

    @Override
    public Date convertToEntityAttribute(String dbData) {
        try {
            Date parse = simpleDateFormat.parse(dbData);
            return parse;
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return new Date();
    }
}

我的想法是，JPQL 足够智能，可以支持日期函数的表达，并将它们转换为相应的数据库方言(或者有办法让它变得智能)。但目前(在 h2 上)它仅绑定(bind)到 VARCHAR 并进行字符串比较(假设这不是适用于日期的特殊 H2 语法):

select user0_.id as id1_0_, user0_.regdat as regdat2_0_ from user user0_ where user0_.regdat>?
Hibernate: select user0_.id as id1_0_, user0_.regdat as regdat2_0_ from user user0_ where user0_.regdat>?
2020-05-11 17:00:38.877 TRACE 4288 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [20.07.2020]

Answer 1

My idea was that JPQL is smart enough to support the expression of date functions and converts them to the corresponding database dialect

JPA 无法创造奇迹。没有明确定义或“明显”的方法将 Date 转换为 VARCHAR。许多数据库只是简单地定义一个日期来简化日期规范，但这取决于数据库，至少在某些情况下取决于配置和当前日期，因此不太适合 JPA 使用。

在数据库中存储 Date 实例的正确方法是在数据库中使用 DATE 类型。然后转换也会发生。当然，您仍然必须确保时区都处理正确。

如果您想坚持使用 VARCHAR，您可以使用 SpEL expression在带注释的查询中进行转换。

您没有询问这一点，但由于您使用 H2 和 MySQL，我假设您使用 H2 进行测试。不要那样做。请改用测试容器。不同的数据库引擎之间存在细微的差异，您希望使用用于生产的相同数据库进行测试。