Spring Boot整合Sqlite时JPA日期参数查询失效问题求助

• 你当前使用的com.enigmabridge:hibernate4-sqlite-dialect为长期未维护的过时版本，未适配Java 8引入的LocalDate/LocalDateTime等JSR-310时间类型，默认将所有Java时间类型参数序列化为epoch毫秒时间戳，和你Sqlite库中存储的YYYY-MM-DD HH:MM:SS格式字符串时间完全不匹配，是查询无结果的核心原因。
• Sqlite本身无原生日期字段类型，时间值以字符串/整型/浮点型三种亲和格式存储，旧方言未针对该特性做参数绑定适配，无法自动完成Java时间类型到Sqlite时间字符串的转换。
• 你之前实现的天数动态传参写法（用||拼接修饰符字符串）是符合Sqlite语法的正确实现，无需修改。

方案1：替换为维护中的Sqlite Hibernate方言（推荐） #

第一步：替换依赖 #

移除旧的hibernate4-sqlite-dialect依赖，引入目前Hibernate社区官方维护的Sqlite方言，同时升级sqlite-jdbc到稳定版本：

<!-- pom.xml 依赖示例 -->
<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.42.0.1</version>
</dependency>
<dependency>
    <groupId>org.hibernate.orm</groupId>
    <artifactId>hibernate-community-dialects</artifactId>
    <version>6.2.7.Final</version> <!-- 和你项目使用的Hibernate版本保持一致即可 -->
</dependency>

如果你项目使用的是Hibernate 5.x版本，替换社区方言依赖为org.hibernate:hibernate-sqlite-dialect:5.6.15.Final即可，方言类路径与配置方式不变。

第二步：修改数据源配置 #

在Spring Boot配置文件中指定正确的方言，同时配置时区和你树莓派系统时区一致，避免时间偏移：

# application.yml 配置示例
spring:
  jpa:
    database-platform: org.hibernate.community.dialect.SQLiteDialect
    properties:
      hibernate:
        jdbc:
          time_zone: Asia/Shanghai # 替换为你实际使用的时区，和原C++应用保持一致

第三步：修正实体类字段映射 #

确保Temperature实体类中timestamp字段的类型和映射正确，由于你库中存储的是带时分秒的时间字符串，字段不要用LocalDate类型，改用LocalDateTime：

@Entity
@Table(name = "temps")
public class Temperature {
    @Id
    private Long id;
    // 字段映射为字符串类型的时间，对应Sqlite中存储的datetime格式字符串
    @Column(columnDefinition = "TEXT")
    private LocalDateTime timestamp;
    private Double temp;
    // 省略getter/setter
}

第四步：简化Repository查询写法 #

修正后无需手动做时间戳转换，直接传参即可正常查询，注意区间查询时结束时间要传当日最后一秒，否则会漏掉结束日期当天的所有数据：

// 业务层调用
LocalDateTime end = LocalDateTime.now().with(LocalTime.MAX);
LocalDateTime start = end.minusDays(2).with(LocalTime.MIN);
return temperatureRepository.findTemperaturesBetweenDates(start, end);

// Repository层写法
@Query("SELECT op FROM Temperature op WHERE op.timestamp between :startDate AND :endDate")
List<Temperature> findTemperaturesBetweenDates(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);

// 动态天数偏移查询可以直接保留你之前的正确写法
@Query("SELECT op FROM Temperature op WHERE op.id between 1 and 4 AND op.timestamp > datetime('now', 'localtime', '-' || :days || ' days')")
List<Temperature> findTemperaturesMinusDays(@Param("days") int days);

方案2：不替换依赖的临时适配方案 #

如果暂时不想升级依赖，可以自定义JPA属性转换器，将Java时间类型自动转为Sqlite识别的时间字符串，避免Hibernate将其序列化为毫秒时间戳：

1. 编写时间类型转换器：

@Converter(autoApply = true)
public class LocalDateTimeConverter implements AttributeConverter<LocalDateTime, String> {
    private static final DateTimeFormatter FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    @Override
    public String convertToDatabaseColumn(LocalDateTime attribute) {
        return attribute == null ? null : attribute.format(FORMATTER);
    }

    @Override
    public LocalDateTime convertToEntityAttribute(String dbData) {
        return dbData == null ? null : LocalDateTime.parse(dbData, FORMATTER);
    }
}

2. 实体类中timestamp字段使用LocalDateTime类型，转换器会自动完成参数绑定和结果映射，无需手动在SQL中写unixepoch转换逻辑。

• 不要用LocalDate类型直接和带时分秒的timestamp字段做比较，LocalDate默认只会映射为当天0点的时间值，会导致查询范围不符合预期。
• 如果使用原生SQL查询，尽量避免硬编码时间格式，和JPA查询保持相同的参数类型即可。

内容的提问来源于stack exchange，提问作者ChrisHigs