html调用servlet（JDBC在Servlet中的使用）（2）
5.修改数据
5.1编写查询条件页面
修改单条数据的时候，首先是查询出单个数据的详细信息，然后根据实际需要部分修改或者全部修改。修改之后，数据会提交到数据库，数据库中保存更新以后的数据。
查询出单条数据的查询条件页面代码如下：
QueryToUpdate.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>QueryToUpdate.html</title> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="this is my page"> <meta http-equiv="content-type" content="text/html; charset=UTF-8"> <!--<link rel="stylesheet" type="text/css" href="./styles.css">--> </head> <body> <form name="f1" id="f1" action="/jdbc_servlet/servlet/QueryToUpdateServlet" method="post"> <table border="0"> <tr> <td>请输入要修改的部门编号:</td><tr></tr> <td><input type="text" name="id" ></td> </tr> <tr> <td colspan="2" align="left"><input type="submit" value="提交"></td> </tr> </table> </form> </body> </html>
5.2 编写显示部门详细信息的Servlet
输入要修改的部门编号以后，进入根据部门编号查询部门信息的Servlet，把部门详细信息显示到页面中，一些不可以修改的字段可以设置成只读，这样就不会把这些数据修改了，该Servlet的代码如下：
QueryToUpdateServlet.java
package com.cn.update; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class QueryToUpdateServlet extends HttpServlet { /** * Destruction of the servlet. <br> */ public void destroy() { super.destroy(); // Just puts "destroy" string in log // Put your code here } /** * The doGet method of the servlet. <br> * * This method is called when a form has its tag value method equals to get. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=gb2312"); request.setCharacterEncoding("gb2312"); PrintWriter out = response.getWriter(); String id = request.getParameter("id"); Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); System.out.println("创建驱动成功！"); con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bank", "root", "1234"); System.out.println("数据库连接成功！"); String sql = "SELECT * FROM dept WHERE id=?"; pstmt = con.prepareStatement(sql); pstmt.setString(1, id); rs = pstmt.executeQuery(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } //显示单个部门的信息 out.println("<html>" + "<head><title>显示单个部门信息</title></head>" + "<body>"); out.println("<h1>显示单个部门信息</h1><br><br>"); out.print("<form action='/jdbc_servlet/servlet/UpdateDeptServlet' method='post'>"); try { while(rs.next()){ out.println("部门编号："); out.print("<br>"); //在文本框中显示部门编号，设置成只读 out.println("<input type='text' name='id' readonly='true' value="); out.println(rs.getString(1).toString()); out.print(">"); out.print("<br>"); out.println("部门名称："); out.print("<br>"); //在文本框中显示部门名称 out.println("<input type='text' name='d_name' value="); out.println(rs.getString(2).toString()); out.print(">"); out.print("<br>"); out.println("部门地址："); out.print("<br>"); //在文本框中显示部门地址 out.println("<input type='text' name='address' value="); out.println(rs.getString(3).toString()); out.print(">"); out.print("<br>"); out.println("部门人数："); out.print("<br>"); //在文本框中显示部门人数 out.println("<input type='text' name='empnumber' value="); out.println(rs.getString(4).toString()); out.print(">"); out.print("<br>"); //提交按钮 out.print("<input type='submit' value='Submit'>"); out.print("</form>"); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } out.flush(); out.close(); } /** * The doPost method of the servlet. <br> * * This method is called when a form has its tag value method equals to post. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=gb2312"); request.setCharacterEncoding("gb2312"); PrintWriter out = response.getWriter(); this.doGet(request, response); out.flush(); out.close(); } /** * Initialization of the servlet. <br> * * @throws ServletException if an error occurs */ public void init() throws ServletException { // Put your code here } }
5.3编写处理修改操作的Servlet
在UpdateDeptServlet中，修改数据以后，显示出数据库表中的全部信息。UpdateDeptServlet的代码如下：
UpdateDeptServlet.java
package com.cn.update; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class UpdateDeptServlet extends HttpServlet { /** * The doGet method of the servlet. <br> * * This method is called when a form has its tag value method equals to get. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); this.doPost(request, response); out.flush(); out.close(); } /** * The doPost method of the servlet. <br> * * This method is called when a form has its tag value method equals to post. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=gb2312"); request.setCharacterEncoding("gb2312"); PrintWriter out = response.getWriter(); Connection con = null; PreparedStatement ps = null; ResultSet rs = null; Statement sta = null; String id = request.getParameter("id"); String address = request.getParameter("address"); int empnumber = Integer.parseInt(request.getParameter("empnumber")); String d_name = request.getParameter("d_name"); try { Class.forName("com.mysql.jdbc.Driver"); System.out.println("创建驱动成功！"); con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bank", "root", "1234"); System.out.println("数据库连接成功！"); String sql = "UPDATE dept SET id=?,address=?,empnumber=?,d_name=? WHERE id=?"; ps = con.prepareStatement(sql); //下面设置修改的数据值 ps.setString(1, id); ps.setString(2, address); ps.setInt(3, empnumber); ps.setString(4, d_name); ps.setString(5, id); ps.executeUpdate(); System.out.println("修改成功！"); /* * 添加成功后，显示全部信息 */ sta = con.createStatement(); rs = sta.executeQuery("SELECT * FROM dept"); //在页面中显示表中的所有信息 out.println( "<html>"+ "<head><title>部门表信息</title></head>"+ "<body>"); out.println("<h1>部门表信息:</h1><br><br>"); //循环遍历输出查询结果 while(rs.next()){ out.print("部门编号："); out.print(rs.getString(1)+"\t"); out.print("部门名称："); out.print(rs.getString(2)+"\t"); out.print("部门地址："); out.print(rs.getString(3)+"\t"); out.print("部门人数："); out.print(rs.getString(4)+"\t"); out.println("<br>"); } out.print("</body></html>"); out.close(); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } out.flush(); out.close(); } /** * Initialization of the servlet. <br> * * @throws ServletException if an error occurs */ public void init() throws ServletException { // Put your code here } }
6. 删除数据
删除数据时，要指定删除的条件，否则会把表中的所有数据删除。删除以后，被删除的数据在表中就不存在了。下面的例子是根据部门编号删除数据的例子。首先在页面中输入要删除的部门编号，输入要删除的部门编号的页面代码入下：
delete.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <title>delete.html</title> <meta http-equiv="keywords" content="keyword1,keyword2,keyword3"> <meta http-equiv="description" content="this is my page"> <meta http-equiv="content-type" content="text/html; charset=gb2312"> <!--<link rel="stylesheet" type="text/css" href="./styles.css">--> </head> <body> <form name="f1" id="f1" action="/jdbc_servlet/servlet/DeleteByIdServlet" method="post"> <table border="0"> <tr> <td>请输入要删除的部门编号:</td><tr></tr> <td><input type="text" name="id" ></td> </tr> <tr> <td colspan="2" align="left"><input type="submit" value="删除"></td> </tr> </table> </form> </body> </html>
当输入删除条件后，单击【删除】按钮，会进入form表单指定的DeleteByIdServlet中，这是一个servlet，用来处理删除操作。DeleteByIdServlet中的代码如下：
DeleteByIdServlet.java
package com.cn.delete; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; public class DeleteByIdServlet extends HttpServlet { /** * The doGet method of the servlet. <br> * * This method is called when a form has its tag value method equals to get. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); this.doPost(request, response); out.flush(); out.close(); } /** * The doPost method of the servlet. <br> * * This method is called when a form has its tag value method equals to post. * * @param request the request send by the client to the server * @param response the response send by the server to the client * @throws ServletException if an error occurred * @throws IOException if an error occurred */ public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=gb2312"); request.setCharacterEncoding("gb2312"); PrintWriter out = response.getWriter(); String id = request.getParameter("id"); Connection con = null; // ResultSet rs = null; PreparedStatement ps = null; try { Class.forName("com.mysql.jdbc.Driver"); System.out.println("创建驱动成功！"); con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bank", "root", "1234"); System.out.println("数据库连接成功！"); String sql = "DELETE FROM dept WHERE id=?"; ps = con.prepareStatement(sql); ps.setString(1, id); ps.executeUpdate(); System.out.println("删除成功！"); //显示结果信息 out.println("<html><head><title>" +"删除部门表数据</title></head>"+"<body>"); out.println("<h1>删除部门表数据成功！</h1>"); out.print("</body></html>"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } out.flush(); out.close(); } /** * Initialization of the servlet. <br> * * @throws ServletException if an error occurs */ public void init() throws ServletException { // Put your code here } }
在DeleteByIdServlet中，获得页面传递过来的部门编号，然后根据部门编号删除该编号对应的数据，删除成功则在页面中提示删除成功。
