Example usage for java.sql Statement executeBatch

Introduction

In this page you can find the example usage for java.sql Statement executeBatch.

Prototype

int[] executeBatch() throws SQLException;

Source Link

Document

Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.

Usage

From source file:Main.java

public static void main(String[] argv) throws Exception {

    Class.forName("com.mysql.jdbc.Driver");
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbctutorial", "root", "root");

    con.setAutoCommit(false);// w  ww.j  av  a 2s.com
    String table1 = "INSERT emp_sal VALUES('v',1200)";
    String table2 = "DELETE FROM movies WHERE title = 'r'";
    Statement st = con.createStatement();
    st.addBatch(table1);
    st.addBatch(table2);
    int count[] = st.executeBatch();
    con.commit();
    con.close();
    System.out.println("Successfully!");
}

---

From source file:Main.java

public static void main(String[] args) throws Exception {
    String driver = "sun.jdbc.odbc.JdbcOdbcDriver";

    Connection con;/*w w  w  .  j  a  v  a  2s  .co  m*/
    Statement stmt;
    ResultSet rs;

    try {
        Class.forName(driver);
        con = DriverManager.getConnection("jdbc:odbc:databaseName", "student", "student");
        // Start a transaction
        con.setAutoCommit(false);

        stmt = con.createStatement();
        stmt.addBatch("UPDATE EMP SET JOB = 1");

        // Submit the batch of commands for this statement to the database
        stmt.executeBatch();

        // Commit the transaction
        con.commit();

        // Close the existing to be safe before opening a new one
        stmt.close();

        // Print out the Employees
        stmt = con.createStatement();
        rs = stmt.executeQuery("SELECT * FROM EMP");
        // Loop through and print the employee number, job, and hiredate
        while (rs.next()) {
            int id = rs.getInt("EMPNO");
            int job = rs.getInt("JOB");
            String hireDate = rs.getString("HIREDATE");

            System.out.println(id + ":" + job + ":" + hireDate);
        }
        con.close();
    } catch (SQLException ex) {
        ex.printStackTrace();
    }
}

---

From source file:TestBatchUpdate.java

public static void main(String args[]) {
    Connection conn = null;/* w ww.j  ava2 s . c o  m*/
    Statement stmt = null;
    ResultSet rs = null;
    try {
        conn = getConnection();
        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        conn.setAutoCommit(false);
        stmt.addBatch("INSERT INTO batch_table(id, name) VALUES('11', 'A')");
        stmt.addBatch("INSERT INTO batch_table(id, name) VALUES('22', 'B')");
        stmt.addBatch("INSERT INTO batch_table(id, name) VALUES('33', 'C')");
        int[] updateCounts = stmt.executeBatch();
        conn.commit();

        rs = stmt.executeQuery("SELECT * FROM batch_table");
        while (rs.next()) {
            String id = rs.getString("id");
            String name = rs.getString("name");
            System.out.println("id=" + id + "  name=" + name);
        }

    } catch (BatchUpdateException b) {
        System.err.println("SQLException: " + b.getMessage());
        System.err.println("SQLState: " + b.getSQLState());
        System.err.println("Message: " + b.getMessage());
        System.err.println("Vendor error code: " + b.getErrorCode());
        System.err.print("Update counts: ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + " ");
        }
    } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        System.err.println("SQLState: " + ex.getSQLState());
        System.err.println("Message: " + ex.getMessage());
        System.err.println("Vendor error code: " + ex.getErrorCode());
    } catch (Exception e) {
        System.err.println("Exception: " + e.getMessage());
    } finally {
        try {
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception ignore) {
        }
    }
}

---

From source file:Main.java

