Tomcat6.0配置连接池连接SQL Server 2008数据库

  Tomcat6.0配置连接池连接SQL Server 2008数据库

1:在\apache-tomcat-6.0.18\conf中加入sqljdbc4.jar包，否则会所报org.apache.jasper.JasperException: java.lang.UnsupportedOperationException: Java Runtime Environment (JRE) version 1.6 is not supported by this driver.  Please use the JDBC 4 driver (sqljdbc4.jar) instead.
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:522)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:416)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

2:在你的项目里的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="jspjdbc/myDataSource" 
 auth="Container"
 type="javax.sql.DataSource"
 driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
 url="jdbc:sqlserver://localhost:1433;DataBaseName=express_yto"
 username="sa"
 password="skymusic"
 maxActive="100"
 maxIdle="30"
 maxWait="1000"/>
</Context>
3:打开WEB-INF下的Web.xml文件，加入如下代码：


<resource-ref>
       <description>DB Connection</description>
       <res-ref-name>jspjdbc/myDataSource</res-ref-name>
       <res-type>javax.sql.DataSource</res-type>
       <res-auth>Container</res-auth>
   </resource-ref>   


4:写一个index.jsp测试一把


<%@ page language="java" import="java.util.*" pageEncoding="gb2312"%>
<%@ page import="java.sql.*"%>
<%@ page import="javax.sql.DataSource"%>
<%@ page import="javax.naming.*"%>
 <%
     Connection con = null;
     Statement stat = null;
     ResultSet rs = null;
     DataSource ds = null;


     //从数据源连接池取得连接
     Context ctx = new InitialContext();
     if(ctx!=null){
      ds = (DataSource)ctx.lookup("java:comp/env/jspjdbc/myDataSource");
     con = ds.getConnection();
         
     //查询数据表
     stat = con.createStatement();
     String sql = "select * from dict_sex";
     rs = stat.executeQuery(sql);
     //输出查询结果到页面
     while (rs.next()){
      out.println("<li>账号："+rs.getString(2).trim());
      out.println("密码："+rs.getString(3).trim()+"</li>");
     }
     //关闭连接，释放资源
     rs.close();
     stat.close();
     con.close();
     }
  



    %>