web实验三
通过使用JSP技术设计一个简单的数据库管理系统，了解展示页面和编辑页面的区别，掌握Web服务器与MySQL数据库的连接和数据库操作的方法，掌握使用Java语言编写JSP文件的方法。
在jsp界面中写代码，没有前后端分离。
主界面：
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ page language="java" import="java.sql.*" %> <%@ page errorPage="error.jsp"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>客户管理系统首页</title> <link rel="stylesheet" type="text/css" href="css/style.css"> </head> <body> <h1>客户管理系统</h1> <a href="add.jsp">添加客户信息</a> <br /> <br /> <table style="width: 50%;"> <tr> <th>姓名</th> <th>住址</th> <th>手机号码</th> <th>管理</th> </tr> <% Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db?useUnicode=true&characterEncoding=utf-8", "root", "123456"); //使用Statement对象 Statement stmt = con.createStatement(); ResultSet rsCount = stmt.executeQuery("select count(*) as result from web_customer"); int count = 0; while(rsCount.next()){ count = rsCount.getInt(1); } if (count>0) { ResultSet rs = stmt.executeQuery("select * from web_customer"); while (rs.next()) { int id = rs.getInt(1); out.println("<tr><td>" + rs.getString(2) + "</td><td>" + rs.getString(3) + "</td><td>" + rs.getString(4) + "</td><td><a href='edit.jsp?id=" + id + "'>修改</a> <a href='del.jsp?id=" + id + "'>删除</a></td></tr>"); } rs.close(); } else { out.println("<h2>还没有用户信息，请添加用户信息</h2>"); } rsCount.close(); stmt.close(); con.close(); %> </table> <br /> <hr /> <div style="text-align: center; width: 100%; font-size: 12px; color: #333;"> ©版权所有：石家庄铁道大学信息科学与技术学院 <a href="Lab03.png" target="_blank">网站地图</a> </div> </body> </html>
保存界面：
<%@ page language="java" contentType="text/html; charset=UTF-8" import="java.sql.*" errorPage="error.jsp" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <script> </script> <title>添加用户信息</title> <link rel="stylesheet" type="text/css" href="css/style.css"> </head> <script type="text/javascript"> function test_phone() { var phone = document.getElementById("phone1").value; var dihit = phone.length; var text = new RegExp("[0-9]"); if (dihit==11 && text.test(phone)) { alert("输入正确"); document.getElementById("submit1").disabled=false; } else { alert("请输入11位数字"); document.getElementById("submit1").setAttribute("disabled",true); } } </script> <body> <form action="addsave.jsp" method="post"> <h2>添加用户信息</h2> <table style="width: 50%"> <tr> <th width="30%">客户姓名：</th> <td width="70%"><input name="name" type="text" required="required"></td> </tr> <tr> <th>住址：</th> <td><input name="address" type="text" required="required"></td> </tr> <tr> <th>手机号码：</th> <td><input name="phone" type="text" required="required" id="phone1" onchange="test_phone()"></td> </tr> <tr> <td colspan="2"><input type="submit" name="submit" value="添加" id="submit1"><input type="reset" value="重置"></td> </tr> </table> <a href="home_page.jsp">放弃添加</a> </form> </body> </html>
保存记录：
<%@ page language="java" contentType="text/html; charset=UTF-8" import="java.sql.*" errorPage="error.jsp" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>添加客户信息</title> </head> <body> <% request.setCharacterEncoding("utf-8"); String name = request.getParameter("name"); String address = request.getParameter("address"); String phone = request.getParameter("phone"); Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=utf-8", "root", "123456"); //使用Statement对象 // Statement stmt = con.createStatement(); // String sql = "insert into bookinfo(bookname,author,price) values('" + bookname + "','" + author + "'," + price + ")"; // System.out.println(sql); // int i = stmt.executeUpdate(sql); PreparedStatement stmt = con.prepareStatement("insert into web_customer(name,address,phone) values(?, ?, ?)"); stmt.setString(1, name); stmt.setString(2, address); stmt.setString(3, phone); int i = stmt.executeUpdate(); if (i == 1) { out.println("<h2>添加成功！</h2><br/>"); out.println("<a href='home_page.jsp'>返回首页</a>"); } else { out.println("<h2>添加失败！</h2><br/>"); out.println("<a href='add.jsp'>重新添加</a>"); } stmt.close(); con.close(); %> </body> </html>
修改界面：
<%@ page language="java" contentType="text/html; charset=UTF-8" import="java.sql.*" errorPage="error.jsp" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>修改用户信息</title> <link rel="stylesheet" type="text/css" href="css/style.css"> </head> <body> <% request.setCharacterEncoding("utf-8"); String id = request.getParameter("id"); Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db?useUnicode=true&characterEncoding=utf-8", "root", "123456"); PreparedStatement stmt = con.prepareStatement("select * from web_customer where id=?"); stmt.setString(1, id); ResultSet rs = stmt.executeQuery(); rs.next(); %> <form action="editsave.jsp" method="post"> <h2>修改用户信息</h2> <table style="width:50%"> <tr> <th width="30%">姓名：</th> <td width="70%"><input name="name" type="text" value="<%=rs.getString(2)%>"></td> </tr> <tr> <th>住址：</th> <td><input name="address" type="text" value="<%=rs.getString(3)%>"></td> </tr> <tr> <th>手机号码：</th> <td><input name="phone" type="text" value="<%=rs.getString(4)%>">元</td> </tr> <tr> <td colspan="2"><input type="hidden" name="id" value="<%=id%>"> <input type="submit" value="修改"> <input type="reset" value="重置"></td> </tr> </table> <a href="home_page.jsp">放弃修改</a> </form> <% rs.close(); stmt.close(); con.close(); %> </body> </html>
保存修改：
<%@ page language="java" contentType="text/html; charset=UTF-8" import="java.sql.*" errorPage="error.jsp" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>修改完成</title> </head> <body> <% request.setCharacterEncoding("utf-8"); String name = request.getParameter("name"); String address = request.getParameter("address"); String phone = request.getParameter("phone"); String id = request.getParameter("id"); Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db?useUnicode=true&characterEncoding=utf-8", "root", "123456"); Statement stmt = con.createStatement(); String sql = "update web_customer set name='" + name + "',address='" + address + "',phone=" + phone + " where id=" + id; System.out.println(sql); int i = stmt.executeUpdate(sql); if (i == 1) { out.println("<h2>修改成功！</h2><br/>"); out.println("<a href='home_page.jsp'>返回首页</a>"); } else { out.println("<h2>修改失败！</h2><br/>"); out.println("<a href='edit.jsp?id='" + id + ">重新添加</a>"); } stmt.close(); con.close(); %> </body> </html>
删除界面：
<%@ page language="java" contentType="text/html; charset=UTF-8" import="java.sql.*" errorPage="error.jsp" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>删除客户信息</title> <link rel="stylesheet" type="text/css" href="css/style.css"> </head> <body> <% request.setCharacterEncoding("utf-8"); Class.forName("com.mysql.jdbc.Driver"); Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/db?useUnicode=true&characterEncoding=utf-8", "root", "123456"); Statement stmt=con.createStatement(); String id=request.getParameter("id"); int i=stmt.executeUpdate("delete from web_customer where id="+id); if(i==1) { out.println("<h2>删除成功！</h2><br/>"); } else { out.println("<h2>删除失败！</h2><br/>"); } out.println("<a href='home_page.jsp'>返回首页</a>"); stmt.close(); con.close(); %> </body> </html>
运行截图：
