Java中使用c3p0的连接池 [英] Connection pooling in java using c3p0

问题描述

我想在我编写的无网络应用程序Java程序中使用c3p0进行连接池。我使用传统的单例连接，但对它的性能不满意，因此我决定使用连接池。我看一下c3p0网站，这是他们讲述使用c3p0的内容：

I want to using c3p0 for connection pooling in none-web application java program that i wrote. I used traditional singleton connection and I am not satisfied with its performance, so I decided to go for connection pooling. I take a look at c3p0 website and here is what they told about using c3p0:

ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass( "org.postgresql.Driver" ); //loads the jdbc driver            
cpds.setJdbcUrl( "jdbc:postgresql://localhost/testdb" );
cpds.setUser("swaldman");                                  
cpds.setPassword("test-password");                                  

// the settings below are optional -- c3p0 can work with defaults
cpds.setMinPoolSize(5);                                     
cpds.setAcquireIncrement(5);
cpds.setMaxPoolSize(20);

// The DataSource cpds is now a fully configured and usable pooled DataSource

我想知道如何将其用于ms sql Windows身份验证连接，但我不知道如何使用？另外，如何通过该连接设置查询？似乎使用连接池与传统数据库连接是一个完全不同的世界，对此我是陌生的。
这是我计算出的内容：

I want to know how could i use that for ms sql windows authentication connection, but I could not figure it how? Also how can I set my query through that connection? It seems that using connection pooling is a whole different world than traditional database connection and I am new to that. Here is what i figure out:

public class DatabaseManager {

    private static DataSource dataSource;
    private static final String DRIVER_NAME;
    private static final String URL;
    private static final String UNAME;
    private static final String PWD;
    private static final String dbName;

    static {

        dbName="SNfinal";
        DRIVER_NAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        URL = "jdbc:sqlserver://localhost:1433;" +
                      "databaseName="+dbName+";integratedSecurity=true";
        UNAME = "";
        PWD = "";
        dataSource = setupDataSource();
    }
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    private static DataSource setupDataSource() {
        ComboPooledDataSource cpds = new ComboPooledDataSource();
        try {
            cpds.setDriverClass(DRIVER_NAME);
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
        cpds.setJdbcUrl(URL);
        cpds.setUser(UNAME);
        cpds.setPassword(PWD);
        cpds.setMinPoolSize(1000);
        cpds.setAcquireIncrement(1000);
        cpds.setMaxPoolSize(20000);
        return cpds;
    }
    public static ResultSet executeQuery(String SQL, String dbName)
    {
        ResultSet rset = null ;

        try {
            Connection con=DatabaseManager.getConnection();
               Statement st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
               rset = st.executeQuery(SQL);

        }
        catch (SQLException e) {
            System.out.println(e.getMessage());
            System.exit(0);
        }

        return rset;
     }

    public static void executeUpdate(String SQL, String dbName)
    {

        try {
            Connection con=DatabaseManager.getConnection();
               Statement st = con.createStatement();
               st.executeUpdate(SQL);

        }

        catch (SQLException e) {
            System.out.println(e.getMessage());
            System.exit(0);
        }
     }

}

当我使用该类可以很好地处理大约2000个查询，之后它会停止处理与资源分配有关的一些异常！

When I use this class it works well for around 2000 queries, after that it stop working with some exceptions related to resource allocation!

推荐答案

如果您正在将应用程序从使用单个缓存的连接演变为使用连接池，主要要做的是...

if you are evolving an application from using a single cached Connection to using a Connection pool, the main thing you have to do is...

1）不存储任何连接作为对象的静态或成员变量。仅存储对数据源的引用，cpds是上面的代码示例；

1) do not store any Connections as static or member variables of objects. store only a reference to the DataSource, cpds is the code sample above;

2）每次需要使用Connection时，请在池上调用getConnection（）-支持的数据源；

2) each time you need to use a Connection, call getConnection() on the the pool-backed DataSource;

