首页 > 代码库 > Servlet中操作数据库

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 librariesimport 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

Servlet中操作数据库