英文:
Why can 'CURRENT_DATE' not as a parameter when using NamedParameterJdbcTemplate in JAVA?
问题
在mysql中,我使用以下SQL语句并且它运行良好。
select * from student where CREATE_TIME>=DATE_SUB(curdate(),INTERVAL 24 HOUR)
现在我想将日期作为参数使用,所以我使用了NamedParameterJdbcTemplate
。如果我传递日期如'2020-05-30',它也能正常运行。但是当我传递'CURRENT_DATE'或'curdate()'时,它无法返回任何结果。如何修改我的代码?
NamedParameterJdbcTemplate nameJdbc = new NamedParameterJdbcTemplate(jdbcTemplate);
Map<String,Object> paramMap = new HashMap<String, Object>();
// 对于'2020-05-30'可以,'CURRENT_DATE'不行;
paramMap.put("dateStr",dateStr);
String sql = "SELECT *"
+ " FROM student where CREATE_TIME>=DATE_SUB(:dateStr,INTERVAL 24 HOUR)";
return nameJdbc.query(sql,paramMap,
new BeanPropertyRowMapper<>(Student.class));
英文:
In mysql,I use this sql and it runs well.
select * from student where CREATE_TIME>=DATE_SUB(curdate(),INTERVAL 24 HOUR)
Now I want to use the date as a parameter,SO I use NamedParameterJdbcTemplate
,If I pass the date like '2020-05-30',it also runs well.But when I pass 'CURRENT_DATE' or 'curdate()',it could not search any result.How to change my code?
NamedParameterJdbcTemplate nameJdbc = new NamedParameterJdbcTemplate(jdbcTemplate);
Map<String,Object> paramMap = new HashMap<String, Object>();
//dateStr when '2020-05-30' is ok,'CURRENT_DATE' is not ok;
paramMap.put("dateStr",dateStr);
String sql = "SELECT *"
+ " FROM student where CREATE_TIME>=DATE_SUB(:dateStr,INTERVAL 24 HOUR)" ;
return nameJdbc.query(sql,paramMap,
new BeanPropertyRowMapper<>(Student.class));
答案1
得分: 1
不起作用,因为CURRENT_DATE
或类似的函数调用被插入为字符串参数,而不是关键字。
你可以做的一件事是,如果参数为空,手动将SQL字符串中的:dateStr
替换为CURRENT_DATE
,否则将其值放入参数映射中。
一个更好的选择是,如果参数为空,只需使用LocalDate.now().toString()
来设置日期字符串,并始终在SQL语句中设置它。
英文:
It doesn't work, because the CURRENT_DATE
, or an equivalent function call is being inserted as a string parameter, and not as a keyword.
One thing you could do is to manually replace the :dateStr
in the SQL string with CURRENT_DATE
if the parameter is empty, otherwise put its value in the parameter map.
A better option would be to just use LocalDate.now().toString()
to set the date string, if the parameter is empty, and always set it in the SQL statement.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论