十步搞定Servlet中的mssql2000数据库连接池

servlet|sql|数据|数据库|数据库连接

1.从aspx?FamilyID=4f8f2f01-1ed7-4c4d-8f7b-3d47969e66ae&DisplayLang=en#filelist">http://www.microsoft.com/downloads/details.aspx?FamilyID=4f8f2f01-1ed7-4c4d-8f7b-3d47969e66ae&DisplayLang=en#filelist下载"Microsoft SQL Server 2000 Driver for JDBC",并安装,得到msbase.jar,mssqlserver.jar和msutil.jar三个文件,将三个文件COPY到TOMCAT 4.1下common\lib文件夹中

2.在TOMCAT 4.1的SERVER.XML中HOST域中添加如下代码

  factory
        org.apache.commons.dbcp.BasicDataSourceFactory
    
    
  maxActive
        100
    
    
  maxIdle
        30
    
    
        maxWait
        10000
    
 
  username
  sa
 
 
  password
  你的密码
 
 
  driverClassName
  com.microsoft.jdbc.sqlserver.SQLServerDriver
 
 
  url
  jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=pubs
       
 3.在webapps下新建文件夹"myweb","myweb\WEB-INF","myweb\WEB-INF\classes"

4.在WEB-INF下新建文件"web.xml",并添加如下内容

    "java.sun.com/dtd/web-app_2_3.dtd">http://java.sun.com/dtd/web-app_2_3.dtd">

 My Web
 
  invoker
  /servlet/*
 
 
  jdbc/mydb
  javax.sql.DataSource
  Container

5.编写servlet程序JDBCPoolServ.java

import java.sql.*;
import javax.naming.Context;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;

public class JDBCPoolServ extends HttpServlet
{
 public void doGet(HttpServletRequest request,
  HttpServletResponse response) throws ServletException,IOException {
    DataSource ds=null;
    Connection con=null;
    String val="null",title="JDBC Pooling Test";
  
    try{
     Context initCtx = new InitialContext();
     if(initCtx == null )
            throw new Exception("Boom - No Context");
           
     ds = (DataSource)initCtx.lookup(
      "java:comp/env/jdbc/mydb");
     if (ds != null){
      con = ds.getConnection();
      if (con != null){
       Statement stmt=con.createStatement();
       ResultSet rs=stmt.executeQuery("select * from authors");
       rs.next();
      
       val=rs.getString("au_id");
       rs.close();
       stmt.close();
      }      
      con.close();
     }
    
    }
    catch(Exception ex){
     System.out.println(ex.getMessage());
    }
   
    response.setContentType("text/html");
  PrintWriter out = response.getWriter();
  out.println("");
        out.println("");
        out.println("");
        out.println("");
        out.println("");
        out.println("

" + val + "
");
        out.println("");
        out.println("");
    }
}

6.编译JDBCPoolServ.java得到JDBCPoolServ.class(注意加入servlet.jar包),将其COPY到"myweb\WEB-INF\classes"下

7.启动SQL SERVER2000

8.启动TOMCAT

9.浏览http://127.0.0.1:8080/myweb/servlet/JDBCPoolServ

10.在IE中可看到"172-32-1176"

注意:如果无法正常运行请检查以上文件夹名,URL和JAVA类名大小写是否一致