下面的代码只能从附加文件的20000个数字中插入226个数据到TEMPTABLE的数字列中

Question

下面的代码只能将提供的文件中的20000个数字中的226个插入TEMPTABLE的数字列，然后抛出

[   java.sql.SQL.Exception:ORA-00604: error occurred at recursive SQL level 
1ORA-01000: maximum open cursors exceeded
ora-00604:  error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded   ]

代码如下:

private void ButtonloadActionPerformed(java.awt.event.ActionEvent evt) {                                                  
      PreparedStatement pre2;           
      //loading the database driver and initiating a connection to it
      //all statement in a try and catch block
      try {
          String driverName="oracle.jdbc.driver.OracleDriver";
                Class.forName(driverName);               
                String url ="jdbc:oracle:thin:@"+serverName+":"+serverport+":"+sid;
                conn =DriverManager.getConnection(url,username,password);  
// uploading the content on the csv file in the path into the TempTable in the DB
          try (BufferedReader br = new BufferedReader(new FileReader(path))) {
              String line;
               while ((line=br.readLine())!=null) {
                  String[] value=line.split(",");
                  String sql1 ="insert into TEMPTABLE(numbers)values('"+(value[0])+"')";
                  pre2=conn.prepareStatement(sql1);                  
                  pre2.executeUpdate();                                    
              }
              br.close();  // closing the buffered reader
              conn.close();   //closing database connection to free system resources 
          }
     }
     catch(ClassNotFoundException | SQLException | IOException e) {
         JOptionPane.showMessageDialog(null,e);
     }

谁能帮我解决这个问题?

Answer 1

您没有关闭您的PreparedStatements，并且它们都使用数据库上的游标资源。

您可以在executeUpdate()之后添加pre2.close() -这将解决当前问题。但它将非常缓慢且资源密集—正如在其他地方所指出的，您应该研究批处理和绑定变量。

Answer 2

 try (BufferedReader br = new BufferedReader(new FileReader(path))) {
  String sql1 ="insert into TEMPTABLE(numbers)values(?)";    
   pre2=conn.prepareStatement(sql1);                  
              String line;
                   while ((line=br.readLine())!=null) {
                      String[] value=line.split(",");
                    pre2.setString(1,value[0]); //if its number use setInt
                    pre2.addBatch();
                  }
              pre2.executeBatch();
pre2.close(0);
                   br.close();// closing the buffered reader
                  conn.close(); //closing database connection to free system resources 
              }
    }
    catch(ClassNotFoundException | SQLException | IOException e){
    JOptionPane.showMessageDialog(null,e);}

尝试添加批处理。这将加快您的性能