How to execute multiple SQL statements from java

Question

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

User · Answer

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

User · Answer

you can achieve that using Following example uses addBatch  amp  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

[java] How to execute multiple SQL statements from java

Examples related to java

Examples related to jdbc