原文发表于: http://www.laozizhu.com/view.jsp?articleId=34
先看有问题的代码
- 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(); // 用完了就关闭好了
- }
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();