Java Spring JDBC模板问题

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);
}

错误：

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.

它适用于：

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);
}

所以问题是当我使用两个时？在我的查询中标记。 我怎样才能让它与 2 一起工作？标记？？？

Answer 1

问题可能出在这部分：

'? days'

问号位于文字字符串内，因此 sql 解析器无法识别它。 您可以尝试使用字符串连接运算符重写它，尽管我不能 100% 确定在这种情况下这是有效的语法。

根据 postgres wiki 上的此页面 您应该能够简单地省略字符串“days”，因为添加日期和整数会被解释为添加指定的天数。

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

Answer 2

将 SQL 部分重写

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

为

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

，并使用完整的 java.sql.Date 值而不是 days 设置它。

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

（再次强调，它是 java.sql.Date，而不是 java.util.Date！）

Answer 3

该错误表明您在第一个 sql 语句中只有 1 个参数（即？），但您传递了两个参数。 Spring 不知道如何处理第二个参数。