Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Write an example JDBC program demonstrating the batch processing with statement object?
Grouping related SQL statements into a batch and executing/submitting them at once is known as batch processing. The Statement interface provides methods to perform batch processing such as addBatch(), executeBatch(), clearBatch().
Follow the steps given below to perform batch updates using the Statement object:
Register the driver class using the registerDriver() method of the DriverManager class. Pass the driver class name to it, as a parameter.
Connect to the database using the getConnection() method of the DriverManager class. Passing URL (String), username (String), password (String) as parameters to it.
Create a Statement object using the createStatement() method of the Connection interface.
Set the auto-commit to false using setAutoCommit() method of the Connection interface.
Add the required statements to the batch using the addBatch() method of the Statement interface.
Execute the batch using the executeBatch() method. of the Statement interface.
Commit the changes made using the commit() method of the Statement interface.
Example:
Suppose we have created a table named Dispatches with the description
+-------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+-------+ | Product_Name | varchar(255) | YES | | NULL | | | Name_Of_Customer | varchar(255) | YES | | NULL | | | Month_Of_Dispatch | varchar(255) | YES | | NULL | | | Price | int(11) | YES | | NULL | | | Location | varchar(255) | YES | | NULL | | +-------------------+--------------+------+-----+---------+-------+
Following program inserts data into this table using batch processing (with statement object).
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class BatchProcessing_Statement {
public static void main(String args[])throws Exception {
//Getting the connection
String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//CREATE TABLE Dispatches( Product_Name VARCHAR(255), Name_Of_Customer
VARCHAR(255), Month_Of_Dispatch VARCHAR(255), Price INT, Location VARCHAR(255));
//Creating a Statement object
Statement stmt = con.createStatement();
//Setting auto-commit false
con.setAutoCommit(false);
//Statements to insert records
String insert1 = "INSERT INTO Dispatches( Product_Name , Name_Of_Customer , "
+ "Month_Of_Dispatch , Price, Location) VALUES "
+ "('KeyBoard', 'Amith', 'January', 1000, 'hyderabad')";
String insert2 = "INSERT INTO Dispatches( Product_Name , Name_Of_Customer , "
+ "Month_Of_Dispatch , Price, Location) VALUES "
+ "('Earphones', 'SUMITH', 'March', 500, 'Vishakhapatnam')";
String insert3 = "INSERT INTO Dispatches( Product_Name , Name_Of_Customer , "
+ "Month_Of_Dispatch , Price, Location) VALUES "
+ "('Mouse', 'Sudha', 'September', 200, 'Vijayawada')";
//Adding the statements to the batch
stmt.addBatch(insert1);
stmt.addBatch(insert2);
stmt.addBatch(insert3);
//Executing the batch
stmt.executeBatch();
//Saving the changes
con.commit();
System.out.println("Records inserted......");
}
}Output
Connection established...... Records inserted......
If you verify the contents of the Dispatches table you can observe the inserted records.
+--------------+------------------+-------------------+-------+----------------+ | Product_Name | Name_Of_Customer | Month_Of_Dispatch | Price | Location | +--------------+------------------+-------------------+-------+----------------+ | KeyBoard | Amith | January | 1000 | hyderabad | | Earphones | SUMITH | March | 500 | Vishakhapatnam | | Mouse | Sudha | September | 200 | Vijayawada | +--------------+------------------+-------------------+-------+----------------+
107 Views
