jdbc_transactions.java

/////////////

// use transactions

try
{
  // close auto commit
  conn.setAutoCommit(false);

   //1 or more queries or updates

   // commit all operation
   conn.commit();
}
catch(Exception e)
{
   // if exception, roll back
   conn.rollback();
}
finally
{
   conn.close();
}

/////////////////

// use batch updates
/*
Use DatabaseMetaData.supportBatchUpdates() method to determine if your JDBC support the functionality
Use createStatement() to create a Statement object.
Set setAutoCommit(false).
Use addBatch() to add multiple SQL statements for batch processing.
Use executeBatch() to get a counter list.
commit() to eecute all SQL statements.
*/

// ex 1 - createStatement

// create a statement object
Statement stmt = conn.createStatement();

// close auto commit
conn.setAutoCommit(false);

// create a SQL statement
String SQL1 = "INSERT INTO Students (id, name, age) VALUES(6,'Mike', 21)";
// add SQL statement to stmt
stmt.addBatch(SQL1);

// another SQL statement
String SQL2 = "INSERT INTO Students (id, name, age) VALUES(7, 'Angle', 23)";
// add SQL statement to stmt
stmt.addBatch(SQL);

// create an array to record update
int[] count = stmt.executeBatch();

// commit
conn.commit();


// ex 2 - prepareStatement

// create a SQL statement
String SQL = "INSERT INTO students(id, name, age) VALUES(?, ?, ?)";

// create a PrepareStatement object
PreparedStatemen pstmt = conn.prepareStatement(SQL);

// close auto commit
conn.setAutoCommit(false);

// bind params
pstmt.setInt( 1, 8 );
pstmt.setString( 2, "Cindy" );
pstmt.setInt( 3, 17 );

// add SQL statement to pstmt 
pstmt.addBatch();

// bind params
pstmt.setInt( 1, 9 );
pstmt.setString( 2, "Jeff" );
pstmt.setInt( 3, 22 );

// add SQL statement to pstmt
pstmt.addBatch();

// create an array to record insert
int[] count = pstmt.executeBatch();

// commit
conn.commit()