事实上，当某一线程需要用JDBC在MySQL或其他数据库上执行操作时，需要用到由连接池提供的连接。使用连接完成线程后，线程会将连接返回给连接池，以便该连接能够被其他需要使用连接的线程使用。
从连接池“借出”连接时，该连接仅供请求它的线程使用。从编程观点看，其效果等同于每次需要JDBC连接时调用DriverManager.getConnection()，但是，采用连接池技术，可通过使用新的或已有的连接结束线程。
连接池技术能显著增加Java应用程序的性能，同时还能降低资源使用率。连接池技术的主要优点包括：
· 缩短了连接创建时间
与其他数据库相比，MySQL提供了快速的连接设置功能，连接时间通常不是问题，但创建新的JDBC连接仍会导致联网操作和一定的IDBC驱动开销，如果这类连接是“循环”使用的，使用该方式，可避免这类不利因素。
· 简化的编程模型
使用连接池技术时，每个单独线程能够像创建了自己的JDBC连接那样进行操作，从而允许使用直接的JDBC编程技术。
· 受控的资源使用
如果不使用连接池技术，而是在每次需要时为线程创建新的连接，那么应用程序的资源使用将十分浪费，而且在负载较重的情况下会导致无法预期的结果。
注意，与MySQL的每个连接均会在客户端和服务器端造成一定的开销（每寸、CPU、关联转换等）。每个连接均会对应用程序和MySQL服务器的可用资源带来一定的限制。无论连接是否执行任何有用的任务，仍将使用这些资源中的相当一部分。
连接池能够使性能最大化，同时还能将资源利用控制在一定的水平之下，如果超过该水平，应用程序将崩溃而不仅仅是变慢。
幸运的是，Sun公司通过JDBC-2.0“可选”接口，完成了JDBC中连接池概念的标准化实施，所有主要应用服务器均实施了能够与MySQL Connector/J一起良好工作的这类API。
通常，你可以在应用服务器的配置文件中配置连接池，并通过Java命名和目录接口（JNDI）访问它。在下面的代码中，介绍了在J2E应用服务器上运行的应用程序中使用连接池的方法：
示例26.12. 与J2EE应用服务器一起使用连接池
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class MyServletJspOrEjb {
public void doSomething() throws Exception {
/*
* Create a JNDI Initial context to be able to
* lookup the DataSource
*
* In production-level code, this should be cached as
* an instance or static variable, as it can
* be quite expensive to create a JNDI context.
*
* Note: This code only works when you are using servlets
* or EJBs in a J2EE application server. If you are
* using connection pooling in standalone Java code, you
* will have to create/configure datasources using whatever
* mechanisms your particular connection pooling library
* provides.
*/
InitialContext ctx = new InitialContext();
/*
* Lookup the DataSource, which will be backed by a pool
* that the application server provides. DataSource instances
* are also a good candidate for caching as an instance
* variable, as JNDI lookups can be expensive as well.
*/
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/MySQLDB");
/*
* The following code is what would actually be in your
* Servlet, JSP or EJB 'service' method...where you need
* to work with a JDBC connection.
*/
Connection conn = null;
Statement stmt = null;
try {
conn = ds.getConnection();
/*
* Now, use normal JDBC programming to work with
* MySQL, making sure to close each resource when you're
* finished with it, which allows the connection pool
* resources to be recovered as quickly as possible
*/
stmt = conn.createStatement();
stmt.execute("SOME SQL QUERY");
stmt.close();
stmt = null;
conn.close();
conn = null;
} finally {
/*
* close any jdbc instances here that weren't
* explicitly closed during normal code path, so
* that we don't 'leak' resources...
*/
if (stmt != null) {
try {
stmt.close();
} catch (sqlexception sqlex) {
// ignore -- as we can't do anything about it here
}
stmt = null;
}
if (conn != null) {
try {
conn.close();
} catch (sqlexception sqlex) {
// ignore -- as we can't do anything about it here
}
conn = null;
}
}
}
}
如上例所示，获得JNDI InitialContext并查找到数据库后，其余代码与过去在JDBC编程中使用的类似。
使用连接池时需要牢记的最重要事项是，无论在代码中出现了什么（异常、控制流等），连接以及由连接创建的任何部分（语句、结果集等）均应被关闭，以便能再次使用它们。如不然，它们将纠缠在一起，在最好的情况下，意味着它们所代表的MySQL服务器资源（缓冲区、锁定、套接字等）可能会捆绑一段时间，在最坏的情况下，可能会导致永久捆绑。
连接池的最佳大小是什么？
与所有其他配置经验规则一样，回答是“它取决于具体情况”。尽管最佳大小取决与预期的负载和平均的数据库事务时间，最佳的连接池大小小于你的预期。例如，如果使用的是Sun公司的Java Petstore Blueprint应用程序，对于包含15～20个连接的连接池，使用MySQL和Tomcat，在可接受的相应时间下，可服务于中等程度的负载（600个并发用户）。
要想确定用于应用程序的连接池大小，应使用诸如Apache Jmeter或The Grinder等工具创建负载测试脚本，并对应用程序进行负载测试。
确定出发点的一种简单方法是，将连接池的最大连接数配置为“无限”，运行负载测试，并测量最大的并发连接数。随后，应进行反向操作，确定出使应用程序具有最佳性能的连接池的最小和最大值。
下述内容基于关于Tomcat-5.x的指示说明，http://jakarta.apache.org/tomcat/tomcat-5.0-doc/jndi-datasource-examples-howto.html，在编写本文档时它是最新的。
首先安装与Connector/J in $CATALINA_HOME/common/lib一起提供的.jar文件，以便它能用于已安装的所有应用程序。
其次，在定义Web应用程序的Context（场景）内，通过为$CATALINA_HOME/conf/server.xml增加声明资源，配置JNDI DataSource：
<Context ....>
...
<Resource name="jdbc/MySQLDB"
auth="Container"
type="javax.sql.DataSource"/>
<!-- The name you used above, must match _exactly_ here!
The connection pool will be bound into JNDI with the name
"java:/comp/env/jdbc/MySQLDB"
-->
<ResourceParams name="jdbc/MySQLDB">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<!-- Don't set this any higher than max_connections on your
MySQL server, usually this should be a 10 or a few 10's
of connections, not hundreds or thousands -->
<parameter>
<name>maxActive</name>
<value>10</value>
</parameter>
<!-- You don't want to many idle connections hanging around
if you can avoid it, only enough to soak up a spike in
the load -->
<parameter>
<name>maxIdle</name>
<value>5</value>
</parameter>
<!-- Don't use autoReconnect=true, it's going away eventually
and it's a crutch for older connection pools that couldn't
test connections. You need to decide if your application is
supposed to deal with SQLExceptions (hint, it should), and
how much of a performance penalty you're willing to pay
to ensure 'freshness' of the connection -->
<parameter>
<name>validationQuery</name>
<value>SELECT 1</value>
</parameter>
<!-- The most conservative approach is to test connections
before they're given to your application. For most applications
this is okay, the query used above is very small and takes
no real server resources to process, other than the time used
to traverse the network.
If you have a high-load application you'll need to rely on
something else. -->
<parameter>
<name>testOnBorrow</name>
<value>true</value>
</parameter>
<!-- Otherwise, or in addition to testOnBorrow, you can test
while connections are sitting idle -->
<parameter>
<name>testWhileIdle</name>
<value>true</value>
</parameter>
<!-- You have to set this value, otherwise even though
you've asked connections to be tested while idle,
the idle evicter thread will never run -->
<parameter>
<name>timeBetweenEvictionRunsMillis</name>
<value>10000</value>
</parameter>
<!-- Don't allow connections to hang out idle too long,
never longer than what wait_timeout is set to on the
server...A few minutes or even fraction of a minute
is sometimes okay here, it depends on your application
and how much spikey load it will see -->
<parameter>
<name>minEvictableIdleTimeMillis</name>
<value>60000</value>
</parameter>
<!-- Username and password used when connecting to MySQL -->
<parameter>
<name>username</name>
<value>someuser</value>
</parameter>
<parameter>
<name>password</name>
<value>somepass</value>
</parameter>
<!-- Class name for the Connector/J driver -->
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<!-- The JDBC connection url for connecting to MySQL, notice
that if you want to pass any other MySQL-specific parameters
you should pass them here in the URL, setting them using the
parameter tags above will have no effect, you will also
need to use & to separate parameter values as the
ampersand is a reserved character in XML -->
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost:3306/test</value>
</parameter>
</ResourceParams>
</Context>
一般而言，应遵循关于相应Tomcat版本的安装说明，这是因为，在Tomcat中配置数据源的方式会随时变化，很不幸，如果在XML文件中使用了错误的语法，很可能会以异常结束，如下所示：
Error: java.sql.SQLException: Cannot load JDBC driver class 'null ' SQL state: null
下述说明适用于JBoss-4.x。要想使应用服务器能够使用JDBC驱动类，请将与Connector/J一起提供的.jar文件拷贝到用于服务器配置的lib目录下（通常称为“默认”）。随后，在相同的配置目录下，在名为“deploy”的子目录下，创建以“-ds.xml”结尾的数据源配置文件，用于通知Jboss将该文件当作JDBC数据源。该文件应包含下述内容：
<datasources>
<local-tx-datasource>
<!-- This connection pool will be bound into JNDI with the name
"java:/MySQLDB" -->
<jndi-name>MySQLDB</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/dbname</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>user</user-name>
<password>pass</password>
<min-pool-size>5</min-pool-size>
<!-- Don't set this any higher than max_connections on your
MySQL server, usually this should be a 10 or a few 10's
of connections, not hundreds or thousands -->
<max-pool-size>20</max-pool-size>
<!-- Don't allow connections to hang out idle too long,
never longer than what wait_timeout is set to on the
server...A few minutes is usually okay here,
it depends on your application
and how much spikey load it will see -->
<idle-timeout-minutes>5</idle-timeout-minutes>
<!-- If you're using Connector/J 3.1.8 or newer, you can use
our implementation of these to increase the robustness
of the connection pool. -->
<exception-sorter-class-name>com.mysql.jdbc.integration.jboss.ExtendedMysqlExceptionSorter</exception-sorter-class-name>
<valid-connection-checker-class-name>com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker</valid-connection-checker-class-name>
</local-tx-datasource>
</datasources>