实验9 JSP访问数据库（二） - ToB企服应用市场:ToB评测及商务社交产业平台

package com.example.servlet;
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class StudentQueryServlet extends HttpServlet {
// JDBC数据库连接配置
private static final String DB_URL = "jdbc:mysql://localhost:3307/student"; // 替换成你的数据库地址,我这里写的是3307不是3306
private static final String DB_USER = "root"; // 数据库用户名
private static final String DB_PASS = "123456"; // 数据库密码
// 处理GET请求
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 获取用户输入的学生姓名
String studentName = request.getParameter("studentName");
// 设置响应内容类型为HTML
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
// 默认返回“查无此人”信息
String result = "查无此人";
// 连接数据库并执行查询
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS)) {
String sql = "SELECT * FROM students WHERE name LIKE ?"; // SQL模糊查询
try (PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setString(1, "%" + studentName + "%"); // 设置模糊查询的姓名
ResultSet rs = stmt.executeQuery();
// 如果查询到结果，则显示学生信息
if (rs.next()) {
result = "<h3>查询结果：</h3>";
do {
result += "<p>姓名：" + rs.getString("name") + "<br>"
+ "年龄：" + rs.getInt("age") + "<br>"
+ "专业：" + rs.getString("major") + "</p>";
} while (rs.next());
}
}
} catch (SQLException e) {
e.printStackTrace();
result = "数据库连接错误";
}
// 输出HTML响应
out.println("<html><body>");
out.println("<h1>学生信息查询</h1>");
out.println("<form method='GET' action='StudentQueryServlet'>");
out.println("请输入学生姓名：<input type='text' name='studentName' />");
out.println("<input type='submit' value='查询' />");
out.println("</form>");
out.println("<hr>");
out.println(result);
out.println("</body></html>");
}
}

复制代码

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
version="3.1">
<display-name>sample</display-name>
<servlet>
<servlet-name>StudentQueryServlet</servlet-name>
<servlet-class>com.example.servlet.StudentQueryServlet</servlet-class>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>StudentQueryServlet</servlet-name>
<url-pattern>/StudentQueryServlet</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
</web-app>

复制代码

<%@ page contentType="text/html; charset=UTF-8" %>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<title>学生信息查询</title>
</head>
<body>
<h1>学生信息查询</h1>
<form method="GET" action="StudentQueryServlet">
<label for="studentName">请输入学生姓名：</label>
<input type="text" id="studentName" name="studentName" required />
<input type="submit" value="查询" />
</form>
</body>
</html>

复制代码

create database student;
use student;
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
age INT,
major VARCHAR(255)
);
INSERT INTO students (name, age, major) VALUES ('John Doe', 21, 'Computer Science');
INSERT INTO students (name, age, major) VALUES ('Jane Smith', 22, 'Physics');
INSERT INTO students (name, age, major) VALUES ('Alice Johnson', 20, 'Mathematics');

复制代码

package com.example.servlet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.*;
public class StudentLogin extends HttpServlet {
private static final String DB_URL = "jdbc:mysql://localhost:3307/student";
private static final String DB_USER = "root";
private static final String DB_PASS = "123456";
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String id = request.getParameter("id");
String pwd = request.getParameter("pwd");
response.setContentType("text/html;charset=UTF-8");
String result = null;
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS)) {
String sql = "SELECT * FROM student02 WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, id);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
String dbPwd = rs.getString("pwd");
if (dbPwd.equals(pwd)) {
result = "登录成功";
} else {
result = "密码错误";
}
} else {
result = "学号不存在，请注册";
response.sendRedirect("register.jsp"); // 学号不存在，重定向到注册页面
return;
}
}
} catch (SQLException e) {
e.printStackTrace();
result = "数据库连接错误";
}
response.getWriter().write(result);
}
}

复制代码

