android - 在 @Query 的引用部分使用变量

Question

尝试使用 sqlite 的日期函数编写查询，但无法识别要识别的变量。

我是这样写的:

@Query("SELECT * FROM shifts WHERE startDate >= :startDate AND startDate < date(:startDate, + ':days day') AND jobId = :jobId")
LiveData<List<Shift>> loadDateRange(LocalDate startDate,@IntRange(from = 1) int days,@IntRange(from = 0) int jobId);

我试过用几种不同的方式转义 '，但没有成功。

构建只返回Unused parameter days。

Answer 1

你不妨试试

@Query("WITH dayscte AS (SELECT '+'|| :days ||' days')" +
    "SELECT * FROM shifts WHERE startDate >= :startDate AND startDate < date(:startDate,(SELECT * FROM ctedays)) AND jobId = :jobId")

或者最好是:-

@Query("SELECT * FROM shifts WHERE startDate >= :startDate AND startDate < date(:startDate,(SELECT '+'|| :days ||' days')) AND jobId = :jobId")

使用上述的变体(为方便起见进行了简化)即

@Query("WITH dayscte AS (SELECT '+'||:days ||' days')" + " SELECT * FROM shifts WHERE startDate >= :startDate AND startDate < date(:startDate,(SELECT * FROM dayscte)) AND jobId = :jobId")
Shifts getShiftsTest(String days, String startDate, int jobId);

使用:-

    long jobId = dictionaryDao.insertShifts(new Shifts("2019-01-01"));
    Shifts s = dictionaryDao.getShiftsTest("0","2019-01-01",1);

将预期的 s 返回为 null(即没有提取行但查询已成功运行)。

通过添加带有子查询而不是 CTE 的缩短版本来测试返回实际值:-

@Query("SELECT * FROM shifts WHERE startDate <= date(:startDate,(SELECT '+'||:days||' days')) AND jobId = :jobId")
Shifts getShiftsTest3(String days, String startDate, int jobId);

和:-

    long jobId = dictionaryDao.insertShifts(new Shifts("2019-01-01"));
    Shifts s1 = dictionaryDao.getShiftsTest("0","2019-01-01",1);
    Shifts s2 = dictionaryDao.getShiftsTest3("0","2019-01-01",1);

结果(在 之后添加了一个断点)产生(如预期的那样):-