Java Oracle 连接池 - 关闭连接异常

发布于 2024-09-07 04:26:50 字数 2434 浏览 4 评论 0 原文

这篇文章的目的不是提出问题,而是确认我正在正确地做事。我看过很多类似的帖子,但我不确定我是否完全理解其中所说的一切。

问题是,在一段时间后,我在尝试建立与 Oracle 数据库的连接时遇到异常。 (我使用的是 Tomcat 6.0 和 Spring)

以前我有以下配置:

private PoolDataSource poolDataSource = null;

public MainDAOImpl(String url, String username, String password)
        throws Exception
{
    poolDataSource = PoolDataSourceFactory.getPoolDataSource();

    try
    {
        poolDataSource.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
        poolDataSource.setURL(url);
        poolDataSource.setUser(username);
        poolDataSource.setPassword(password);
    }
    catch( SQLException e )
    {
        ...
    }
}

public List<Object> getValues(String query)
{
    Connection connection = null;
    PreparedStatement preparedStatement = null;

    try
    {
        connection = poolDataSource.getConnection();
        preparedStatement = connection.prepareStatement(query);

        ...
    }
    catch( SQLException e )
    {
        ...
    }
    finally
    {
        //close connections
    }
}

但是,有时 preparedStatement = connection.prepareStatement(query); 会抛出带有“Closed Exception”消息的 SQLException。

需要注意的是,每次服务器重新启动时,MainDAOImpl 的构造函数只会被调用一次(它是通过 Spring 注入的依赖项)。

我最近将我的设置更改为:

private DataSource dataSource = null;

public MainDAOImpl()
        throws Exception
{
    try
    {
        Context initContext = new InitialContext();
        Context envContext = (Context)initContext.lookup("java:/comp/env");
        dataSource = (DataSource)envContext.lookup("jdbc/myOracleConn");
    }
    catch( NamingException e )
    {
        ...
    }
}

poolDataSource.getConnection() 更改为 dataSource.getConnection()

我还在 Tomcat 中的上下文中添加了以下资源:

<Resource name="jdbc/myOracleConn" auth="Container"
          type="javax.sql.DataSource"
          driverClassName="oracle.jdbc.OracleDriver"
          url="<myURL>"
          username="<myUsername>" password="<myPassword>"
          maxActive="20" maxIdle="10" maxWaith="-1" />

这基本上遵循 http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html 逐字逐句。

一切似乎都正常。我的问题是,这些更改会解决我的关闭连接问题还是我需要做一些不同的事情?

谢谢,

北京

This post is intended to be less of a question and more a confirmation that I'm doing things correctly. I've seen many similar posts but I'm not sure I fully understand everything that's been said.

The problem is that, after a certain amount of time, I get an exception when trying to establish a connection to my oracle database. (I'm using Tomcat 6.0 and Spring)

Previously I had the following configuration:

private PoolDataSource poolDataSource = null;

public MainDAOImpl(String url, String username, String password)
        throws Exception
{
    poolDataSource = PoolDataSourceFactory.getPoolDataSource();

    try
    {
        poolDataSource.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
        poolDataSource.setURL(url);
        poolDataSource.setUser(username);
        poolDataSource.setPassword(password);
    }
    catch( SQLException e )
    {
        ...
    }
}

public List<Object> getValues(String query)
{
    Connection connection = null;
    PreparedStatement preparedStatement = null;

    try
    {
        connection = poolDataSource.getConnection();
        preparedStatement = connection.prepareStatement(query);

        ...
    }
    catch( SQLException e )
    {
        ...
    }
    finally
    {
        //close connections
    }
}

However, sometimes the preparedStatement = connection.prepareStatement(query); threw an SQLException with a "Closed Exception" message.

It's important to note that the MainDAOImpl's constructor gets called only once per server restart (it's dependency injected via Spring).

I've recently changed my setup like so:

private DataSource dataSource = null;

public MainDAOImpl()
        throws Exception
{
    try
    {
        Context initContext = new InitialContext();
        Context envContext = (Context)initContext.lookup("java:/comp/env");
        dataSource = (DataSource)envContext.lookup("jdbc/myOracleConn");
    }
    catch( NamingException e )
    {
        ...
    }
}

and poolDataSource.getConnection() to dataSource.getConnection().

I've also added the following Resource to my Context in Tomcat:

<Resource name="jdbc/myOracleConn" auth="Container"
          type="javax.sql.DataSource"
          driverClassName="oracle.jdbc.OracleDriver"
          url="<myURL>"
          username="<myUsername>" password="<myPassword>"
          maxActive="20" maxIdle="10" maxWaith="-1" />

This basically follows http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html word-for-word.

Everything seems to be working. My question is, will these changes solve my closed connection problem or is there something different I need to do?

Thanks,

B.J.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

无声无音无过去 2024-09-14 04:26:50

首先,如果你使用 Spring 进行依赖注入,我建议你也使用 DI 将 DAO 的依赖注入其中。

换句话说,您的 DAO 应该注入一个 DataSource,而不是 DAO 实现知道 1) 要构造什么类型的 DataSource 或 2) 如何以及在何处在 JNDI 中查找它。春天 可以为您处理 JNDI 查找

我还建议使用 Spring 的 JdbcTemplate,因为它为您自己的原始 JDBC 调用提供了一个很好的包装器。

最后,您收到的实际异常可能只是因为数据库服务器正在关闭长时间打开的连接。不确定您使用的是哪种连接池实现,但在 commons-dbcp 中有 “validationQuery”的选项,池将在返回连接之前执行该选项以验证连接是否仍然有效。我确信大多数其他池都提供类似的功能,我在这里推荐 - 这样您的 DAO 就永远不会从池中接收过时的连接。

First of all, if you are using Spring for dependency injection, I would recommend that you also use DI to inject the DAO's dependencies into it.

In other words, your DAO should have a DataSource injected into it, rather than the DAO implementation knowing either 1) what type of DataSource to construct or 2) how and where to look it up in JNDI. Spring can handle JNDI lookups for you.

I'd also recommend using Spring's JdbcTemplate, as it makes for a great wrapper over raw JDBC calls yourself.

Finally, the actual exception you are getting may just be because the database server is closing long-open connections. Not sure which connection pool implementation you are using, but in commons-dbcp there is an option for a "validationQuery" which the pool will execute before returning a connection to verify the connection is still valid. I'm sure most other pools supply similar features, which I would recommend here - this way your DAO is never receiving stale connections from the pool.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文