经过归纳得出J2EE sqlserver 连接数据库的三种配置：
一、连接池方式：
1、连接迟3个包+sqlserver驱动包复制到tomcat/common/lib
2、配置tomcat/conf/context.xml，注意2000和2005 驱动名字和路径
[xhtml] view plaincopyprint?
<Resource name=”jdbc/pubs”
auth=”Container” type=”javax.sql.DataSource” maxActive=”100″
maxIdle=”30″ maxWait=”10000″ username=”sa” password=”120010″
driverClassName=”com.microsoft.jdbc.sqlserver.SQLServerDriver”
url=”jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=books”/>
[xhtml] view plaincopyprint?
<Resource name=”jdbc/pubs”
auth=”Container” type=”javax.sql.DataSource” maxActive=”100″
maxIdle=”30″ maxWait=”10000″ username=”sa” password=”120010″
driverClassName=”com.microsoft.jdbc.sqlserver.SQLServerDriver”
url=”jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=books”/>
[java] view plaincopyprint?
//2000：
driverClassName=”com.microsoft.jdbc.sqlserver.SQLServerDriver”
url=”jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=books”/>
//2005：
driverClassName=”com.microsoft.sqlserver.jdbc.SQLServerDriver”
url=”jdbc:sqlserver://localhost:1433;DatabaseName=books”/>
[java] view plaincopyprint?
//2000：
driverClassName=”com.microsoft.jdbc.sqlserver.SQLServerDriver”
url=”jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=books”/>
//2005：
driverClassName=”com.microsoft.sqlserver.jdbc.SQLServerDriver”
url=”jdbc:sqlserver://localhost:1433;DatabaseName=books”/>
3、在工程web.xml添加节点
[c-sharp] view plaincopyprint?
<resource-ref>
<res-ref-name>jdbc/pubs</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
[c-sharp] view plaincopyprint?
<resource-ref>
<res-ref-name>jdbc/pubs</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
4、得到连接的方法内导如以下几个包：
[c-sharp] view plaincopyprint?
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
// 得到Connection对象的方法
public static Connection getConnection(){
try {
Context ic = new InitialContext();
DataSource source = (DataSource)ic.lookup
(“java:comp/env/jdbc/bbs”);
con = source.getConnection();
}catch(NamingException ex){
ex.printStackTrace();
}catch(SQLException ex){
ex.printStackTrace();
}
return con;
}
[c-sharp] view plaincopyprint?
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
// 得到Connection对象的方法
public static Connection getConnection(){
try {
Context ic = new InitialContext();
DataSource source = (DataSource)ic.lookup
(“java:comp/env/jdbc/bbs”);
con = source.getConnection();
}catch(NamingException ex){
ex.printStackTrace();
}catch(SQLException ex){
ex.printStackTrace();
}
return con;
}
================================================================================
二、读取属性文件方式
[java] view plaincopyprint?
*.properties文件
driverName=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://localhost:1433;DatabaseName=books
user=sa
password=123
//读取*.properties文件的类
import java.io.InputStream;
import java.util.Properties;
public final class Env extends Properties {
private static Env instance;
public static Env getInstance(){
if(instance != null){
return instance;
}else{
makeInstance();
return instance;
}
}
//synchronized 同步方法,保证同一时间只能被一个用户调用
private static synchronized void makeInstance(){
if(instance == null){
instance = new Env();
}
}
private Env(){
InputStream is =getClass().getResourceAsStream(“db.properties”);//配置文件位置
try{
load(is);
}catch(Exception ex){
System.err.println(“请确认读取的文件是否存在!”);
}
}
//测试连接是否成功方法方法
public static void main(String[] args) {
System.out.println(getInstance().getProperty(“driverName”));
}
}
[java] view plaincopyprint?
*.properties文件
driverName=com.microsoft.sqlserver.jdbc.SQLServerDriver
url=jdbc:sqlserver://localhost:1433;DatabaseName=books
user=sa
password=123
//读取*.properties文件的类
import java.io.InputStream;
import java.util.Properties;
public final class Env extends Properties {
private static Env instance;
public static Env getInstance(){
if(instance != null){
return instance;
}else{
makeInstance();
return instance;
}
}
//synchronized 同步方法,保证同一时间只能被一个用户调用
private static synchronized void makeInstance(){
if(instance == null){
instance = new Env();
}
}
private Env(){
InputStream is =getClass().getResourceAsStream(“db.properties”);//配置文件位置
try{
load(is);
}catch(Exception ex){
System.err.println(“请确认读取的文件是否存在!”);
}
}
//测试连接是否成功方法方法
public static void main(String[] args) {
System.out.println(getInstance().getProperty(“driverName”));
}
}
注意类的调用：譬如String url = Env.getInstance().getProperties(“url”);就能得到相应
的字符窜
驱动driverName，用户user, 密码password获取方式同上
==========================================================
三、读取xml文件中的节点
首先报连接池包3个和1个数据库驱动包复制到工程下WEB-INF/lib中
1、工程下的web.xml要添加以下节点：
[xhtml] view plaincopyprint?
<context-param>
<param-name>driverName</param-name>
<param-value>com.microsoft.sqlserver.jdbc.SQLServerDriver</param-value>
</context-param>
<context-param>
<param-name>url</param-name>
<param-value>jdbc:sqlserver://localhost:1433;DatabaseName=bbs</param-value>
</context-param>
<context-param>
<param-name>userName</param-name>
<param-value>sa</param-value>
</context-param>
<context-param>
<param-name>passWord</param-name>
<param-value>123</param-value>
</context-param>
[xhtml] view plaincopyprint?
<context-param>
<param-name>driverName</param-name>
<param-value>com.microsoft.sqlserver.jdbc.SQLServerDriver</param-value>
</context-param>
<context-param>
<param-name>url</param-name>
<param-value>jdbc:sqlserver://localhost:1433;DatabaseName=bbs</param-value>
</context-param>
<context-param>
<param-name>userName</param-name>
<param-value>sa</param-value>
</context-param>
<context-param>
<param-name>passWord</param-name>
<param-value>123</param-value>
</context-param>
2、新建一个普通类继承Httpservlet类，并实现ServletContextListener监听接口，如下：
[c-sharp] view plaincopyprint?
import java.sql.Connection;
import java.sql.ResultSet;
import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.http.HttpServlet;
public class ContextListener extends HttpServlet implements ServletContextListener {
/**
* 销毁servlet
*/
public void contextDestroyed(ServletContextEvent sc) {
}
/**
* 初始化
*/
public void contextInitialized(ServletContextEvent sc) {
System.out.println(“开启服务：”);
ServletContext servletContext = sc.getServletContext();
String driverName = servletContext.getInitParamete(“driverName”);
String url = servletContext.getInitParameter(“url”);
String userName = servletContext.getInitParameter(“userName”);
String passWord = servletContext.getInitParameter(“passWord”);
BaseDAO.setDriverName(driverName);
BaseDAO.setUrl(url);
BaseDAO.setUser(userName);
BaseDAO.setPassword(passWord);
}
}
[c-sharp] view plaincopyprint?
import java.sql.Connection;
import java.sql.ResultSet;
import javax.servlet.ServletContext;
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.http.HttpServlet;
public class ContextListener extends HttpServlet implements ServletContextListener {
/**
* 销毁servlet
*/
public void contextDestroyed(ServletContextEvent sc) {
}
/**
* 初始化
*/
public void contextInitialized(ServletContextEvent sc) {
System.out.println(“开启服务：”);
ServletContext servletContext = sc.getServletContext();
String driverName = servletContext.getInitParamete(“driverName”);
String url = servletContext.getInitParameter(“url”);
String userName = servletContext.getInitParameter(“userName”);
String passWord = servletContext.getInitParameter(“passWord”);
BaseDAO.setDriverName(driverName);
BaseDAO.setUrl(url);
BaseDAO.setUser(userName);
BaseDAO.setPassword(passWord);
}
}
3、在公共类BaseDao中
[java] view plaincopyprint?
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource; //连接池要到包
/*
* 获取数据库连接
*/
public class BaseDAO {
private static Connection con;
private static String driverName;
private static String url;
private static String userName;
private static String passWord;
//获取连接对象Connection
public static Connection getConnection(){
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName(driverName);
dataSource.setUrl(url);
dataSource.setUsername(userName);
dataSource.setPassword(passWord);
try{
con = dataSource.getConnection();
} catch(SQLException ex) {
ex.printStackTrace();
}
return con;
}
/*
* 配置:从web.xml
*/
//驱动名称
public static String getDriverName() {
return getDriverName();
}
public static void setDriverName(String driverName) {
BaseDAO.driverName = driverName;
}
//URL
public static String getUrl(){
return getUrl();
}
public static void setUrl(String url) {
BaseDAO.url = url;
}
//用户名
public static String getUser(){
return getUser();
}
public static void setUser(String userName) {
BaseDAO.userName = userName;
}
//密码
public static String getPassWord(){
return getPassWord();
}
public static void setPassword(String passWord) {
BaseDAO.passWord = passWord;
}
}
[java] view plaincopyprint?
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource; //连接池要到包
/*
* 获取数据库连接
*/
public class BaseDAO {
private static Connection con;
private static String driverName;
private static String url;
private static String userName;
private static String passWord;
//获取连接对象Connection
public static Connection getConnection(){
BasicDataSource dataSource = new BasicDataSource();
dataSource.setDriverClassName(driverName);
dataSource.setUrl(url);
dataSource.setUsername(userName);
dataSource.setPassword(passWord);
try{
con = dataSource.getConnection();
} catch(SQLException ex) {
ex.printStackTrace();
}
return con;
}
/*
* 配置:从web.xml
*/
//驱动名称
public static String getDriverName() {
return getDriverName();
}
public static void setDriverName(String driverName) {
BaseDAO.driverName = driverName;
}
//URL
public static String getUrl(){
return getUrl();
}
public static void setUrl(String url) {
BaseDAO.url = url;
}
//用户名
public static String getUser(){
return getUser();
}
public static void setUser(String userName) {
BaseDAO.userName = userName;
}
//密码
public static String getPassWord(){
return getPassWord();
}
public static void setPassword(String passWord) {
BaseDAO.passWord = passWord;
}
}