public static void main(String[] args) throws Exception {
    Connection conn = getConnection();
    conn.setAutoCommit(false);// w ww  . ja va  2s  .  co m
    Statement st = conn.createStatement();

    st.executeUpdate("create table survey (id int, name VARCHAR(30) );");
    st.addBatch("DELETE FROM survey");

    st.addBatch("INSERT INTO survey(id, name) " + "VALUES(444, 'ginger')");
    // we intentionally pass a table name (animals_tableZZ)
    // that does not exist
    st.addBatch("INSERT INTO survey(id, name) " + "VALUES(555, 'lola')");
    st.addBatch("INSERT INTO survey(id, name) " + "VALUES(666, 'freddy')");

    // Execute the batch
    int[] updateCounts = st.executeBatch();

    checkUpdateCounts(updateCounts);

    // since there were no errors, commit
    conn.commit();

    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    outputResultSet(rs);

    rs.close();
    st.close();
    conn.close();
}

---

From source file:Main.java

public static void main(String[] args) throws Exception {
    Class.forName("org.apache.derby.jdbc.ClientDriver");
    Connection con = DriverManager.getConnection("jdbc:derby://localhost:1527/testDb", "name", "pass");
    Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    String insertEmp1 = "insert into emp values(10,'A','trainee')";
    String insertEmp2 = "insert into emp values(11,'B','trainee')";
    String insertEmp3 = "insert into emp values(12,'C','trainee')";
    con.setAutoCommit(false);/*from   w w  w  . j a v a2 s  .  c om*/
    stmt.addBatch(insertEmp1);
    stmt.addBatch(insertEmp2);
    stmt.addBatch(insertEmp3);
    ResultSet rs = stmt.executeQuery("select * from emp");
    rs.last();
    System.out.println("rows before batch execution= " + rs.getRow());
    stmt.executeBatch();
    con.commit();
    System.out.println("Batch executed");
    rs = stmt.executeQuery("select * from emp");
    rs.last();
    System.out.println("rows after batch execution= " + rs.getRow());
}

---

From source file:BatchUpdate.java

public static void main(String args[]) throws SQLException {

    ResultSet rs = null;//w  ww  .  j  a v a 2 s  .co m
    PreparedStatement ps = null;

    String url = "jdbc:mySubprotocol:myDataSource";

    Connection con;
    Statement stmt;
    try {

        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {

        con = DriverManager.getConnection(url, "myLogin", "myPassword");
        con.setAutoCommit(false);

        stmt = con.createStatement();

        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");

        int[] updateCounts = stmt.executeBatch();
        con.commit();
        con.setAutoCommit(true);

        ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");

        System.out.println("Table COFFEES after insertion:");
        while (uprs.next()) {
            String name = uprs.getString("COF_NAME");
            int id = uprs.getInt("SUP_ID");
            float price = uprs.getFloat("PRICE");
            int sales = uprs.getInt("SALES");
            int total = uprs.getInt("TOTAL");
            System.out.print(name + "   " + id + "   " + price);
            System.out.println("   " + sales + "   " + total);
        }

        uprs.close();
        stmt.close();
        con.close();

    } catch (BatchUpdateException b) {
        System.err.println("-----BatchUpdateException-----");
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Message:  " + b.getMessage());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts:  ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + "   ");
        }
        System.err.println("");

    } catch (SQLException ex) {
        System.err.println("-----SQLException-----");
        System.err.println("SQLState:  " + ex.getSQLState());
        System.err.println("Message:  " + ex.getMessage());
        System.err.println("Vendor:  " + ex.getErrorCode());
    }
}

---

From source file:BatchUpdate.java

