CODEWITHSUNDEEP
javaspringjdbcspring-mvc
Jaanus
Jaanus

Reputation: 16561

Java Spring JDBC template problem

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???

Upvotes: 3

Views: 4913

Answers (3)

J&#246;rn Horstmann
J&#246;rn Horstmann

Reputation: 34044

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 + ?

Upvotes: 6

sbridges
sbridges

Reputation: 25150

The error is saying that you only have 1 param (ie a ?) in the first sql statement, but you are passing in two args. Spring doesn't know what to do with the second arg.

Upvotes: 0

BalusC
BalusC

Reputation: 1109635

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!)

Upvotes: 5

Related Questions