java.sql.SQLException: ORA-01000: 超出打开游标的最大数 解决方法

java.sql.SQLException: ORA-01000: 超出打开游标的最大数 解决方法

先看有问题的代码 
查看复制到剪切板打印

1. import java.sql.Connection;   
2. import java.sql.DriverManager;   
3. import java.sql.SQLException;   
4. import java.sql.Statement;   
5.   
6. public class Test {   
7.   public Connection getConnection() {   
8.      String url = "jdbc:oracle:thin:@localhost:1521:ora9i";   
9.      String user = "scott";   
10.      String password = "tiger";   
11.      Connection con = null;   
12.     try {   
13.        Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();   
14.        con = DriverManager.getConnection(url, user, password);   
15.      } catch (Exception e) {   
16.        e.printStackTrace();   
17.      }   
18.     return con;   
19.    }   
20.   
21.   public static void main(String[] args) throws SQLException {   
22.     long a = 13819100000L;   
23.     long b = 13819100600L; // 问题点   
24.      Connection con = null;   
25.      Statement stmt = null;   
26.      Test insert = new Test();   
27.     try {   
28.        con = insert.getConnection();   
29.       for (long c = a; c <= b; c++) {   
30.          String sql = "insert into telepnum values(" + c + ")";   
31.          stmt = con.createStatement(); // 这里是问题的所在   
32.          stmt.executeUpdate(sql);   
33.        }   
34.        System.out.println("OK");   
35.      } catch (Exception e) {   
36.        e.printStackTrace();   
37.      } finally {   
38.       if (con != null) {   
39.          con.close();   
40.        }   
41.      }   
42.    }   
43. }  

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 增加关闭语句 
查看 复制到剪切板 打印

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

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 将这句话移动到循环外面，推荐用这个 
查看 复制到剪切板 打印

1. con = insert.getConnection();   
2. stmt = con.createStatement(); // 移动到这里，Statemet是可以重用的   
3. for (long c = a; c <= b; c++) {   
4.    String sql = "insert into telepnum values(" + c + ")";   
5.    stmt.executeUpdate(sql);   
6. }   
7. stmt.close(); // 用完了就关闭好了  

con = insert.getConnection(); stmt = con.createStatement(); // 移动到这里，Statemet是可以重用的 for (long c = a; c <= b; c++) { String sql = "insert into telepnum values(" + c + ")"; stmt.executeUpdate(sql); } stmt.close(); // 用完了就关闭好了

3 改装成批量更新 
查看 复制到剪切板 打印

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

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();

就这么多了！