Statement or PreparedStatement to execute queries. Apart from that, JDBC provides a batch processing feature through which we can execute a bulk of queries in one go for a database.You can batch both SQL inserts, updates, and deletes. It does not make sense to batch select statements.
There are two ways to execute batch updates:
- Using a
Statement - Using a
PreparedStatement
Steps to Process Update SQL Statement with JDBC
- Establishing a connection.
- Create a statement.
- Execute the query.
- Use try-with-resources statements to automatically close JDBC resources.
From JDBC 4.0, we don't need to include Class.forName() in our code to load the JDBC driver. When the method getConnection is called, the DriverManager will automatically load the suitable driver among the JDBC drivers that were loaded at initialization and those loaded explicitly using the same class loader as the current application.
Any JDBC 4.0 drivers that are found in your classpath are automatically loaded. (However, you must manually load any drivers prior to JDBC 4.0 with the method Class.forName().)
Table of Contents
- Batch Update Example using Statement
- Batch Update Example using PreparedStatement
- Conclusion
1. Batch Update Example using Statement
The Statement interface provides the following methods to perform batch operations:
addBatch(String sql): Adds the given SQL command to the current list of commands for thisStatementobject.executeBatch(): Submits a batch of commands to the database for execution and returns an array of update counts.
Example: Batch Update Using Statement
package com.javaguides.jdbc.batch;
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
/**
* Batch update operation using Statement Interface
*
* @autor Ramesh Fadatare
*/
public class BatchUpdateExample {
public static void main(String[] args) {
batchUpdate();
}
private static void batchUpdate() {
try (Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/jdbc_example?useSSL=false", "root", "password");
Statement statement = connection.createStatement()) {
connection.setAutoCommit(false);
statement.addBatch("UPDATE products SET price = 80000.00 WHERE name = 'Laptop'");
statement.addBatch("UPDATE products SET price = 35000.00 WHERE name = 'Smartphone'");
statement.addBatch("UPDATE products SET price = 55000.00 WHERE name = 'Tablet'");
statement.addBatch("UPDATE products SET price = 22000.00 WHERE name = 'Smartwatch'");
statement.addBatch("UPDATE products SET price = 17000.00 WHERE name = 'Headphones'");
int[] updateCounts = statement.executeBatch();
System.out.println(Arrays.toString(updateCounts));
connection.commit();
} catch (BatchUpdateException batchUpdateException) {
printBatchUpdateException(batchUpdateException);
} catch (SQLException e) {
printSQLException(e);
}
}
public static void printSQLException(SQLException ex) {
for (Throwable e : ex) {
if (e instanceof SQLException) {
e.printStackTrace(System.err);
System.err.println("SQLState: " + ((SQLException) e).getSQLState());
System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
System.err.println("Message: " + e.getMessage());
Throwable t = ex.getCause();
while (t != null) {
System.out.println("Cause: " + t);
t = t.getCause();
}
}
}
}
public static void printBatchUpdateException(BatchUpdateException b) {
System.err.println("----BatchUpdateException----");
System.err.println("SQLState: " + b.getSQLState());
System.err.println("Message: " + b.getMessage());
System.err.println("Vendor: " + b.getErrorCode());
System.err.print("Update counts: ");
int[] updateCounts = b.getUpdateCounts();
for (int i = 0; i < updateCounts.length; i++) {
System.err.print(updateCounts[i] + " ");
}
}
}
Output:
[1, 1, 1, 1, 1]
2. Batch Update Example using PreparedStatement
It is also possible to have a parameterized batch insert or update using PreparedStatement.
Example: Batch Update Using PreparedStatement
package com.javaguides.jdbc.batch;
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Arrays;
/**
* Batch update operation using PreparedStatement Interface
*
* @autor Ramesh Fadatare
*/
public class BatchUpdateExample {
public static void main(String[] args) {
parameterizedBatchUpdate();
}
private static void parameterizedBatchUpdate() {
String UPDATE_PRODUCTS_SQL = "UPDATE products SET price = ? WHERE name = ?";
try (Connection connection = DriverManager
.getConnection("jdbc:mysql://localhost:3306/jdbc_example?useSSL=false", "root", "password");
PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_PRODUCTS_SQL)) {
connection.setAutoCommit(false);
preparedStatement.setBigDecimal(1, new BigDecimal("80000.00"));
preparedStatement.setString(2, "Laptop");
preparedStatement.addBatch();
preparedStatement.setBigDecimal(1, new BigDecimal("35000.00"));
preparedStatement.setString(2, "Smartphone");
preparedStatement.addBatch();
preparedStatement.setBigDecimal(1, new BigDecimal("55000.00"));
preparedStatement.setString(2, "Tablet");
preparedStatement.addBatch();
preparedStatement.setBigDecimal(1, new BigDecimal("22000.00"));
preparedStatement.setString(2, "Smartwatch");
preparedStatement.addBatch();
preparedStatement.setBigDecimal(1, new BigDecimal("17000.00"));
preparedStatement.setString(2, "Headphones");
preparedStatement.addBatch();
int[] updateCounts = preparedStatement.executeBatch();
System.out.println(Arrays.toString(updateCounts));
connection.commit();
} catch (BatchUpdateException batchUpdateException) {
printBatchUpdateException(batchUpdateException);
} catch (SQLException e) {
printSQLException(e);
}
}
public static void printSQLException(SQLException ex) {
for (Throwable e : ex) {
if (e instanceof SQLException) {
e.printStackTrace(System.err);
System.err.println("SQLState: " + ((SQLException) e).getSQLState());
System.err.println("Error Code: " + ((SQLException) e).getErrorCode());
System.err.println("Message: " + e.getMessage());
Throwable t = ex.getCause();
while (t != null) {
System.out.println("Cause: " + t);
t = t.getCause();
}
}
}
}
public static void printBatchUpdateException(BatchUpdateException b) {
System.err.println("----BatchUpdateException----");
System.err.println("SQLState: " + b.getSQLState());
System.err.println("Message: " + b.getMessage());
System.err.println("Vendor: " + b.getErrorCode());
System.err.print("Update counts: ");
int[] updateCounts = b.getUpdateCounts();
for (int i = 0; i < updateCounts.length; i++) {
System.err.print(updateCounts[i] + " ");
}
}
}
Output:
[1, 1, 1, 1, 1]
The Connection.commit method makes the batch of updates to the products table permanent. This method needs to be called explicitly because the auto-commit mode for this connection was disabled previously. The following line enables auto-commit mode for the current Connection object:
connection.setAutoCommit(true);
Now, each statement in the example will automatically be committed after it is executed, and it no longer needs to invoke the method commit.
Conclusion
In this tutorial, we have covered the basics of using JDBC to perform batch updates on a MySQL database table. We demonstrated how to establish a connection, execute a batch of SQL update statements using both Statement and PreparedStatement, and close the connection using the try-with-resources statement. This guide should help you get started with JDBC and understand how to use batch processing effectively with MySQL.
Comments
Post a Comment
Leave Comment