Java/Database SQL JDBC/Batch Update

Материал из Java эксперт
Перейти к: навигация, поиск

Batch update: add Batch commands

   <source lang="java">

import java.sql.BatchUpdateException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Connection conn = getMySqlConnection();
   ResultSet rs = null;
   Statement stmt = null;
   try {
       conn = getMySqlConnection();
       stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                   ResultSet.CONCUR_UPDATABLE);
       conn.setAutoCommit(false);
       stmt.addBatch("INSERT INTO survey(id, name) VALUES("11", "Alex")");
       stmt.addBatch("INSERT INTO survey(id, name) VALUES("22", "Mary")");
       stmt.addBatch("INSERT INTO survey(id, name) VALUES("33", "Bob")");
       int[] updateCounts = stmt.executeBatch();
       System.out.println(updateCounts);
       conn.rumit();
       rs = stmt.executeQuery("SELECT * FROM survey");
       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) {
       e.printStackTrace();
       System.err.println("Exception: " + e.getMessage());
   }
   finally {
       rs.close();
       stmt.close();
       conn.close();
   }
 }
 private static Connection getHSQLConnection() throws Exception {
   Class.forName("org.hsqldb.jdbcDriver");
   System.out.println("Driver Loaded.");
   String url = "jdbc:hsqldb:data/tutorial";
   return DriverManager.getConnection(url, "sa", "");
 }
 public static Connection getMySqlConnection() throws Exception {
   String driver = "org.gjt.mm.mysql.Driver";
   String url = "jdbc:mysql://localhost/demo2s";
   String username = "oost";
   String password = "oost";
   Class.forName(driver);
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static Connection getOracleConnection() throws Exception {
   String driver = "oracle.jdbc.driver.OracleDriver";
   String url = "jdbc:oracle:thin:@localhost:1521:caspian";
   String username = "mp";
   String password = "mp2";
   Class.forName(driver); // load Oracle driver
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }

}


 </source>
   
  
 
  



Batch Update Demo

   <source lang="java">

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class MainClass {

 public static void main(String[] args) {
   Connection connection = null;
   Statement statement = null;
   try {
     Class.forName("com.mysql.jdbc.Driver").newInstance();
     String url = "jdbc:mysql://localhost/chapter04_jdbc21";
     connection = DriverManager.getConnection(url, "username", "password");
     statement = connection.createStatement();
     String update1 = "UPDATE employees SET email = "a@a.ru" WHERE email = "a@b.ru"";
     statement.addBatch(update1);
     String update2 = "UPDATE employees SET email = "b@b.ru" WHERE email = "b@c.ru"";
     statement.addBatch(update2);
     String update3 = "UPDATE employees SET email = "c@c.ru" WHERE email = "c@d.ru"";
     statement.addBatch(update3);
     statement.executeBatch();
   } catch (Exception e) {
     e.printStackTrace();
   } finally {
     if (statement != null) {
       try {
         statement.close();
       } catch (SQLException e) {
       } // nothing we can do
     }
     if (connection != null) {
       try {
         connection.close();
       } catch (SQLException e) {
       } // nothing we can do
     }
   }
 }

}


 </source>
   
  
 
  



Batch update for MySQL

   <source lang="java">

import java.sql.DriverManager; import java.sql.Connection; import java.sql.Statement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.BatchUpdateException; public class TestBatchUpdate {

 public static Connection getConnection() throws Exception {
   String driver = "org.gjt.mm.mysql.Driver";
   String url = "jdbc:mysql://localhost/octopus";
   String username = "root";
   String password = "root";
   Class.forName(driver);
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static void main(String args[]) {
   Connection conn = null;
   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.rumit();
     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) {
     }
   }
 }

}


 </source>
   
  
 
  



Batch Update Insert

   <source lang="java">

import java.sql.BatchUpdateException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Connection conn = getMySqlConnection();
   ResultSet rs = null;
   Statement stmt = null;
   try {
       conn = getMySqlConnection();
       stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                   ResultSet.CONCUR_UPDATABLE);
       conn.setAutoCommit(false);
       stmt.addBatch("INSERT INTO survey(id, name) VALUES("11", "Alex")");
       stmt.addBatch("INSERT INTO survey(id, name) VALUES("22", "Mary")");
       stmt.addBatch("INSERT INTO survey(id, name) VALUES("33", "Bob")");
       int[] updateCounts = stmt.executeBatch();
       System.out.println(updateCounts);
       conn.rumit();
       rs = stmt.executeQuery("SELECT * FROM survey");
       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) {
       e.printStackTrace();
       System.err.println("Exception: " + e.getMessage());
   }
   finally {
       rs.close();
       stmt.close();
       conn.close();
   }
 }
 private static Connection getHSQLConnection() throws Exception {
   Class.forName("org.hsqldb.jdbcDriver");
   System.out.println("Driver Loaded.");
   String url = "jdbc:hsqldb:data/tutorial";
   return DriverManager.getConnection(url, "sa", "");
 }
 public static Connection getMySqlConnection() throws Exception {
   String driver = "org.gjt.mm.mysql.Driver";
   String url = "jdbc:mysql://localhost/demo2s";
   String username = "oost";
   String password = "oost";
   Class.forName(driver);
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static Connection getOracleConnection() throws Exception {
   String driver = "oracle.jdbc.driver.OracleDriver";
   String url = "jdbc:oracle:thin:@localhost:1521:caspian";
   String username = "mp";
   String password = "mp2";
   Class.forName(driver); // load Oracle driver
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }

}


 </source>
   
  
 
  



