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.

解决方案

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