Java/Database SQL JDBC/Batch Update
Содержание
- 1 Batch update: add Batch commands
- 2 Batch Update Demo
- 3 Batch update for MySQL
- 4 Batch Update Insert
- 5 Check Batch Update Result
- 6 Create a batch update in JDBC
- 7 Deal with batch update exception and results
- 8 Demo Prepared Statement Add Batch MySQL
- 9 Determining If a Database Supports Batching
- 10 Prepared Statement With Batch Update
- 11 Statement Batch Update
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>