Check Batch Update Result

   <source lang="java">

import java.sql.BatchUpdateException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Connection conn = getMySqlConnection();
   ResultSet rs = null;
   Statement stmt = null;
   try {
       conn = getMySqlConnection();
       stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                                   ResultSet.CONCUR_UPDATABLE);
       conn.setAutoCommit(false);
       stmt.addBatch("INSERT INTO survey(id, name) VALUES("11", "Alex")");
       stmt.addBatch("INSERT INTO survey(id, name) VALUES("22", "Mary")");
       stmt.addBatch("INSERT INTO survey(id, name) VALUES("33", "Bob")");
       int[] updateCounts = stmt.executeBatch();
       System.out.println(updateCounts);
       conn.rumit();
       rs = stmt.executeQuery("SELECT * FROM survey");
       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) {
       e.printStackTrace();
       System.err.println("Exception: " + e.getMessage());
   }
   finally {
       rs.close();
       stmt.close();
       conn.close();
   }
 }
 private static Connection getHSQLConnection() throws Exception {
   Class.forName("org.hsqldb.jdbcDriver");
   System.out.println("Driver Loaded.");
   String url = "jdbc:hsqldb:data/tutorial";
   return DriverManager.getConnection(url, "sa", "");
 }
 public static Connection getMySqlConnection() throws Exception {
   String driver = "org.gjt.mm.mysql.Driver";
   String url = "jdbc:mysql://localhost/demo2s";
   String username = "oost";
   String password = "oost";
   Class.forName(driver);
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static Connection getOracleConnection() throws Exception {
   String driver = "oracle.jdbc.driver.OracleDriver";
   String url = "jdbc:oracle:thin:@localhost:1521:caspian";
   String username = "mp";
   String password = "mp2";
   Class.forName(driver); // load Oracle driver
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }

}


 </source>
   
  
 
  



Create a batch update in JDBC

   <source lang="java">
 

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class Main {

 public static void main(String[] args) throws Exception {
   Class.forName("com.mysql.jdbc.Driver");
   Connection conn = null;
   try {
     conn = DriverManager.getConnection("jdbc:mysql://localhost/testdb", "root", "");
     conn.setAutoCommit(false);
     Statement st = conn.createStatement();
     st.addBatch("INSERT INTO products (id, name, quantity, price) VALUE ("1", "M", 10.99, 20)");
     st.addBatch("INSERT INTO products (id, name, quantity, price) VALUE ("2", "D", 15.99, 19)");
     st.addBatch("INSERT INTO products (id, name, quantity, price) VALUE ("3", "T", 5.99, 25)");
     st.addBatch("INSERT INTO products (id, name, quantity, price) VALUE ("4", "S", 8.99, 50)");
     int[] updateCounts = st.executeBatch();
     conn.rumit();
   } catch (SQLException e) {
     if (conn != null) {
       conn.rollback();
     }
     e.printStackTrace();
   } finally {
     if (conn!= null) {
       conn.close();
     }
   }
 }

}


 </source>
   
  
 
  



Deal with batch update exception and results

   <source lang="java">