3）请务必在每次使用后可靠地关闭（）连接（即，在一个finally块中，每个资源都以自己的方式包裹起来）尝试/捕获，或者如果您的代码库是Java 7，则通过 try-with-资源）。如果不这样做，最终将泄漏连接并耗尽池。 c3p0有一些黑客可以帮助您，但是最好的建议是不要写泄漏代码。

3) be sure to close() the Connection after each use, reliably (i.e. in a finally block with each resource close wrapped in its own try/catch or, if your codebase is Java 7, via try-with-resources). if you don't, you'll eventually leak Connections and exhaust the pool. c3p0 has some hacks to help you with that, but the best advice is not to write leaky code.

无论您通过身份验证来获取单个Connection还是通过池进行身份验证的方式。您是否需要在环境中进行特殊或不寻常的身份验证？

however you authenticate to acquire a single Connection should be how you authenticate via the pool. do you have to do something special or unusual to authenticate in your environment?

因此，大问题很大。

首先有些零碎：System.exit（0）是响应异常的一种坏方法。您的方法接受没有功能的 dbName 参数。

First some odds and ends: System.exit(0) is a bad way to respond to Exceptions. Your methods accept a dbName parameter that has no function.

但是，巨大，巨大，糟糕的问题是您请勿进行任何资源清理。您的 executeQuery 和 executeUpdate 方法打开Connections，然后无法关闭它们。这将导致短期内资源泄漏。如果要在此类方法中打开Connection，则必须以某种方式返回它们，以便可以在使用后将它们关闭（）。那会很麻烦。您可以重新定义接受Connecion对象的方法，即...

But the big, huge, bad problem is that you do no resource cleanup whatsoever. Your executeQuery and executeUpdate methods open Connections and then fail to close() them. That will lead to resource leaks in short order. If you want to open Connection inside methods like these, you have to return them in some manner so that they can be close()ed after use. That will get cumbersome. You can redefine the methods to accept Connecion objects, that is, something like...

ResultSet executeQuery( Connection con, String query ) { 
  ...
}

...或者更好，只是让您的客户直接使用JDBC API，这实际上比使用实际上作用很小的 execute 方法要简单得多。

...or better yet, just let your clients use the JDBC api directly, which will in fact be much simpler than using your execute methods that in fact do very little.

如果您的代码库是Java 7，请尝试-with-resources 是确保清理JDBC资源的便捷方法。如果没有，则必须使用显式的finally子句（内部对close（）的调用最终嵌套在它们自己的try / catches中。）

If you're codebase is Java 7, try-with-resources is a convenient way to ensure JDBC resources are cleaned up. If not, you'll have to use explicit finally clauses (with the calls to close() inside finally nested in their own try/catches).

关于异常您所看到的，他们的消息非常清楚原因。在关闭（）后使用ResultSet。问题是为什么。我没有一个简单的答案，但是总的来说，您对资源清理不是很干净；我怀疑这是问题所在。令您感到惊讶的是，由于您泄漏了Connections并且应该已经用完了，因此使用该代码可以运行2000个查询。所以有一些奥秘。但是，以一种或另一种方式，您有时试图在ResultSets被close（）后使用它们，可能是其他某个Thread。也许您正在对close（）连接执行不明显的操作，例如使用resultSet.getStatement（），getConnection（）查找需要close（）的资源，然后在完成工作之前关闭Connection的连接

As for the Exceptions you're seeing, their messages are pretty clear about the cause. You are using ResultSets after they have been close()ed. The question is why. I don't have a simple answer, but in general you are not being very clean about resource clean-up; I suspect that is the problem. I'm surprised you manage to get 2000 queries to ever run with this code, since you are leaking Connections and should have run out. So there are some mysteries. But one way or another, you are occasionally trying to use ResultSets after they have been close()ed, probably by some other Thread. Maybe you are doing something non-obvious to close() Connections, like using resultSet.getStatement(),getConnection() to find the resource you need to close(), and then close()ing the Connection before you've finished working with the ResultSet?

祝你好运！

这篇关于Java中使用c3p0的连接池的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！