How to Execute In() SQL Queries with Spring's Jdbctemplate Effectively

How to execute IN() SQL queries with Spring's JDBCTemplate effectively?

You want a parameter source:

Set<Integer> ids = ...;

MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("ids", ids);

List<Foo> foo = getJdbcTemplate().query("SELECT * FROM foo WHERE a IN (:ids)",
     parameters, getRowMapper());

This only works if getJdbcTemplate() returns an instance of type NamedParameterJdbcTemplate

Can I execute a plain SQL statement with the Spring JDBC template?

From my point of view:

• a non-plain sql is a query in jql.
• Some stuff is quite complicate to achive with jql (ie. joins over a few tables) in an already existent data scheme.
• jpa requests primary keys in tables - which is not always the case in existent databases
• the question aims on 'i wanna have a fallback to traditional queries to do my everyday's life'

Is there a better way to execute this SQL statement in Spring using JdbcTemplate?

Yes.If you directly use the request param on your sql it can lead to SQL injection attacks. We can always go with the prepared statement by adding a placeholder to where the values must be added.

Use queryForList(String sql, Object... args) or queryForList(String sql, Object[] args, Class<T> elementType)

Eg:-

String employeeId= "1";
String sql = "select id,name,address from employee where id = ?"; 
getJdbcTemplate(). queryForList(sql, new Object[]{employeeId}, Employee.class);

JdbcTemplate IN Clause for String elements

There a few other similar questions out there which might have helpful answers for you:

How to execute IN() SQL queries with Spring's JDBCTemplate effectivly?

To make this style of query work on my end, I have to switch from plain old JDBCTemplate to NamedParameterJdbcTemplate.

Here is some example code:

String query = "select * from table where columnName in (:listOfValues)";
List<String> nameRecordIDs = new ArrayList<String>(); 
// ...
// add values to collection, then
// ...
Map namedParameters = Collections.singletonMap("listOfValues", nameRecordIDs);
namedparameterJdbcTemplate.query(query, namedParameters,new MyMapper());

---

Related Topics