import java.sql.BatchUpdateException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class DemoBatchUpdateException {

 public static Connection getMySQLConnection() throws Exception {
   String driver = "org.gjt.mm.mysql.Driver";
   String url = "jdbc:mysql://localhost/databaseName";
   String username = "root";
   String password = "root";
   Class.forName(driver);
   return DriverManager.getConnection(url, username, password);
 }  
 public static void checkUpdateCounts(int[] updateCounts) {
   for (int i = 0; i < updateCounts.length; i++) {
     if (updateCounts[i] >= 0) {
       // Successfully executed; the number represents number of affected rows
       System.out.println("OK: updateCount=" + updateCounts[i]);
     } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
       // Successfully executed; number of affected rows not available
       System.out.println("OK: updateCount=Statement.SUCCESS_NO_INFO");
     } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
       System.out.println("updateCount=Statement.EXECUTE_FAILED");
     }
   }
 }
 public static void main(String[] args)throws Exception {
   Connection conn = getMySQLConnection();
   Statement stmt = null;
   try {
     conn.setAutoCommit(false);
     stmt = conn.createStatement();
     stmt.addBatch("DELETE FROM animalsTable");
     stmt.addBatch("INSERT INTO animalsTable(id, name) VALUES(1, "A")");
     stmt.addBatch("INSERT INTO animalsTable(id, name) VALUES(2, "B")");
     stmt.addBatch("INSERT INTO animalsTable(id, name) VALUES(3, "C")");
     int[] updateCounts = stmt.executeBatch();
     checkUpdateCounts(updateCounts);
     conn.rumit();
   } catch (BatchUpdateException e) {
     int[] updateCounts = e.getUpdateCounts();
     checkUpdateCounts(updateCounts);
     try {
       conn.rollback();
     } catch (Exception e2) {
       e.printStackTrace();
     }
   } catch (Exception e) {
     e.printStackTrace();
   } finally {
     stmt.close();
     conn.close();
   }
 }

}


 </source>
   
  
 
  



Demo Prepared Statement Add Batch MySQL

   <source lang="java">

import java.sql.BatchUpdateException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.Statement; public class DemoPreparedStatementAddBatchMySQL {

 public static Connection getConnection() throws Exception {
   String driver = "org.gjt.mm.mysql.Driver";
   String url = "jdbc:mysql://localhost/databaseName";
   String username = "root";
   String password = "root";
   Class.forName(driver);
   Connection conn = DriverManager.getConnection(url, username, password);
   return conn;
 }
 public static void checkUpdateCounts(int[] updateCounts) {
   for (int i = 0; i < updateCounts.length; i++) {
     if (updateCounts[i] >= 0) {
       System.out.println("Successfully executed; updateCount=" + updateCounts[i]);
     } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
       System.out.println("Successfully executed; updateCount=Statement.SUCCESS_NO_INFO");
     } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
       System.out.println("Failed to execute; updateCount=Statement.EXECUTE_FAILED");
     }
   }
 }
 public static void main(String[] args)throws Exception {
   Connection conn = null;
   PreparedStatement pstmt = null;
   try {
     conn = getConnection();
     conn.setAutoCommit(false);
     String query = "insert into add_batch_table(stringCol, intCol) values(?, ?)";
     pstmt = conn.prepareStatement(query);
     pstmt.setString(1, "1");
     pstmt.setInt(2, 100);
     pstmt.addBatch();
     pstmt.setString(1, "2");
     pstmt.setInt(2, 200);
     pstmt.addBatch();
     pstmt.setString(1, "3");
     pstmt.setInt(2, 300);
     pstmt.addBatch();
     int[] updateCounts = pstmt.executeBatch();
     checkUpdateCounts(updateCounts);
     conn.rumit();
   } catch (BatchUpdateException e) {
     int[] updateCounts = e.getUpdateCounts();
     checkUpdateCounts(updateCounts);
     try {
       conn.rollback();
     } catch (Exception e2) {
       e.printStackTrace();
     }
   } catch (Exception e) {
     e.printStackTrace();
   } finally {
     pstmt.close();
     conn.close();
   }
 }

}


 </source>
   
  
 
  



Determining If a Database Supports Batching

   <source lang="java">
 

import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; public class Main {

 public static void main(String[] argv) throws Exception {
   String driverName = "com.jnetdirect.jsql.JSQLDriver";
   Class.forName(driverName);
   String serverName = "127.0.0.1";
   String portNumber = "1433";
   String mydatabase = serverName + ":" + portNumber;
   String url = "jdbc:JSQLConnect://" + mydatabase;
   String username = "username";
   String password = "password";
   Connection connection = DriverManager.getConnection(url, username, password);
   DatabaseMetaData dmd = connection.getMetaData();
   if (dmd.supportsBatchUpdates()) {
     // Batching is supported
   } else {
     // Batching is not supported
   }
 }

}


 </source>
   
  
 
  



Prepared Statement With Batch Update

   <source lang="java">
 

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class Main {

 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);
   String sql = "INSERT product VALUES(?,?)";
   PreparedStatement prest = con.prepareStatement(sql);
   prest.setString(1, "A");
   prest.setInt(2, 2002);
   prest.addBatch();
   prest.setString(1, "B");
   prest.setInt(2, 1998);
   prest.addBatch();
   prest.setString(1, "C");
   prest.setInt(2, 1980);
   prest.addBatch();
   prest.setString(1, "D");
   prest.setInt(2, 1975);
   prest.addBatch();
   int count[] = prest.executeBatch();
   con.rumit();
   con.close();
 }

}


 </source>
   
  
 
  



Statement Batch Update

   <source lang="java">
 

import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class Main {

 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);
   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.rumit();
   con.close();
   System.out.println("Successfully!");
 }

}


 </source>