Java Batch Update Example With SQL Statement & PreparedStatement
In this example, we are showing how to use Java Batch Update Example With SQL Statement & PreparedStatement
In batch update, update happens on the database as batches, which will increase the processing speed and performance. We can use batch updates for both JDBC Statement & PreparedStatement. You can see the JDBC examples for both Statement & PreparedStatement below.
In this tutorial, we are using H2 database and inserting some data using Java Batch Update
H2 is an open source software implementation of Java SQL database. The main features of H2 are.
- Very fast, open source, JDBC API
- Embedded and server modes; in-memory databases
- Browser based Console application
- Small footprint: around 1.5 MB jar file size
Reference -> http://www.h2database.com/html/main.html
Required Libraries
For using H2 database, You need to download
Project Structure
Batch Update Example Using H2 Database
package com.h2.examples;
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import org.h2.tools.DeleteDbFiles;
/*
Batch Update Example
In this example, we are showing how to use batch update with SQL Statement & PreparedStatement
*/
public class H2DatabaseBatchExample {
private static final String DB_DRIVER = "org.h2.Driver";
private static final String DB_CONNECTION = "jdbc:h2:~/test";
private static final String DB_USER = "";
private static final String DB_PASSWORD = "";
public static void main(String[] args) throws Exception {
try {
// delete the database named 'test' in the user home directory for initialization
DeleteDbFiles.execute("~", "test", true);
batchInsertWithStatement();
batchInsertWithPreparedStatement();
} catch (SQLException e) {
e.printStackTrace();
}
}
//Batch Update With SQL PreparedStatement Example
private static void batchInsertWithPreparedStatement() throws SQLException {
Connection connection = getDBConnection();
PreparedStatement preparedStatement = null;
String Query = "INSERT INTO PERSON" + "(id, name) values" + "(?,?)";
try {
connection.setAutoCommit(false);
preparedStatement = connection.prepareStatement(Query);
preparedStatement.setInt(1, 4);
preparedStatement.setString(2, "Rockey");
preparedStatement.addBatch();
preparedStatement.setInt(1, 5);
preparedStatement.setString(2, "Jacky");
preparedStatement.addBatch();
int[] countWithoutException = preparedStatement.executeBatch();
System.out.println("Inserted = " + countWithoutException.length);
connection.commit();
} catch (BatchUpdateException e) {
System.out.println("Exception Message " + e.getLocalizedMessage());
} catch (Exception e) {
e.printStackTrace();
} finally {
preparedStatement.close();
connection.close();
}
}
//Batch Update With SQL Statement Example
private static void batchInsertWithStatement() throws SQLException {
Connection connection = getDBConnection();
Statement stmt = null;
try {
connection.setAutoCommit(false);
stmt = connection.createStatement();
stmt.execute("CREATE TABLE PERSON(id int primary key, name varchar(255))");
stmt.addBatch("INSERT INTO PERSON(id, name) VALUES(1, 'A')");
stmt.addBatch("INSERT INTO PERSON(id, name) VALUES(2, 'B')");
stmt.addBatch("INSERT INTO PERSON(id, name) VALUES(3, 'C')");
int[] countWithoutException = stmt.executeBatch();
System.out.println("Inserted = " + countWithoutException.length);
connection.commit();
} catch (BatchUpdateException e) {
System.out.println("Exception Message " + e.getLocalizedMessage());
} catch (Exception e) {
e.printStackTrace();
} finally {
stmt.close();
connection.close();
}
}
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return dbConnection;
}
}
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import org.h2.tools.DeleteDbFiles;
/*
Batch Update Example
In this example, we are showing how to use batch update with SQL Statement & PreparedStatement
*/
public class H2DatabaseBatchExample {
private static final String DB_DRIVER = "org.h2.Driver";
private static final String DB_CONNECTION = "jdbc:h2:~/test";
private static final String DB_USER = "";
private static final String DB_PASSWORD = "";
public static void main(String[] args) throws Exception {
try {
// delete the database named 'test' in the user home directory for initialization
DeleteDbFiles.execute("~", "test", true);
batchInsertWithStatement();
batchInsertWithPreparedStatement();
} catch (SQLException e) {
e.printStackTrace();
}
}
//Batch Update With SQL PreparedStatement Example
private static void batchInsertWithPreparedStatement() throws SQLException {
Connection connection = getDBConnection();
PreparedStatement preparedStatement = null;
String Query = "INSERT INTO PERSON" + "(id, name) values" + "(?,?)";
try {
connection.setAutoCommit(false);
preparedStatement = connection.prepareStatement(Query);
preparedStatement.setInt(1, 4);
preparedStatement.setString(2, "Rockey");
preparedStatement.addBatch();
preparedStatement.setInt(1, 5);
preparedStatement.setString(2, "Jacky");
preparedStatement.addBatch();
int[] countWithoutException = preparedStatement.executeBatch();
System.out.println("Inserted = " + countWithoutException.length);
connection.commit();
} catch (BatchUpdateException e) {
System.out.println("Exception Message " + e.getLocalizedMessage());
} catch (Exception e) {
e.printStackTrace();
} finally {
preparedStatement.close();
connection.close();
}
}
//Batch Update With SQL Statement Example
private static void batchInsertWithStatement() throws SQLException {
Connection connection = getDBConnection();
Statement stmt = null;
try {
connection.setAutoCommit(false);
stmt = connection.createStatement();
stmt.execute("CREATE TABLE PERSON(id int primary key, name varchar(255))");
stmt.addBatch("INSERT INTO PERSON(id, name) VALUES(1, 'A')");
stmt.addBatch("INSERT INTO PERSON(id, name) VALUES(2, 'B')");
stmt.addBatch("INSERT INTO PERSON(id, name) VALUES(3, 'C')");
int[] countWithoutException = stmt.executeBatch();
System.out.println("Inserted = " + countWithoutException.length);
connection.commit();
} catch (BatchUpdateException e) {
System.out.println("Exception Message " + e.getLocalizedMessage());
} catch (Exception e) {
e.printStackTrace();
} finally {
stmt.close();
connection.close();
}
}
private static Connection getDBConnection() {
Connection dbConnection = null;
try {
Class.forName(DB_DRIVER);
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
return dbConnection;
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return dbConnection;
}
}
Output
Inserted Count Using SQL Statement = 3 Inserted Count Using SQL PreparedStatement = 2
1 2 Next
You might also like following tutorials :
 |  |  |  |  |