How to execute multiple SQL statements from java
java, jdbc
Solution
you can achieve that using following example uses addBatch & executeBatch commands to execute multiple SQL commands simultaneously.
Batch processing allows you to group related SQL statements into a batch and submit them with one call to the database. reference
When you send several SQL statements to the database at once, you reduce the amount of communication overhead, thereby improving performance.
- JDBC drivers are not required to support this feature. You should use the `DatabaseMetaData.supportsBatchUpdates()` method to determine if the target database supports batch update processing. The method returns true if your JDBC driver supports this feature.
- The addBatch() method of Statement, PreparedStatement, and CallableStatement is used to add individual statements to the batch. The `executeBatch()` is used to start the execution of all the statements grouped together.
- The executeBatch() returns an array of integers, and each element of the array represents the update count for the respective update statement.
- Just as you can add statements to a batch for processing, you can remove them with the clearBatch() method. This method removes all the statements you added with the `addBatch()` method. However, you cannot selectively choose which statement to remove.
EXAMPLE:
import java.sql.*;
public class jdbcConn {
public static void main(String[] args) throws Exception{
Class.forName("org.apache.derby.jdbc.ClientDriver");
Connection con = DriverManager.getConnection
("jdbc:derby://localhost:1527/testDb","name","pass");
Statement stmt = con.createStatement
(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
String insertEmp1 = "insert into emp values
(10,'jay','trainee')";
String insertEmp2 = "insert into emp values
(11,'jayes','trainee')";
String insertEmp3 = "insert into emp values
(12,'shail','trainee')";
con.setAutoCommit(false);
stmt.addBatch(insertEmp1);//inserting Query in stmt
stmt.addBatch(insertEmp2);
stmt.addBatch(insertEmp3);
ResultSet rs = stmt.executeQuery("select * from emp");
rs.last();
System.out.println("rows before batch execution= "
+ rs.getRow());
stmt.executeBatch();
con.commit();
System.out.println("Batch executed");
rs = stmt.executeQuery("select * from emp");
rs.last();
System.out.println("rows after batch execution= "
+ rs.getRow());
}
}
refer http://www.tutorialspoint.com/javaexamples/jdbc_executebatch.htm
Problem
I want to execute the multiple queries or job in one execute. Something like this eg: ``` String query = """ SELECT * FROM tab1; INSERT INTO tab1 VALUES( … ); UPDATE tab1 … ; DELETE FROM tab1 … ; """ ; Statement st = con1.createStatement(); ResultSet rs = st.executeQuery(query); ``` Or multiple SELECT queries. Queries will be dynamic. But I am not able to do this. What is the way to run multiple queries separated by semicolon?