CODEWITHSUNDEEP
javamysqlsqljdbcdatabase-connection
Washu
Washu

Reputation: 835

No operations allowed after statement closed issue

I have the next methods in my singleton to execute the JDBC connections

public void openDB() throws ClassNotFoundException, IllegalAccessException,
        InstantiationException, SQLException {
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    String url = "jdbc:mysql://localhost/mbpe_peru";//mydb
    conn = DriverManager.getConnection(url, "root", "admin");
    st = conn.createStatement();
}

public void sendQuery(String query) throws SQLException {
    st.executeUpdate(query);
}

public void closeDB() throws SQLException {
    st.close();
    conn.close();
}

And I'm having a problem in a void where i have to call this twice.

private void jButton1ActionPerformed(ActionEvent evt) {

Main.getInstance().openDB();
Main.getInstance().sendQuery("call insertEntry('"+EntryID()+"','"+SupplierID()+"');");
Main.getInstance().closeDB();

Main.getInstance().openDB();
for(int i=0;i<dataBox.length;i++){
Main.getInstance().sendQuery("call insertCount('"+EntryID()+"','"+SupplierID()+"','"+BoxID()+"');
Main.getInstance().closeDB();
}
}

I have already tried to keep the connection open and send the 2 querys and after that closed and it didnt work... The only way it worked was to not use the methods, declare the commands for the connection and use different variables for the connection and the statement. I thought that if i close the Connecion and the Statement I could use the variable once again since is a method but I'm not able to. Is there any way to solve this using my methods for the JDBC connection?

Upvotes: 1

Views: 8004

Answers (3)

nos
nos

Reputation: 229058

Your loop is

   Main.getInstance().openDB();
   for(int i=0;i<dataBox.length;i++){
      Main.getInstance().sendQuery(....);
      Main.getInstance().closeDB();
    }

You're closing the database on each iteration. You should e.g. do

   Main.getInstance().openDB();
   for(int i=0;i<dataBox.length;i++){
      Main.getInstance().sendQuery(....);
    }
    Main.getInstance().closeDB();

Upvotes: 1

Joop Eggen
Joop Eggen

Reputation: 109532

Create two statements. And better use a parametrised statement with setters.

Class.forName("com.mysql.jdbc.Driver").newInstance();
String url = "jdbc:mysql://localhost/mbpe_peru";//mydb
Connection conn = DriverManager.getConnection(url, "root", "admin");

CallableStatement insertEntrySt = conn.prepareCall("CALL insertEntry(?, ?)");
insertEntrySt.setInt(1, EntryID());
insertEntrySt.setInt(2, SupplierID());
insertEntrySt.executeUpdate();
insertEntrySt.close();

CallableStatement insertCountSt = conn.prepareCall("CALL insertCount(?, ?, ?)");
...

conn.close(;

Upvotes: 0

Code-Apprentice
Code-Apprentice

Reputation: 83517

I suggest changing your code as follows:

private void jButton1ActionPerformed(ActionEvent evt) {
    Main.getInstance().openDB();
    Main.getInstance().sendQuery("call insertEntry('"+EntryID()+"','"+SupplierID()+"',);

    for(int i=0;i<dataBox.length;i++){
        Main.getInstance().sendQuery("call insertCount('"+EntryID()+"','"+SupplierID()+"','"+BoxID()+"');
    }

    Main.getInstance().closeDB();
}

I haven't tested this, so I hope it works.

Upvotes: 0

Related Questions