Skip to content
Advertisement

Why can ‘CURRENT_DATE’ not as a parameter when using NamedParameterJdbcTemplate in JAVA?

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.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement