我如何正确地关闭游标在循环体，因为我得到错误:java.sql.SQLException: - ORA-01000:最大

Question

我遇到了ORA-01000 SQL异常，因为下面的代码获取了超过千条记录，所以在这方面，我想知道我如何在循环体中适当地关闭ResultSet对象，以便我可以摆脱这个异常。请帮帮我……我真的很感激……

注意:-即使我在Oracle数据库中打开了2000个游标。

try
    {
        String usercode = session.get("usercode").toString();
        Date dor = null;
        Date eff_date =null;
        String emp_category_code = "";
        String emp_id = "";
        String pay_com = "";
        double new_da = 0;
        double old_da =0;
        int caseCount =0;
         con = DBConnect.makeconnect();
         con.setAutoCommit(false);
         String div_name = session.get("division").toString();
        PreparedStatement ps = con.prepareStatement("select emp_id ,category,pay_comm,da as old_da,nvl(pm.EMP_DOR,pm.EMP_DOD) as dor from ABC pm where pm.DIV_NM  = ? and sent_to_trea= '1'");
        ps.setString(1,div_name);
        ResultSet rs = ps.executeQuery();
        while(rs.next())
        {
            emp_id = rs.getString("emp_id");
            emp_category_code = rs.getString("category");
            pay_com = rs.getString("pay_comm");
            dor = rs.getDate("dor");
            old_da = rs.getDouble("old_da");
            ps = con.prepareStatement("select eff_date,per_amount from XYZ where  cat_code=? and relief='DA' and  eff_date=(select max(EFF_DATE) from XYZ where PAY_COM=? and cat_code=?) and pay_com=?");
            ps.setString(1, emp_category_code);
            ps.setString(2, pay_com);
            ps.setString(3, emp_category_code);
            ps.setString(4, pay_com);
            ResultSet rst = ps.executeQuery();
            if(rst.next())
            {
               eff_date = rst.getDate("eff_date");
                new_da = rst.getDouble("per_amount");
            }
            ps = con.prepareStatement("select retirement_gratuity from CAL where emp_id = ?");
            ps.setString(1,emp_id);
            rst = ps.executeQuery();
            if(rst.next())
            {
                double ret_grat = rst.getDouble("retirement_gratuity");
                if(ret_grat >=1000000)
                {
                    continue;
                }
            }
            if(dor.compareTo(eff_date)>0)
            {
                if(new_da >old_da)
                {
                    ps = con.prepareStatement("select emp_id from AFFECTET where emp_id = ?");
                    ps.setString(1,emp_id);
                     rst = ps.executeQuery();
                     if(!rst.next())
                     {
                     ps = con.prepareStatement("insert into upops.AFFECTED (EMP_ID,PREVIOUS_DA,NEW_DA,PREV_GRATUITY,NEW_GRATUITY,REVISION_NO,DONE,EFF_DATE,PAY_COMM,CATEGORY) (select pm.emp_id,pm.da," + new_da + ",c.RETIREMENT_GRATUITY,0,0,'N',to_date('"+eff_date+"','yyyy-MM-dd'),pm.pay_comm,pm.category from upops.pensioner_mast pm ,upops.calculation c where c.emp_id=pm.emp_id and pm.emp_id = '"+emp_id+"')");
                    int executeUpdate = ps.executeUpdate();
                    if (executeUpdate > 0) {
                         ret = CheckUtils.fileMovement(emp_id,usercode , "33", con);
                        if (ret.equals("SUCCESS")) {
                            caseCount++;
                        }
                    }
                     }
                }
            }
            else
            {
                continue;
            }
         rst.close();
        }
         ret = "SUCCESS";
       if(ret.equals("SUCCESS"))
       {
           con.commit();
       }

    }
    catch(Exception ex)
    {
        ex.printStackTrace();;
    }
    finally
    {
        try
        {
            con.close();
        }
        catch(Exception ex){}
    }

Answer 1

如果您必须使用Java 7及以上版本，我建议您使用try with resources，这是在Java 7中引入的新功能。

Java 7中的Try-with-resources是一个新的exception handling机制，可以更容易地正确关闭try-catch block.中使用的resources

关于你方代码:

finally
    {
        try
        {
            con.close();
        }
        catch(Exception ex){}
    }

你注意到那个难看的重复尝试了吗?

但是，如果您使用try with resources，则会自动调用close()，无论它是否抛出异常，它都将被抑制(如Java语言规范14.20.3中指定的那样)。对于数据库连接和资源情况也是如此。

。:(JDBC with try with resources)

try (Connection con = DriverManager.getConnection(yourConnectionURL);
         PreparedStatement ps = createPreparedStatement(con, userId); 
         ResultSet rs = ps.executeQuery()) {
         // process the resultset here, all resources will be cleaned up
    } catch (SQLException e) {
        e.printStackTrace();
    }
private PreparedStatement createPreparedStatement(Connection con, int userId) throws SQLException {
    String sql = "SELECT id, username FROM users WHERE id = ?";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setInt(1, userId);
    return ps;
}