java - org.apache.tomcat.dbcp.dbcp.SQLNestedException : Cannot get a connection, 池错误等待空闲对象超时

Question

当我将数据从 arreylist 插入到一个 teble 中并且我正在使用 thred 时，我得到了这个异常。 谁能告诉我，因为我终于关闭了所有连接。

 public class RunnableThread implements Runnable {

  List<String> li = new ArrayList<String>();         

               public RunnableThread(ArrayList<String> lii){    
               this.li = lii;

                  }

             public void run() {                    
                Connection con =null;         
                PreparedStatement pstmt = null;
                ResultSet rs = null;
                try {

                  Iterator<String> it=li.iterator();                                                     
                    String splitter="!";
                        while(it.hasNext()){
                            String value =it.next().toString(); 
                    StringTokenizer strTok = new StringTokenizer(value,splitter);
                    String SaleOrderNum=strTok.nextElement().toString().trim();
                    String ItemNum=strTok.nextElement().toString().trim();              
                    String Material=strTok.nextElement().toString().trim();
                    String docType=strTok.nextElement().toString();             
                    String ReqQty=strTok.nextElement().toString().trim();               
                    String PurchOrderDate=strTok.nextElement().toString().trim();               
                    String PurchOrderNum=strTok.nextElement().toString().trim();    
                    String SoldToParty=strTok.nextElement().toString().trim();
                    String SoldToPartyName=strTok.nextElement().toString().trim();              
                    String DelivQty=strTok.nextElement().toString().trim();
                    String SalesOffice=strTok.nextElement().toString().trim();              
                    String Unit=strTok.nextElement().toString().trim();
                    String GIDate=strTok.nextElement().toString().trim();   
                    String Plant=strTok.nextElement().toString().trim();
                    String MaterialDesc=strTok.nextElement().toString().trim();             
                    String ShipToPartyCode=strTok.nextElement().toString().trim();
                    String ShipToPartyName=strTok.nextElement().toString().trim();
                    String ShipToPartyCity=strTok.nextElement().toString().trim();
                    String User_id=strTok.nextElement().toString().trim();                      
                    con = db.getConnection();
                    con.setAutoCommit(false);                                                                   

                    pstmt = con.prepareStatement("INSERT INTO **********) VALUES  ( ? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,getDate())");

                        pstmt.setString(1, );
                        pstmt.setString(2, );
                        pstmt.setString(3, );
                        pstmt.setString(4, );
                        pstmt.setString(5, );
                        pstmt.setString(6, );           
                        pstmt.setString(7, );           
                        pstmt.setString(8, );
                        pstmt.setString(9, );
                        pstmt.setString(10, );
                        pstmt.setString(11, );                  
                        pstmt.setString(12,);
                        pstmt.setString(13, );
                        pstmt.setString(14, );
                        pstmt.setString(15,);
                        pstmt.setString(16,);
                        pstmt.setString(17, );
                        pstmt.setString(18,);                   
                        pstmt.setString(19, );                                                      
                        pstmt.executeUpdate();                                                                                                                                     
                        con.commit();   
                        }               

                        } catch (InterruptedException e) {
                            e.printStackTrace();             

                        }catch (SQLException e) {   
                            e.printStackTrace();                         

                       }catch (Exception exe) {
                            exe.printStackTrace();                                              
                       } 
                       finally {
                        ReleaseResources.closeAll(rs, pstmt, con);                  
                      System.out.println("rel runable finly");
                        }   
    }

}

context.xml

  name="jdbc/opendb"
 auth="Container"
 type="javax.sql.DataSource"   
 initialSize="10"
 maxActive="300"
 maxIdle="70"
 minIdle="13"
 timeBetweenEvictionRunsMillis="34000"
 minEvictableIdleTimeMillis="55000"
 validationQuery="SELECT 1"
 validationInterval="34"
 testOnBorrow="true"
 autoReconnect="true" 
 removeAbandoned="true"
 removeAbandonedTimeout="233"
 logAbandoned="true"
 username="xx"
 password="xxxx" 
 driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
 url="jdbc:sqlserver://XXXX:1533;DatabaseName=XXX;" 

这是 tamcat 的堆栈跟踪。

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool error Timeout waiting  for idle object
at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:114)
at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at com.DB.db.getConnection(db.java:41)
at com..VL01N.RunnableThread.run(RunnableThread.java:62)
at java.lang.Thread.run(Unknown Source)
Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1134)
at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
... 4 more
 rel runable finly

Answer 1

资源管理有问题。您在 while 循环内打开了太多连接(以及 preparedStatements)，但只关闭了最后一个实例。

请看下面的片段:

@Override
public void run() {
    Connection con =null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        Iterator<String> it=li.iterator();
        String splitter="!";
        while(it.hasNext()) {
            String ...
            // Requesting new connection inside the while loop.
            con = db.getConnection();
            con.setAutoCommit(false);

            pstmt = con.prepareStatement("INSERT INTO **********) VALUES  ( ? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,getDate())");
            pstmt.setString... 

            con.commit(); 
            // Only last con & pstmt will get closed. Rest all will be lost.
        }
    } CATCH BLOCKS... {
    }  finally {
        ReleaseResources.closeAll(rs, pstmt, con);
        System.out.println("rel runable finly");
    }
}

您应该在 while 循环之外打开连接，并适本地管理 preparedStatement。喜欢:

@Override
public void run() {
    Connection con =null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
        Iterator<String> it=li.iterator();
        String splitter="!";

        con = db.getConnection();
        con.setAutoCommit(false);
        pstmt = con.prepareStatement("INSERT INTO **********) VALUES  ( ? ,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,getDate())");

        while(it.hasNext()) {
            // Do all the operations
            String ...
            pstmt.setString...
        }
        con.commit();
    } CATCH BLOCKS... {
    }  finally {
        ReleaseResources.closeAll(rs, pstmt, con);
        System.out.println("rel runable finly");
    }
}