CODEWITHSUNDEEP
javasqlspring-bootjdbctemplate
shukurov23
shukurov23

Reputation: 23

Cannot put value into SQL Statement in Java Spring Boot

I have private static final String SQL_SEARCH_ALL = "SELECT * FROM product WHERE name LIKE '%?%'"; SQL statement, where I need to pass a query String. When instead of ? mark I pass a string like '%cola%' it works fine.

I pass paramater using JdbcTemplate as I have used to

return jdbcTemplate.query(SQL_SEARCH_ALL, new Object[]{searchInput}, productRowMapper);

That returns zero objects.

I have problem with passing value into this statement.

Upvotes: 2

Views: 545

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

The standard way of doing this in Java (or really any language) is to use a single ? placeholder and then bind the wildcard expression to that placeholder:

private static final String SQL_SEARCH_ALL = "SELECT * FROM product WHERE name LIKE ?";
String searchInput = "%cola%";
return jdbcTemplate.query(SQL_SEARCH_ALL, new Object[]{ searchInput }, productRowMapper);

Upvotes: 1

The Impaler
The Impaler

Reputation: 48810

JDBC parameters are not strings that are replaced anywhere you use a ? marker; that would open the query to SQL Injection. Depending on each database and each JDBC driver the allowed locations for a ? parameter marker vary.

It's safe to say a parameter can usually go where a typical literal value can go, but it's not always the case. For example, in DB2 the clause FETCH FIRST 5 ROWS ONLY does not accept a ? instead of the literal 5.

In your case, the typical solution is to use CONCAT(). For example:

private static final String SQL_SEARCH_ALL =
  "SELECT * FROM product WHERE name LIKE CONCAT('%', ?, '%')";

Of course you need to decide what do you want to happen is a null parameter is provided. Probably a COALESCE() function is in order there.

Upvotes: 2

Related Questions