Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

passing different types of arguments to jdbctemplate query

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"
}
like image 758
Suleman khan Avatar asked Dec 26 '14 23:12

Suleman khan


People also ask

Which JdbcTemplate execute parameterized query method?

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.

How does JdbcTemplate select multiple columns?

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.

What is JdbcTemplate queryForObject?

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.


1 Answers

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

like image 186
Michal Avatar answered Sep 20 '22 18:09

Michal