2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

Insert multiple record in a batch using spring JDBC Template

We are writing some new code in our application where need to insert multiple records, its may be More than 1000 at a time. And our code insert those records one by one which cause performance issue. We want to take the help of  JDBC Template batch processing.

Can any body suggest us how to do this?

userimage

For batch programs, JDBC drivers usually provide an optimization for reducing network round-trips named 'JDBC batch inserts/updates'. When these are used, inserts/updates are queued at the driver level before being sent to the database.

When a threshold is reached, then the whole batch of queued statements is sent to the database in one go. This prevents the driver from sending the statements one by one, which would waist multiple network round-trips.


Find the example of JDBC Template batch insert in below,


package com.kodetalk.dao.jdbcbatch;

import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;

public class JdbcBatchDAOImpl implements JdbcBatchDAO {
    private Logger logger = Logger.getLogger(this.getClass().getSimpleName());

    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public void storeBatch(final List<JdbcBatch> jdbcBatchEntries) {

        int[] updateCounts = jdbcTemplate.batchUpdate(Constants.INSERT_SQL,
                new BatchPreparedStatementSetter() {
                    public void setValues(PreparedStatement ps, int i)
                            throws SQLException {
                        ps.setBigDecimal(1, new BigDecimal(jdbcBatchEntries
                                .get(i).getMessageId().toString()));
                        ps.setString(2, jdbcBatchEntries.get(i)
                                .getMessageType());
                        ps.setString(3, jdbcBatchEntries.get(i).getMessage());
                    }

                    public int getBatchSize() {
                        return jdbcBatchEntries.size();
                    }
                });

        logger.log(Level.INFO, String
                .format("Inserted %s records using spring jdbc batching",updateCounts.length));
    }
}

Answer is