CODEWITHSUNDEEP
springspring-batchspring-jdbc
user1454829
user1454829

Reputation: 79

set batch size in spring JDBC batch update

How do I set batch size in spring JDBC batch update to improve performance? Listed below is my code snippet.

public void insertListOfPojos(final List<Student> myPojoList) {

    String sql = "INSERT INTO " + "Student " + "(age,name) " + "VALUES "
            + "(?,?)";
    try {
        jdbcTemplateObject.batchUpdate(sql,
                new BatchPreparedStatementSetter() {

                    @Override
                    public void setValues(PreparedStatement ps, int i)
                            throws SQLException {

                        Student myPojo = myPojoList.get(i);
                        ps.setString(2, myPojo.getName());
                        ps.setInt(1, myPojo.getAge());

                    }

                    @Override
                    public int getBatchSize() {
                        return myPojoList.size();
                    }
                });
    } catch (Exception e) {
        System.out.println("Exception");
    }
}

I read that with Hibernate you can provide your batch size in the configuration xml. For example, <property name="hibernate.jdbc.batch_size" value="100"/>. Is there something similar in Spring's jdbc?

Upvotes: 3

Views: 13530

Answers (2)

Michael Pralow
Michael Pralow

Reputation: 6630

if you use JDBC directly, you decide yourself how much statements are used in one commit, while using one of the provided JDBCWriters you decide the batch* size with the configured commit-rate

*afaik the actual spring version uses the prepared statement batch methods under the hood, see https://github.com/SpringSource/spring-framework/blob/master/spring-jdbc/src/main/java/org/springframework/jdbc/core/JdbcTemplate.java#L549

Upvotes: 0

Luca Basso Ricci
Luca Basso Ricci

Reputation: 18413

There is no option for jdbc that looks like Hibernate; I think you have to get a look to specif RDBMS vendor driver options when preparing connection string.

About your code you have to use

BatchPreparedStatementSetter.getBatchSize()

or

JdbcTemplate.batchUpdate(String sql, final Collection<T> batchArgs, final int batchSize, final ParameterizedPreparedStatementSetter<T> pss)

Upvotes: 1

Related Questions