数据库连接池

⑴tomcat中的连接池

以tomcat 6为例。首先在项目根目录下新建一个文件夹META-INF。在这个文件夹中新建一个文件context.xml。

<?xml version="1.0" encoding="UTF-8"?> <Context reloadable="true" crossContext="true">  <!-- Default set of monitored resources -->  <WatchedResource>WEB-INF/web.xml</WatchedResource>  <!-- Uncomment this to disable session persistence across Tomcat restarts -->  <!--   <Manager pathname="" />  -->  <Resource  name="jdbc/test"  auth="Container"  type="javax.sql.DataSource"  driverClassName="oracle.jdbc.driver.OracleDriver"  url="jdbc:oracle:thin:@localhost:1521:TESTDB"  username="imti"  password="123456"  maxActive="20"  maxIdle="10"  maxWait="-1"/> </Context>

 

使用连接池，注意在JNDI中加上：java:comp/env/

<%@ page contentType="text/html;charset=gb2312"%> <%@ page import="java.sql.*"%> <%@ page import="javax.sql.*"%> <%@ page import="javax.naming.*"%> <%        final String JNDINAME = "java:comp/env/jdbc/test" ;        Connection conn = null ;        Statement stmt = null ;        ResultSet rs = null ;        String sql = null ; %> <%        try        {               // 初始化查找命名空间               Context ctx = new InitialContext() ;               // 找到DataSource               DataSource ds = (DataSource)ctx.lookup(JNDINAME) ;               conn = ds.getConnection() ;               stmt = conn.createStatement() ;               sql = "SELECT sno,sname,ssex,sage,sdept FROM student" ;               rs = stmt.executeQuery(sql) ; %> <table border="1" width="80%">        <tr>               <td>学号</td>               <td>姓名</td>               <td>性别</td>               <td>年龄</td>               <td>院系</td>        </tr> <%               while(rs.next())               {             String no = rs. getString (1) ;                      String name = rs.getString(2) ;                      String sex = rs.getString(3) ;                      int age = rs.getInt(4) ;             String dept = rs.getString(5) ;   %>                      <tr>                             <td><%=no%></td>                             <td><%=name%></td>                             <td><%=sex%></td>                             <td><%=age%></td>                             <td><%=dept%></td>                      </tr> <%               } %>        </table>       <%               rs.close() ;               stmt.close() ;               conn.close() ;        }        catch(Exception e)        {               out.println(e) ;        } %>

 

⑵proxool连接池

一个开源工具，性能很好的数据库连接池，被广泛的使用。

首先将它的两个.jar文件放到项目的WEB-INF/lib文件夹下。然后编写proxool.xml文件，并将其放置在WEB-INF文件夹下。

<?xml version="1.0" encoding="ISO-8859-1"?> <!-- the proxool configuration can be embedded within your own application's.Anything outside the "proxool" tag is ignored. --> <something-else-entirely>  <proxool>   <alias>Oracle10g</alias>   <driver-url>jdbc:oracle:thin:@localhost:1521:TESTDB</driver-url>   <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>   <driver-properties>    <property name="user" value="imti" />    <property name="password" value="123456" />   </driver-properties>   <maximum-connection-count>10</maximum-connection-count>   <house-keeping-test-sql>select CURRENT_DATE</house-keeping-test-sql>  </proxool> </something-else-entirely>

注意：红色标记部分将来作为程序中使用的连接字符串。

 

然后，需要配置web.xml文件

<servlet>   <servlet-name>ServletConfigurator</servlet-name>   <servlet-class>org.logicalcobwebs.proxool.configuration.ServletConfigurator</servlet-class>   <init-param>    <param-name>xmlFile</param-name>    <param-value>WEB-INF/proxool.xml</param-value>   </init-param>   <load-on-startup>1</load-on-startup>  </servlet>    <servlet>   <servlet-name>proxool</servlet-name>   <servlet-class>org.logicalcobwebs.proxool.admin.servlet.AdminServlet</servlet-class>  </servlet>   <servlet-mapping>   <servlet-name>proxool</servlet-name>   <url-pattern>/proxool</url-pattern>  </servlet-mapping>

当有数据库连接时，可以通过/proxool映射，来观察数据库连接情况。

 

使用范例：

<%@ page contentType="text/html;charset=gb2312"%> <%@ page import="java.sql.*"%> <%        Connection conn = null ;        Statement stmt = null ;        ResultSet rs = null ;        String sql = null ; %> <%        try        {               conn = DriverManager.getConnection("proxool.Oracle10g") ;               stmt = conn.createStatement() ;               sql = "SELECT sno,sname,ssex,sage,sdept FROM student" ;               rs = stmt.executeQuery(sql) ; %> <table border="1" width="80%">        <tr>               <td>学号</td>               <td>姓名</td>               <td>性别</td>               <td>年龄</td>               <td>院系</td>        </tr> <%               while(rs.next())               {             String no = rs. getString (1) ;                      String name = rs.getString(2) ;                      String sex = rs.getString(3) ;                      int age = rs.getInt(4) ;             String dept = rs.getString(5) ; %>                      <tr>                             <td><%=no%></td>                             <td><%=name%></td>                             <td><%=sex%></td>                             <td><%=age%></td>                             <td><%=dept%></td>                      </tr> <%               } %>        </table>       <%               rs.close() ;               stmt.close() ;               conn.close() ;        }        catch(Exception e)        {               out.println(e) ;        } %>