public static void main(String args[]) {
    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con;/*from www. j a  va2  s. c  o m*/
    Statement stmt;

    try {
        Class.forName("myDriver.ClassName");
    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {
        con = DriverManager.getConnection(url, "myLogin", "myPassword");
        stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        con.setAutoCommit(false);
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut', 49, 9.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
        stmt.addBatch("INSERT INTO COFFEES " + "VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");
        int[] updateCounts = stmt.executeBatch();
        ResultSet uprs = stmt.executeQuery("SELECT * FROM COFFEES");
        System.out.println("Table COFFEES after insertion:");

        while (uprs.next()) {
            String name = uprs.getString("COF_NAME");
            int id = uprs.getInt("SUP_ID");
            float price = uprs.getFloat("PRICE");
            int sales = uprs.getInt("SALES");
            int total = uprs.getInt("TOTAL");
            System.out.print(name + " " + id + " " + price);
            System.out.println(" " + sales + " " + total);
        }
        uprs.close();
        stmt.close();
        con.close();

    } catch (BatchUpdateException b) {
        System.err.println("SQLException: " + b.getMessage());
        System.err.println("SQLState: " + b.getSQLState());
        System.err.println("Message: " + b.getMessage());
        System.err.println("Vendor: " + b.getErrorCode());
        System.err.print("Update counts: ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + " ");
        }

    } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        System.err.println("SQLState: " + ex.getSQLState());
        System.err.println("Message: " + ex.getMessage());
        System.err.println("Vendor: " + ex.getErrorCode());
    }
}

---

From source file:CreateRef.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";

    Connection con;//from w  w  w  . j  a  va2 s  .c om
    Statement stmt;
    try {
        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {
        String createManagers = "CREATE TABLE MANAGERS OF MANAGER "
                + "(OID REF(MANAGER) VALUES ARE SYSTEM GENERATED)";

        String insertManager1 = "INSERT INTO MANAGERS " + "(MGR_ID, LAST_NAME, FIRST_NAME, PHONE) VALUES "
                + "(000001, 'MONTOYA', 'ALFREDO', '8317225600')";

        String insertManager2 = "INSERT INTO MANAGERS " + "(MGR_ID, LAST_NAME, FIRST_NAME, PHONE) VALUES "
                + "(000002, 'HASKINS', 'MARGARET', '4084355600')";

        String insertManager3 = "INSERT INTO MANAGERS " + "(MGR_ID, LAST_NAME, FIRST_NAME, PHONE) VALUES "
                + "(000003, 'CHEN', 'HELEN', '4153785600')";

        con = DriverManager.getConnection(url, "myLogin", "myPassword");

        stmt = con.createStatement();
        stmt.executeUpdate(createManagers);

        con.setAutoCommit(false);

        stmt.addBatch(insertManager1);
        stmt.addBatch(insertManager2);
        stmt.addBatch(insertManager3);
        int[] updateCounts = stmt.executeBatch();

        con.commit();

        System.out.println("Update count for:  ");
        for (int i = 0; i < updateCounts.length; i++) {
            System.out.print("    command " + (i + 1) + " = ");
            System.out.println(updateCounts[i]);
        }

        stmt.close();
        con.close();

    } catch (BatchUpdateException b) {
        System.err.println("-----BatchUpdateException-----");
        System.err.println("Message:  " + b.getMessage());
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts for successful commands:  ");
        int[] rowsUpdated = b.getUpdateCounts();
        for (int i = 0; i < rowsUpdated.length; i++) {
            System.err.print(rowsUpdated[i] + "   ");
        }
        System.err.println("");
    } catch (SQLException ex) {
        System.err.println("------SQLException------");
        System.err.println("Error message:  " + ex.getMessage());
        System.err.println("SQLState:  " + ex.getSQLState());
        System.err.println("Vendor:  " + ex.getErrorCode());
    }
}

---

From source file:InsertStores.java

