How to delete multiple rows with JdbcTemplate

java spring spring-jdbc
Sunil Kumar Naik picture Sunil Kumar Naik · May 31, 2016 · Viewed 11.3k times · Source

Want to delete multiple rows with the help of JdbcTemplate. In the below code msgNos is a String variable containing comma separated value like 26,27. After executing the statements it is only deleting one record.

String sqlQuery = " delete from canned_message where msg_no in (?)";
Object[] params = {msgNos};
int rows = smsdbJdbcTemplate.update(sqlQuery, params);

Answer

Karthikeyan Vaithilingam picture Karthikeyan Vaithilingam · May 31, 2016

Instead of org.springframework.jdbc.core.JdbcTemplate use org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate where you can use named parameter.

Then in your query you can pass List as parameter value in the in clause

String sqlQuery = "delete from canned_message where msg_no in (:msgNos)";
List<Integer> params = <array list of number>;
Map namedParameters = Collections.singletonMap("msgNos", params);
int rows = smsdbJdbcTemplate.update(sqlQuery, namedParameters);

Note: I've used List<Integer> use appropriate datatype as you need.

Related questions

Spring JDBC Template for calling Stored Procedures

What is the correct way to invoke stored procedures using modern day (circa 2012) Spring JDBC Template? Say, I have a stored procedure that declares both IN and OUT parameters, something like this: mypkg.doSomething( id OUT int, name IN String, …

Read More
Spring Boot default H2 jdbc connection (and H2 console)

I am simply trying to see the H2 database content for an embedded H2 database which spring-boot creates when I don't specify anything in my application.properties and start with mvn spring:run. I can see hibernate JPA creating the …

Read More
Spring - @Transactional - What happens in background?

I want to know what actually happens when you annotate a method with @Transactional? Of course, I know that Spring will wrap that method in a Transaction. But, I have the following doubts: I heard that Spring creates a proxy …

Read More