CODEWITHSUNDEEP
javaspring-bootjdbcprepared-statementjdbctemplate
cheshire
cheshire

Reputation: 1159

jdbcTemplate query prepared statement with multiple parameters

I am building my sql string like this:

String sql = "SELECT * FROM horse WHERE 1=1 ";
if (horse.getName() != null) {
    sql += "AND UPPER(name) LIKE ? ";
}
if (horse.getDescription() != null) {
    sql += "AND UPPER(description) LIKE ? ";
}
if (horse.getRating() != null) {
   sql += "AND rating=? ";
}

I want to find a match for entity depending on which parameters are passed. So if only name and rating are passed I would get something like: SELECT * FROM horse WHERE 1=1 AND UPPER(name) LIKE ? AND rating=?

Now I pass the sql string to query like this:

List<Horse> matchingHorses = jdbcTemplate.query(sql, new Object[]{horse.getName()}, mapHorse());

This returns a correct result but I have to pass to new Object[] {} only the parameters that I know that user is gonna pass or else I do not get anything. For example if user passes something like this:

{
  "description":"desc"
}

I won't get any results even if there is a description with "desc". If I do this:

List<Horse> matchingHorses = jdbcTemplate.query(sql, new Object[]{horse.getName(), horse.getDescription(), horse.getRating()}, mapHorse());

and pass only name I get:

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [SELECT * FROM horse WHERE 1=1 AND UPPER(name) LIKE ? ];
Invalid value "2" for parameter "parameterIndex" [90008-200]; nested exception is org.h2.jdbc.JdbcSQLDataException: Invalid value "2" for parameter "parameterIndex" [90008-200]

Here is my mapHorse() row mapper:

private RowMapper<Horse> mapHorse() {
        return (resultSet, i) -> {
            Long horseId = resultSet.getLong("id");
            String horseName = resultSet.getString("name");
            String horseDesc = resultSet.getString("description");
            int horseRating = resultSet.getInt("rating");
            return new Horse(
                horseId,
                horseName,
                horseDesc,
                horseRating,
            );
        };
}

How do I implement this correctly?

Upvotes: 2

Views: 10896

Answers (1)

Prasanna Wagh
Prasanna Wagh

Reputation: 111

You can use NamedParameterJdbcTemplate.

MapSqlParameterSource params = new MapSqlParameterSource();
if (horse.getName() != null) {
    sql += "AND UPPER(name) LIKE :name ";
    params.addValue("name", horse.getName());
}
if (horse.getDescription() != null) {
    sql += "AND UPPER(description) LIKE :description ";
    params.addValue("description", horse.getDescription());
}
if (horse.getRating() != null) {
    sql += "AND rating=:rating ";
    params.addValue("rating ", horse.getRating());
}
namedParameterJdbcTemplate.query(sql, params, mapHorse());

suggestion- better if you you use string builder.

Upvotes: 1

Related Questions