JDBC General Statement and Batch Query

How to create a Batch job?

  • When we execute several SQL commands in one run, we can say that we run these commands in one batch.
  • As many SQL commands can be executed without returning any ResultSet, where only a integer status values or count returns, we can run those in a batch.
  • The Statement interface has methods for running several SQL commands in a batch.
  • Optional methods to used in creating a batch job:
    Methods in Statement interface Description
    void addBatch(String sql) Adds the given SQL command to the current list of commands for this Statement object. The commands in this list can be executed as a batch by calling the method executeBatch
    void clearBatch() Empties this Statement object's current list of SQL commands.
    int[] executeBatch() Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.
    Example using Batch with a DriverManager connection:
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    
    public class SQLBatchInsert {
    
     public static void main(String[] args) {
      Connection conn = null;
      Statement stmt = null;
      try {
       // Register a driver for the MySQL database
       Class.forName("com.mysql.jdbc.Driver");
       // Create a url for accessing the MySQL
       // database CarDB
       String url = "jdbc:mysql://localhost:3306/CarDB";
       // user and passord to access the database
       String username = "root";
       String password = "root";
       // User the DriverManager to get a Connection to the database
       conn = DriverManager.getConnection(url, username, password);
       // Create a  Statement
       // that is non-updateable, non-scrollable conserning ResultSet
       stmt = conn.createStatement();
       // Create an ALTER SQL statement to add a column in
       // the table CARRENTAL and add it as new Batch to be executed
       stmt.addBatch("ALTER TABLE CARRENTAL " +
                           "ADD COLUMN TotalCost numeric");
       // Create a SQL Delete and add it as new Batch to be executed
       stmt.addBatch("Delete from Carpark where regNo='XY34567'");
       // Create Insert of a new row in the CarPark table
       // with a random generated dayprice
       // and add it as new Batch to be executed
       stmt.addBatch("Insert into CarPark (regNo,cartype," +
               "model, dayprice)" +
               " values ('XY34567','BMW',2009," +
               "200 +" + Math.round(Math.random() * 200) + ")");
       // Create Insert of a new row in the CarRental table for the 
       // new car 
       // and add it as new Batch to be executed
       stmt.addBatch("insert  into CARRENTAL(REGNO,RENTALDAYS) values" +
               "('XY34567',10)");
       // Create SQL Update that calculates the Cost of each rental
       // and add it as new Batch to be executed
       stmt.addBatch("Update CARRENTAL r set TotalCost=RENTALDAYS*" +
               "(Select c.dayPrice from CARPARK c where c.RegNO=r.RegNO)");
       // Execute all jobs in the Batch
       stmt.executeBatch();
       // Create a SQL select for a ResultSet with a query that returns
       // all the columns  from the dB Carpark and CarRental tables.
       ResultSet resultSet = stmt.executeQuery("select * from Carpark c, " +
               "CARRENTAL r where c.REGNO=r.REGNO ");
       // As cursor is at the before first row position
       // we use the next() method to
       // test and read the first row in the ResultSet.
       if (resultSet.next()) {
        // Then we use a loop to retrieve rows and column data
        // and creates a html coded table output
        System.out.println("<table border='1' >");
        System.out.println("<tr>");
        System.out.print("<th>regNo</th>");
        System.out.println("<th>cartype</th>");
        System.out.println("<th>model</th>");
        System.out.println("<th>day<br/>Price</th>");
        System.out.println("<th>Cost</th>");
        System.out.println("</tr>");
        do {
         System.out.println("<tr>");
         System.out.print("<td>" + resultSet.getString("regNo") + "</td>");
         System.out.println("<td>" + resultSet.getString("cartype") + "</td>");
         System.out.println("<td>" + resultSet.getInt("model") + "</td>");
         System.out.println("<td>" + resultSet.getDouble("dayPrice") + "</td>");
         System.out.println("<td>" + resultSet.getDouble("TotalCost") + "</td>");
         System.out.println("</tr>");
        } while (resultSet.next());
        System.out.println("</table>");
       }
      } catch (ClassNotFoundException ex) {
       Logger.getLogger(SQLBatchInsert.class.getName()).log(Level.SEVERE, null, ex);
      } catch (SQLException e) {
       e.printStackTrace();
      } finally {
       try {
        // Close the Statement, which also close the ResultSet
        stmt.close();
        conn.close();
       } catch (Exception xe) {
        xe.printStackTrace();
       }
      }
     }
    }

    You should use the close() method in the Statement object when you do not need it anymore.

    You should also use the close() method in the Connection object when you do not need it anymore.

    You can download this example here (needed tools can be found in the right menu on this page).

    If we run this application the result should be:
    As pure output Translated by a Browser
    <table border='1' >
    <tr>
    <th>regNo</th><th>cartype</th>
    <th>model</th>
    <th>day<br/>Price</th>
    <th>Cost</th>
    </tr>
    <tr>
    <td>DE12345</td><td>AUDI</td>
    <td>2003</td>
    <td>250.0</td>
    <td>1250.0</td>
    </tr>
    <tr>
    <td>DE12345</td><td>AUDI</td>
    <td>2003</td>
    <td>250.0</td>
    <td>750.0</td>
    </tr>
    <tr>
    <td>RE23456</td><td>FORD</td>
    <td>2005</td>
    <td>350.0</td>
    <td>2450.0</td>
    </tr>
    <tr>
    <td>RE23456</td><td>FORD</td>
    <td>2005</td>
    <td>350.0</td>
    <td>1750.0</td>
    </tr>
    <tr>
    <td>CE23473</td><td>AUDI</td>
    <td>2001</td>
    <td>400.0</td>
    <td>1600.0</td>
    </tr>
    <tr>
    <td>DE34562</td><td>OPEL</td>
    <td>2001</td>
    <td>340.0</td>
    <td>1020.0</td>
    </tr>
    <tr>
    <td>DE34562</td><td>OPEL</td>
    <td>2001</td>
    <td>340.0</td>
    <td>1700.0</td>
    </tr>
    <tr>
    <td>XY34567</td><td>BMW</td>
    <td>2009</td>
    <td>331.0</td>
    <td>3310.0</td>
    </tr>
    </table>
    regNocartype model day
    Price
    Cost
    DE12345AUDI 2003 250.0 1250.0
    DE12345AUDI 2003 250.0 750.0
    RE23456FORD 2005 350.0 2450.0
    RE23456FORD 2005 350.0 1750.0
    CE23473AUDI 2001 400.0 1600.0
    DE34562OPEL 2001 340.0 1020.0
    DE34562OPEL 2001 340.0 1700.0
    XY34567BMW 2009 331.0 3310.0

© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.