when to use ParameterizedPreparedStatementSetter over BatchPreparedStatementSetter viceversa, using Spring batchUpdate?

Question

I am now confused on using spring batch updates using BatchPreparedStatementSetter & ParameterizedPreparedStatementSetter under jdbcTemplate.batchupdate.

I have gone through various blogs and even spring docs but unable to grasp the concept behind it.

In one of the blog (Spring JDBC performing batch update example) they say:

ParameterizedPreparedStatementSetter: Using this, JdbcTemplate can execute multiple batch based on the batch size passed in the batchUpdate method.

BatchPreparedStatementSetter : Using this, JdbcTemplate will run only execute single batch based on the batch size returned by implementation this interface.

I wanted to know if i want to INSERT data into SQL Server DB. what would be difference in using each statements behind the scene. would they be sending DB call with

MULTIPLE INSERT CALLS BUT PREPARING STATEMENT ONCE
INSERT INTO TABLE VALUES(1,2,3)
INSERT INTO TABLE VALUES(2,3,5)

or

SINGLE INSERT WITH MULTIPLE VALUES AND PREPARING STATEMENT ONCE"
INSERT INTO TABLE VALUES(1,2,3),(2,3,5)

BTW i am using below sqljdbc version

<dependency>
    <groupId>microsoft</groupId>
    <artifactId>sqljdbc</artifactId>
    <version>4</version>
</dependency>

and Spring version: 4.2.3.RELEASE

shrikant.sharma shrikant.sharma · Accepted Answer · 2019-11-10T05:37:25

BatchPreparedStatementSetter and ParameterizedPreparedStatementSetter are two prepaid statements provides by spring. BatchPreparedStatementSetter will execute the entire batch at once, ie if we have 100 records, BatchPreparedStatementSetter will insert all of them at once. This might be a problem if the underlying database doesn't except more then let's say 50 statements at once. That's why it has return type of 1-dimensional array.

@Override
    public int[] batchInsertAll(List<User> users) {
        return jdbcTemplate.batchUpdate(SQL_INSERT_USER, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, users.get(i).getFirstName());
                ps.setString(2, users.get(i).getLastName());
            }
            @Override
            public int getBatchSize() {
                return users.size();
            }
        });
    }

if we want to insert the entire batch in the batch of chunks, then BatchPreparedStatementSetter will be used. with BatchPreparedStatementSetter we can execute batch into batches. That's why it has return type of 2-dimensional array. The below code will execute a batch of users.size statements into batch of 5.

@Override
public int[][] batchInsertAll(List<User> users) {
    return jdbcTemplate.batchUpdate(SQL_INSERT_USER, users, 5, new ParameterizedPreparedStatementSetter<User>() {
        @Override
        public void setValues(PreparedStatement ps, User user) throws SQLException {
            ps.setString(1, user.getFirstName());
            ps.setString(2, user.getLastName());
        }
    });
}

Hope this answers your question.

when to use ParameterizedPreparedStatementSetter over BatchPreparedStatementSetter viceversa, using Spring batchUpdate?

1 Answers