Functional Requirements

• Username: 6–12 characters; alphanumeric or underscore; must start with a letter.
• Psasword: At least 8 characters; only letters and digits; masked as "•" or "*" in input.
• Gender: Implemented via radio buttons or dropdown with options "Male" or "Female".
• Student ID: Exactly 8 digits, starting with "2018".
• Name: Full name input.
• Email: Must match format xxxx@xxxx.xxxx.
• On clicking "Add", all data should be inserted into a MySQL database.
• Successful JDBC connection to the database must be demonstrable.

Data Model: User Bean

package com.bean;

public class Student {
    private String username;
    private String password;
    private String fullName;
    private String studentId;
    private String gender;
    private String phone;
    private String email;
    private String college;
    private String department;
    private String className;
    private String enrollmentYear;
    private String hometown;
    private String remarks;

    // Getters and setters
    public String getUsername() { return username; }
    public void setUsername(String username) { this.username = username; }

    public String getPassword() { return password; }
    public void setPassword(String password) { this.password = password; }

    public String getFullName() { return fullName; }
    public void setFullName(String fullName) { this.fullName = fullName; }

    public String getStudentId() { return studentId; }
    public void setStudentId(String studentId) { this.studentId = studentId; }

    public String getGender() { return gender; }
    public void setGender(String gender) { this.gender = gender; }

    public String getPhone() { return phone; }
    public void setPhone(String phone) { this.phone = phone; }

    public String getEmail() { return email; }
    public void setEmail(String email) { this.email = email; }

    public String getCollege() { return college; }
    public void setCollege(String college) { this.college = college; }

    public String getDepartment() { return department; }
    public void setDepartment(String department) { this.department = department; }

    public String getClassName() { return className; }
    public void setClassName(String className) { this.className = className; }

    public String getEnrollmentYear() { return enrollmentYear; }
    public void setEnrollmentYear(String enrollmentYear) { this.enrollmentYear = enrollmentYear; }

    public String getHometown() { return hometown; }
    public void setHometown(String hometown) { this.hometown = hometown; }

    public String getRemarks() { return remarks; }
    public void setRemarks(String remarks) { this.remarks = remarks; }

    public Student(String username, String password, String fullName, String studentId,
                   String gender, String phone, String email, String college,
                   String department, String className, String enrollmentYear,
                   String hometown, String remarks) {
        this.username = username;
        this.password = password;
        this.fullName = fullName;
        this.studentId = studentId;
        this.gender = gender;
        this.phone = phone;
        this.email = email;
        this.college = college;
        this.department = department;
        this.className = className;
        this.enrollmentYear = enrollmentYear;
        this.hometown = hometown;
        this.remarks = remarks;
    }
}

Database Connection Utility

package com.dbutil;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnection {
    private static final String URL = "jdbc:mysql://localhost:3306/student_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false";
    private static final String USER = "root";
    private static final String PASSWORD = "101032";

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            System.out.println("MySQL JDBC Driver loaded successfully.");
        } catch (ClassNotFoundException e) {
            System.err.println("JDBC Driver not found.");
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        try {
            Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
            System.out.println("Database connection established.");
            return conn;
        } catch (SQLException e) {
            System.err.println("Failed to connect to database.");
            e.printStackTrace();
            return null;
        }
    }

    public static void main(String[] args) {
        getConnection(); // Test connection
    }
}

Servlet for Handling Form Submission

package com.servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.dbutil.DatabaseConnection;

public class StudentRegistrationServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");

        String username = request.getParameter("username");
        String password = request.getParameter("password");
        String fullName = request.getParameter("name");
        String studentId = request.getParameter("studentID");
        String gender = request.getParameter("sex");
        String phone = request.getParameter("phone");
        String email = request.getParameter("email");
        String college = request.getParameter("xueyuan");
        String department = request.getParameter("xi");
        String className = request.getParameter("classes");
        String enrollmentYear = request.getParameter("year");
        String hometown = request.getParameter("shengyuandi");
        String remarks = request.getParameter("beizhu");

        String sql = "INSERT INTO students (username, password, full_name, student_id, gender, phone, email, college, department, class_name, enrollment_year, hometown, remarks) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = DatabaseConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            if (conn == null) {
                throw new SQLException("No database connection.");
            }

            stmt.setString(1, username);
            stmt.setString(2, password);
            stmt.setString(3, fullName);
            stmt.setString(4, studentId);
            stmt.setString(5, gender);
            stmt.setString(6, phone);
            stmt.setString(7, email);
            stmt.setString(8, college);
            stmt.setString(9, department);
            stmt.setString(10, className);
            stmt.setString(11, enrollmentYear);
            stmt.setString(12, hometown);
            stmt.setString(13, remarks);

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            // In production, log error and show user-friendly message
        }

        response.sendRedirect(request.getContextPath() + "/success.jsp");
    }
}

