Java & MySQL – Batching with Statement Object
Here is a typical sequence of steps to use Batch Processing with Statement Object −
-
Create a Statement object using either createStatement() methods.
-
Set auto-commit to false using setAutoCommit().
-
Add as many as SQL statements you like into batch using addBatch() method on created statement object.
-
Execute all the SQL statements using executeBatch() method on created statement object.
-
Finally, commit all the changes using commit() method.
This sample code has been written based on the environment and database setup done in the previous chapters.
Copy and paste the following example in TestApplication.java, compile and run as follows −
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestApplication { static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; static final String USER = "guest"; static final String PASS = "guest123"; public static void printResultSet(ResultSet rs) throws SQLException{ // Ensure we start with first row rs.beforeFirst(); while(rs.next()){ // Display values System.out.print("ID: " + rs.getInt("id")); System.out.print(", Age: " + rs.getInt("age")); System.out.print(", First: " + rs.getString("first")); System.out.println(", Last: " + rs.getString("last")); } System.out.println(); } public static void main(String[] args) { // Open a connection try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS); Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE) ) { conn.setAutoCommit(false); ResultSet rs = stmt.executeQuery("Select * from Employees"); printResultSet(rs); // Create SQL statement String SQL = "INSERT INTO Employees (first, last, age) " + "VALUES(''Zia'', ''Ali'', 30)"; // Add above SQL statement in the batch. stmt.addBatch(SQL); // Create one more SQL statement SQL = "INSERT INTO Employees (first, last, age) " + "VALUES(''Raj'', ''Kumar'', 35)"; // Add above SQL statement in the batch. stmt.addBatch(SQL); // Create one more SQL statement SQL = "UPDATE Employees SET age = 35 " + "WHERE id = 7"; // Add above SQL statement in the batch. stmt.addBatch(SQL); // Create an int[] to hold returned values int[] count = stmt.executeBatch(); //Explicitly commit statements to apply changes conn.commit(); rs = stmt.executeQuery("Select * from Employees"); printResultSet(rs); stmt.close(); rs.close(); } catch (SQLException e) { e.printStackTrace(); } } }
Now let us compile the above example as follows −
C:>javac TestApplication.java C:>
When you run TestApplication, it produces the following result −
C:>java TestApplication ID: 1, Age: 23, First: Zara, Last: Ali ID: 2, Age: 30, First: Mahnaz, Last: Fatma ID: 3, Age: 35, First: Zaid, Last: Khan ID: 4, Age: 33, First: Sumit, Last: Mittal ID: 5, Age: 40, First: John, Last: Paul ID: 7, Age: 20, First: Sita, Last: Singh ID: 8, Age: 20, First: Rita, Last: Tez ID: 9, Age: 20, First: Sita, Last: Singh ID: 1, Age: 23, First: Zara, Last: Ali ID: 2, Age: 30, First: Mahnaz, Last: Fatma ID: 3, Age: 35, First: Zaid, Last: Khan ID: 4, Age: 33, First: Sumit, Last: Mittal ID: 5, Age: 40, First: John, Last: Paul ID: 7, Age: 35, First: Sita, Last: Singh ID: 8, Age: 20, First: Rita, Last: Tez ID: 9, Age: 20, First: Sita, Last: Singh ID: 10, Age: 30, First: Zia, Last: Ali ID: 11, Age: 35, First: Raj, Last: Kumar C:>