How to Execute Multiple SQL Statements from Java

How to execute multiple SQL statements from java

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

Execute multiple SQL statements in java

Following example uses addBatch & executeBatch commands to execute multiple SQL commands simultaneously.

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);
      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());
   }
} 

Result:
The above code sample will produce the following result.The result may vary.

rows before batch execution= 6
Batch executed
rows after batch execution= = 9 

Source: Execute multiple SQL statements

How to execute multiple SQL queries defined in a SQL file

Spark doesn’t support executing multiple queries with a single call to spark.sql (see this SO answer). If you don’t mind calling spark.sql multiple times, you can read the .sql file into a string, split it into separate queries, and then pass it to spark.sql, like so:

import java.nio.file.*;

public static void main(String[] args) {
    String[] commands;

    try {
        Path sqlPath = FileSystems.getDefault().getPath("query.sql");
        String sql = new String(Files.readAllBytes(sqlPath));
        commands = sql.split(";", 0);
    } catch (Exception e) {
        System.out.println("failed to read SQL file");
        return;
    }

    for (int i = 0; i < commands.length; i++) {
        spark.sql(commands[i]);
    }
}

Multiple queries executed in java in single statement

I was wondering if it is possible to execute something like this using JDBC.

"SELECT FROM * TABLE;INSERT INTO TABLE;"

Yes it is possible. There are two ways, as far as I know. They are

1. By setting database connection property to allow multiple queries,
   separated by a semi-colon by default.
2. By calling a stored procedure that returns cursors implicit.

Following examples demonstrate the above two possibilities.

Example 1: ( To allow multiple queries ):

While sending a connection request, you need to append a connection property allowMultiQueries=true to the database url. This is additional connection property to those if already exists some, like autoReConnect=true, etc.. Acceptable values for allowMultiQueries property are true, false, yes, and no. Any other value is rejected at runtime with an SQLException.

String dbUrl = "jdbc:mysql:///test?allowMultiQueries=true";  

Unless such instruction is passed, an SQLException is thrown.

You have to use execute( String sql ) or its other variants to fetch results of the query execution.

boolean hasMoreResultSets = stmt.execute( multiQuerySqlString );

To iterate through and process results you require following steps:

READING_QUERY_RESULTS: // label  
    while ( hasMoreResultSets || stmt.getUpdateCount() != -1 ) {  
        if ( hasMoreResultSets ) {  
            Resultset rs = stmt.getResultSet();
            // handle your rs here
        } // if has rs
        else { // if ddl/dml/...
            int queryResult = stmt.getUpdateCount();  
            if ( queryResult == -1 ) { // no more queries processed  
                break READING_QUERY_RESULTS;  
            } // no more queries processed  
            // handle success, failure, generated keys, etc here
        } // if ddl/dml/...

        // check to continue in the loop  
        hasMoreResultSets = stmt.getMoreResults();  
    } // while results

Example 2: Steps to follow:

1. Create a procedure with one or more select, and DML queries.
2. Call it from java using CallableStatement.
3. You can capture multiple ResultSets executed in procedure.
   
   DML results can't be captured but can issue another select
   to find how the rows are affected in the table.

Sample table and procedure:

mysql> create table tbl_mq( i int not null auto_increment, name varchar(10), primary key (i) );
Query OK, 0 rows affected (0.16 sec)

mysql> delimiter //
mysql> create procedure multi_query()
    -> begin
    ->  select count(*) as name_count from tbl_mq;
    ->  insert into tbl_mq( names ) values ( 'ravi' );
    ->  select last_insert_id();
    ->  select * from tbl_mq;
    -> end;
    -> //
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> call multi_query();
+------------+
| name_count |
+------------+
|          0 |
+------------+
1 row in set (0.00 sec)

+------------------+
| last_insert_id() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

+---+------+
| i | name |
+---+------+
| 1 | ravi |
+---+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Call Procedure from Java:

CallableStatement cstmt = con.prepareCall( "call multi_query()" );  
boolean hasMoreResultSets = cstmt.execute();  
READING_QUERY_RESULTS:  
    while ( hasMoreResultSets ) {  
        Resultset rs = stmt.getResultSet();
        // handle your rs here
    } // while has more rs

Java Execute multiple SQL statements and write in excel

You cannot use addBatch for select queries. statement.addBatch() is only for update/insert/delete queries or queries which does not provide any results.

    String TotalAssets = "SELECT COUNT(*) AS TOTAL_ASSET FROM M_OBJECT WHERE TYPE_ IN(1,2,3)";
    stmt1 = con.createStatement();
    ResultSet rsTotalAssets = stmt1.executeQuery(TotalAssets);
    rsTotalAssets.next();
    int totalAssetsCount = rsTotalAssets.getInt("TOTAL_ASSET");

Use the totalAssetsCount in setting the appropriate cell value. Repeat the above code for TotalJobs and TotalTasks.

close the statement and resultset in the finally block

    rsTotalAssets.close();
    stmt1.close();

Execute multiple sql SELECT queries using JDBC from a Java Program

I mean do I have to execute different statements for each query?

You do. If you were selecting the same columns in both queries (or at least the same # and type of columns), you could combine the queries with a UNION ALL. Since the columns differ, you must use different statements (and connections - if you want to run both queries simultaneously). If you run the queries sequentially, one connection is sufficient.

How to execute multiple SQL statement using Java?

Okay so there are many problems with your code. First off,

//STEP 2: Register JDBC driver
Class.forName("com.mysql.jdbc.Driver");

You would have to pass a Driver implementation, not the interface itself; but the good news is that this code is unnecessary as of JDBC 4.0. It already scans your classpath to locate drivers for you.

Second, your connection is not being closed with each query. There's nowhere in your code where you're calling connec.close(). JDBC won't close the connection for you, either.

Third, you don't need to do this with nested for loops! That is an awful idea. Your concept of SQL queries and JDBC needs some sharpening. You can simply do the following:

for(int i=0;i<identities.length;i++) {
    String sql =  "SELECT * FROM cgm_counters WHERE id="+identities[i];
    ResultSet rs = stmt.executeQuery(sql);
    while(rs.next()){
        //Retrieve by column name
        double net_ppe  = rs.getDouble("spend");
        System.out.println("The value of the netppe :"+net_ppe);
    }
}

Even better would be to do a batch query.

String batch = "(";
for (int i = 0; i < identities.length;i++) {
    if (i < identities.length() - 1) 
        batch += "?, ";
    else 
        batch += "?)"
}

String sql =  "SELECT * FROM cgm_counters WHERE id in " + batch;
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
    double net_ppe  = rs.getDouble("spend");
    System.out.println("The value of the netppe :"+net_ppe);
}

It seems you are doing a preliminary round of queries to "check" whether each ID is in the table, but this is not necessary. If the ID is not in the table, then you'll get an empty result set in return. There's nothing wrong with an empty result set. Your while loop will never run in this case because rs.next() will return false. You can also check whether it's empty by calling rs.first(). This way doesn't move the cursor.

---

Related Topics