Updated April 17, 2023
Introduction to JDBC Batch Insert
The following article provides an outline for JDBC Batch Insert. Java database connectivity, which is JDBC, provides the different types of functionalities to the user, in which those batches insert is one of the functionalities that are provided by the JDBC. Basically, JDBC is a Java API used to make the conversation with a database as per user requirements. Batch insert means we can execute more than one insert statement, which is we are able to execute a bunch of insert statements over the single network as per user requirement. Normally batch operation is used to increase the performance of the database as well as it is also helpful for the data consistency that is the primary goal of batch processing.
Syntax of JDBC Batch Insert
We have multiple syntaxes for batch insert operation, but we will see batch insert by using the PreparedStatement object as follows.
PreparedStatement p_stmt_obj = con_obj.prepareStatement(“insert into specified table name or user defined table name values(value for colm 1, value for colm 2, ……value for colm N)”);Explanation:
- In the above syntax, we use the preparedStatement object to implement the Batch insert operation as shown in the above syntax. Here first we created an object of method and after that, we wrote the statement for insert query.
- In the insert statement, we need to pass the user-defined table name that is the specified table that we want with values as per user requirement.
How does Batch Insert performs in JDBC?
The JDBC Batch is utilized for doing mass additions into a social information base. The Batch activity uncovered in JDBC (Java Database Connectivity API) assists with packaging together with a gathering of tasks and executes them as a solitary unit. This assists with trying not to settle on rehashed information base decisions for every activity and consequently saves the number of organization calls to be made to the data set.
It is important that when executing a lot of tasks in a cluster at least one activity could bomb, prompting an unsteady condition of the information base; consequently, we will run the group tasks in exchange units. Consider nuclear units. This would guarantee that if any of the tasks in the clump falls flat, the entire cluster fizzles. Furthermore, if all tasks in the bunch succeed, the entire cluster succeeds
Execution and information consistency is the essential intentions to do clump preparing.
- It is used to improve the performance: Some utilization cases require a lot of information to be embedded into a data set table. While utilizing JDBC, one of the approaches to accomplish this without bunch handling, is to execute numerous questions consecutively.
- It provides the data consistency: In specific conditions, information should be driven into different tables. This prompts an interrelated exchange where the grouping of inquiries being pushed is significant. Any mistakes happening during execution should bring about a rollback of the information moved by past questions assuming any.
How Batch Insert Works?
You can stack clumps of information into Vertica utilizing arranged INSERT proclamations server-side articulations that you set up once, and afterwards, call over and again. You launch an individual from the PreparedStatement class with a SQL articulation that contains an incentive for segment 1 thus placeholders for information. Then, at that point set the boundaries utilizing information type-explicit techniques on the PreparedStatement object, like setString() and setInt(). When your boundaries are set, call the addBatch() strategy to add the line to the group. At the point when you have a total bunch of information prepared, call the executeBatch() strategy to execute the supplement cluster.
In the background, the clump embed is changed over into a COPY proclamation. At the point when the association’s AutoCommit boundary is debilitated, Vertica keeps the COPY proclamation open and uses it to stack ensuing clusters until the exchange is submitted. Utilizing a solitary COPY explanation for different clump embeds makes stacking information more effective. In case you are stacking numerous groups, you ought to handicap the AutoCommit property of the data set to exploit this expanded proficiency. When performing clump embeds, explore different avenues regarding different bunch and column sizes to decide the settings that give the best exhibition.
Example of JDBC Batch Insert
Given below is the example mentioned:
Code:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
public class B_Inserts_P_Stmt {
public static void main(String args[]){
String m_url = " jdbc:mysql://localhost ";
Connection con = DriverManager.getConnection(m_url, "root", "root");
System.out.println("Connection successfully established with database. . .");
Statement stmt_obj = con_obj.createStatement();
Con_obj.setAutoCommit(false);
PreparedStatement p_stmt_obj = con_obj.prepareStatement("INSERT INTO stud VALUES (?, ?, ?, ?, ?)");
p_stmt_obj.setString(1, "COMP");
p_stmt_obj.setString(2, "Jenny");
p_stmt_obj.setString(3, "Mumbai");
p_stmt_obj.setInt(4, 80);
p_stmt_obj.setString(5, "pass");
p_stmt_obj.addBatch();
p_stmt_obj.setString(1, "IT");
p_stmt_obj.setString(2, "Johan");
p_stmt_obj.setString(3, "Hydrabad");
p_stmt_obj.setInt(4, 65);
p_stmt_obj.setString(5, "pass");
p_stmt_obj.addBatch();
p_stmt_obj.setString(1, "COMP");
p_stmt_obj.setString(2, "Rohit");
p_stmt_obj.setString(3, "Mumbai");
p_stmt_obj.setInt(4, 75);
p_stmt_obj.setString(5, "pass");
p_stmt_obj.addBatch();
p_ stmt_obj.executeBatch();
con_obj.commit();
System.out.println("Data inserted successfully......");
}
}Explanation:
- In the above example, we try to implement the batch insert by using preparedStatement as shown. In this example first, we need to create the preparedStatement method to execute the insert into the statement. After the creation of the statement, we need to set the different values that we need to insert.
- In this example, we have a stud table and we need to insert the student’s personal details such as the department of the student, name of the student, city of student, and marks of student and result of the student as shown. Each iteration we need to add the addBatch() method. The final output or we can say that the end result of the above example we illustrated by using the following screenshot as follows.
Output:
Conclusion
From the above article, we have seen the basic syntax of batch insert and we also saw different examples of JDBC batch insert. From this article, we saw how and when we use the JDBC batch insert.
Recommended Articles
This is a guide to JDBC Batch Insert. Here we discuss the introduction, how to perform batch insert in JDBC? and examples respectively. You may also have a look at the following articles to learn more –
