Java Tutorial/Database/Batch Update — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 08:05, 1 июня 2010
Содержание
Add batch SQL command into Statement
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); conn.setAutoCommit(false); 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.rumit(); ResultSet rs = st.executeQuery("SELECT * FROM survey"); outputResultSet(rs); rs.close(); st.close(); conn.close(); } public static void checkUpdateCounts(int[] updateCounts) { for (int i=0; i<updateCounts.length; i++) { if (updateCounts[i] >= 0) { System.out.println("OK; updateCount="+updateCounts[i]); } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) { System.out.println("OK; updateCount=Statement.SUCCESS_NO_INFO"); } else if (updateCounts[i] == Statement.EXECUTE_FAILED) { System.out.println("Failure; updateCount=Statement.EXECUTE_FAILED"); } }
}
private static void outputResultSet(ResultSet rs) throws Exception { ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); for (int i = 1; i < numberOfColumns + 1; i++) { String columnName = rsMetaData.getColumnName(i); System.out.print(columnName + " "); } System.out.println(); System.out.println("----------------------"); while (rs.next()) { for (int i = 1; i < numberOfColumns + 1; i++) { System.out.print(rs.getString(i) + " "); } System.out.println(); } } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}</source>
OK; updateCount=0 OK; updateCount=1 OK; updateCount=1 OK; updateCount=1 ID NAME ---------------------- 444 ginger 555 lola 666 freddy
Check Batch Update count
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); conn.setAutoCommit(false); Statement st = conn .createStatement(); st.executeUpdate("create table survey (id int, name VARCHAR(30) );"); String INSERT_RECORD = "insert into survey(id, name) values(?,?)"; PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD); pstmt.setString(1, "1"); pstmt.setString(2, "name1"); pstmt.addBatch(); pstmt.setString(1, "2"); pstmt.setString(2, "name2"); pstmt.addBatch(); // execute the batch int[] updateCounts = pstmt.executeBatch(); checkUpdateCounts(updateCounts); // since there were no errors, commit conn.rumit(); ResultSet rs = st.executeQuery("SELECT * FROM survey"); outputResultSet(rs); rs.close(); st.close(); conn.close(); } public static void checkUpdateCounts(int[] updateCounts) { for (int i=0; i<updateCounts.length; i++) { if (updateCounts[i] >= 0) { System.out.println("OK; updateCount="+updateCounts[i]); } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) { System.out.println("OK; updateCount=Statement.SUCCESS_NO_INFO"); } else if (updateCounts[i] == Statement.EXECUTE_FAILED) { System.out.println("Failure; updateCount=Statement.EXECUTE_FAILED"); } }
}
private static void outputResultSet(ResultSet rs) throws Exception { ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); for (int i = 1; i < numberOfColumns + 1; i++) { String columnName = rsMetaData.getColumnName(i); System.out.print(columnName + " "); } System.out.println(); System.out.println("----------------------"); while (rs.next()) { for (int i = 1; i < numberOfColumns + 1; i++) { System.out.print(rs.getString(i) + " "); } System.out.println(); } } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}</source>
OK; updateCount=1 OK; updateCount=1 ID NAME ---------------------- 1 name1 2 name2
Check BatchUpdateException
<source lang="java">
import java.sql.BatchUpdateException; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); conn.setAutoCommit(false); Statement st = conn.createStatement(); st.executeUpdate("create table survey (id int, name VARCHAR(30) );"); String INSERT_RECORD = "insert into survey(id, name) values(?,?)"; PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD); pstmt.setString(1, "1"); pstmt.setString(2, "name1"); pstmt.addBatch(); pstmt.setString(1, "2"); pstmt.setString(2, "name2"); pstmt.addBatch(); try { // execute the batch int[] updateCounts = pstmt.executeBatch(); } catch (BatchUpdateException e) { int[] updateCounts = e.getUpdateCounts(); checkUpdateCounts(updateCounts); try { conn.rollback(); } catch (Exception e2) { e.printStackTrace(); System.exit(1); } } // since there were no errors, commit conn.rumit(); ResultSet rs = st.executeQuery("SELECT * FROM survey"); outputResultSet(rs); rs.close(); st.close(); conn.close(); } public static void checkUpdateCounts(int[] updateCounts) { for (int i = 0; i < updateCounts.length; i++) { if (updateCounts[i] >= 0) { System.out.println("OK; updateCount=" + updateCounts[i]); } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) { System.out.println("OK; updateCount=Statement.SUCCESS_NO_INFO"); } else if (updateCounts[i] == Statement.EXECUTE_FAILED) { System.out.println("Failure; updateCount=Statement.EXECUTE_FAILED"); } } } private static void outputResultSet(ResultSet rs) throws Exception { ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); for (int i = 1; i < numberOfColumns + 1; i++) { String columnName = rsMetaData.getColumnName(i); System.out.print(columnName + " "); } System.out.println(); System.out.println("----------------------"); while (rs.next()) { for (int i = 1; i < numberOfColumns + 1; i++) { System.out.print(rs.getString(i) + " "); } System.out.println(); } } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}</source>
ID NAME ---------------------- 1 name1 2 name2
Determining If a Database Supports Scrollable Result Sets
<source lang="java">
import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.ResultSet; 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.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE)) { System.out.println("Insensitive scrollable result sets are supported"); } if (dmd.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE)) { System.out.println("Sensitive scrollable result sets are supported"); } if (!dmd.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE) && !dmd.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE)) { System.out.println("Updatable result sets are not supported"); } }
}</source>
Use Batch Multiple Updates with PreparedStatement
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); conn.setAutoCommit(false); Statement st = conn .createStatement(); st.executeUpdate("create table survey (id int, name VARCHAR(30) );"); String INSERT_RECORD = "insert into survey(id, name) values(?,?)"; PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD); pstmt.setString(1, "1"); pstmt.setString(2, "name1"); pstmt.addBatch(); pstmt.setString(1, "2"); pstmt.setString(2, "name2"); pstmt.addBatch(); // execute the batch int[] updateCounts = pstmt.executeBatch(); checkUpdateCounts(updateCounts); // since there were no errors, commit conn.rumit(); ResultSet rs = st.executeQuery("SELECT * FROM survey"); outputResultSet(rs); rs.close(); st.close(); conn.close(); } public static void checkUpdateCounts(int[] updateCounts) { for (int i=0; i<updateCounts.length; i++) { if (updateCounts[i] >= 0) { System.out.println("OK; updateCount="+updateCounts[i]); } else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) { System.out.println("OK; updateCount=Statement.SUCCESS_NO_INFO"); } else if (updateCounts[i] == Statement.EXECUTE_FAILED) { System.out.println("Failure; updateCount=Statement.EXECUTE_FAILED"); } }
}
private static void outputResultSet(ResultSet rs) throws Exception { ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); for (int i = 1; i < numberOfColumns + 1; i++) { String columnName = rsMetaData.getColumnName(i); System.out.print(columnName + " "); } System.out.println(); System.out.println("----------------------"); while (rs.next()) { for (int i = 1; i < numberOfColumns + 1; i++) { System.out.print(rs.getString(i) + " "); } System.out.println(); } } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}</source>
OK; updateCount=1 OK; updateCount=1 ID NAME ---------------------- 1 name1 2 name2
Using a Loop to Set Values
<source lang="java">
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Statement; public class Main {
public static void main(String[] args) throws Exception { Connection conn = getConnection(); conn.setAutoCommit(false); Statement st = conn .createStatement(); st.executeUpdate("create table survey (id int, name VARCHAR(30) );"); String INSERT_RECORD = "insert into survey(id, name) values(?,?)"; PreparedStatement pstmt = conn.prepareStatement(INSERT_RECORD); for(int i=0;i<10;i++){ pstmt.setInt(1, i); pstmt.setString(2, "name"+i); pstmt.executeUpdate(); }
ResultSet rs = st.executeQuery("SELECT * FROM survey"); outputResultSet(rs); rs.close(); st.close(); conn.close(); } private static void outputResultSet(ResultSet rs) throws Exception { ResultSetMetaData rsMetaData = rs.getMetaData(); int numberOfColumns = rsMetaData.getColumnCount(); for (int i = 1; i < numberOfColumns + 1; i++) { String columnName = rsMetaData.getColumnName(i); System.out.print(columnName + " "); } System.out.println(); System.out.println("----------------------"); while (rs.next()) { for (int i = 1; i < numberOfColumns + 1; i++) { System.out.print(rs.getString(i) + " "); } System.out.println(); } } private static Connection getConnection() throws Exception { Class.forName("org.hsqldb.jdbcDriver"); String url = "jdbc:hsqldb:mem:data/tutorial"; return DriverManager.getConnection(url, "sa", ""); }
}</source>
ID NAME ---------------------- 0 name0 1 name1 2 name2 3 name3 4 name4 5 name5 6 name6 7 name7 8 name8 9 name9