实验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表中的个人信息，每行一条个人信息，表格新增两列，分别提供“修改”和“删除”两个链接。点击“修改”链接时，跳转到新页面可供用户修改和保存；点击“删除”时，跳转到新页面完成删除操作并提示操作结果。