title: Using Prepared Statement multiple times efficiently
body: <p>Below is the code which I am using to insert <code>multiple records( around 5000-7000)</code> in the Oracle Database using Prepared Statement.</p>
  
  <p>The way I am doing currently is good? Or it can be improve more using some <code>batch thing</code>?</p>
  
  <pre><code>pstatement = db_connection.prepareStatement(PDSLnPConstants.UPSERT_SQL);
  
  for (Entry&lt;Integer, LinkedHashMap&lt;Integer, String&gt;&gt; entry : MAPPING.entrySet()) {
  
      pstatement.setInt(1, entry.getKey());
      pstatement.setString(2, entry.getValue().get(LnPConstants.CGUID_ID));
      pstatement.setString(3, entry.getValue().get(LnPConstants.PGUID_ID));
      pstatement.setString(4, entry.getValue().get(LnPConstants.SGUID_ID));
      pstatement.setString(5, entry.getValue().get(LnPConstants.UID_ID));
      pstatement.setString(6, entry.getValue().get(LnPConstants.ULOC_ID));
      pstatement.setString(7, entry.getValue().get(LnPConstants.SLOC_ID));
      pstatement.setString(8, entry.getValue().get(LnPConstants.PLOC_ID));
      pstatement.setString(9, entry.getValue().get(LnPConstants.ALOC_ID));
      pstatement.setString(10, entry.getValue().get(LnPConstants.SITE_ID));
      pstatement.executeUpdate();
  
      pstatement.clearParameters();
  }
  </code></pre>
  
  <p><strong>Udpated Code That I am Using:-</strong></p>
  
  <pre><code>public void runNextCommand() {
  
      Connection db_connection = null;
      PreparedStatement pstatement = null;
      int batchLimit = 1000;
      boolean autoCommit = false;
  
      try {
          db_connection = getDBConnection();
  
          autoCommit = db_connection.getAutoCommit();
          db_connection.setAutoCommit(false); //Turn off autoCommit
          pstatement = db_connection.prepareStatement(LnPConstants.UPSERT_SQL); // create a statement
  
          for (Entry&lt;Integer, LinkedHashMap&lt;Integer, String&gt;&gt; entry : GUID_ID_MAPPING.entrySet()) {
              pstatement.setInt(1, entry.getKey());
              pstatement.setString(2, entry.getValue().get(LnPConstants.CGUID_ID));
              pstatement.setString(3, entry.getValue().get(LnPConstants.PGUID_ID));
              pstatement.setString(4, entry.getValue().get(LnPConstants.SGUID_ID));
              pstatement.setString(5, entry.getValue().get(LnPConstants.UID_ID));
              pstatement.setString(6, entry.getValue().get(LnPConstants.ULOC_ID));
              pstatement.setString(7, entry.getValue().get(LnPConstants.SLOC_ID));
              pstatement.setString(8, entry.getValue().get(LnPConstants.PLOC_ID));
              pstatement.setString(9, entry.getValue().get(LnPConstants.ALOC_ID));
              pstatement.setString(10, entry.getValue().get(LnPConstants.SITE_ID));
              pstatement.addBatch();
  
              batchLimit--;
  
              if(batchLimit == 0){
                  pstatement.executeBatch();
                  pstatement.clearBatch();
                  batchLimit = 1000;
              }
              pstatement.clearParameters();
          }
  
      } catch (SQLException e) {
          getLogger().log(LogLevel.ERROR, e);
      } finally {
          try {
              pstatement.executeBatch();
              db_connection.commit();
              db_connection.setAutoCommit(autoCommit);
          } catch (SQLException e1) {
              getLogger().log(LogLevel.ERROR, e1.getMessage(), e1.fillInStackTrace());
          }
  
          if (pstatement  != null) {
              try {
                  pstatement.close();
                  pstatement = null;
              } catch (SQLException e) {
                  getLogger().log(LogLevel.ERROR, e.getMessage(), e.fillInStackTrace());
              }
          }
          if (db_connection!= null) {
              try {
                  db_connection.close();
                  db_connection = null;
              } catch (SQLException e) {
                  getLogger().log(LogLevel.ERROR, e.getMessage(), e.fillInStackTrace());
              }
          }
      }
  }
  </code></pre>