public class DatabaseBean {
private DataSource myDataSource;
public DatabaseBean() {
try {
Context ctx = new InitialContext();
ecwinsDataSource = (DataSource) ctx.lookup("jdbc/myDataSource");
} catch (NamingException ex) {
ex.printstacktrace();
}
}
public Connection getConnection() throws ClassNotFoundException,sqlException,InstantiationException,illegalaccessexception {
Connection connection = myDataSource.getConnection();
System.out.println("Succesfully connected: " + connection);
//Sample: Succesfully connected: com.sun.gjc.spi.jdbc40.ConnectionHolder40@7fb213a5
return connection;
}
}
这样连接池就很快就被填满了;在通过“db相关”视图进行几次导航之后,应用程序将停止以下操作：
RAR5117 : Failed to obtain/create connection from connection pool [ MysqL_testPool ]. Reason : In-use connections equal max-pool-size and expired max-wait-time. Cannot allocate more connections. RAR5114 : Error allocating connection : [Error in allocating a connection. Cause: In-use connections equal max-pool-size and expired max-wait-time. Cannot allocate more connections.] java.sql.sqlException: Error in allocating a connection. Cause: In-use connections equal max-pool-size and expired max-wait-time. Cannot allocate more connections.
我在每个方法中关闭连接和其他资源.应用程序使用独立连接运行所有OK.
我究竟做错了什么？任何提示或建议将不胜感激.
解决方法
try-with-resources块中的所有(Connection,Statement和ResultSet).
public void create(Entity entity) throws sqlException {
try (
Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement(sql_CREATE);
) {
statement.setSomeObject(1,entity.getSomeproperty());
// ...
statement.executeUpdate();
}
}
或者当您不在Java 7时,在try-finally块中.最终关闭它们将保证在异常情况下也关闭它们.
public void create(Entity entity) throws sqlException {
Connection connection = null;
PreparedStatement statement = null;
try {
connection = dataSource.getConnection();
statement = connection.prepareStatement(sql_CREATE);
statement.setSomeObject(1,entity.getSomeproperty());
// ...
statement.executeUpdate();
} finally {
if (statement != null) try { statement.close(); } catch (sqlException logorIgnore) {}
if (connection != null) try { connection.close(); } catch (sqlException logorIgnore) {}
}
}
是的,即使使用连接池,您仍然需要自己关闭连接.这是起始者的一个常见错误,他们认为它会自动处理关闭.这不是真的.连接池即返回一个封装的连接,它在close()中执行以下操作：
public void close() throws sqlException {
if (this.connection is still eligible for reuse) {
do not close this.connection,but just return it to pool for reuse;
} else {
actually invoke this.connection.close();
}
}
不关闭它们将导致连接未被释放回池重新使用,因此它将一次又一次地获取一个新的连接,直到数据库用完,从而导致应用程序崩溃.
也可以看看：
> How often should Connection,Statement and ResultSet be closed in JDBC?
> Is it safe to use a static java.sql.Connection instance in a multithreaded system?
> Closing JDBC Connections in Pool