I am trying to retrieve records from the database by using where clause with few different types of arguments. This is the simple method which I wrote where I am passing breedId and gender as an arguments.
public List<Dog> listByBreedIdAndGender(long breedId, String gender) {
return query("SELECT * FROM dog_entity WHERE breed__id = ? AND gender = ?",
new MapSqlParameterSource(":breedId", breedId)
.addValue(":gender", gender));
}
private List<Dog> query(String sql, MapSqlParameterSource parameters) {
List<Dog> dogs = jdbcTemplate.query(sql, new DogRowMapper(), parameters);
return dogs;
}
I ran this method but got below exception. Can anyone let me know how to pass multiple arguments to the jdbcTemplate.query(), I am kinda new to it.
{
timestamp: 1419637479460
status: 500
error: "Internal Server Error"
exception: "org.springframework.dao.TransientDataAccessResourceException"
message: "PreparedStatementCallback; SQL [SELECT * FROM dog_entity WHERE breed__id = ? AND gender = ?]; Invalid argument value: java.io.NotSerializableException; nested exception is java.sql.SQLException: Invalid argument value: java.io.NotSerializableException"
path: "/api/2/m"
}
We can execute parameterized query using Spring JdbcTemplate by the help of execute() method of JdbcTemplate class. To use parameterized query, we pass the instance of PreparedStatementCallback in the execute method.
List<Map<String, Object>> rows = jdbcTemplate. queryForList("SELECT name, middle, family FROM table"); Every Map in this List represents a row in the returned query, the key represents the column name, and the value is the value of that column for that row.
queryForObject() is a method provided by JdbcTemplate , which is used to fetch the single record from the database. queryForObject() takes 3 parameters, sql_query , row_mapper and parameter.
Please use
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));
}
Please make sure the jdbcTemplate is NamedParameterJdbcTemplate .
If you need to use JdbcTemplate then
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());
}
or if you insist on usage of the private query method
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;
}
Please make sure breed__id has correct number of _ characters.
The concept is to either use NamedParameterJdbcTemplate with parameters designated by :name (such as :gender) or simple JdbcTemplate with positional parameters (such as new Object[] { breedId, gender } where breedId matches the first ? and the gender the second ?).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With