Spring Boot Sqlite查询日期

Question

我有一个使用Sqlite数据库的树莓Pi温度记录器，它与我的嵌入式C Civetweb应用程序配合得很好。

我想我会有一些乐趣，尝试把一些东西与Spring靴连接起来。我可以进行一些查询，但几乎没有任何查询日期的方法。

我使用org.xerialsqlite jdbc3.36.0.3和com.enigmabridgehibernate4-sqlite方言0.1.2设置sqlite数据源。

在我的Pi上，返回数据并显示最后一天的温度图的主要查询是：

SELECT timestamp, temp, id FROM temps WHERE timestamp > datetime('now', 'localtime', '-1 days');

但这种类型的查询似乎不适用于JPA，因为查询天数在单引号字符串文本内，所以所有参数都按原样传递。如果我直接在查询中指定日期，它将返回数据，但我希望将其作为参数传递。

所以我尝试在Spring Boot中查询两个日期，如下所示：

LocalDate end = LocalDate.now();
LocalDate start = end.minusDays(2);

return temperatureRepository.findTemperaturesBetweenDates(start, end);

在存储库中使用此选项：

@Query("SELECT op FROM Temperature op WHERE timestamp between :startDate AND :endDate")
List<Temperature> findTemperaturesBetweenDates(@Param(value="startDate") LocalDate startDate, @Param(value="endDate") LocalDate endDate);

此查询不返回任何结果。同样，在查询中直接用单引号指定日期，而不使用参数，这样会返回数据。我也尝试了nativeQuery，但结果相同。

将Hibernate上的日志记录级别更改为trace，我可以看到正在使用的参数，它似乎正在将日期转换为纪元时间。

/* SELECT
    op 
FROM
    Temperature op 
WHERE
    timestamp between :startDate AND :endDate */ select
        temperatur0_.timestamp as timestam1_0_,
        temperatur0_.id as id2_0_,
        temperatur0_.temp as temp3_0_ 
    from
        temps temperatur0_ 
    where
        timestamp between ? and ? Hibernate: 
/* SELECT
    op 
FROM
    Temperature op 
WHERE
    timestamp between :startDate AND :endDate */ select
        temperatur0_.timestamp as timestam1_0_,
        temperatur0_.id as id2_0_,
        temperatur0_.temp as temp3_0_ 
    from
        temps temperatur0_ 
    where
        timestamp between ? and ?

2022-06-06 23:18:45.393 DEBUG 9328-[nio-8080-exec-4]o.s.j.d.DriverManager数据源：创建新的JDBC DriverManager连接到[JDBC:sqlite:src/main/resources/sqltemlog.db]2022-06-06 23:18:45.394 TRACE 9328-[nio-8080-exec-4]o.h.r.j.i.ResourceRegistryStandardImpl：注册语句[/*从温度op中选择op，其中时间戳介于：startDate和：endDate之间/选择Temperature0\u。时间戳为timestam1\u 0\u，Temperature0\u。id为id2\u 0\u，Temperature0\u。temp为temp3\u 0\u。从temps Temperature0\u，其中时间戳介于？和？参数=null之间]2022-06-06 23:18:45.394跟踪9328-[nio-8080-exec-4]o.h.e.jdbc。内部的JdbcCoordinatorImpl：注册上一条查询语句[/SELECT op FROM Temperature op WHERE timestamp between:startDate AND:endDate/SELECT temperature0\u。timestamp as timestam1\u 0\u，temperature0\u。id as id2\u 0\u，temperature0\u。temp as temp3\u 0\u，FROM temps temperature0\u，WHERE timestamp between？AND？
parameters=null]2022-06-06 23:18:45.394 TRACE 9328---[nio-8080-exec-4]o.h.type。描述符。sql。BasicBinder：绑定参数[1]为[日期]-[2022-06-04]2022-06-06 23:18:45.394跟踪9328-[nio-8080-exec-4]o.h.type。描述符。sql。BasicBinder：绑定参数[2]为[日期]-[2022-06-06]2022-06-06 23:18:45.394 TRACE 9328-[nio-8080-exec-4]org。冬眠装载机。Loader:Bound[3]parameters total 2022-06-06 23:18:45.394 TRACE 9328---[nio-8080-exec-4]o.h.r.j.i.ResourceRegistryStandardImpl：注册结果集[org.sqlite.jdbc4]。JDBC4ResultSet@23105e31]2022-06-06 23:18:45.394跟踪9328---[nio-8080-exec-4]组织。冬眠装载机。加载器：处理结果集2022-06-06 23:18:45.394跟踪9328---[nio-8080-exec-4]组织。冬眠装载机。加载器：完成处理结果集（0行）2022-06-06 23:18:45.394跟踪9328---[nio-8080-exec-4]组织。冬眠装载机。加载器：水合对象总数：0 2022-06-06 23:18:45.395跟踪9328---[nio-8080-exec-4]o.h.r.j.i.ResourceRegistryStandardImpl：释放语句[/从温度op中选择op，其中时间戳介于：startDate和：endDate之间/选择Temperature0\uTimestamp为timestam1\u0\u0，Temperature0\uID为id2\u 0\u0，Temperature0\uTemp为temp3\u 0\u0，其中时间戳介于？和？
参数=[1654297200000，1654470000000]]2022-06-06 23:18:45.395 TRACE 9328---[nio-8080-exec-4]o.h.r.j.i.ResourceRegistrate RyStandardImpl：关闭结果集[org.sqlite.jdbc4。JDBC4ResultSet@23105e31]2022-06-06 23:18:45.395跟踪9328-[nio-8080-exec-4]o.h.r.j.i.ResourceRegistryStandardImpl：关闭准备好的语句[/从温度op中选择op，其中时间戳介于：startDate和：endDate之间*/选择temperature0\uTimestamp作为timestam1\u0\u0，temperature0\uID作为id2\u0\u0，temperature0\uTemp作为temp3\u0\uTemps Tempperature0\u0\u0，其中时间戳介于？和？
参数=[1654297200000, 1654470000000]]

