下面分别以Tomcat5.0和Tomcat5.5/6.0为例,介绍连接池在Tomcat中的配置和使用方式。
一、Tomcat5.0
A.将数据库的JDBC直联包放入TOMCAT_HOME/common/lib中
B.配置TOMCAT_HOME/conf/server.xml文件,在<Service><Engine>段中加入一个DefaultContext <DefaultContext> <Resource auth="Container" name="jdbc/achieve" type="javax.sql.DataSource"/> <ResourceParams name="jdbc/achieve"> <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://localhost:1433;databasename=pubs</value> </parameter> <parameter> <name>username</name> <value>sa</value> </parameter> <parameter> <name>password</name> <value></value> </parameter> <parameter> <name>maxActive</name> <value>30</value> </parameter> <parameter> <name>maxIdle</name> <value>10</value> </parameter> <parameter> <name>maxWait</name> <value>10000</value> </parameter> </ResourceParams> </DefaultContext>
C.在JSP或servlet中使用JNDI查找服务 <%@ page contentType = "text/html; charset=gb2312" %> <%@ page import = "java.sql.*" %> <%@ page import = "javax.naming.*" %> <%@ page import = "javax.sql.*" %> <html> <head><title>test</title></head> <body> <% Context ctx=null; DataSource ds=null; Connection conn=null; PreparedStatement stmt=null; ResultSet rs=null;
ctx = new InitialContext(); if( ctx == null ) out.println("no context"); ds = (DataSource) ctx.lookup("java:comp/env/jdbc/achieve"); if( ds == null ) out.println("no datasource"); conn = ds.getConnection(); stmt = conn.prepareStatement("select * from jobs"); rs = stmt.executeQuery(); while(rs.next()){ out.print(rs.getString(2)); out.print("<br/>"); } if( rs != null ) rs.close(); if( stmt != null ) stmt.close(); if( conn != null) conn.close(); if( ctx != null ) ctx.close();
%> </body></html>
D.常见问题
Q:出现javax.naming.NameNotFoundException: Name jdbc is not bound in this Context A: 在yourWebApp/WEB-INF/web.xml中增加 <resource-ref> <description>jdbc/achieve</description> <res-ref-name>jdbc/achieve</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref>
Q:出现org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class '?' for onnect URL 'null'
A: 在%TOMCAT_HOME%/conf/Catalina/localhost下找到你的web应用对应的.xml文件,如test.xml,并在此文件的下添入代码: <Context path="/sun" docBase="sun" debug="5" reloadable="true" crossContext="true"> <ResourceLink name="jdbc/achieve" global="jdbc/achieve" type="javax.sql.DataSource"/> </Context>
重启tomcat。
二、Tomcat5.5/6.0
A.将数据库的JDBC直联包放入TOMCAT_HOME/common/lib中,6.0在TOMCAT_HOME/lib
B.配置TOMCAT_HOME/conf/context.xml文件,在<context>段中加入 <Resource name="jdbc/achieve" auth="container" type="javax.sql.DataSource" maxActive="30" maxIdle="10" maxWait="10000" username="sa" password="" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" url="jdbc:sqlserver://localhost:1433;databasename=pubs"/>
C.(可以不做这一步)在工程的WEB-INF/web.xml中增加 <resource-ref> <description>jdbc/achieve</description> <res-ref-name>jdbc/achieve</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> </resource-ref>
D.在JSP或servlet中使用JNDI查找服务 <%@ page contentType = "text/html" pageEncoding="utf-8" %> <%@ page import = "java.sql.*" %> <%@ page import = "javax.naming.*" %> <%@ page import = "javax.sql.*" %> <html> <head><title>test</title></head> <body> <% Context ctx=null; DataSource ds=null; Connection conn=null; PreparedStatement stmt=null; ResultSet rs=null;
ctx = new InitialContext(); if( ctx == null ) out.println("no context"); ds = (DataSource) ctx.lookup("java:comp/env/jdbc/achieve"); if( ds == null ) out.println("no datasource"); conn = ds.getConnection(); stmt = conn.prepareStatement("select * from jobs"); rs = stmt.executeQuery(); while(rs.next()){ out.print(rs.getString(2)); out.print("<br/>"); } if( rs != null ) rs.close(); if( stmt != null ) stmt.close(); if( conn != null) conn.close(); if( ctx != null ) ctx.close();
%> </body>
</html>