实验9 JSP访问数据库（二）
实验9 JSP访问数据库（二）
目的：
1、熟悉JDBC的数据库访问模式。
2、掌握预处理语句的使用
实验要求：
1、使用Tomcat作为Web服务器
2、通过JDBC访问数据库，实现增删改查功能的实现
3、要求提交实验报告，将代码和实验结果页面截图放入报告中
实验内容：
1.编写一个网页，实现根据输入学生姓名的模糊查询，如果查找不到就显示“查无此人”
index.java
<%@ page import="java.sql.*" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.Statement" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.util.ArrayList" %>
<%@ page import="java.util.List" %>
<%@ page import="com.xxx.Student" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %><%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head><title>模糊查询</title>
</head>
<body>
<form action="index.jsp" method="post"><input type="text" name="info"></br><input type="submit" value="查询">
</form>
<%List<Student> studentList = new ArrayList<>();Connection connnection = null;PreparedStatement statement = null;ResultSet resultSet = null;String url = "jdbc:mysql://localhost:3306/test9";Class.forName("com.mysql.jdbc.Driver");connnection = DriverManager.getConnection(url,"root","123456");String sql = "select * from student where stuname like ?";statement = connnection.prepareStatement(sql);String info = request.getParameter("info");statement.setString(1,"%" + info +"%");resultSet = statement.executeQuery();while(resultSet.next()){int id = resultSet.getInt("stuid");String name = resultSet.getString("stuname");String sex = resultSet.getString("stusex");Student stu = new Student(id,name,sex);studentList.add(stu);}// 将数据设置到作用域当中request.setAttribute("studentList",studentList);
%><c:if test="${!empty studentList}"><table align="center" width="800" border="1" style="border-collapse: collapse;"><tr><th>学号</th><th>姓名</th><th>性别</th></tr><c:forEach items="${studentList}" var="stu"><tr align="center"><td>${stu.id}</td><td>${stu.name}</td><td>${stu.sex}</td></tr></c:forEach></table></c:if>
<c:if test="${studentList.size() == 0}"><h4>查无此人</h4>
</c:if><%resultSet.close();statement.close();connnection.close();
%>
</body>
</html>
Student.java
package com.xxx;public class Student {private Integer id;private String name;private String sex;public Student() {}public Student(Integer id, String name, String sex) {this.id = id;this.name = name;this.sex = sex;}public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getSex() {return sex;}public void setSex(String sex) {this.sex = sex;}
}
2.实现学生注册和登录功能，输入学生学号（stuId）和密码(stuPwd)，如果学号不存在，跳转到注册界面，完成注册功能；如果学号存在，密码匹配显示“登录成功”，否则显示“登录失败”。
注：要求用PreparedStatement实现。
login.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head><title>Title</title>
</head>
<body>
<form action="login_2.jsp" method="post"><label for="username">用户名：</label><input type="text" id="username" name="username" required><br><label for="password">密码：</label><input type="password" id="password" name="password" required><input type="submit" value="登陆">
</form>
</body>
</html>
login_2.jsp
<%@ page import="java.sql.*" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head><title>Title</title>
</head>
<body>
<%--表单提交到这--%>
<%String url = "jdbc:mysql://localhost:3306/test9";String stuId = request.getParameter("username");String stuPwd = request.getParameter("password");try {// 加载和注册JDBC驱动Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManager.getConnection(url, "root", "123456");//检查学号是否存在String sql1 = "select * from password where stuId = ?";PreparedStatement stmt1 = conn.prepareStatement(sql1);stmt1.setString(1,stuId);ResultSet rs1 = stmt1.executeQuery();if(rs1.next() && rs1.getInt(1) > 0){String sql2 = "select * from password where stuId = ? and stuPwd = ?";PreparedStatement stmt2 = conn.prepareStatement(sql2);stmt2.setString(1,stuId);stmt2.setString(2,stuPwd);ResultSet rs2 = stmt2.executeQuery();if (rs2.next() && rs2.getInt(1) > 0) {response.sendRedirect("loginSuccess.jsp");} else {response.sendRedirect("loginFail.jsp");}rs2.close();stmt2.close();}else{response.sendRedirect("register_2.jsp");}rs1.close();stmt1.close();conn.close();} catch (SQLException se) {se.printStackTrace();} catch (Exception e) {e.printStackTrace();}
%>
</body>
</html>
register_2.jsp
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.PreparedStatement" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head><title>注册页面</title>
</head>
<body>
<form action="register.jsp" method="post"><label for="username">用户名：</label><input type="text" id="username" name="username" required><br><label for="password">密码：</label><input type="password" id="password" name="password" required><input type="submit" value="注册">
</form>
</body>
</html>
register.jsp
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.PreparedStatement" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head><title>注册页面</title>
</head>
<body>
<%String url = "jdbc:mysql://localhost:3306/test9";Class.forName("com.mysql.jdbc.Driver");Connection conn = DriverManager.getConnection(url,"root","123456");String username = request.getParameter("username");String password = request.getParameter("password");String sql = "insert into password(stuID,stuPwd) values(?,?)";PreparedStatement stmt = conn.prepareStatement(sql);stmt.setString(1,username);stmt.setString(2,password);int count = stmt.executeUpdate();if(count != 0){
%><a href="login.jsp">注册成功，返回登录页面</a><%}stmt.close();conn.close();%>
</body>
</html>loginSuccess.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head><title>登陆成功</title>
</head>
<body><h4>登陆成功</h4>
</body>
</html>
loginFail.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head><title>登陆失败</title>
</head>
<body><h4>登录失败</h4>
</body>
</html>
（登陆成功）
（登陆失败（密码不匹配））
（跳转到注册页面（账号不存在））
3.（选做）参考学习平台上“JDBC操作_增删改”的视频，使用表格展示数据库中stuinfo表中的个人信息，每行一条个人信息，表格新增两列，分别提供“修改”和“删除”两个链接。点击“修改”链接时，跳转到新页面可供用户修改和保存；点击“删除”时，跳转到新页面完成删除操作并提示操作结果。