# JDBC Batch Processing

> Batch processing allows grouping related SQL statements into a batch by calling `addBatch()`, and submitting them with a single call to `executeBatch()`. Sending several SQL statements with `executeBatch()` improves performance by reducing the amount of communication overhead. The `clearBatch()` method can be called to remove all statements previously added to the batch.

> Consider the following class Batch with constructor and method `createTable()`defining table Record:

>         public class Batch
>         {
>             static final int nRecords = 5;
>             Connection con;

>             public Batch(String url) throws ClassNotFoundException, SQLException
>             {
>                 String user = null;
>                 String password = null;

>                 // Load the driver
>                 Class.forName("com.mcobject.jdbc.McoDriver");

>                 // Connect to the database
>                 con = DriverManager.getConnection(url, user, password);

>                 createTable();

>                 insertBatch(nRecords*3);

>                 // Finally close the database
>                 con.close();
>             }

>             public void createTable() throws SQLException
>             {
>                 Statement stmt = null;
>                 try
>                 {
>                     con.setAutoCommit(true);
>                     String sql = "create table Record(idx int primary key, dec_value int, str_value string)";
>                     stmt = con.createStatement();
>                     stmt.executeUpdate(sql);
>                     stmt.close();
>                 }catch(SQLException e){
>                     e.printStackTrace();
>                     throw e;
>                 }
>             }

> The following method demonstrates the use of `addBatch()`, `clearBatch()` and `executeBatch()`:

>             public void insertBatch(int initial_idx) throws SQLException {
>             Statement stmt = null;
>             try
>             {
>                 stmt = con.createStatement();
>                 int next_to_last_idx = initial_idx + nRecords;
>                 for (int n=0; n < 3; n++)
>                 {
>                     for (int i=initial_idx; i<next_to_last_idx; i++)
>                     {
>                         String sql = "insert into Record values(" + i +
>                                     "," + (100+i) + ",'Str200" + i + "')";
>                         stmt.addBatch(sql);
>                     }
>                     if (n == 2)
>                     {
>                         stmt.clearBatch();
>                     }
>                     int[] res = stmt.executeBatch();
>                     if (n == 2)
>                     {
>                         if (res.length != 0)
>                         {
>                             throw new SQLException("Unexpected : result (" +
>                                     res.length + " instead of 0)!");
>                         }
>                     } else {
>                         if (res.length != nRecords)
>                         {
>                             throw new SQLException("Unexpected : result (" +
>                                     res.length + " instead of " + nRecords + ")!");
>                         }
>                         for (int j = 0; j < res.length; j++)
>                         {
>                             if (res[j] != 1)
>                             {
>                                 throw new SQLException("Unexpected : result [" +
>                                         j + "] (" + res[j] + " instead of 1)!");
>                             }
>                         }
>                     }
>                     initial_idx += nRecords;
>                     next_to_last_idx += nRecords;
>                 }
>                 stmt.close();
>                 con.commit();

>             }catch(SQLException e){
>                 e.printStackTrace();
>                 throw e;
>             }
>         }

> Clearly, to execute these methods, a Batch instance must be created in a main function like the following:

>         public static void main(String[] args) throws Exception
>         {
>             new Batch("jdbc:extremedb:localhost:5001");
>         }

> ### Example

> > In a console window from directory `samples/jdbc/JDBCBatch` run

> >         ../../../target/bin/xsql -i -c xsql.cfg

> > Then in a second console window run

> >         On Unix/Linux:

> >         JDBCBatch.sh

> >         On Windows:

> >         JDBCBatch.bat