在我非常简单的查询中查找日期范围的存款对象:
String sqlQuery ="select d from Deposit d where status in('PENDING', 'SKIPPED') and d.depositDate <= '${endDateString}'"
def allUnprocessedDeposits = Deposit.executeQuery(sqlQuery)
不返回depositDate等于endDateString的行。(?!?)例如,如果我将所有d.depositDate行更新为相同的日期,并将该日期提供为endDateString,则不会返回任何行。
使用grails 2.0.3和MySql 5.1…
感谢任何有答案的人。这看起来很简单,但却令人恼火地失败了。
当您只给DateTime字段一天时,mySQL假设为午夜,因此任何时间在午夜之后的内容都不会包含在您的结果中。你最好说date<[结束日期+1]
根据您提供的信息,最有可能的解释是depositDate
列被定义为DATETIME或TIMESTAMP。请注意,除了日期之外,这些数据类型还存储时间值(分辨率低至一秒)。例如
'2012-07-20 11:35:46'
DATETIME值的比较所以DATE文字(没有时间成分),例如
'2012-07-20 11:35:46' <= '2012-07-20'
相当于与时间值为午夜的DATETIME文字进行比较:
'2012-07-20 11:35:46' <= '2012-07-20 00:00:00'
这显然会返回FALSE。这就是为什么您的查询没有返回您期望的行的最可能的解释
建议修复:
对于DATETIME和TIMESTAMP列上的谓词,获取"一天"的正常模式是从给定一天的午夜到第二天的午夜进行范围扫描。我们要检查的是该列是否小于第二天的午夜:
'2012-07-20 11:35:46' < DATE_ADD('2012-07-20', INTERVAL 1 DAY)
这相当于与21日午夜相比。
'2012-07-20 11:35:46' < '2012-07-21 00:00:00'
在您的情况下,由于您已经有了"结束日期"值,最简单的更改就是简单地更改查询文本。
只需在传入的日期值上添加一天,然后将比较运算符从<=
更改为<
。(我假设您只提供日期部分,并允许时间部分默认为午夜。)
... d.depositDate < DATE_ADD('${endDateString}',INTERVAL 1 DAY)"
我们更喜欢这种模式,因为它在DATE
、DATETIME
和TIMESTAMP
上同样有效。
(注意:我们更喜欢这种模式的另一个原因是,它适用于分辨率更高的"时间点"值(例如,Microsoft SQL Server DATETIME,其精度低至3毫秒,检查<=23:59.59最终是不够的。)
您的代码可以确保参数值仅为日期,或者是时间成分为午夜的日期,但通过将参数包装在CAST( AS DATE)
中,SQL查询很容易做到这一点
注意:您希望避免在任何函数中包装列引用d.depositDate
,因为这将禁用MySQL执行索引范围扫描操作(以满足谓词)的能力。
注意:关于SQL注入漏洞的所有常见警告都适用于此,如果参数值由用户提供,则需要使用绑定参数或转义提供的。。。。值
考虑一下当恶意用户提供以下值时会发生什么:
2012-07-20'; DELETE FROM Deposit ; SELECT '1
考虑将哪些语句传递到数据库。有几种方法可以解决这一问题,挫败此类攻击。
BTW,您应该使用带参数的查询,它更安全:
String sqlQuery ="select d from Deposit d where status in('PENDING', 'SKIPPED') and d.depositDate <= :endDate"
def allUnprocessedDeposits = Deposit.executeQuery(sqlQuery,[endDate:endDateVariable])