CODEWITHSUNDEEP
javadatabasespringjdbc
Monika Michael
Monika Michael

Reputation: 1274

How to pass multiple named parameters to Spring JdbcTemplate?

I have following code in a Spring Dao which works just fine - 

Object args[] = { userId, restaurantId };
int userOrderCount = getJdbcTemplate()
    .queryForInt(
         "SELECT COUNT(orderid) FROM orders WHERE useridfk_order = ? AND restaurantidfk_order = ?", 
         args
    );

However if I decide to use NamedParameters for my query as follows - 

int userOrderCount = getNamedParameterJdbcTemplate()
    .queryForInt(
         "SELECT COUNT(orderid) FROM orders WHERE useridfk_order = :userId AND restaurantidfk_order = :restaurantId", 
         new MapSqlParameterSource(":restaurantId", restaurantId)
             .addValue(":userId", userId)
    );

I am getting this exception - 

org.springframework.dao.InvalidDataAccessApiUsageException: No value supplied for the SQL parameter 'userId': No value registered for key 'userId'.

I know the golden adage "Don't fix it if it ain't broken".

But still, I can't help but wonder why this is happening?

Upvotes: 11

Views: 42376

Answers (1)

erimerturk
erimerturk

Reputation: 4288

use this.

new MapSqlParameterSource("restaurantId", restaurantId)
    .addValue("userId", userId);

instead of this.

new MapSqlParameterSource(":restaurantId", restaurantId)
    .addValue(":userId", userId);

Upvotes: 33

Related Questions