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));
Advertisement
Answer
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.