Saturday 22 October 2016

Insert 100000 records in database with minimum time?

Using PreparedStatement batch

1. Create DB connection.
2. Create query for prepared statement.
3. setAutoCommit false.
4. Prepare stamen and add it to batch.
5. If batch size is multiple of 2000 then commit the database.
6. Repeat step 4 and 5 till the complete data is loaded.
7. close connection.

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 % Constants.BATCH_SIZE == 0) {
                           ps.executeBatch();
                           connection.commit();
                     }
                }
                ps.executeBatch();
                connection.commit();
                ps.close();
                connection.close();
           } catch (SQLException e) {
                System.out.println("SQLException:"+e.getMessage());
           }

           return count;
     }

No comments:

Post a Comment

Related Posts Plugin for WordPress, Blogger...