Batch_PreparedStatement2
Also see : Batch_preparedStatement3.htm
  • This example uses an alternate way of batch updating with prepared statement
  • Also uses Statement object to feed Result set  with SQL query.
  • Autocommit False::
    c.setAutoCommit(false);
    //st = c.createStatement();
    //7369 name SMITH JOB CLERK manager 7902 hired1980-12-17 sal 800.0
    //7521 name WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
    String sql1 = "UPDATE emp SET sal= ? WHERE EMPNO = ?";
    ps=c.prepareStatement(sql1);
    ps.setDouble(1,1500.00);
    ps.setInt(2, 7369);
    ps.addBatch();
    //ps.executeBatch();
    ps.setDouble(1,1950.00);
    ps.setInt(2, 7521);
    ps.addBatch();
    int[] affectedRecords = ps.executeBatch();
    c.commit();

Batch / update fields in two rows

Code :

package javatemplate1;

import java.sql.*;
import java.util.Properties;
import java.io.*;
/**
*
* @author Manas14
*/
public class JavaTemplate1 {
// private static String dbURL;

/**
* @param args the command line arguments
*/
public static void main(String[] args) {
// TODO code application logic here
System.out.println("main block executing ");
Connection c = null; Statement st = null;
String dbURL = "jdbc:postgresql://localhost:5432/pgsdemo1";
String user = "postgres"; String pwd = "postgre_manas9";
PreparedStatement ps= null;
// connecting to db
try {
c = DriverManager.getConnection(dbURL,user,pwd);
System.out.println("Connected to database successfully");
c.setAutoCommit(false);
st = c.createStatement();
//7369 name SMITH JOB CLERK manager 7902 hired1980-12-17 sal 800.0
//7521 name WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
String sql1 = "UPDATE emp SET sal= ? WHERE EMPNO = ?";
ps=c.prepareStatement(sql1);
ps.setDouble(1,1500.00);
ps.setInt(2, 7369);
ps.addBatch();
//ps.executeBatch();
ps.setDouble(1,1950.00);
ps.setInt(2, 7521);
ps.addBatch();
int[] affectedRecords = ps.executeBatch();
c.commit();
ResultSet rs = st.executeQuery("SELECT * FROM emp;");
// rs = st.execute();
while(rs.next())
{
int eno= rs.getInt("EMPNO");
String name = rs.getString("ename");
String job = rs.getString("JOB");
int manager = rs.getInt("MGR");
Date date = rs.getDate("HIREDATE");
float salary = rs.getFloat("SAL");
// double commision = rs.getInt("COMM");
int deptno = rs.getInt("DEPTNO");
System.out.println("deptno " + deptno + " eno " + eno+ " name "+
name + " JOB " + job +" manager " + manager +
" hired" + date + " sal " + salary );

}
System.out.println(" affectedRecords " +affectedRecords.length);
} catch (SQLException ex) { ex.getErrorCode();
String message = ex.getMessage();
System.out.println(message);
} finally {
System.out.println("going through final block");
try {
if (ps== null || ps.isClosed()) {
} else {
ps.close();
}
if (c != null && !c.isClosed()) {
c.close(); }
} catch (SQLException ex) { ex.getErrorCode();
ex.getMessage();
}
}
System.out.println("dis-Connected to database successfully");
// st.close(); con.commit();con.close();
}

}

Result