Tomcat数据库连接池(JSP+MS Sql Server 2005)
1. 在conf/server.xml中参考配置如下代码<Host name="localhost"appBase="webapps"
unpackWARs="true" autoDeploy="true"
xmlValidation="false" xmlNamespaceAware="false">
<Context path="/mywebtest" source="D:\googlecode\java\myweb" reloadable="true" debug="0">
<Resource auth="Container"
maxActive="10" maxIdle="10" maxWait="-1"
name="jdbc/DBPool"
type="javax.sql.DataSource"
driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
url="jdbc:sqlserver://localhost:1433;DatabaseName=webtestdb"
username="sa"
password="*****"
removeAbondoned="true"
/>
</Context>
</Host>
2. 在web工程中的web.xml中设置数据源参数，参考配置如下：
<resource-ref>
<description>Mysql Datasource example</description>
<res-ref-name>jdbc/DBPool</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Unshareable</res-sharing-scope>
</resource-ref>
3. 创建DBPool类，参照代码如下：
package com.fbin.jdbc;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class DBPool {
private static DataSource pool;
static{
Context env = null;
try{
env = (Context)new InitialContext().lookup("java:comp/env");
pool = (DataSource)env.lookup("jdbc/DBPool");
if(pool == null){
System.out.println("DBPool is an unknown DataSource");
}
}catch(NamingException ne){
ne.printStackTrace();
}
}
public static DataSource getPool(){
return pool;
}
}
最好用单例模式创建
4.在jsp中引用，参考代码如下：
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@page import="com.fbin.jdbc.DBPool"%>
<%@page import="java.sql.*" %>
<html>
<body>
<h2>Hello fanbin!</h2>
<%
java.sql.Connection conn;
java.lang.String strConn;
try{
/*MS sql server 2005*/
//Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
//conn= java.sql.DriverManager.getConnection("jdbc:sqlserver://localhost:1433;DatabaseName=webtestdb","sa","***");
/*MySql*/
//Class.forName("com.mysql.jdbc.Driver").newInstance();
//conn= java.sql.DriverManager.getConnection("jdbc:mysql://localhost:3306/webtestdb","root","****");
/*数据源获取*/
conn = DBPool.getPool().getConnection();
%>
连接MS SQL server 数据库成功!
<%
Statement stmt=conn.createStatement();
ResultSet rs = null;
rs = stmt.executeQuery("SELECT * FROM .");
while(rs.next()){
out.println(rs.getString("name")+":"+ rs.getString(2) +":"+rs.getInt("age"));
}
} catch (java.sql.SQLException e){
out.println(e.toString());
}
%>
</body>
</html>
页:
[1]