passing different types of arguments to jdbctemplate query

Question

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"
}

Solution

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

Comments

[Suleman khan]

Still getting the same error. Do I need to make any changes to my private method also ?

[Suleman khan]

I am using this import import org.springframework.jdbc.core.JdbcTemplate;.

[Michal]

So use the one I recommend.

[Suleman khan]

Thanks for your quick response! I am actually having some more methods which will be having more different types of arguments that's what I am that private method so that I don't have repeat the same thing over and over. is there anyway I just have to modify in the private call it where ever I want with whatever arguments.

[Michal]

Sorry that's off topic but for me is your private method almost useless. If you do call the private method you save the instance variable jdbcTemplate and the fest-wired new DogRowMapper. That does not help you much, neither in terms of typing nor in terms of abstraction.

[Suleman khan]

why I said that because I am going to have similar query methods.

[Michal]

That does not make the private method useful. The private method is useless as everything that matters at least a little bit is defined outside the private method - the SQL query, the parameter values, the row mapper functionality, even the jdbcTemplate instance. Let me put it another way for you. If you plan to have 20 methods to call your private method, and then you go without the private method, what do you loose? In my opinion you only have 20-times to type jdbcTemplate. and new DogRowMapper. Anything else?