Servlet连接数据库

测试连接数据库为MS Sql Server 2008

步骤一：去微软下载sqljdbc_4.0

步骤二：无需安装，解压出来，把sqljdbc4.jar包copy to Tomcat的lib目录下

步骤三：将auth目录下的sqljdbc_auth.dll文件copy to c:windowssystem32下。

Servlet代码：

package com.my;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import java.util.List;
import java.util.ArrayList;

public class Hello extends HttpServlet {
    public Hello() {}
    
    public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
    
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            Connection conn = DriverManager.getConnection("jdbc:sqlserver://127.0.0.1;DatabaseName=DBNAME;integratedSecurity=True;");
            Statement stm = conn.createStatement();
            ResultSet rs = stm.executeQuery("SELECT TOP 10 * FROM Project");
            List<String> list = new ArrayList<String>();
            while(rs.next()) {
                list.add(rs.getString("name"));
            }
            rs.close();
            stm.close();
            
            PreparedStatement stmPrepare = conn.prepareStatement("SELECT TOP 10 * FROM Project WHERE id=?");
            stmPrepare.setInt(1, 1);
            ResultSet rsPrepare = stmPrepare.executeQuery();
            List<String> listPrepare = new ArrayList<String>();
            while(rsPrepare.next()) {
                listPrepare.add(rsPrepare.getString("name"));
            }
            rsPrepare.close();
            stmPrepare.close();
            conn.close();
            
            resp.setContentType("text/html;charset="UTF-8"");
            PrintWriter pw = resp.getWriter();
            pw.print("<html>");
            pw.print("<header>");
            pw.print("</header>");
            pw.print("<body>");
            pw.print("<form action="" method="">");
            for(int i=0; i<list.size(); i++) {
                pw.print("<h2>" + list.get(i) + "</h2>");
            }
            for(int i=0; i<listPrepare.size(); i++) {
                pw.print("<h3>" + listPrepare.get(i) + "</h3>");
            }
            pw.print("</form>");
            pw.print("</body>");
            pw.print("</html>");
        }
        catch(IOException e) {
            e.printStackTrace();
        }
        catch(SQLException e) {
            e.printStackTrace();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }
    
    public void doPost(HttpServletRequest req, HttpServletResponse resp) {
        //
    }
}

web.xml:

    <servlet>
      <servlet-name>hello</servlet-name>
      <servlet-class>com.my.Hello</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>hello</servlet-name>
        <url-pattern>/hello</url-pattern>
    </servlet-mapping>