Java Spring JDBC template problem

Question

public List<Weather> getWeather(int cityId, int days) {
    logger.info("days: " + days);
    return getSimpleJdbcTemplate().query("SELECT weather.id, cities.name, weather.date, weather.degree " +
                                        "FROM weather JOIN cities ON weather.city_id = cities.id " +
                                        "WHERE weather.city_id = ? AND weather.date BETWEEN now()::date AND (now() + '? days')::date",
                                        this.w_mapper, cityId, days);
}

error :

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [SELECT weather.id, cities.name, weather.date, weather.degree FROM weather JOIN cities ON weather.city_id = cities.id WHERE weather.city_id = ? AND weather.date BETWEEN now()::date AND (now() + '? days')::date]; The column index is out of range: 2, number of columns: 1.; nested exception is org.postgresql.util.PSQLException: The column index is out of range: 2, number of columns: 1.

it works with :

public List<Weather> getWeather(int cityId, int days) {
    logger.info("days: " + days);
    return getSimpleJdbcTemplate().query("SELECT weather.id, cities.name, weather.date, weather.degree " +
                                        "FROM weather JOIN cities ON weather.city_id = cities.id " +
                                        "WHERE weather.city_id = ? AND weather.date = now()::date",
                                        this.w_mapper, cityId);
}

so the problem is when im using two ? marks in my query. how can i make it work to with 2 ? marks???

Answer 1

The problem is probably in this part:

'? days'

The question mark is inside a literal string and so it is not recognized by the sql parser. You could try to rewrite it using the string concatenation operator, although I'm not 100% sure that is valid syntax in this case.

According to this page on the postgres wiki you should be able to simply omit the string 'days', since adding a date and an integer is interpreted as adding the specified number of days.

BETWEEN now()::date AND now()::date + ?

Answer 2

Rewrite the SQL part

AND weather.date BETWEEN now()::date AND (now() + '? days')::date

as

AND weather.date BETWEEN now()::date AND ?

and set it with a fullworthy java.sql.Date value instead of days.

Calendar calendar = Calendar.getInstance();
calendar.add(Calendar.DATE, days);
Date endDate = new Date(calendar.getTimeInMillis());
// ...

(once again, it's java.sql.Date, not java.util.Date!)