Java连接池最佳实践？

Question

Java连接池最佳实践？

javadatabasemultithreadingconnection-poolingbonecp

15

因为受够了c3p0的频繁锁定，我决定使用BoneCP作为数据库连接池的替代方案。我的服务器应用程序每分钟需要处理约7,000个项目并将这些项目记录到我们的MySQL数据库中。我目前有100个工作线程，并已设置我的连接池如下：

BoneCPConfig config = new BoneCPConfig();
      config.setJdbcUrl("jdbc:mysql://"+Settings.MYSQL_HOSTNAME+"/"+Settings.MYSQL_DATABASE+"?autoReconnectForPools=true" ); 
      config.setUsername(Settings.MYSQL_USERNAME); 
      config.setPassword(Settings.MYSQL_PASSWORD);
      config.setMinConnectionsPerPartition(5);
      config.setMaxConnectionsPerPartition(10);
      config.setPartitionCount(5);
      config.setAcquireIncrement(5);
      connectionPool = new BoneCP(config); // setup the connection pool

这些设置对于这样的应用程序是否可接受？我之所以问是因为在运行了一两分钟后，当尝试在池上调用 getConnection 时，我会遇到 BoneCP 异常。感谢您的帮助。

这是我在工作线程中用于数据库调用的代码，它不可能在 dbConn = this.dbPool.getConnection() 行失败。我没有正确关闭连接吗？

private void insertIntoDb() {
    try {  
        Connection dbConn = this.dbPool.getConnection();

        try {
            PreparedStatement ps3 = dbConn.prepareStatement("INSERT IGNORE INTO test_table1 SET test1=?, test2=?, test3=?");
            ps3.setString(1, "some string");
            ps3.setString(2, "some other string");
            ps3.setString(3, "more strings");
            ps3.execute();
            ps3.close();

            PreparedStatement ps4 = dbConn.prepareStatement("INSERT IGNORE INTO test_table2 SET test1=?, test2=?, test3=?");
            ps4.setString(1, "some string");
            ps4.setString(2, "some other string");
            ps4.setString(3, "more strings");
            ps4.execute();
            ps4.close();

        } catch(SQLException e) {
            logger.error(e.getMessage());
        } finally {
            try {
                dbConn.close();
            } catch (SQLException e) {
                logger.error(e.getMessage());
            }
        }
    } catch(SQLException e) {
        logger.error(e.getMessage());
    }
}

这是我看到的错误：

 [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
 [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
 [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
 [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
 [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
 [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.
 [java]  WARN [com.google.common.base.internal.Finalizer] (ConnectionPartition.java:141) - BoneCP detected an unclosed connection and will now attempt to close it for you. You should be closing this connection in your application - enable connectionWatch for additional debugging assistance.

ERROR pool-2-thread-39 2010-09-04 13:36:19,798 com.test.testpackage.MyTask - null
java.sql.SQLException
    at com.jolbox.bonecp.BoneCP.getConnection(BoneCP.java:381)

- James

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Pascal Thivent · Accepted Answer

这些设置对于这样的应用程序是否可接受？我问这个问题是因为在运行一两分钟后，当尝试在池中调用getConnection时，我得到了boneCP异常。感谢您的帮助。

如果你有100个工作人员，为什么要将池限制为50个连接（分区数乘以每个分区的最大连接数，即您的情况下为5 x 10）？

我没有正确关闭连接吗？

看起来没问题（但也许启用connectionWatch以查看警告的确切内容）。就我个人而言，我关闭所有我使用的资源，包括语句和结果集。以防万一，这是我使用的习惯用法：

Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;

try {
    conn = pool.getConnection();
    pstmt = conn.prepareStatement(SOME_SQL);
    pstmt.setFoo(1, foo);
    ...
    rs = pstmt.executeQuery();
    ...
} finally {
    if (rs != null) try { rs.close(); } catch (SQLException quiet) {}
    if (pstmt != null) try { pstmt.close(); } catch (SQLException quiet) {}
    if (conn != null) try { conn.close(); } catch (SQLException quiet) {}
}

你可以将上述调用分组放在实用类的静态方法中。

或者，你可以使用Commons DbUtils中的DbUnit.closeQuietly(Connection, Statement, ResultSet)，它已经实现了这一功能。