33

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 semi colon.

BalusC
  • 1,082,665
  • 372
  • 3,610
  • 3,555
happy
  • 2,550
  • 17
  • 64
  • 109
  • Please clarify your question. Are you trying to handle multiple SELECT result sets, or handling batch updating of multiple INSERTS. You are getting 3 very different answers which would indicate a question is not specific enough. – Brad Jun 07 '12 at 10:31
  • Please have a look over this link: http://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement – Shastry Jan 29 '14 at 07:19

2 Answers2

41

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

Akib Bagwan
  • 138
  • 1
  • 2
  • 14
Hemant Metalia
  • 29,730
  • 18
  • 72
  • 91
  • 2
    I'm not sure you're answering the question. You're talking about INSERT statements being batched, and the question is related to multiple SELECT statements and therefore must handle ResultSets – Brad Jun 07 '12 at 10:16
  • @Brad the question is `I want to execute the multiple queries or job in one execute` so i thing for multiple queries.. – Hemant Metalia Jun 07 '12 at 10:28
  • 1
    Maybe a working example, but a bit unformatted. I found this one more helpful: http://stackoverflow.com/questions/10797794/multiple-queries-executed-in-java-in-single-statement – Zon Aug 11 '14 at 17:32
  • Perfect explaination - worked a treat, I am using Derby DB too :) – thonnor Oct 16 '15 at 21:03
  • @thonnor glad it helped. – Hemant Metalia Jan 20 '16 at 06:59
6

I'm not sure that you want to send two SELECT statements in one request statement because you may not be able to access both ResultSets. The database may only return the last result set.

Multiple ResultSets

However, if you're calling a stored procedure that you know can return multiple resultsets something like this will work

CallableStatement stmt = con.prepareCall(...);
try {
...

boolean results = stmt.execute();

while (results) {
    ResultSet rs = stmt.getResultSet();
    try {
    while (rs.next()) {
        // read the data
    }
    } finally {
        try { rs.close(); } catch (Throwable ignore) {}
    }

    // are there anymore result sets?
    results = stmt.getMoreResults();
}
} finally {
    try { stmt.close(); } catch (Throwable ignore) {}
}

Multiple SQL Statements

If you're talking about multiple SQL statements and only one SELECT then your database should be able to support the one String of SQL. For example I have used something like this on Sybase

StringBuffer sql = new StringBuffer( "SET rowcount 100" );
sql.append( " SELECT * FROM tbl_books ..." );
sql.append( " SET rowcount 0" );

stmt = conn.prepareStatement( sql.toString() );

This will depend on the syntax supported by your database. In this example note the addtional spaces padding the statements so that there is white space between the staments.

Community
  • 1
  • 1
Brad
  • 15,186
  • 11
  • 60
  • 74
  • 1
    The original question refers to non-homogeneous statements - so this answer is more correct then the one referring to a batch execution. – where_ Apr 11 '21 at 06:59
  • Should I use StringBuffer or simple concat will suffice in this case ? – Zied Orabi Feb 01 '22 at 14:24
  • You can use `concat` too because it doesn't matter how you actually construct the `String`. – Adrian Mar 31 '22 at 09:08