Admin: 1 версия

2010-06-01T06:33:37Z

1 версия

в 18:01, 31 мая 2010

2010-05-31T18:01:44Z

Новая страница

== 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>

← Предыдущая	Версия 06:33, 1 июня 2010
(нет различий)

Java/Database SQL JDBC/Batch Update - История изменений

Admin: 1 версия

в 18:01, 31 мая 2010