Best Practices for Passing Different Types of Arguments to JDBC Template Query

Introduction

In Spring framework, the JDBC Template provides a convenient way to interact with databases. A common challenge is passing different types of arguments to queries, especially when using prepared statements. This article addresses this issue by analyzing a typical error and presenting best practices.

Error Analysis

The user attempted to retrieve records using a where clause with parameters, but encountered an exception: "Invalid argument value: java.io.NotSerializableException". This error often occurs due to incorrect parameter mapping or the use of an inappropriate JDBC template type.

Solution: Using NamedParameterJdbcTemplate

For named parameters, use NamedParameterJdbcTemplate with parameter names designated by colon (e.g., :breedId). Here's the corrected code:

public List<Dog> listByBreedIdAndGender(long breedId, String gender) {  
   return jdbcTemplate.query("SELECT * FROM dog_entity WHERE breed__id = :breedId AND gender = :gender", 
      new MapSqlParameterSource()
      .addValue("breedId", breedId)
      .addValue("gender", gender));
}

Ensure that the jdbcTemplate is an instance of NamedParameterJdbcTemplate.

Solution: Using JdbcTemplate

If using the standard JdbcTemplate, positional parameters with question marks (?) are used. Pass parameters as an Object array:

public List<Dog> listByBreedIdAndGender(long breedId, String gender) {  
   return jdbcTemplate.query("SELECT * FROM dog_entity WHERE breed__id = ? AND gender = ?",
      new Object[] { breedId, gender }, 
      new DogRowMapper());         
}

Alternatively, if a private query method is preferred:

public List<Dog> listByBreedIdAndGender(long breedId, String gender) {  
   return query("SELECT * FROM dog_entity WHERE breed__id = ? AND gender = ?",
      new Object[] { breedId, gender });         
}

private List<Dog> query(String sql, Object[] parameters) {  
   List<Dog> dogs = jdbcTemplate.query(sql, parameters, new DogRowMapper());
   return dogs;
}

Conclusion

To pass different types of arguments to JDBC Template queries, choose between NamedParameterJdbcTemplate for named parameters or JdbcTemplate for positional parameters. Always ensure correct parameter mapping and use the appropriate template type to avoid exceptions.