tomcat6中连接池的使用

1、新建一个web工程，在tomcat的server.xml中，将应用和连接池绑定。

<?xml version="1.0" encoding="UTF-8"?>
 <Host appBase="webapps" autoDeploy="true" name="localhost"
 	unpackWARs="true" xmlNamespaceAware="false" xmlValidation="false">
 	<Context docBase="connectionPoolTest" path="/connectionPoolTest"
 		reloadable="false"
 		source="org.eclipse.jst.jee.server:connectionPoolTest">
 		<Resource driverClassName="oracle.jdbc.driver.OracleDriver"
 			maxActive="100" maxIdle="100" maxWait="5000" name="testPool"
 			password="oracle" type="javax.sql.DataSource"
 			url="jdbc:oracle:thin:@localhost:1521:orcl" username="system" />
 	</Context>
 </Host>

2、创建2个测试连接的类。

package connection;
 
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.sql.Statement;
 
 import javax.naming.InitialContext;
 import javax.naming.NamingException;
 import javax.sql.DataSource;
 import javax.sql.rowset.CachedRowSet;
 
 import com.sun.rowset.CachedRowSetImpl;
 
 public class DBUtil {
 	/**
 	 * 根据数据源名称获取数据源
 	 * 
 	 * @param dsName
 	 * @return
 	 * @throws NamingException
 	 */
 	public static DataSource getDataSource(String dsName) {
 		DataSource ds = null;
 		try {
 			ds = (DataSource) new InitialContext().lookup("java:comp/env/"
 					+ dsName);
 			System.out.println("tomcat datasource");
 		} catch (NamingException e) {
 			try {
 				System.out.println("weblogic datasource");
 				ds = (DataSource) new InitialContext().lookup(dsName);
 			} catch (NamingException e1) {
 				e1.printStackTrace();
 			}
 		}
 		return ds;
 	}
 
 	/**
 	 * 获取一个默认的数据连接
 	 * 
 	 * @return
 	 * @throws SQLException
 	 * @throws NamingException
 	 */
 	public static Connection getDefaultConnection() throws SQLException,
 			NamingException {
 		return DBUtil.getDataSource("HXPT").getConnection();
 	}
 
 	/**
 	 * 简单查询获取数据集,不包含条件。
 	 * 
 	 * @param sql
 	 * @return
 	 * @throws SQLException
 	 * @throws NamingException
 	 */
 	public static CachedRowSet execSimpleQuery(String sql) throws SQLException,
 			NamingException {
 		CachedRowSet rs = new CachedRowSetImpl();
 		Connection conn = getDefaultConnection();
 		Statement stmt = conn.createStatement();
 		ResultSet resultset = stmt.executeQuery(sql);
 		rs.populate(resultset);
 		resultset.close();
 		stmt.close();
 		conn.close();
 		return rs;
 	}
 
 	/**
 	 * 查询获取数据集,包含条件。
 	 * 
 	 * @param sql
 	 * @return
 	 * @throws SQLException
 	 * @throws NamingException
 	 */
 	public static CachedRowSet execQuery(String sql, Object[] params)
 			throws SQLException, NamingException {
 		CachedRowSet rs = new CachedRowSetImpl();
 		Connection conn = getDefaultConnection();
 		PreparedStatement psmt = conn.prepareStatement(sql);
 		for (int i = 0; i < params.length; i++) {
 			psmt.setObject(i + 1, params[i]);
 		}
 		ResultSet resultset = psmt.executeQuery();
 		rs.populate(resultset);
 		resultset.close();
 		psmt.close();
 		conn.close();
 		return rs;
 	}
 
 	/**
 	 * 执行DML语句。
 	 * 
 	 * @param sql
 	 * @return
 	 * @throws NamingException
 	 * @throws SQLException
 	 * @throws SQLException
 	 * @throws NamingException
 	 */
 	public static void execUpdate(String sql, Object[] params)
 			throws SQLException, NamingException {
 		Connection conn = getDefaultConnection();
 		PreparedStatement psmt = conn.prepareStatement(sql);
 		for (int i = 0; i < params.length; i++) {
 			psmt.setObject(i + 1, params[i]);
 		}
 		psmt.executeUpdate();
 		psmt.close();
 		conn.close();
 	}
 }
package connection;
 
 import java.sql.Connection;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.Date;
 import java.text.SimpleDateFormat;
 import javax.naming.NamingException;
 import javax.sql.rowset.CachedRowSet;
 
 public class TestConnection {
 	public String welcome() throws NamingException, SQLException {
 		// 获取连接
 		Date result = null;
 		SimpleDateFormat sdt = null;
 		Connection conn = DBUtil.getDefaultConnection();
 		ResultSet rs = conn.createStatement().executeQuery(
 				"select sysdate from dual");
 		while (rs.next()) {
 			result = rs.getTimestamp("sysdate");
 			sdt = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
 			result = new Date(result.getTime());
 		}
 		// 测试连接是否成功
 		return "当前时间：" + "</br>" + sdt.format(result) + "</br>";
 	}
 
 	public String testRs() throws SQLException, NamingException {
 		StringBuffer sb = new StringBuffer("tableList:").append("</br>");
 		CachedRowSet rs = DBUtil
 				.execQuery(
 						"select table_name from user_tables where table_name like ? and rownum<?",
 						new Object[] { "%", 100 });
 		while (rs.next()) {
 			sb.append(rs.getString("table_name")).append("</br>");
 		}
 		return sb.toString();
 	}
 }
 

3、新建一个测试连接的jsp文件。

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 <%@page import="connection.TestConnection;"%>
 <%@ page language="java" contentType="text/html; charset=utf-8"
 	pageEncoding="utf-8"%>
 <html>
 <head>
 <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
 <title>welcome</title>
 </head>
 <body>
 <%
 	TestConnection tc = new TestConnection();
 	out.print(tc.welcome());
 	out.print(tc.testRs());
 %>
 </body>
 </html>

4、将ojdbc14.jar放入该工程的\WEB-INF\lib中，启动应用。