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

score 61 · Accepted Answer · edited Jul 18 '23 at 07:57

61

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

edited Jul 18 '23 at 07:57

SRJ

2,092
3
17
36

answered Dec 27 '14 at 00:07

Michal

2,353
1
15
18

Still getting the same error. Do I need to make any changes to my private method also ? – Suleman khan Dec 27 '14 at 00:10
I am using this import `import org.springframework.jdbc.core.JdbcTemplate;`. – Suleman khan Dec 27 '14 at 00:16
So use the one I recommend. – Michal Dec 27 '14 at 00:25
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. – Suleman khan Dec 27 '14 at 00:30
2

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. – Michal Dec 27 '14 at 00:35
why I said that because I am going to have similar query methods. – Suleman khan Dec 27 '14 at 00:39
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? – Michal Dec 27 '14 at 00:53

passing different types of arguments to jdbctemplate query

1 Answers1

Related