当前位置： 文江博客话题详情

创建数据库连接池

发布于 2024-08-10 04:02:51 字数 75 浏览 7 评论 0原文

需要有关创建数据库连接池（无论数据库如何）的信息，以及它们的效率如何？在什么条件下他们可以提高绩效。

如何显式地创建它？

原文

Need information on creating a connection pool to the database (irrespective of the database) , and how efficient they are? What are the conditions where they can enhance performance.

How to create it explicitly?

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

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

发布评论

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

评论（7）

烟凡古楼 2024-08-17 04:02:51

您的问题有点模棱两可：

您想要自行开发连接池实现吗？如果是这样，这是一个很好的起点： http://java.sun .com/developer/onlineTraining/Programming/JDCBook/conpool.html 但是在生产环境中强烈建议不要这样做。最好使用现有且经过彻底测试的连接池 API，例如 DBCP 或 C3P0。

或者您想知道如何使用连接池？如果是这样，答案取决于您正在使用的连接池 API。幸运的是，通常可以在相关 API 的网站上找到它。

或者您想知道何时/为什么使用连接池？如果是这样，如果您有一个长期存在的应用程序（例如 Web 应用程序）并且您需要更频繁地连接数据库，那么它肯定会提高连接性能。正常的JDBC做法是：在最短的时间内获取并关闭Connection、Statement和ResultSet 可能的范围（即在同一方法块内）。由于连接相当昂贵，并且可能需要长达 200 毫秒甚至更长的时间，因此使用连接池要快得多。它按需提供连接并负责实际关闭连接。但这并不意味着您可以改变编写 JDBC 的方式，您仍然需要在最可能的范围内获取和关闭它们。您唯一需要改变的是获取连接的方式。例如，从 更改

connection = driverManager.getConnection();

为

connection = connectionPool.getConnection();

只要您的 JDBC 代码写得好，就不需要进行更多更改。

Your question is a bit ambiguous:

Do you want to homegrow a connection pool implementation? If so, this is a nice starting point: http://java.sun.com/developer/onlineTraining/Programming/JDCBook/conpool.html But this is highly discouraged for production environments. Better use an existing and thoroughly tested connection pooling API, like DBCP or C3P0.

Or do you want to know how to use a connection pool? If so, the answer depends on the connection pooling API you're using. It's fortunately usually available at the website of the API in question.

Or do you want to know when/why to use a connection pool? If so, it will surely enhance connecting performance if you have a long-living application (e.g. a webapplication) and you need to connect the database more than often. The normal JDBC practice is namely: acquire and close the Connection, Statement and ResultSet in the shortest possible scope (i.e. inside the very same method block). Because connecting is fairly expensive and can take up to 200ms of time or even more, using a connection pool is much faster. It gives connections on demand and takes care about actually closing the connection. That does however not mean that you may change the way you write JDBC, you still need to acquire and close them in the shorest possible scope. The only thing you need to change is the way you acquire the connection. E.g. change from

connection = driverManager.getConnection();

to

connection = connectionPool.getConnection();

No more changes are needed as long as your JDBC code is well-written.

回复 原文
迷路的信 2024-08-17 04:02:51

Apache DBCP 的介绍页面很好地总结了这一点：

为每个连接创建一个新连接
用户可能会很耗时（通常
需要多秒时钟
time），以便执行数据库
交易可能需要
毫秒。打开一个连接
用户可能不可行
公共托管的互联网应用程序
其中同时用户数
可以非常大。因此，
开发人员通常希望分享
所有之间开放连接的“池”
应用程序的当前用户。
实际用户数
在任何给定时间执行请求
通常只占很小的比例
活跃用户总数，以及
在请求处理期间是唯一的
数据库连接的时间
必需的。应用程序本身记录
进入 DBMS，并处理任何用户
内部帐户问题。

他们的效率如何？取决于实施。通常，我希望池在启动时或根据请求实例化连接。第一个连接将需要与数据库的真实连接，此后当您请求连接时，将为您提供一个现有的池连接。因此，第一个连接请求将花费最多时间，之后您只需从集合中提取对象（非常快）。

The intro page to Apache DBCP sums it up nicely:

Creating a new connection for each
user can be time consuming (often
requiring multiple seconds of clock
time), in order to perform a database
transaction that might take
milliseconds. Opening a connection per
user can be unfeasible in a
publicly-hosted Internet application
where the number of simultaneous users
can be very large. Accordingly,
developers often wish to share a
"pool" of open connections between all
of the application's current users.
The number of users actually
performing a request at any given time
is usually a very small percentage of
the total number of active users, and
during request processing is the only
time that a database connection is
required. The application itself logs
into the DBMS, and handles any user
account issues internally.

