Execute multiple SQL statements in java

Question

I want to execute a query in Java.

I create a connection. Then I want to execute an INSERT statement, when done, the connection is closed but I want to execute some insert statement by a connection and when the loop is finished then closing connection.

What can I do ?

My sample code is :

public NewClass() throws SQLException {
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch (ClassNotFoundException e) {
        System.out.println("Where is your Oracle JDBC Driver?");
        return;
    }
    System.out.println("Oracle JDBC Driver Registered!");

    Connection connection = null;
    try {
        connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:orcl1", "test",
                "oracle");
    } catch (SQLException e) {
        System.out.println("Connection Failed! Check output console");
        return;
    }

    if (connection != null) {
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * from test.special_columns");
        while (rs.next()) {
            this.ColName = rs.getNString("column_name");
            this.script = "insert into test.alldata (colname) ( select " +  ColName + "   from test.alldata2       ) " ;
            stmt.executeUpdate("" + script);
        }        
    }
    else {
        System.out.println("Failed to make connection!");
    }        
}

When the select statement ("SELECT * from test.special_columns") is executed, the loop must be twice, but when (stmt.executeUpdate("" + script)) is executed and done, then closing the connection and return from the class.

i dont think that, because when i print the scripts and execute them on for sqlPlus , they are ok and inserting is successful — saeed.sh
– saeed.sh, Commented Jun 11, 2014 at 7:47
Add some System.out.println() statement to the loop to see what happend or use a debugger. — Jens
– Jens, Commented Jun 11, 2014 at 7:49
I suggest putting try/catch around your loop - and printing any exception that you may get. There may be something you don't quite expect. — Aleks G
– Aleks G, Commented Jun 11, 2014 at 7:51

MinhD · Accepted Answer · 2015-04-13 02:55:20Z

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

Nick Holt · Accepted Answer · 2014-06-11 09:27:43Z

In the abscence of the schema or the data contained in each table I'm going to make the following assumptions:

The table special_columns could look like this:

column_name
-----------
column_1
column_2
column_3

The table alldata2 could look like this:

column_1  | column_2  | column_3
---------------------------------
value_1_1 | value_2_1 | value_3_1
value_1_2 | value_2_2 | value_3_2

The table alldata should, after inserts have, happened look like this:

colname
---------
value_1_1
value_1_2
value_2_1
value_2_2
value_3_1
value_3_2

Given these assumptions you can copy the data like this:

try (
  Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl1", "test", "oracle")
)
{
  StringBuilder columnNames = new StringBuilder();

  try (
    Statement select = connection.createStatement();
    ResultSet specialColumns = select.executeQuery("SELECT column_name FROM special_columns");
    Statement insert = connection.createStatement()
  )
  {
    while (specialColumns.next())
    {
      int batchSize = 0;             

      insert.addBatch("INSERT INTO alldata(colname) SELECT " + specialColumns.getString(1) + " FROM alldata2"); 

      if (batchSize >= MAX_BATCH_SIZE)
      { 
        insert.executeBatch();
        batchSize = 0;
      }
    }

    insert.executeBatch();
  }

A couple of things to note:

MAX_BATCH_SIZE should be set to a value based on your database configuration and the data being inserted.
this code is using the Java 7 try-with-resources feature to ensure the database resources are released when they're finished with.
you haven't needed to do a Class.forName since the service provider mechanism was introduced as detailed in the JavaDoc for DriverManager.

Mark Rotteveel · Accepted Answer · 2014-06-11 09:23:20Z

There are two problems in your code. First you use the same Statement object (stmt) to execute the select query, and the insert. In JDBC, executing a statement will close the ResultSet of the previous execute on the same object.

In your code, you loop over the ResultSet and execute an insert for each row. However executing that statement will close the ResultSet and therefor on the next iteration the call to next() will throw an SQLException as the ResultSet is closed.

The solution is to use two Statement objects: one for the select and one for the insert. This will however not always work by default, as you are working in autoCommit (this is the default), and with auto commit, the execution of any statement will commit any previous transactions (which usually also closes the ResultSet, although this may differ between databases and JDBC drivers). You either need to disable auto commit, or create the result set as holdable over commit (unless that already is the default of your JDBC driver).

Collectives™ on Stack Overflow

Execute multiple SQL statements in java

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related