JDBC Batch Insert-Update Records using Oracle
- Tuesday, October 18, 2011, 17:47
- JDBC Oracle, JDBC Tutorial
- Add a comment
The Statement and PreperedStatement interface family supports batch processing that enables you to submit multiple DML statements with one call to the database. This can help you minimize the number of database calls you make.To minimize the number of calls, you can send a batch of statements with one call and execute them together. You can also inform the database to undo all the changes in the batch if one statement fails. This transactional approach will ensure data integrity and consistency by preventing “orphan” data from being written to the database.
JDBC 2.0 and beyond supports batch processing of INSERT and UPDATE statements, which may be useful in the scenarios I describe here. However, JDBC drivers are not required to support this feature. You should use the DatabaseMetaData.supportsBatchUpdates() method to determine if the target database supports batch update processing. The method returns true if your JDBC driver supports this feature.
Let’s now look at an example to see batch update with the Statement Object.
import java.sql.*; import java.util.*; import java.io.*; public class BatchUpdateDemo { public static void main(String s[]) throws Exception { Driver d= (Driver) ( Class.forName( "oracle.jdbc.driver.OracleDriver").newInstance()); Properties p=new Properties (); p.put("user","scott"); p.put("password","tiger"); Connection con=d.connect("jdbc:oracle:thin:@mysys:1521:khan",p); Statement st=con.createStatement(); //statement1 st.addBatch("insert into emp(empno,sal,deptno)"+ " values("+s[0]+",1000,10)"); //statement2 st.addBatch("update emp set sal=2000 where empno="+s[0]); //statement3 st.addBatch("insert into emp(empno,sal,deptno) values(202,1000,10)"); //statement4 st.addBatch("insert into emp(empno,sal,deptno) values(203,1000,10)"); try { int[] counts=st.executeBatch(); System.out.println("Batch Executed Successfully"); for (int i=0;i< counts.length;i++){ System.out.println("Records effected by statement"+(i+1)+ ": "+counts[i]); }//for }//try catch(BatchUpdateException e){ System.out.println("Batch terminated with an abnormal condition"); int[] counts=e.getUpdateCounts(); System.out.println("Batch terminated at statement"+(counts.length+1)); for (int i=0;i< counts.length;i++) { System.out.println("Records effected by the statement"+ (i+1)+ ": "+counts[i]); }//for }//catch con.close(); }//main }//class
About the Author
Tousif Khan has written 22 posts on this blog.