public static void main(String args[]) {

    String url = "jdbc:mySubprotocol:myDataSource";
    Connection con;//from  ww  w . j a v  a2  s  .  c o m
    Statement stmt;
    try {
        Class.forName("myDriver.ClassName");

    } catch (java.lang.ClassNotFoundException e) {
        System.err.print("ClassNotFoundException: ");
        System.err.println(e.getMessage());
    }

    try {

        con = DriverManager.getConnection(url, "myLogin", "myPassword");

        stmt = con.createStatement();
        con.setAutoCommit(false);

        String insertStore1 = "INSERT INTO STORES VALUES (" + "100001, "
                + "ADDRESS(888, 'Main_Street', 'Rancho_Alegre', " + "'CA', '94049'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))";

        stmt.addBatch(insertStore1);

        String insertStore2 = "INSERT INTO STORES VALUES (" + "100002, "
                + "ADDRESS(1560, 'Alder', 'Ochos_Pinos', " + "'CA', '94049'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000001))";

        stmt.addBatch(insertStore2);

        String insertStore3 = "INSERT INTO STORES VALUES (" + "100003, "
                + "ADDRESS(4344, 'First_Street', 'Verona', " + "'CA', '94545'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))";

        stmt.addBatch(insertStore3);

        String insertStore4 = "INSERT INTO STORES VALUES (" + "100004, "
                + "ADDRESS(321, 'Sandy_Way', 'La_Playa', " + "'CA', '94544'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf', " + "'Kona', 'Kona_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000002))";

        stmt.addBatch(insertStore4);

        String insertStore5 = "INSERT INTO STORES VALUES (" + "100005, "
                + "ADDRESS(1000, 'Clover_Road', 'Happyville', " + "'CA', '90566'), "
                + "COF_ARRAY('Colombian', 'French_Roast', 'Espresso', "
                + "'Colombian_Decaf', 'French_Roast_Decaf'), "
                + "(SELECT OID FROM MANAGERS WHERE MGR_ID = 000003))";

        stmt.addBatch(insertStore5);

        int[] updateCounts = stmt.executeBatch();

        ResultSet rs = stmt.executeQuery("SELECT * FROM STORES");

        System.out.println("Table STORES after insertion:");
        System.out.println("STORE_NO  LOCATION          COF_TYPE     MGR");
        while (rs.next()) {
            int storeNo = rs.getInt("STORE_NO");
            Struct location = (Struct) rs.getObject("LOCATION");
            Object[] locAttrs = location.getAttributes();
            Array coffeeTypes = rs.getArray("COF_TYPE");
            String[] cofTypes = (String[]) coffeeTypes.getArray();

            Ref managerRef = rs.getRef("MGR");
            PreparedStatement pstmt = con.prepareStatement("SELECT MANAGER FROM MANAGERS WHERE OID = ?");
            pstmt.setRef(1, managerRef);
            ResultSet rs2 = pstmt.executeQuery();
            rs2.next();
            Struct manager = (Struct) rs2.getObject("MANAGER");
            Object[] manAttrs = manager.getAttributes();

            System.out.print(storeNo + "   ");
            System.out.print(locAttrs[0] + " " + locAttrs[1] + " " + locAttrs[2] + ", " + locAttrs[3] + "  "
                    + locAttrs[4] + " ");
            for (int i = 0; i < cofTypes.length; i++)
                System.out.print(cofTypes[i] + " ");
            System.out.println(manAttrs[1] + ", " + manAttrs[2]);

            rs2.close();
            pstmt.close();
        }

        rs.close();
        stmt.close();
        con.close();

    } catch (BatchUpdateException b) {
        System.err.println("-----BatchUpdateException-----");
        System.err.println("SQLState:  " + b.getSQLState());
        System.err.println("Message:  " + b.getMessage());
        System.err.println("Vendor:  " + b.getErrorCode());
        System.err.print("Update counts:  ");
        int[] updateCounts = b.getUpdateCounts();
        for (int i = 0; i < updateCounts.length; i++) {
            System.err.print(updateCounts[i] + "   ");
        }
        System.err.println("");

    } catch (SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
        System.err.println("SQLState:  " + ex.getSQLState());
        System.err.println("Message:  " + ex.getMessage());
        System.err.println("Vendor:  " + ex.getErrorCode());
    }
}

---

From source file:org.drugepi.hdps.db.SqlUtils.java

public static void executeSqlBatch(Statement s) throws Exception {
    queryCounter++;/*from w  w  w  .j  a v a  2  s  .  co  m*/

    long startTime = logQueryStart(queryCounter, "SQL Batch");
    s.executeBatch();
    logQueryEnd(queryCounter, startTime);
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• »