Servlet中操作数据库

以下内容引用自http://wiki.jikexueyuan.com/project/servlet/database-access.html：

前提先新建数据库及插入模拟数据：

create table Employees
    (
     id int not null,
     age int not null,
     first varchar (255),
     last varchar (255)
    );
INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');
INSERT INTO Employees VALUES (101, 25, 'Mahnaz', 'Fatma');
INSERT INTO Employees VALUES (102, 30, 'Zaid', 'Khan');
INSERT INTO Employees VALUES (103, 28, 'Sumit', 'Mittal');

实例：

//Loading required libraries
import java.io.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
public class DatabaseAccess extends HttpServlet{   
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
    {
       // JDBC driver name and database URL
       final String JDBC_DRIVER="com.mysql.jdbc.Driver";  
       final String DB_URL="jdbc:mysql://localhost:3306/TEST?serverTimezone=UTC";
       //  Database credentials
       final String USER = "root";
       final String PASS = "root";
       // Set response content type
       response.setContentType("text/html");
       PrintWriter out = response.getWriter();
       String title = "Database Result";
       String docType =
         "<!doctype html public \"-//w3c//dtd html 4.0 " +
          "transitional//en\">\n";
          out.println(docType +
          "<html>\n" +
          "<head><title>" + title + "</title></head>\n" +
          "<body bgcolor=\"#f0f0f0\">\n" +
          "<h1 align=\"center\">" + title + "</h1>\n");
          
       Connection conn = null;
       Statement stmt = null;
       try{
          // Register JDBC driver
          Class.forName(JDBC_DRIVER);
          // Open a connection
          conn = DriverManager.getConnection(DB_URL,USER,PASS);
          // Execute SQL query
          stmt = conn.createStatement();
          String sql;
          sql = "SELECT id, first, last, age FROM Employees";
          ResultSet rs = stmt.executeQuery(sql);
          // Extract data from result set
          while(rs.next()){
             //Retrieve by column name
             int id  = rs.getInt("id");
             int age = rs.getInt("age");
             String first = rs.getString("first");
             String last = rs.getString("last");
             //Display values
             out.println("ID: " + id + "<br>");
             out.println(", Age: " + age + "<br>");
             out.println(", First: " + first + "<br>");
             out.println(", Last: " + last + "<br>");
          }
          out.println("</body></html>");
          // Clean-up environment
          rs.close();
          stmt.close();
          conn.close();
       }catch(SQLException se){
          //Handle errors for JDBC
          se.printStackTrace();
       }catch(Exception e){
          //Handle errors for Class.forName
          e.printStackTrace();
       }finally{
          //finally block used to close resources
          try{
             if(stmt!=null)
                stmt.close();
          }catch(SQLException se2){
          }// nothing we can do
          try{
             if(conn!=null)
             conn.close();
          }catch(SQLException se){
             se.printStackTrace();
          }//end finally try
       } //end try
    }
}

现在来编译Servlet并在web.xml文件中创建以下条目：

 <servlet>
     <servlet-name>DatabaseAccess</servlet-name>
     <servlet-class>DatabaseAccess</servlet-class>
 </servlet>
 <servlet-mapping>
     <servlet-name>DatabaseAccess</servlet-name>
     <url-pattern>/DatabaseAccess</url-pattern>
 </servlet-mapping> 

现在使用URL http://localhost:8080/DatabaseAccess调用这个Servlet，将显示如下所示响应：

 

测试工程：https://github.com/easonjim/5_java_example/tree/master/servletbasics/test10

posted @ 2017-06-10 02:12  EasonJim  阅读(904)  评论(0)  编辑  收藏  举报