如何建立tomcat的连接池
王俊彧 2005-1-23
ORALCE
1、JDBC 驱动
首先为 Tomcat 增加 Oracle 的 JDBC 驱动，Oracle 8.1.7 自带的 JDBC 驱动是 classes12.jar 和 nls_charset12.jar（不必添加classpath）。
他们的位置在oracle\jdbc\lib下
2、配置文件
把 Tomcat 的配置文件 server.xml 中的相应上下文修改为（以数据库192.168.0.1:1521:iwjp为例）
<Context path="/test" docBase="test" debug="0" reloadable="true">
<Resource name="jdbc/testpool" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/testpool">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>oracle.jdbc.driver.OracleDriver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:oracle:thin:@192.168.0.1:1521:iwjp</value>
</parameter>
<parameter>
<name>username</name>
<value>ecydeen2</value>
</parameter>
<parameter>
<name>password</name>
<value>ecydeen2</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>10</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>10</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>-1</value>
</parameter>
</ResourceParams>
</Context>
红色字体表示要修改的部分
3、测试程序
执行一个 SQL 语句的演示
以下是这个测试 JSP 文件的核心内容
<%@ page contentType="text/html; charset=GBK"%>
<%@ page import="java.sql.*,javax.naming.*"%>
<%
try {
Context initCtx = new InitialContext();
Context ctx = (Context) initCtx.lookup("java:comp/env");
Object obj = (Object) ctx.lookup("jdbc/testpool");
javax.sql.DataSource ds = (javax.sql.DataSource)obj;
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
String strSQL = "SELECT * FROM tab";
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()){
out.println(rs.getString(1));
out.println("<br>");
}
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
...
</html>
<%
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
throw e;
}
%>
SQLSERVER
1、JDBC驱动
从http://www.microsoft.com/downloads/details.aspx?FamilyID=4f8f2f01-1ed7-4c4d-8f7b-3d47969e66ae&DisplayLang=en#filelist下载"Microsoft SQL Server 2000 Driver for JDBC",并安装,得到msbase.jar,mssqlserver.jar和msutil.jar三个文件,将三个文件COPY到TOMCAT 4.1下common\lib文件夹中
1、配置文件
将SERVER.XML文件的context部分用以下部分替代：
<Context path="/myapp" docBase="myapp" debug="0" reloadable="true">
<Resource name="jdbc/SqlServerDB" auth="Container" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/SqlServerDB">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
</parameter>
<parameter>
<name>url</name> <value>jdbc:microsoft:sqlserver://192.168.1.27:1433;databaseName=pubs</value>
</parameter>
<parameter>
<name>username</name>
<value>sa</value>
</parameter>
<parameter>
<name>password</name>
<value>sa</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>20</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>20</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>-1</value>
</parameter>
</ResourceParams>
</Context>
web.xml下增加以下内容：
<resource-ref>
<description>SqlServer Datasource example</description>
<res-ref-name>jdbc/SqlServerDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
2、 测试程序
<%@ page contentType="text/html;charset=GBK"%>
<%@ page import= "java.sql.* "%>
<%@ page import= "javax.naming.* "%>
<%
try{
Context initCtx = new InitialContext();
Context ctx = (Context) initCtx.lookup("java:comp/env");
//获取连接池对象
Object obj = (Object) ctx.lookup("jdbc/SqlServerDB");
//类型转换
javax.sql.DataSource ds = (javax.sql.DataSource)obj;
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
String strSql = " insert into test(id,name) values('00001','fany')";
stmt.executeUpdate(strSql);
strSql = " select id,name from test ";
ResultSet rs = stmt.executeQuery(strSql);
if(rs.next()){
out.println(rs.getString(1));
out.println(rs.getString(2));
}
}catch(Exception ex){
ex.printStackTrace();
throw new SQLException("cannot get Connection pool.");
}
%>
<h1>ok</h1>
3、 说明
1、 红色字体表示根据具体情况需要修改的。/myapp是程序所在的目录，在webapp
Mysql
修改CATALINA_HOME\conf\server.xml
<!-- Tomcat Root Context -->
<Context path="/myapp" docBase="myapp" debug="0">
<Logger className="org.apache.catalina.logger.FileLogger"
prefix="localhost_DBTest_log." suffix=".txt"
timestamp="true"/>
<Resource name="jdbc/MysqlDB"
auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/MysqlDB">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<!-- Maximum number of dB connections in pool. Make sure you
configure your mysqld max_connections large enough to handle
all of your db connections. Set to 0 for no limit.
-->
<parameter>
<name>maxActive</name>
<value>100</value>
</parameter>
<!-- Maximum number of idle dB connections to retain in pool.
Set to 0 for no limit.
-->
<parameter>
<name>maxIdle</name>
<value>30</value>
</parameter>
<!-- Maximum time to wait for a dB connection to become available
in ms, in this example 10 seconds. An Exception is thrown if
this timeout is exceeded. Set to -1 to wait indefinitely.
-->
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
<!-- MySQL dB username and password for dB connections -->
<parameter>
<name>username</name>
<value>sa</value>
</parameter>
<parameter>
<name>password</name>
<value>111111</value>
</parameter>
<!-- Class name for mm.mysql JDBC driver -->
<parameter>
<name>driverClassName</name>
<value>org.gjt.mm.mysql.Driver</value>
</parameter>
<!-- The JDBC connection url for connecting to your MySQL dB.
The autoReconnect=true argument to the url makes sure that the
mm.mysql JDBC Driver will automatically reconnect if mysqld closed the
connection. mysqld by default closes idle connections after 8 hours.
-->
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost:3306/test?useUnicode=true&characterEnco ding=GBK</value>
<!--Here must use & not use & -->
</parameter>
</ResourceParams>
</Context>
2.修改webapps/ROOT/INF-WEB/web.xml
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE web-app PUBLIC
"-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
"http://java.sun.com/dtd/web-app_2_3.dtd">
<web-app>
<description>MySQL Test App</description>
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/MysqlDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
3.测试用的testdb.jsp内容:
<%@ page contentType="text/html;charset=GBK"%>
<%@ page import= "java.sql.* " %>
<%@ page import= "javax.naming.* "%>
<%
try{
Context initCtx = new InitialContext();
Context ctx = (Context) initCtx.lookup("java:comp/env");
//获取连接池对象
Object obj = (Object) ctx.lookup("jdbc/MysqlDB");
//类型转换
javax.sql.DataSource ds = (javax.sql.DataSource)obj;
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
String strSql = " insert into test(id,name) values('007','hello,中国!') ";
stmt.executeUpdate(strSql);
strSql = " select id,name from test ";
ResultSet rs = stmt.executeQuery(strSql);
while(rs.next()){
out.println(rs.getString(1));
out.println(rs.getString(2)+"<BR>");
}
conn.close();
}catch(Exception ex){
ex.printStackTrace();
throw new SQLException("cannot get Connection pool.");
}
%>
<hr>