How to pass interval parameter into sql request using namedparameterjdbctemplate java

Question

I am dealing with NamedParameterJdbcTemplate in java. I'm trying through NamedParameterJdbcTemplate to pass the interval parameter (3 month) to the sql query DELETE FROM message WHERE dt_log <NOW () - INTERVAL ': period'; I pass in DAO a period with type String for the interval parameter and put it in mapSqlParameterSource.addValue ("period", period); When executing the request, it throws the following error: org.postgresql.util.PSQLException: ERROR: invalid input syntax for type interval: ': period', although I am passing the correct value "3 month" (checked the whole request in pgAdmin). I tried to pass the PGInterval object to the parameter in the same way, but it gives the same error. But the request itself will work if you do not insert the parameter, but simply write the request itself with an interval: '3 month' by hand and it works if you insert a link to a static value with the String type in which "3 month" is specified in the sql request. The DAO class code is given below.

private final String DELETE_OLD_ROWS = "DELETE FROM mood.message WHERE dt_log <NOW() - INTERVAL ':period';";

@Override
public void delete_old_records(String period) {
    MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
    mapSqlParameterSource.addValue("period", period);
    this.getNamedParameterJdbcTemplate().update(this.DELETE_OLD_ROWS, mapSqlParameterSource);
}

Maybe someone can tell me what I'm doing wrong?)

Answer 1

I had the same issue. And this is working to me, where :month is the number of month

"DELETE FROM mood.message WHERE dt_log <NOW() - :month * '1 month'::interval;"

Answer 2

The root cause of your issue is the fact that in Oracle SQL we can't bind a value to the interval parameter.

Instead, you can take a look at the NUMTODSINTERVAL NUMTOYMINTERVAL methods. They can be leveraged for this.

Interval specification you have in your code

DELETE FROM mood.message WHERE dt_log <NOW() - INTERVAL ':period';

Replace with NUMTODSINTERVAL or NUMTOYMINTERVAL.

DELETE FROM mood.message WHERE dt_log <NOW() - NUMTOYMINTERVAL(:period, 'MONTH');