如何使用 Java 创建 PostgreSQL 连接池？

How create an PostgreSQL connection pool using Java?

提问by Renato Dinhani

I'm trying to use a Connection Pool, but I don't understand it right. Who implements it? The software, the driver or the database?

我正在尝试使用连接池，但我不太明白。谁来实施？软件、驱动程序还是数据库？

How I do to have my program running using a Connection Pool? I'm using the native PostgreSQL driver.

如何使用连接池运行我的程序？我正在使用本机 PostgreSQL 驱动程序。

I need an code example. I'm doing an webcrawler and it has many connections to the database.

我需要一个代码示例。我正在做一个网络爬虫，它与数据库有很多连接。

回答by Tomasz Nurkiewicz

There are several possibilities:

有几种可能：

• application server/servlet container may provide you with connection pool, see e.g. Tomcat 7 JNDI Datasource for PostgresQL.

• You might create connection pool manually using open source libraries like DBCPor C3P0.

• Finally your database JDBC driver may provide some built-in connection pool implementation, see PostgresQL Connection Pools and Data Sourcesand PGConnectionPoolDataSource(I don't know how recent and up-to-date these classes are).

• 应用程序服务器/servlet 容器可以为您提供连接池，参见例如Tomcat 7 JNDI Datasource for PostgresQL。

• 您可以使用DBCP或 C3P0等开源库手动创建连接池。

• 最后，您的数据库 JDBC 驱动程序可能会提供一些内置的连接池实现，请参阅PostgresQL Connection Pools and Data Sources和PGConnectionPoolDataSource（我不知道这些类是最新的和最新的）。

No matter which option you choose, in principle it always works the same way: the client maintains a pool of network connections to the database. Every time you request new connection using DataSource, connection pool will peek free connection and give to you. When you think you are closing the connection, it will actually be released and put back into the pool. Other thread may now use the same, already established connection.

无论您选择哪个选项，原则上它总是以相同的方式工作：客户端维护一个到数据库的网络连接池。每次您使用 请求新连接时DataSource，连接池都会查看空闲连接并提供给您。当您认为要关闭连接时，它实际上会被释放并放回池中。其他线程现在可以使用相同的、已经建立的连接。

Pooling has many advantages:

池化有很多优点：

• there is no overhead of TCP/IP connection, authorization, etc. - it is only done once.

• pool will take care of broken connections, it may also test connection before giving it to you

• finally, the number of active database connections is more stable, connection pool should refuse returning connection if you have already opened too much

• 没有 TCP/IP 连接、授权等开销 - 它只完成一次。

• pool 会处理断开的连接，它也可能会在给你之前测试连接

• 最后，活动数据库连接数更稳定，如果你已经打开太多连接池应该拒绝返回连接

回答by Tom Anderson

The pooling itself is done by code that sits between the application code and the database driver.

池化本身是由位于应用程序代码和数据库驱动程序之间的代码完成的。

Who puts that code there? Could be anyone. It could be you - there are libraries like DBCP that your code could use to put a pool on top of the database. It could be a J2EE container, like Tomcat or JBoss. It could even be the database - as Tomasz points out, PostgreSQL comes with pooling code.

谁把那个代码放在那里？可以是任何人。可能是您 - 有像 DBCP 这样的库，您的代码可以使用它在数据库顶部放置一个池。它可以是 J2EE 容器，如 Tomcat 或 JBoss。它甚至可以是数据库——正如 Tomasz 指出的那样，PostgreSQL 带有池化代码。

It sounds like you aren't using a J2EE container, in which case it's down to you or the database. Personally, i'd prefer a dedicated pooling implementation, like DBCP, over one supplied by the database. The database's programmers care most about the database; the pool's programmers care most about the pool.

听起来您没有使用 J2EE 容器，在这种情况下，这取决于您或数据库。就个人而言，我更喜欢专用的池实现，例如 DBCP，而不是数据库提供的池实现。数据库的程序员最关心数据库；池的程序员最关心池。

So, get DBCP (IMHO, it's better than the alternatives), and use that.

所以，获得 DBCP（恕我直言，它比替代品更好），并使用它。

回答by Suraj Chandran

I think you are looking for connection pooling done at client side that makes connection to the database. The basic idea is that the establishing a new connection is costly as it involves setting up connection, setting up DB side objects, authentication etc. and hence reuse the connection objects.

我认为您正在寻找在客户端完成的连接池连接到数据库。基本思想是建立新连接的成本很高，因为它涉及建立连接、设置 DB 侧对象、身份验证等，从而重用连接对象。