How efficient are they ? Depends on the implementation. Typically I would expect a pool to instantiate connections either at start-up or on request. The first connection will require a real connection to the database, and thereafter when you request a connection, you're given an existing pooled connection. So the first connection request will take the most time, and afterwards you're just pulling objects from a collection (very fast).

回复 原文
相守太难 2024-08-17 04:02:51

创建与数据库的连接是非常昂贵的操作。连接池是创建和缓存的数据库连接的实例。每当需要与数据库建立新连接时，都会使用池中的一个连接，而不是创建新连接。某些平台（例如 .NET + SQL Server）默认使用连接池（您不需要创建自己的连接池）。因此，它们基本上通过节省每次创建新连接的时间来提高性能。

Creating connections to databases are very expensive operations. Connection pools are instances of database connections that are created and cached. Anytime a new connection to a database is desired, one from the pool is used instead of creating a new connection. Some platforms like .NET + SQL Server use connection pools by default (you don't need to create your own). So, they basically enhance performance by saving time in creating new connections each time.

回复 原文
む无字情书 2024-08-17 04:02:51

使用连接池，您可以节省每次访问的时间，因为连接已经建立。

此外，至少在 Oracle 上，您将编译后的语句保持链接到连接，因此重复执行相同的 SQL 语句甚至更快。

（如果您使用Java/JDBC，请参阅PreparedStatement）

唯一的性能下降风险是，当您在池中保留太多空闲连接时，相关资源（您这边和数据库上）就会被浪费。

Using a connection pool, you save time at every access because connection is already established.

Moreover, at least on Oracle, you keep the compiled statement linked to the connection, so repetitive execution of same SQL statement is even quicker.

(see PreparedStatement if you are in Java/JDBC)

The only risk of counter-performance is when you keep too many idle connections in your pool, the associated ressources (your side and on database) are wasted.

回复 原文
巴黎夜雨 2024-08-17 04:02:51

查看 BoneCP (http://jolbox.com) 的基准部分中的一些数字。请记住，preparedStatements 等与连接相关联，因此如果您自己处理连接，则需要一次又一次地准备它们（连接池也会为您缓存这些连接）。

到目前为止我最好的解决方案：使用lazyDataSource，它只在您真正需要时才提供连接（即不是盲目地 - 如果数据可以来自缓存，那么您可以避免数据库命中）

Have a look at BoneCP (http://jolbox.com) in the benchmark section for some numbers. Remember that preparedStatements etc are tied to a connection so you'll need to prepare them again and again if you're dealing with connections yourself (a connection pool will cache those for you too).

My best solution so far: Use a lazyDataSource that only gives you a connection when you really need it (i.e. not blindly - if the data can come from a cache then you can avoid the database hit)

回复 原文
意中人 2024-08-17 04:02:51

使用tomcat创建数据库连接池

1. Tomcat 在里面输入资源：conf/context.xml

将资源条目放入 context.xml 文件中：

<!-- jdbc/jndiName jndi --> 
<Resource name="jdbc/jndiName" auth="Container" type="javax.sql.DataSource" initialSize="1" maxActive="100" maxIdle="30" maxWait="10000" username="enter username" password="enter password" driverClassName="diver name" url="jdbc database url"/>

2.创建一个将创建连接池的类

public class MyConnectionFactory {
    private static String module = "[ QuoteConnectionFactory ]";
    private static QuoteConnectionFactory connectionFactory;

    protected QuoteConnectionFactory() {
    }

    /**
     *
     * @return=>getInstance() is a static method which will return the instance
     *                        of its own class
     */
    public static QuoteConnectionFactory getInstance() {
        if (connectionFactory == null)
            connectionFactory = new QuoteConnectionFactory();
        return connectionFactory;
    }

    /**
     *
     * @param jndiName

     */
    public Connection getConnection(String jndiName) {
        System.out.println("jndiName=======" + jndiName);
        Connection conn = null;
        InitialContext cxt = null;
        DataSource dataSource = null;
        try {
            cxt = new InitialContext();
            Context envContext  = (Context)cxt.lookup("java:/comp/env");
            dataSource = (DataSource)envContext.lookup(jndiName);
        } catch (NamingException e) {

        } catch (Exception e) {

        }

        if (dataSource == null) {

            try {
                conn = dataSource.getConnection();
            } catch (Exception e) {

            }

            System.out.println("connection===================" + conn);
            return conn;
        }
    }

3.编辑web.xml文件

<resource-ref>
    <description>DB Connection</description>
    <res-ref-name>jdbc/jndiName</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

4。在代码中使用

Connection con= QuoteConnectionFactory.getInstance(). getConnection("jndiName");

Creating database connection pool using tomcat

1. Tomcat enter resource inside : conf/context.xml

Put the resource entries in context.xml file:

<!-- jdbc/jndiName jndi --> 
<Resource name="jdbc/jndiName" auth="Container" type="javax.sql.DataSource" initialSize="1" maxActive="100" maxIdle="30" maxWait="10000" username="enter username" password="enter password" driverClassName="diver name" url="jdbc database url"/>

2. create a class which will create the connection pool

public class MyConnectionFactory {
    private static String module = "[ QuoteConnectionFactory ]";
    private static QuoteConnectionFactory connectionFactory;

    protected QuoteConnectionFactory() {
    }

    /**
     *
     * @return=>getInstance() is a static method which will return the instance
     *                        of its own class
     */
    public static QuoteConnectionFactory getInstance() {
        if (connectionFactory == null)
            connectionFactory = new QuoteConnectionFactory();
        return connectionFactory;
    }

    /**
     *
     * @param jndiName

     */
    public Connection getConnection(String jndiName) {
        System.out.println("jndiName=======" + jndiName);
        Connection conn = null;
        InitialContext cxt = null;
        DataSource dataSource = null;
        try {
            cxt = new InitialContext();
            Context envContext  = (Context)cxt.lookup("java:/comp/env");
            dataSource = (DataSource)envContext.lookup(jndiName);
        } catch (NamingException e) {

        } catch (Exception e) {

        }

        if (dataSource == null) {

            try {
                conn = dataSource.getConnection();
            } catch (Exception e) {

            }

            System.out.println("connection===================" + conn);
            return conn;
        }
    }

3. edit web.xml file

<resource-ref>
    <description>DB Connection</description>
    <res-ref-name>jdbc/jndiName</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

4. Use in code

Connection con= QuoteConnectionFactory.getInstance(). getConnection("jndiName");
回复 原文
软的没边 2024-08-17 04:02:51

创建数据库连接可能是也可能不是一项昂贵的操作，具体取决于您的环境以及您打算用它来做什么。

如果您要运行一个非常简单的查询，那么连接可能需要与查询一样长（或更长）的时间。

有些数据库的连接开销比其他数据库大得多；如果调整正确，mysql 应该有很少的时间（高于建立 tcp 连接和进行协议握手的时间）。但是，如果您的服务器的延迟非常高，那么即使这也可能非常重要（特别是如果您打算只执行几个查询）。

如果您计划执行 100 个查询或一些非常慢的查询，那么连接时间就变得微不足道了。

一般来说，我会说每次打开一个新连接，直到您可以证明这是一个真正的性能问题。使用连接池可能会导致错误，这是我们不喜欢的：

  • 连接状态在池中上次使用后没有完全重置 - 因此某些状态会持续存在并产生意外行为，从而导致错误
  • 连接以某种方式关闭（也许）通过状态防火墙超时）无法检测到，因此应用程序尝试使用关闭的连接，导致长时间延迟或失败

Creating a database connection may or may not be an expensive operation, depending on your environment and what you intend to do with it.

If you're going to run a single very easy query, then connecting probably takes as long (or longer) than the query.

Some databases have a much bigger connection overhead than others; if tuned correctly, mysql should have very little (above the time to make a tcp connection and do the protocol handshake). However, if latency to your server is very high, even this can be quite significant (particularly if you intend to do only a few queries).

If you're planning to do, say, 100 queries, or a few really slow queries, then the connection time disappears into insignificance.

In generally I'd say open a new connection each time until you can demonstrate that it's a real performance problem. Using connection pooling can lead to BUGS, which we don't like:

  • Connection state wasn't COMPLETELY reset after the previous use in the pool - so some state lingers and creates unexpected behaviour resulting in a bug
  • Connection was closed in some way (perhaps by a stateful firewall timeout) which cannot be detected, therefore an app tries to use a closed connection, causing a long delay or failure
回复 原文
~没有更多了~

关于作者

年华零落成诗

暂无简介

0 文章
0 评论
23 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

1CH1MKgiKxn9p

文章 0 评论 0

ゞ记忆︶ㄣ

文章 0 评论 0

JackDx

文章 0 评论 0

信远

文章 0 评论 0

yaoduoduo1995

文章 0 评论 0

霞映澄塘

文章 0 评论 0

更多

友情链接

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