package com.example.servlet;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.*;
public class StudentRegister extends HttpServlet {
private static final String DB_URL = "jdbc:mysql://localhost:3307/student";
private static final String DB_USER = "root";
private static final String DB_PASS = "123456";
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String id = request.getParameter("id");
String pwd = request.getParameter("pwd");
String name = request.getParameter("name");
response.setContentType("text/html;charset=UTF-8");
String result = null;
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS)) {
// 检查学号是否已经存在
String sql = "SELECT * FROM student02 WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, id);
ResultSet rs = pstmt.executeQuery();
if (rs.next()) {
result = "学号已存在，请重新输入";
response.getWriter().write(result);
} else {
// 插入新学生
sql = "INSERT INTO student02 (id, pwd, name) VALUES (?, ?, ?)";
try (PreparedStatement insertStmt = conn.prepareStatement(sql)) {
insertStmt.setString(1, id);
insertStmt.setString(2, pwd);
insertStmt.setString(3, name);
insertStmt.executeUpdate();
result = "注册成功";
response.getWriter().write(result);
}
}
} catch (SQLException e) {
e.printStackTrace();
result = "数据库连接错误";
response.getWriter().write(result);
}
} catch (SQLException e) {
e.printStackTrace();
result = "数据库连接错误";
response.getWriter().write(result);
}
}
}

复制代码

<%--
Created by IntelliJ IDEA.
User: d'd'd
Date: 2024/11/15
Time: 14:07
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>登录</title>
</head>
<body>
<h1>学生登录</h1>
<form action="StudentLogin" method="post">
<label for="id">学号：</label>
<input type="text" id="id" name="id" required>
<br>
<label for="pwd">密码：</label>
<input type="password" id="pwd" name="pwd" required>
<br>
<input type="submit" value="登录">
</form>
<a href="register.jsp">没有账号？去注册</a>
</body>
</html>

复制代码

<%--
Created by IntelliJ IDEA.
User: d'd'd
Date: 2024/11/15
Time: 14:08
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>注册</title>
</head>
<body>
<h1>学生注册</h1>
<form action="StudentRegister" method="post">
<label for="id">学号：</label>
<input type="text" id="id" name="id" required>
<br>
<label for="pwd">密码：</label>
<input type="password" id="pwd" name="pwd" required>
<br>
<label for="name">姓名：</label>
<input type="text" id="name" name="name" required>
<br>
<input type="submit" value="注册">
</form>
</body>
</html>

复制代码

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
version="3.1">
<display-name>StudentApp</display-name>
<servlet>
<servlet-name>StudentLogin</servlet-name>
<servlet-class>com.example.servlet.StudentLogin</servlet-class>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet>
<servlet-name>StudentRegister</servlet-name>
<servlet-class>com.example.servlet.StudentRegister</servlet-class>
<load-on-startup>2</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>StudentLogin</servlet-name>
<url-pattern>/StudentLogin</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>StudentRegister</servlet-name>
<url-pattern>/StudentRegister</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>login.jsp</welcome-file>
</welcome-file-list>
</web-app>

复制代码

-- 删除数据库
drop database students;
-- 创建数据库
create database students;
-- 使用数据库
use students;
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
password VARCHAR(255) NOT NULL,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
phone VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

复制代码

