ORA-01000:超出打开游标的最大数的原因和解决方案

import java.sql.Connection; 
import java.sql.DriverManager; 
import java.sql.SQLException; 
import java.sql.Statement; 

public class Test { 
  public Connection getConnection() { 
    String url = "jdbc:oracle:thin:@localhost:1521:ora9i"; 
    String user = "scott"; 
    String password = "tiger"; 
    Connection con = null; 
    try { 
      Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); 
      con = DriverManager.getConnection(url, user, password); 
    } catch (Exception e) { 
      e.printStackTrace(); 
    } 
    return con; 
  } 

  public static void main(String[] args) throws SQLException { 
    long a = 13819100000L; 
    long b = 13819100600L; // 问题点 
    Connection con = null; 
    Statement stmt = null; 
    Test insert = new Test(); 
    try { 
      con = insert.getConnection(); 
      for (long c = a; c <= b; c++) { 
        String sql = "insert into telepnum values(" + c + ")"; 
        stmt = con.createStatement(); // 这里是问题的所在 
        stmt.executeUpdate(sql); 
      } 
      System.out.println("OK"); 
    } catch (Exception e) { 
      e.printStackTrace(); 
    } finally { 
      if (con != null) { 
        con.close(); 
      } 
    } 
  } 

分析
在循环里面每次都
stmt=con.createStatement();
而没有释放，这样每个都占用了一个服务器的游标资源，最后造成失败

解决方案

1增加关闭语句

con = insert.getConnection(); 
      for (long c = a; c <= b; c++) { 
        String sql = "insert into telepnum values(" + c + ")"; 
        stmt = con.createStatement(); // 这里是问题的所在 
        stmt.executeUpdate(sql); 
        stmt.close(); // 用完了就关闭好了 
      }

2改装成批量更新

con = insert.getConnection();      con.setAutoCommit(false);      stmt = con.createStatement(); // 移动到这里，Statemet是可以重用的      for (long c = a; c <= b; c++) {        String sql = "insert into telepnum values(" + c + ")";        stmt.addBatch(sql);      }      stmt.executeBatch();      con.commit();