关于连接池的配置

下面分别以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>