spring.application.name=M-stu-sys
# 数据库连接配置
# 这一行指定了数据库的连接URL。
spring.datasource.url=jdbc:mysql://localhost:3306/students?useSSL=false&serverTimezone=UTC
# 用于指定连接数据库的用户名，这里设置为root，你需要将其替换为你实际使用的数据库用户名。
spring.datasource.username=root
# 这是连接数据库的密码，将yourpassword替换为你的数据库密码。
spring.datasource.password=123456
# 明确指定使用的MySQL JDBC驱动类，com.mysql.cj.jdbc.Driver是MySQL 8.0及以上版本使用的驱动类。
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# MyBatis配置
# 告诉MyBatis在哪里查找映射文件（XML文件）。classpath:mapper/*.xml表示在类路径下的mapper目录中查找所有以.xml结尾的文件，这些文件包含了SQL语句和数据库操作的映射定义。
mybatis.mapper-locations=classpath:mapper/*.xml
# 用于设置MyBatis的类型别名包。这样在映射文件中可以使用短名称来代替全限定类名，方便书写SQL语句，提高代码的可读性。例如，如果在com.example.demo.model包中有一个User类，在映射文件中可以直接使用User来代替com.example.demo.model.User。
mybatis.type-aliases-package=org.example.mstusys.entity
# JPA配置（如果项目中用到JPA）
# 这个配置用于控制Hibernate（JPA的实现）如何根据实体类自动创建、更新或验证数据库表结构。update表示根据实体类的定义自动更新数据库表结构，例如添加新的字段、创建新的表等。但在生产环境中使用时要谨慎，因为可能会意外修改数据库结构。其他可选值包括create（每次启动应用时创建新的数据库表，删除旧表，慎用）、create-drop（在应用启动时创建表，关闭时删除表）、validate（只验证表结构是否与实体类定义一致，不进行修改）等。如果你的项目中没有使用JPA，可以删除这一行配置。
spring.jpa.hibernate.ddl-auto=update

复制代码

package org.example.mstusys.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private Integer id;
private String username;
private String password;
private String fullName;
private String email;
private String phone;
private String createdAt;
}

复制代码

package org.example.mstusys.mapper;
import org.apache.ibatis.annotations.*;
import org.example.mstusys.entity.Student;
import java.util.List;
@Mapper
public interface StudentMapper {
// 向students表中插入一条学生记录，插入的字段包括用户名、密码、全名、邮箱和电话，参数为一个Student对象
@Insert("INSERT INTO students (username, password, full_name, email, phone) " +
"VALUES (#{username}, #{password}, #{fullName}, #{email}, #{phone})")
int registerStudent(Student student);
// 根据用户名查找学生记录，返回一个Student对象，如果未找到则返回null
@Select("SELECT * FROM students WHERE username = #{username}")
Student findByUsername(String username);
// 根据学生id查找学生记录，返回一个Student对象，如果未找到则返回null
@Select("SELECT * FROM students WHERE id = #{id}")
Student findById(int id);
// 查询students表中的所有学生记录，返回一个包含所有学生记录的List<Student>集合
@Select("SELECT * FROM students")
List<Student> findAll();
// 根据学生id更新学生记录，更新的字段包括用户名、密码、全名、邮箱和电话，参数为一个Student对象
@Update("UPDATE students SET username = #{username}, password = #{password}, full_name = #{fullName}, " +
"email = #{email}, phone = #{phone} WHERE id = #{id}")
int updateStudent(Student student);
// 根据学生id删除学生记录，返回受影响的行数，如果删除成功则返回1，否则返回0
@Delete("DELETE FROM students WHERE id = #{id}")
int deleteStudent(int id);
}

复制代码

package org.example.mstusys.service;
import org.example.mstusys.entity.Student;
import org.example.mstusys.mapper.StudentMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class StudentService {
// 注入StudentMapper
@Autowired
private StudentMapper studentMapper;
/**
* 注册学生
* @param student
* @return
*/
public int registerStudent(Student student) {
return studentMapper.registerStudent(student);
}
/**
* 登录
* @param username
* @return
*/
public Student login(String username){
return studentMapper.findByUsername(username);
}
/**
* 根据id获取学生信息
* @param id
* @return
*/
public Student getStudentById(int id){
return studentMapper.findById(id);
}
/**
* 获取所有学生信息
* @return
*/
public List<Student> getAllStudents() {
return studentMapper.findAll();
}
/**
* 更新学生信息
* @param student
* @return
*/
public int updateStudent(Student student) {
return studentMapper.updateStudent(student);
}
/**
* 删除学生信息
* @param id
* @return
*/
public int deleteStudent(int id) {
return studentMapper.deleteStudent(id);
}
}

复制代码

package org.example.mstusys.controller;
import org.example.mstusys.entity.Student;
import org.example.mstusys.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
@RequestMapping("/students")
public class StudentController {
@Autowired
private StudentService studentService;
/**
* 注册
* @param student
* @return
*/
@PostMapping("/register")
public String register(@RequestBody Student student){
int result = studentService.registerStudent(student);
return result > 0 ? "Registration successful" : "Registration failed";
}
/**
* 登录
* @param username
* @param password
* @return
*/
@PostMapping("/login")
public String login(@RequestParam String username,@RequestParam String password){
Student student = studentService.login(username);
if(student != null && student.getPassword().equals(password)){
return "Login successful";
}else{
return "Invalid username or password";
}
}
/**
* 根据id查询学生
* @param id
* @return
*/
@GetMapping("/{id}")
public Student getStudentById(@PathVariable int id){
return studentService.getStudentById(id);
}
/**
* 查询所有学生
* @return
*/
@GetMapping("/all")
public List<Student> getAllStudents(){
return studentService.getAllStudents();
}
/**
* 更新学生信息
* @param student
* @return
*/
@PutMapping("/update")
public String updateStudent(@RequestBody Student student) {
int result = studentService.updateStudent(student);
return result > 0 ? "Student updated successfully" : "Update failed";
}
/**
* 删除学生
* @param id
* @return
*/
@DeleteMapping("/delete/{id}")
public String deleteStudent(@PathVariable int id) {
int result = studentService.deleteStudent(id);
return result > 0 ? "Student deleted successfully" : "Delete failed";
}
}