Inserting Records in Batch Using JDBC
Last Updated :
07 Jul, 2021
It is carried out using the functions namely addBatch() and executeBatch() methods. For that lets us do have a prior understanding of JDBC drivers. So, in order to connect to your database in Java, you need a JDBC driver. Every database (MySQL, Oracle, etc.) comes with its own JDBC driver, usually built by the database vendor and found on the database’s website. Considering the example of the MySQL database, you will need to go to the MySQL website, download the MySQL JDBC driver .jar file (also called Connector/J), and add it to your project.
Now let's discuss the methods that they operate on. So, the above two methods namely addBatch() and executeBatch() method is as follows:
Method 1: addBatch()
This method adds the parameter values to the batch internally. You can now add another set of values, to be inserted into the SQL statement. Each set of parameters are inserted into the SQL and executed separately, once the full batch is sent to the database.
Method 2: executeBatch()
This method is called, which executes all the batch updates. The SQL statement plus the parameter sets are sent to the database in one go. The int[] array returned by the executeBatch() method is an array of int telling how many records were affected by each executed SQL statement in the batch.
Syntax: Creation of the database
create table emp(eid varchar(200) unique,ename varchar(200)
Example:
Java
// Step 1: Importing DB(SQL) classes
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
// Class
public class insertion {
// Main driver method
public static void main(String[] args) throws Exception
{
// Try block to check for exceptions
try {
// Step 2: Loading and registering drivers
// Loading drivers using forName() method
Class.forName("com.mysql.jdbc.Driver");
// Registering driver using DriverManager
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost/test", "root", "");
// Step 3: Create a statement
Statement st = con.createStatement();
String sql
= "insert into emp (eid,ename) values (?, ?)";
// Step 4: Execute the query
PreparedStatement pstmt;
pstmt = con.prepareStatement(sql);
final int batchSize = 5000;
int count = 0;
for (int i = 4000; i <= 4500; i++) {
pstmt.setString(1, "181FA0" + i);
pstmt.setString(2, "181FA0" + i);
pstmt.addBatch();
count++;
if (count % batchSize == 0) {
System.out.println("Commit the batch");
pstmt.executeBatch();
}
pstmt.executeBatch();
}
}
catch (Exception e) {
System.out.println("Error:" + e.getMessage());
}
}
}
Output:
eid | ename |
---|
181FA04001 | 181FA04001 |
181FA04002 | 181FA04002 |
. . . | . . . |
181FA04500 | 181FA04500 |
Similar Reads
How to Insert Records to a Table using JDBC Connection? Before inserting contents in a table we need to connect our java application to our database. Java has its own API which JDBC API which uses JDBC drivers for database connections. Before JDBC, ODBC API was used but it was written in C which means it was platform-dependent. JDBC API provides the appl
4 min read
Inserting Single and Multiple Records in MySQL using Java Java Database Connectivity is an API that helps us to connect a Java program to any SQL Database Instance. This connection gives power to developers to programmatically insert, delete, or update data from the database. JDBC contains in-built methods to run queries and updates on the database. In thi
6 min read
Hibernate - Batch Processing Hibernate is storing the freshly inserted objects in the second-level cache. Because of this, there is always the possibility of OutOfMemoryException when  Inserting more than one million objects. But there will be situations to inserting huge data into the database. This can be accomplished by batc
5 min read
Spring Batch - Job Scheduling and Job Execution Spring Batch is a framework for building robust and scalable batch-processing applications in Java. It provides features for job scheduling and job execution, making it well-suited for tasks such as data extraction, transformation, and loading (ETL), report generation, and more. In this explanation,
3 min read
Delete Contents From Table Using JDBC JDBC(Java Database Connectivity) is a standard API(application interface) between the java programming language and various databases like Oracle, SQL, PostgreSQL, etc. It connects the front end(for interacting with the users) with the backend(for storing data). Approach: 1. CREATE DATABASE: Create
2 min read