如何在JDBC中建立连接池?

Can anybody provide examples or links on how to establish a JDBC connection pool?

有人能提供如何建立JDBC连接池的示例或链接吗?

From searching google I see many different ways of doing this and it is rather confusing.

通过搜索谷歌，我发现了许多不同的方法来实现这一点，这是相当令人困惑的。

Ultimately I need the code to return a java.sql.Connection object, but I am having trouble getting started..any suggestions welcome.

最终，我需要代码来返回java.sql。连接对象，但是我启动时遇到了麻烦。欢迎任何建议。

Update: Doesn't javax.sql or java.sql have pooled connection implementations? Why wouldn't it be best to use these?

更新:不javax。sql或java。sql有聚合连接实现吗?为什么不使用这些呢?

13 个解决方案

#1

If you need a standalone connection pool, my preference goes to C3P0 over DBCP (that I've mentioned in this previous answer), I just had too much problems with DBCP under heavy load. Using C3P0 is dead simple. From the documentation:

如果您需要一个独立的连接池，我倾向于使用C3P0而不是DBCP(我在前面的答案中提到过)，我只是在沉重的负载下遇到了太多的DBCP问题。使用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 

But if you are running inside an application server, I would recommend to use the built-in connection pool it provides. In that case, you'll need to configure it (refer to the documentation of your application server) and to retrieve a DataSource via JNDI:

但是，如果您正在应用服务器中运行，我建议使用它提供的内置连接池。在这种情况下，您将需要配置它(参考您的应用服务器的文档)并通过JNDI检索数据源:

DataSource ds = (DataSource) new InitialContext().lookup("jdbc/myDS");

#2

Usually if you need a connection pool you are writing an application that runs in some managed environment, that is you are running inside an application server. If this is the case be sure to check what connection pooling facilities your application server providesbefore trying any other options.

通常，如果您需要一个连接池，您正在编写一个运行在某个托管环境中的应用程序，即您正在应用服务器中运行。如果是这样，在尝试任何其他选项之前，请确保检查您的应用服务器提供的连接池设施。

The out-of-the box solution will be the best integrated with the rest of the application servers facilities. If however you are not running inside an application server I would recommend the Apache Commons DBCP Component. It is widely used and provides all the basic pooling functionality most applications require.

开箱即用的解决方案将是与其他应用服务器设施集成的最佳方案。如果您没有在应用服务器中运行，我建议使用Apache Commons DBCP组件。它被广泛使用，并提供大多数应用程序所需的所有基本池功能。

#3

I would recommend using the commons-dbcp library. There are numerous examples listed on how to use it, here is the link to the move simple one. The usage is very simple:

我建议使用common -dbcp库。关于如何使用它，列出了许多示例，这里是move simple的链接。用法很简单:

 BasicDataSource ds = new BasicDataSource();
 ds.setDriverClassName("oracle.jdbc.driver.OracleDriver")
 ds.setUsername("scott");
 ds.setPassword("tiger");
 ds.setUrl(connectURI);
 ...
 Connection conn = ds.getConnection();

You only need to create the data source once, so make sure you read the documentation if you do not know how to do that. If you are not aware of how to properly write JDBC statements so you do not leak resources, you also might want to read this Wikipedia page.

您只需要创建数据源一次，所以如果不知道如何创建，请确保阅读了文档。如果您不知道如何正确地编写JDBC语句以避免泄漏资源，您可能还想要阅读这个Wikipedia页面。

#4

Don't reinvent the wheel.

不要重新发明*。

Try one of the readily available 3rd party components:

尝试一个现成的第三方组件:

• Apache DBCP - This one is used internally by Tomcat, and by yours truly.
• Apache DBCP——Tomcat内部使用这个版本，您也可以真正地使用它。
• c3p0
• c3p0

Apache DBCP comes with different example on how to setup a pooling javax.sql.DataSource. Here is one sample that can help you get started.

Apache DBCP提供了关于如何设置池java .sql. datasource的不同示例。这里有一个例子可以帮助你开始。

#5

HikariCP

It's modern, it's fast, it's simple. I use it for every new project. I prefer it a lot over C3P0, don't know the other pools too well.

它是现代的，快速的，简单的。每一个新项目我都用它。与C3P0相比，我更喜欢它，我不太了解其他的池。

#6

In the app server we use where I work (Oracle Application Server 10g, as I recall), pooling is handled by the app server. We retrieve a javax.sql.DataSource using a JNDI lookup with a javax.sql.InitialContext.

在我工作的应用服务器(我记得是Oracle Application server 10g)中，池是由应用服务器处理的。我们检索javax.sql。使用带有java .sql. initialcontext的JNDI查找的数据源。

it's done something like this

它是这样做的。

try {     
   context = new InitialContext();
   jdbcURL = (DataSource) context.lookup("jdbc/CachedDS");
   System.out.println("Obtained Cached Data Source ");
}
catch(NamingException e)   
{  
    System.err.println("Error looking up Data Source from Factory: "+e.getMessage());
}

(We didn't write this code, it's copied from this documentation.)

(我们没有编写这个代码，它是从这个文档中复制的。)

#7

As answered by others, you will probably be happy with Apache Dbcp or c3p0. Both are popular, and work fine.

正如其他人回答的那样，您可能会对Apache Dbcp或c3p0感到满意。两者都很受欢迎，工作也不错。

Regarding your doubt

关于你的疑问

Doesn't javax.sql or java.sql have pooled connection implementations? Why wouldn't it be best to use these?

不javax。sql或java。sql有聚合连接实现吗?为什么不使用这些呢?

They don't provide implementations, rather interfaces and some support classes, only revelant to the programmers that implement third party libraries (pools or drivers). Normally you don't even look at that. Your code should deal with the connections from your pool just as they were "plain" connections, in a transparent way.

它们不提供实现，而是提供接口和一些支持类，只关注实现第三方库(池或驱动程序)的程序员。通常你甚至都不去看它。您的代码应该以透明的方式处理来自池的连接，就像它们是“普通”连接一样。

#8

Vibur DBCP is another library for that purpose. Several examples showing how to configure it for use with Hibernate, Spring+Hibernate, or programatically, can be found on its website: http://www.vibur.org/

Vibur DBCP是另一个用于此目的的库。可以在它的网站http://www.vibur.org/上找到几个示例，展示如何配置它以使用Hibernate、Spring+Hibernate或编程方式

Also, see the disclaimer here.

此外，请参阅这里的免责声明。

#9

Pool

池

• Pooling Mechanism is the way of creating the Objects in advance. When a class is loaded.
• 池机制是预先创建对象的方法。加载类时。
• It improves the application performance [By re using same object's to perform any action on Object-Data] & memory [allocating and de-allocating many objects creates a significant memory management overhead].
• 它改进了应用程序的性能[通过使用相同的对象来对对象数据执行任何操作]&内存[分配和分配许多对象会产生大量的内存管理开销]。
• Object clean-up is not required as we are using same Object, reducing the Garbage collection load.
• 对象清理是不需要的，因为我们使用的是同一个对象，减少了垃圾收集负载。

« Pooling [ Object pool, String Constant Pool, Thread Pool, Connection pool]

«池化【对象池，字符串常量池，线程池，连接池】

String Constant pool

字符串常量池

• String literal pool maintains only one copy of each distinct string value. which must be immutable.
• 字符串文字池只维护每个不同字符串值的一个副本。这一定是不可变的。
• When the intern method is invoked, it check object availability with same content in pool using equals method. « If String-copy is available in the Pool then returns the reference. « Otherwise, String object is added to the pool and returns the reference.
• 当调用intern方法时，它使用equals方法检查池中具有相同内容的对象可用性。«如果池中有字符串拷贝，那么返回引用。«否则，字符串对象将被添加到池中并返回引用。

Example: String to verify Unique Object from pool.

示例:用于验证来自池的唯一对象的字符串。

public class StringPoolTest {
    public static void main(String[] args) { // Integer.valueOf(), String.equals()
        String eol = System.getProperty("line.separator"); //java7 System.lineSeparator();

        String s1 = "Yash".intern();
        System.out.format("Val:%s Hash:%s SYS:%s "+eol, s1, s1.hashCode(), System.identityHashCode(s1));
        String s2 = "Yas"+"h".intern();
        System.out.format("Val:%s Hash:%s SYS:%s "+eol, s2, s2.hashCode(), System.identityHashCode(s2));
        String s3 = "Yas".intern()+"h".intern();
        System.out.format("Val:%s Hash:%s SYS:%s "+eol, s3, s3.hashCode(), System.identityHashCode(s3));
        String s4 = "Yas"+"h";
        System.out.format("Val:%s Hash:%s SYS:%s "+eol, s4, s4.hashCode(), System.identityHashCode(s4));
    }
}

Connection pool using Type-4 Driver using 3rd party libraries[ DBCP2, c3p0, Tomcat JDBC]

使用第4类型驱动程序使用第三方库的连接池[DBCP2, c3p0, Tomcat JDBC]

Type 4 - The Thin driver converts JDBC calls directly into the vendor-specific database protocol Ex[Oracle - Thick, MySQL - Quora]. wiki

类型4 -瘦驱动程序直接将JDBC调用转换为特定于供应商的数据库协议Ex[Oracle - Thick, MySQL - Quora]。维基

In Connection pool mechanism, when the class is loaded it get's the physical JDBC connection objects and provides a wrapped physical connection object to user. PoolableConnection is a wrapper around the actual connection.

在连接池机制中，当类被加载时，它将获得物理JDBC连接对象，并向用户提供一个封装的物理连接对象。PoolableConnection是一个围绕实际连接的包装器。

• getConnection() pick one of the free wrapped-connection form the connection objectpool and returns it.
• getConnection()从connection objectpool中选择一个免费的封装连接并返回它。
• close() instead of closing it returns the wrapped-connection back to pool.
• close()而不是close，它返回包装连接到池。

Example: Using ~ DBCP2 Connection Pool with Java 7[try-with-resources]

示例:使用~ DBCP2连接池与Java 7[带有资源的try]

public class ConnectionPool {
    static final BasicDataSource ds_dbcp2 = new BasicDataSource();
    static final ComboPooledDataSource ds_c3p0 = new ComboPooledDataSource();
    static final DataSource ds_JDBC = new DataSource();

    static Properties prop = new Properties();
    static {
        try {
            prop.load(ConnectionPool.class.getClassLoader().getResourceAsStream("connectionpool.properties"));

            ds_dbcp2.setDriverClassName( prop.getProperty("DriverClass") );
            ds_dbcp2.setUrl( prop.getProperty("URL") );
            ds_dbcp2.setUsername( prop.getProperty("UserName") );
            ds_dbcp2.setPassword( prop.getProperty("Password") );
            ds_dbcp2.setInitialSize( 5 );

            ds_c3p0.setDriverClass( prop.getProperty("DriverClass") );
            ds_c3p0.setJdbcUrl( prop.getProperty("URL") );
            ds_c3p0.setUser( prop.getProperty("UserName") );
            ds_c3p0.setPassword( prop.getProperty("Password") );
            ds_c3p0.setMinPoolSize(5);
            ds_c3p0.setAcquireIncrement(5);
            ds_c3p0.setMaxPoolSize(20);

            PoolProperties pool = new PoolProperties();
            pool.setUrl( prop.getProperty("URL") );
            pool.setDriverClassName( prop.getProperty("DriverClass") );
            pool.setUsername( prop.getProperty("UserName") );
            pool.setPassword( prop.getProperty("Password") );
            pool.setValidationQuery("SELECT 1");// SELECT 1(mysql) select 1 from dual(oracle)

            pool.setInitialSize(5);
            pool.setMaxActive(3);
            ds_JDBC.setPoolProperties( pool );
        } catch (IOException e) {   e.printStackTrace();
        } catch (PropertyVetoException e) { e.printStackTrace(); }
    }

    public static Connection getDBCP2Connection() throws SQLException {
        return ds_dbcp2.getConnection();
    }

    public static Connection getc3p0Connection() throws SQLException {
        return ds_c3p0.getConnection();
    }

    public static Connection getJDBCConnection() throws SQLException {
        return ds_JDBC.getConnection();
    }
}
public static boolean exists(String UserName, String Password ) throws SQLException {
    boolean exist = false;
    String SQL_EXIST = "SELECT * FROM users WHERE username=? AND password=?";
    try ( Connection connection = ConnectionPool.getDBCP2Connection();
          PreparedStatement pstmt = connection.prepareStatement(SQL_EXIST); ) {
        pstmt.setString(1, UserName );
        pstmt.setString(2, Password );

        try (ResultSet resultSet = pstmt.executeQuery()) {
            exist = resultSet.next(); // Note that you should not return a ResultSet here.
        }
    }
    System.out.println("User : "+exist);
    return exist;
}

jdbc:<DB>:<drivertype>:<HOST>:<TCP/IP PORT>:<dataBaseName> jdbc:oracle:thin:@localhost:1521:myDBName jdbc:mysql://localhost:3306/myDBName

jdbc:< DB >:< drivertype >: <主机> :< TCP / IP端口>: <数据库名> jdbc:oracle:薄:@localhost:1521:myDBName jdbc:mysql:/ / localhost:3306 / myDBName

connectionpool.properties

connectionpool.properties

URL         : jdbc:mysql://localhost:3306/myDBName
DriverClass : com.mysql.jdbc.Driver
UserName    : root
Password    :

Web Application: To avoid connection problem when all the connection's are closed[MySQL "wait_timeout" default 8 hours] in-order to reopen the connection with underlying DB.

Web应用程序:为了避免连接问题，当所有连接都关闭时[MySQL "wait_timeout"默认为8小时]，以便重新打开与底层数据库的连接。

You can do this to Test Every Connection by setting testOnBorrow = true and validationQuery= "SELECT 1" and donot use autoReconnect for MySQL server as it is deprecated. issue

您可以通过设置testOnBorrow = true和validationQuery= "SELECT 1"来测试每个连接，并且不会在MySQL服务器上使用autoReconnect，因为它不被使用。问题

===== ===== context.xml ===== =====
<?xml version="1.0" encoding="UTF-8"?>
<!-- The contents of this file will be loaded for a web application -->
<Context>
    <Resource name="jdbc/MyAppDB" auth="Container" 
        factory="org.apache.tomcat.jdbc.pool.DataSourceFactory" 
        type="javax.sql.DataSource" 

        initialSize="5" minIdle="5" maxActive="15" maxIdle="10"

        testWhileIdle="true"
            timeBetweenEvictionRunsMillis="30000"

        testOnBorrow="true"
            validationQuery="SELECT 1"
            validationInterval="30000"


        driverClassName="com.mysql.jdbc.Driver" 
        url="jdbc:mysql://localhost:3306/myDBName" 
        username="yash" password="777"
    />
</Context>

===== ===== web.xml ===== =====
<resource-ref>
    <description>DB Connection</description>
    <res-ref-name>jdbc/MyAppDB</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>
===== ===== DBOperations ===== =====
servlet «   init() {}
Normal call used by sevlet  « static {}

static DataSource ds;
static {
    try {
        Context ctx=new InitialContext();
        Context envContext = (Context)ctx.lookup("java:comp/env");
        ds  =   (DataSource) envContext.lookup("jdbc/MyAppDB");
    } catch (NamingException e) {   e.printStackTrace();    }
}

See these also:

看到这些也:

• am-i-using-jdbc-connection-pooling
• am-i-using-jdbc-connection-pooling
• configuring-jdbc-pool-high-concurrency
• configuring-jdbc-pool-high-concurrency

#10

Apache Commons has a library for that purpose: DBCP. Unless you have strange requirements around your pools, I'd use a library as it's bound to be trickier and more subtle than you would hope.

Apache Commons为此提供了一个库:DBCP。除非您对池有奇怪的要求，否则我将使用库，因为它肯定比您希望的更复杂、更微妙。

#11

In late 2017 Proxool, BoneCP, C3P0, DBCP are mostly defunct at this time. HikariCP (created in 2012) seems promising, blows the doors off anything else I know of. http://www.baeldung.com/hikaricp

在2017年末，Proxool, BoneCP, C3P0, DBCP在这个时候已经基本失效。HikariCP(创建于2012年)似乎很有前途，它能把我所知道的一切都打破。http://www.baeldung.com/hikaricp

Proxool has a number of issues:
- Under heavy load can exceed max number of connections and not return below max
- Can manage to not return to min connections even after connections expire
- Can lock up the entire pool (and all server/client threads) if it has trouble connecting to the database during HouseKeeper thread (does not use .setQueryTimeout)
- HouseKeeper thread, while having connection pool lock for its process, requests the Prototyper thread to recreate connections (sweep) which can result in race condition/lockup. In these method calls the last parameter should always be sweep:false during the loop, only sweep:true below it.
- HouseKeeper only needs the single PrototypeController sweep at the end and has more [mentioned above]
- HouseKeeper thread checks for testing of connections before seeing what connections may be expired [some risk of testing expired connection that may be broken/terminated through other timeouts to DB in firewall, etc.]
- The project has unfinished code (properties that are defined but not acted upon)
- The Default max connection life if not defined is 4 hours (excessive)
- HouseKeeper thread runs every five seconds per pool (excessive)

You can modify the code and make these improvements. But as it was created in 2003, and updated in 2008, its lacking nearly 10 years of java improvements that solutions like hikaricp utilize.

Proxool有很多问题:——负载较重的情况下,可以超过最大连接数,而不是回报低于最大,可以管理不会回到最小连接即使连接到期,可以锁定整个池(和所有服务器/客户端线程)如果它有麻烦在管家螺纹连接到数据库(不使用.setQueryTimeout)——管家线程,同时连接池锁的过程中,请求原型线程重新创建连接(清除)，从而导致竞争条件/锁定。在这些方法中，调用最后一个参数时，应该始终是遍历:false，在循环期间，只遍历:true。-管家只需要在最后进行一次原型econtroller扫描，并且有更多的[上面提到的]-管家线程检查连接，在查看哪些连接可能过期之前进行测试[通过防火墙中的其他超时测试失效/终止失效连接的风险]，-项目有未完成的代码(已定义但未执行的属性)-如果没有定义，默认的最大连接时间为4小时(过量)-管家线程每5秒运行一次(过量)，您可以修改代码并进行这些改进。但是，由于它是在2003年创建的，并在2008年更新的，它缺乏像hikaricp这样的解决方案使用的近10年的java改进。

#12

You should consider using UCP. Universal Connection Pool (UCP) is a Java connection pool. It is a features rich connection pool and tightly integrated with Oracle's Real Application Clusters (RAC), ADG, DG databases.

您应该考虑使用UCP。通用连接池(UCP)是一个Java连接池。它具有丰富的连接池，并与Oracle的真实应用程序集群(RAC)、ADG和DG数据库紧密集成。

Refer to this page for more details about UCP.

有关UCP的更多细节，请参阅此页。

#13

MiniConnectionPoolManager is a one-java-file implementation, if you're looking for an embeddable solution and are not too concerned about performances (though I haven't tested it in that regard).

MiniConnectionPoolManager是一个单java文件实现，如果您正在寻找一个可嵌入的解决方案，并且不太关心性能(尽管我还没有在这方面对它进行测试)。

It is multi-licensed EPL, LGPL and MPL.

它是多授权的EPL, LGPL和MPL。

Its documentation also gives alternatives worth checking (on top of DBCP and C3P0):

它的文档还提供了值得检查的备选方案(在DBCP和C3P0之上):

• Proxool
• Proxool
• BoneCP
• BoneCP
• Tomcat JDBC Connection Pool
• Tomcat JDBC连接池