I'm trying to get two data(GenreID & GameID) from two different tables(genre & games) and insert them into another table(games_genre). However, it will close the connection to the database after inserting the GenreID successfully even though i had created another new connection to the database.

I have tried to create connection1 and connection2 to the same database. Connection1 is used to insert GenreID and connection2 is used to insert GameID

<%@ page import="java.sql.*,java.util.*,java.text.*,java.text.SimpleDateFormat" %>
String gametitle = request.getParameter("gametitle");
String [] checkbox1 = request.getParameterValues("checkbox");

try {
   Class.forName("com.mysql.cj.jdbc.Driver");
   String connURL ="jdbc:mysql://localhost/assignment?user=root&password=root&serverTimezone=UTC"; 

   Connection conn = DriverManager.getConnection(connURL);
   Connection conn2 = DriverManager.getConnection(connURL);

   Statement stmt = conn.createStatement();   
   if (checkbox1!= null){
      for(String s: checkbox1){
       String sqlStr2 = "Select * FROM genre WHERE GenreName='" + s + "'";  
       ResultSet rs = stmt.executeQuery(sqlStr2);
       while(rs.next()){
          String genreid = rs.getString("GenreID");
          String sqlStr3 = "INSERT INTO games_genre(GenreID) VALUES ('" + genreid + "')";
          int j = stmt.executeUpdate(sqlStr3);  
          if (j>0) {
           out.println("Adding GenreID Successfully!");}
       }                
      }
   }
   conn.close();

   Statement stmt2 = conn2.createStatement();
   String sqlStr4 = "Select * FROM games WHERE GameTitle='" + gametitle +"'";
   ResultSet rs2 = stmt2.executeQuery(sqlStr4);
   if(rs2.next()){
      String gameid = rs2.getString("GameID");
      String sqlStr5 = "INSERT INTO games_genre(GameID) VALUES ('" + gameid + "')";
      int k = stmt2.executeUpdate(sqlStr5); 
      if (k>0) {
     out.println("Adding GameID Successfully!");            
      }
   }

   conn2.close();
} catch (Exception e) {
   out.println("Error :" + e);
}

Adding Game Successfully! Adding GenreID Successfully! Error :java.sql.SQLException: Operation not allowed after ResultSet closed

1 Answers

0
Swati On

I don't understand that why do you need to create two Connection as you need to access same database . So ,just create multiple Statement to execute multiple query like below :

    Statement stmt=null;
    Statement stmt2=null;
       try {
               Class.forName("com.mysql.cj.jdbc.Driver");
               String connURL ="jdbc:mysql://localhost/assignment?user=root&password=root&serverTimezone=UTC"; 
               Connection conn = DriverManager.getConnection(connURL);
                 stmt = conn.createStatement();   
               if (checkbox1!= null){
                ....
               }
            <!--using same conn object -->
            stmt2 = conn.createStatement();
               String sqlStr4 = "Select * FROM games WHERE GameTitle='" + gametitle +"'";
           ResultSet rs2 = stmt2.executeQuery(sqlStr4);
           if(rs2.next()){
             ...
           }
      <!--finally close connection-->
         conn.close();  

     } catch (Exception e) {
   out.println("Error :" + e);
}

Note : Also try using PreparedStatement for preventing from Sql Injection as concatenating values into a query string is unsafe .