Frontend Registration Form (JSP)

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>

<html>
<head>
<meta charset="UTF-8">
<title>Student Registration</title>
<script src="js/jquery-3.3.1.min.js"></script>
</head>
<body>
<div class="form-container">
  <form action="StudentRegistrationServlet" method="post" onsubmit="return validateForm(this)">
    <p><label>Username:</label> <input type="text" name="username" id="username" required></p>
    <p><label>Password:</label> <input type="password" name="password" id="password" required></p>
    <p><label>Full Name:</label> <input type="text" name="name" id="name" required></p>
    <p><label>Student ID:</label> <input type="text" name="studentID" id="studentID" value="2018" required></p>
    <p>
      <label>Gender:</label>
      <input type="radio" name="sex" value="男" id="male"> Male
      <input type="radio" name="sex" value="女" id="female"> Female
    </p>
    <p><label>Phone:</label> <input type="text" name="phone" id="phone" required></p>
    <p><label>Email:</label> <input type="email" name="email" id="email" required></p>
    <p><label>College:</label> <input type="text" name="xueyuan" id="xueyuan"></p>
    <p><label>Department:</label> <input type="text" name="xi" id="xi"></p>
    <p><label>Class:</label> <input type="text" name="classes" id="classes"></p>
    <p>
      <label>Enrollment Year:</label>
      <select name="year">
        <option value="2014">2014</option>
        <option value="2015">2015</option>
        <option value="2016">2016</option>
        <option value="2017">2017</option>
        <option value="2018">2018</option>
        <option value="2019">2019</option>
      </select>
    </p>
    <p><label>Hometown:</label> <input type="text" name="shengyuandi" id="shengyuandi"></p>
    <p><label>Remarks:</label> <input type="text" name="beizhu" id="beizhu"></p>
    <button type="submit">Register</button>
  </form>
</div>

<script>
function validateForm(form) {
    const username = form.username.value;
    if (!/^[a-zA-Z][a-zA-Z0-9_]{5,11}$/.test(username)) {
        alert("Username must be 6–12 characters, start with a letter, and contain only letters, digits, or underscores.");
        form.username.focus();
        return false;
    }

    const password = form.password.value;
    if (!/^[a-zA-Z0-9]{8,}$/.test(password)) {
        alert("Password must be at least 8 characters and contain only letters and digits.");
        form.password.focus();
        return false;
    }

    const studentId = form.studentID.value;
    if (!/^2018\d{4}$/.test(studentId)) {
        alert("Student ID must be 8 digits starting with '2018'.");
        form.studentID.focus();
        return false;
    }

    const phone = form.phone.value;
    if (!/^\d{11}$/.test(phone)) {
        alert("Phone number must be 11 digits.");
        form.phone.focus();
        return false;
    }

    const email = form.email.value;
    const emailRegex = /^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4}$/;
    if (!emailRegex.test(email)) {
        alert("Invalid email format.");
        form.email.focus();
        return false;
    }

    if (!form.sex[0].checked && !form.sex[1].checked) {
        alert("Please select a gender.");
        return false;
    }

    return true;
}
</script>
</body>
</html>

Common Issues and Fixes

The original implemantation failed due to several critical issues:

1. SQL Injection Vulnerability: Used string concatenation instead of PreparedStatement.
2. Field Name Mismatch: JSP used studnetID and clsaaes, but servlet expected different names.
3. Incorrect Redirect: Redirected to /index.jsp even on failure; no error feedback.
4. Weak Validation: JavaScript validation logic had bugs (e.g., incorrect focus target, flawed regex).
5. Resource Leak: Database connection and statement were not properly closed.

The revised version addresses these by using parameterized queries, consistent field naming, robust client-side validation, and proper resource management via try-with-resources.