我设法让它工作的唯一方法是在查询中使用unixepoch，并在Sqlite中转换参数：

@Query(value =("select timestamp, id, temp from temps where timestamp between DATETIME(ROUND(:startDate / 1000), 'unixepoch') AND DATETIME(ROUND(:endDate / 1000), 'unixepoch')"), nativeQuery = true)
List<Temperature> findTemperaturesTest(@Param(value="startDate") LocalDate startDate, @Param(value="endDate") LocalDate endDate);

有什么想法如何让日期查询在Spring Boot和Sqlite中正常工作吗？谢谢

编辑：

好的，所以只是设法让-X天部分工作，但是我确实有其他查询，结合 /-天和日期，但仍然没有运气正确地将日期传递给查询。

@Query("SELECT op FROM Temperature op WHERE id between 1 and 4 AND timestamp > datetime('now', 'localtime', '-' || :days || ' days')")
List<Temperature> findTemperaturesMinusDays(@Param(value="days") int days);

Answer 1

对，这一切似乎都归结于Sqlite存储日期的方式。我的数据库一直将时间戳字段存储为字符串，如“2022-06-07 23:44:19”，大多数数据库将日期存储为unix时间。

没问题，我只告诉它在查询数据库时将LocalDateTime转换为字符串。嗯，也许没那么容易。我尝试了@Converter、@Type，还有一些我忘记的东西。这些都不起作用。

我的第一次半成功尝试是将类中的数据类型更改为String，并在getter中进行转换。但这意味着@Query参数也必须作为字符串传递。不太好。

因此，我的解决方案是一个自定义存储库，它将LocalDateTime转换为字符串，并将其设置为查询中的参数。

public class TemperatureRepositoryImpl implements TemperatureRepositoryCustom {
    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<Temperature> findTemperaturesBetweenDates(LocalDateTime startDate, LocalDateTime endDate) {
        Query query = entityManager.createNativeQuery("SELECT timestamp, temp, id FROM temps WHERE timestamp BETWEEN :startDate AND :endDate");

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String startDateString = startDate.format(formatter);
        String endDateString = endDate.format(formatter);

        query.setParameter("startDate", startDateString);
        query.setParameter("endDate", endDateString);

        return query.getResultList();
    }
}

编辑

差点受够了。有几个问题-接口存储库不起作用，只有实现，日期不包括时间。最后，使用TypedQuery：

    public List<Temperature> findTemperaturesBetweenDates(LocalDateTime startDate, LocalDateTime endDate) {
        TypedQuery<Temperature> query = entityManager.createQuery("SELECT op FROM Temperature op WHERE timestamp BETWEEN date(:startDate) AND date(:endDate)", Temperature.class);
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String startDateString = startDate.format(formatter);
        String endDateString = endDate.format(formatter);

        query.setParameter("startDate", startDateString);
        query.setParameter("endDate", endDateString);

        return query.getResultList();
    }