How to Execute Multiple SQL Commands on a Database Simultaneously in JDBC?
Last Updated :
08 Jun, 2022
Java Database Connectivity also is known as JDBC is an application programming interface in Java that is used to establish connectivity between a Java application and database. JDBC commands can be used to perform SQL operations from the Java application. Demonstrating execution of multiple SQL commands on a database simultaneously using the addBatch() and executeBatch() commands of JDBC.
The addBatch() command is used to queue the SQL statements and executeBatch() command is used to execute the queued SQL statements all at once. In order to use SQL statements in the Java application, ''java.sql'' package needs to be imported in the beginning of the Java application. The Java application is connected to the database using the getConnection() method of DriverManager class. The getConnection() method takes three parameters URLs, username and password.
Goal: Demonstrates two examples of which one uses the Statement Interface and the other uses PreparedStatement Interface. The PreparedStatement performs better than the Statement interface. Statement interface can be used to execute static SQL queries whereas PreparedStatement interface is used to execute dynamic SQL queries multiple times.
Example 1: Using Statement Interface
In this example, the java.sql package classes and interfaces are imported. The Statement interface is used to execute the sql statements. The table is creation sql statement along with record insertion sql statement are added to the batch using the addBatch() command. When all the statements are batched the executeBatch() command is executed which runs all the batched queries simultaneously. The sql statements may throw SQL Exceptions which must be handled in a try catch block to avoid abrupt termination of the program. After the table is created and records are inserted, to view the data in the table the select query is executed. The result obtained by executing the select query is stored in the ResultSet cursor. The cursor is iterated using the next() method and the records are displayed on the screen.
Implementation: Using the standard interface
Java
// Step 1: Create a database
// SQL database imported
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class BatchCommand {
// Main driver method
public static void main(String args[])
{
// Try block to check if exception occurs
try {
// Step 2: Loading driver class
// Using forName()
Class.forName("oracle.jdbc.OracleDriver");
// Step 3: Create connection object
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe",
"username", "password");
Statement s = con.createStatement();
// Step 4: Create a statement / create table
String sql1
= "CREATE TABLE STUDENT(STUDENTID VARCHAR2(10) PRIMARY KEY,NAME VARCHAR2(20),DEPARTMENT VARCHAR2(10))";
// Step 5: Process a query
// Insert records in the table
String sql2
= "INSERT INTO STUDENT VALUES('S101','JEAN','CSE')";
String sql3
= "INSERT INTO STUDENT VALUES('S102','ANA','CSE')";
String sql4
= "INSERT INTO STUDENT VALUES('S103','ROBERT','ECE')";
String sql5
= "INSERT INTO STUDENT VALUES('S104','ALEX','IT')";
String sql6
= "INSERT INTO STUDENT VALUES('S105','DIANA','IT')";
s.addBatch(sql1);
s.addBatch(sql2);
s.addBatch(sql3);
s.addBatch(sql4);
s.addBatch(sql5);
s.addBatch(sql6);
// Step 6: Process the results
// execute the sql statements
s.executeBatch();
ResultSet rs
= s.executeQuery("Select * from Student");
// Print commands
System.out.println(
"StudentID\tName\t\tDepartment");
System.out.println(
"-------------------------------------------------------");
// Condition to check pointer pointing
while (rs.next()) {
System.out.println(rs.getString(1) + "\t\t"
+ rs.getString(2)
+ "\t\t"
+ rs.getString(3));
}
// Step 7: Close the connection
con.commit();
con.close();
}
// Catch block to handle exceptions
catch (Exception e) {
// Print line number if exception occurred
System.out.println(e);
}
}
}
SQL commands over database using addBatch() method with the involvement of executeBatch()
Java
// Step 1: Importing database
// SQL database imported
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class BatchCommand {
// Main driver method
public static void main(String args[])
{
// Try block to handle if exception occurs
try {
// Step 2: loading driver class
Class.forName("oracle.jdbc.OracleDriver");
// Step 3: create connection object
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe",
"username", "password");
Statement s = con.createStatement();
// Step 4: Create a statement
// Create table
String sql1
= "CREATE TABLE STUDENT(STUDENTID VARCHAR2(10) PRIMARY KEY,NAME VARCHAR2(20),DEPARTMENT VARCHAR2(10))";
// Step 5: Execute a query
// Insert records in the table
String sql2
= "INSERT INTO STUDENT VALUES('S101','JEAN','CSE')";
String sql3
= "INSERT INTO STUDENT VALUES('S102','ANA','CSE')";
String sql4
= "INSERT INTO STUDENT VALUES('S103','ROBERT','ECE')";
String sql5
= "INSERT INTO STUDENT VALUES('S104','ALEX','IT')";
String sql6
= "INSERT INTO STUDENT VALUES('S105','DIANA','IT')";
s.addBatch(sql1);
s.addBatch(sql2);
s.addBatch(sql3);
s.addBatch(sql4);
s.addBatch(sql5);
s.addBatch(sql6);
// Step 6: Process the statements
// Create an int[] to hold returned values
s.executeBatch();
ResultSet rs
= s.executeQuery("Select * from Student");
// Print statements
System.out.println(
"StudentID\tName\t\tDepartment");
System.out.println(
"-------------------------------------------------------");
// Condition check for pointer pointing which
// record
while (rs.next()) {
System.out.println(rs.getString(1) + "\t\t"
+ rs.getString(2)
+ "\t\t"
+ rs.getString(3));
}
// Step 7: Close the connection
con.commit();
con.close();
}
// Catch block to handle exception
catch (Exception e) {
// Print line number where exception occurred
System.out.println(e);
}
}
}
Output
Example 2: In this example, the java.sql package classes and interfaces are imported. The PreparedStatement interface is used to execute the SQL statements. The table is the creation SQL statement along with record insertion SQL statement are added to the batch using the addBatch() command. When all the statements are batched the executeBatch() command is executed which runs all the batched queries simultaneously. The sql statements may throw SQL Exceptions which must be handled in a try-catch block to avoid abrupt termination of the program. After the table is created and records are inserted, to view the data in the table the select query is executed. The result obtained by executing the select query is stored in the ResultSet cursor. The cursor is iterated using the next() method and the records are displayed on the screen. Unlike the previous example, it takes dynamic input from the user. Hence, using the PreparedStatement has performance benefits.
Code Implementation
Java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;
public class AddBatchCommand {
public static void main(String args[])
{
Scanner scan = new Scanner(System.in);
try {
// loading driver class
Class.forName("oracle.jdbc.OracleDriver");
// create connection object
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:xe",
"username", "password");
// create the table
String sql1
= "CREATE TABLE STUDENTS(STUDENTID VARCHAR2(10) PRIMARY KEY,NAME VARCHAR2(20),DEPARTMENT VARCHAR2(10))";
PreparedStatement ps
= con.prepareStatement(sql1);
ps.execute(sql1);
// inserting records
String sql
= "Insert into Students values(?,?,?)";
PreparedStatement ps1
= con.prepareStatement(sql);
for (int i = 0; i < 3; i++) {
System.out.println("Enter Student ID");
String id = scan.nextLine();
System.out.println("Enter Student Name");
String name = scan.nextLine();
System.out.println("Enter the Department");
String dept = scan.nextLine();
ps1.setString(1, id);
ps1.setString(2, name);
ps1.setString(3, dept);
// adding to batch
ps1.addBatch();
}
// executing the batch
ps1.executeBatch();
// viewing the table
ResultSet rs
= ps.executeQuery("Select * from Students");
System.out.println(
"StudentID\tName\t\tDepartment");
System.out.println(
"-------------------------------------------------------");
while (rs.next()) {
System.out.println(rs.getString(1) + "\t\t"
+ rs.getString(2)
+ "\t\t"
+ rs.getString(3));
}
con.commit();
con.close();
}
catch (Exception e) {
System.out.println(e);
}
}
}
Output: Illustrating multiple SQL commands on a database simultaneously:
Similar Reads
How to Insert Multiple Rows at Once in PL/SQL? As the volume and complexity of data continue to grow in modern systems, efficient data management techniques become important. One fundamental operation in database management is the insertion of multiple rows at once. In this article, we understand the techniques and methods available in PL/SQL fo
5 min read
Performing Database Operations in Java | SQL CREATE, INSERT, UPDATE, DELETE and SELECT In this article, we will be learning about how to do basic database operations using JDBC (Java Database Connectivity) API in Java programming language. These basic operations are INSERT, SELECT, UPDATE, and DELETE statements in SQL language. Although the target database system is Oracle Database, t
6 min read
How To Export and Import a .SQL File From Command Line With Options? Structured Query Language is a computer language that we use to interact with a relational database.SQL is a tool for organizing, managing, and retrieving archived data from a computer database. In this article , we will learn to export and import .SQL files with command line options. Export:You can
2 min read
How to Execute a SQLite Statement in Python? In this article, we are going to see how to execute SQLite statements using Python. We are going to execute how to create a table in a database, insert records and display data present in the table. In order to execute an SQLite script in python, we will use the execute() method with connect() objec
2 min read
Multiple Postgres databases in psycopg2 PostgreSQL is the most powerful open-source object-relational database management system. Psycopg2 is the most popular PostgreSQL database adapter for Python language. It simply allows you to work with multiple databases in the same program at the same time. This indicates that you can easily switch
4 min read
How to Execute a Script in SQLite using Python? In this article, we are going to see how to execute a script in SQLite using Python. Here we are executing create table and insert records into table scripts through Python. In Python, the sqlite3 module supports SQLite database for storing the data in the database. Approach Step 1: First we need to
2 min read