Java | How to insert the 100000 records in database using PreparedStatement batch?

Inserting the records one by one will take a lot of time as 100000 execute and commit calls will be required on the PreparedStatment instances.

To avoid the time overhead, we can use the concept of the batch which will insert the 1000 records with one call of executeBatch and commit methods.

1. Create DB connection.

2. Create a query for the prepared statement.

3. Set auto commit false.

4. Prepare statement and add it to batch.

5. If the batch size is multiple of 1000 then commit the database.

6. Repeat step 4 and 5 till the complete data is loaded.

7. Close connection.

Same has been implemented below given method:

public static int uploadFilesData(List<CallDetailDTO> list) {

          

        connection = getConnection();

          

        String query = Constants.INSERT_PREFIX + Constants.TABLE_ROWS + Constants.PREPARED_STMT_VALUE;

        int count = 0;

        try {

             connection.setAutoCommit(false);

             PreparedStatement ps = connection.prepareStatement(query);

             for (CallDetailDTO dto: list) {

                 ps.setString (1,dto.getPartyNumberA());

                 ps.setString (2,dto.getPartyNumberB());

                 ps.setString (3,dto.getCallDate());

                 ps.setString (4,dto.getCallTime());

                 ps.setString (5,dto.getDuration());

                 ps.setString (6,dto.getCellId());

                 ps.setString (7,dto.getLastCellId());

                 ps.setString (8,dto.getCallType());

                 ps.setString (9,dto.getImei());

                 ps.setString (10,dto.getImsi());

                 ps.setString (11,dto.getPpPo());

                 ps.setString (12,dto.getSmsCentre());

                 ps.setString (13,dto.getRoamingNwCied());

                 ps.setString (14,dto.getSheetName());

                 ps.addBatch();

                 if(++count % 1000 == 0) {

                       ps.executeBatch();

                       connection.commit();

                 }

             }

             ps.executeBatch();

             connection.commit();

             ps.close();

             connection.close();

        } catch (SQLException e) {

             System.out.println("SQLException:"+e.getMessage());

        }

        return count;

}