使用 servlet 连接数据库

1.项目工程 web.xml 配置

 1 <servlet>
 2     <description>This is the description of my J2EE component</description>
 3     <display-name>This is the display name of my J2EE component</display-name>
 4     <servlet-name>DBServlet</servlet-name>
 5     <servlet-class>org.olive.DBServlet</servlet-class>
 6     <init-param>
 7             <param-name>driverName</param-name>
 8             <param-value>com.microsoft.jdbc.sqlserver.SQLServerDriver</param-value>
 9         </init-param>
10         <init-param>
11         <param-name>username</param-name>            
12             <param-value>sa</param-value>                
13         </init-param>
14         <init-param>
15             <param-name>password</param-name>        
16             <param-value></param-value>                
17         </init-param>
18         <init-param>
19             <param-name>dbName</param-name>    
20             <param-value>pubs</param-value>    
21         </init-param>
22 
23   </servlet>

1 <servlet-mapping>
2     <servlet-name>DBServlet</servlet-name>
3     <url-pattern>/servlet/DBServlet</url-pattern>
4   </servlet-mapping>

2.servlet java文件

DBServlet.java

 1 package org.olive;
 2 
 3 import java.io.*;                                    //导入java.io包
 4 import java.util.*;
 5 import java.sql.*;
 6 import javax.servlet.*;
 7 import javax.servlet.http.*;
 8 public class DBServlet extends HttpServlet{            //定义一个继承HttpServlet的公有类
 9     ServletConfig config=null;                        //定义一个ServletConfig对象
10     private String driverName="";                    //定义私有字符串常量并初始化
11     private String username="";                    //定义的数据库用户名
12     private String password="";                    //定义的数据库连接密码
13     private String dbName="";                        //定义的数据库名
14     private Connection conn;                        //初始化连接
15     private Statement stmt;                        //初始化数据库操作
16     ResultSet rs=null;                            //初始化结果集
17     public void init(ServletConfig config)throws ServletException{
18      super.init(config);                            //继承父类的init()方法
19      this.config=config;                            //获取配置信息
20      driverName=config.getInitParameter("driverName");//从配置文件中获取JDBC驱动名
21      username=config.getInitParameter("username");    //获取数据库用户名
22       password=config.getInitParameter("password");    //获取数据库连接密码
23       dbName=config.getInitParameter("dbName");    //获取要连接的数据库
24         }
25     public void doGet(HttpServletRequest req,HttpServletResponse resp)throws IOException,ServletException{
26        
27         resp.setContentType("text/html;charset=GBK");    //设置字符编码格式
28         PrintWriter out=resp.getWriter();                //实例化对象，用于页面输出
29           out.println("<html>");                    //实现生成静态Html
30           out.println("<head>");
31           out.println("<meta http-equiv=\"Content-Type\"content=\"text/html;charset=GBK\">");
32           out.println("<title>DataBase Connection</title>");
33           out.println("</head>");
34           out.println("<body bgcolor=\"white\">");
35           out.println("<center>");
36           String url="jdbc:microsoft:sqlserver://localhost:1433; DatabaseName=pubs";
37           try{
38                 Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
39                 conn=DriverManager.getConnection(url,username,password);
40                 stmt=conn.createStatement();
41                 String sql="use pubs select * from publishers";
42                 rs=stmt.executeQuery(sql);
43                   out.println("Servlet访问数据库成功");
44                   out.println("<table border=1 bordercolorlight=#000000>");
45                   out.println("<tr><td width=40>序号</td>");
46                   out.println("<td>书名</td>");
47                   out.println("<td>城市</td>");
48                   out.println("<td>州</td>");
49                   out.println("<td>国</td></tr>");
50                    while(rs.next()){
51                     out.println("<tr><td>"+rs.getString(1)+"</td>");
52                     out.println("<td>"+rs.getString(2)+"</td>");
53                     out.println("<td>"+rs.getString(3)+"</td>");
54                     out.println("<td>"+rs.getString(4)+"</td>");
55                     out.println("<td>"+rs.getString(5)+"</td>");
56                     out.println("<tr>");
57                    }
58                    out.println("</table>");
59                    rs.close();
60                    stmt.close();
61                    conn.close();    
62                   
63               }catch(Exception e){
64               e.printStackTrace();
65               out.println(e.toString());    
66               }
67               out.println("</center>");
68           out.println("</body>");
69           out.println("</html>");
70            }
71            public void doPost(HttpServletRequest req,HttpServletResponse resp)throws IOException,ServletException{
72                this.doGet(req,resp);
73                }
74                public void destory(){
75                    config=null;
76                    driverName=null;
77                    username=null;
78                    password=null;
79                    dbName=null;
80                    conn=null;
81                    stmt=null;
82                    rs=null;
83                    }
84 }