JDBCDemo 利用JDBC实现登录验证、注册、用户信息插入功能

JDBCDemo

利用JDBC实现登录验证、注册、用户信息插入功能

一、代码结构

二、外部库

• JDBCDemo
  • Referenced Library
    • servlet-api.jar
      D:\apache-tomcat-10.0.6\lib\servlet-api.jar
    • mysql-connector-java-8.0.18.jar
      D:\Program Files\mysql\mysql-connector-java-8.0.18.jar
      

三、代码内容

实现登录验证（包括用户米和密码匹配，用户名是否存在，不存在跳转至注册，将注册信息插入数据库表格）

1.Login.java

• src/main/java
  • com.ahau.cs
    • Login.java

package com.ahau.cs;

import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

@WebServlet("/login")
public class Login extends HttpServlet {
    /**
	 * 
	 */
	private static final long serialVersionUID = 3470009273748787145L;

	@Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 连接数据库
            Connection connection = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1:3306/studentdb?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai",
                    "root", "000112"
            );
            if (connection == null) {
                throw new Exception("Database connection failed");
            }
            System.out.println("Database connection successful");

            // 获取表单中的参数
            String name = request.getParameter("username");
            String pw = request.getParameter("userpw");
            System.out.printf("Username: %s\tPassword: %s\n", name, pw);
            PreparedStatement prepareStatement = connection.prepareStatement(
            		"select * from stutable where stuname = ?"
                    // "select exists(select * from stutable where stuname = ? and stupw = ?)"
            );
            // 为SQL中的stuname = ? and stupw = ?设置值
            prepareStatement.setString(1, name);
            // prepareStatement.setString(2, pw);
            ResultSet results = prepareStatement.executeQuery();
            boolean studentExists = results.next();
            if (studentExists) {
            	// 存在,则判断是否匹配
            	if (!results.getString(3).equals(pw)) {
            		// 不匹配(密码错误）
                    response.getWriter().println("<script>alert('Wrong Password!');window.open('login.jsp','_self')</script>");
            	}
            	// 匹配
            	else {
            		response.getWriter().print("Welcome!\t"+ name);
            	}
            } else {
            	// 不存在,则注册
            	response.getWriter().println("<script>alert('No such Name...loading to register');window.open('register.jsp','_self')</script>"); // 跳转到 register.jsp
            }
            
            results.close();
            prepareStatement.close();
            connection.close();
            
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }
}

2.Register.java

• src/main/java
  • com.ahau.cs
    • Register.java

package com.ahau.cs;

import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

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

@WebServlet("/register")
public class Register extends HttpServlet {
    /**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	@Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("UTF-8");
        response.setCharacterEncoding("UTF-8");
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection connection = DriverManager.getConnection(
                    "jdbc:mysql://127.0.0.1:3306/studentdb?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai",
                    "root", "000112"
            );
            if (connection == null) {
                throw new Exception("Database connection failed");
            }
            System.out.println("Database connection successful");

            String name = request.getParameter("username");
            String pw = request.getParameter("userpw");
            System.out.printf("Username: %s\tPassword: %s\n", name, pw);
            // 注册信息插入数据库
            PreparedStatement prepareStatement = connection.prepareStatement(
                "insert into stutable(stuname, stupw) values(?, ?)"
            );
            prepareStatement.setString(1, name);
            prepareStatement.setString(2, pw);
            prepareStatement.executeUpdate();
            response.getWriter().println("Register Successfully!\t" + name);
            prepareStatement.close();
            connection.close();
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }
}

3.login.jsp

• src/main/
  • webapp
    • login.jsp

4.register.jsp

• src/main/
  • webapp
    • register.jsp

5.web.xml(似乎没用)

• src/main/webapp
  • WEB-INF
    • web.xml

<?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_4_0.xsd" id="WebApp_ID" version="4.0">
  <display-name>JdbcDemo</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
</web-app>

五、数据库部分

• 数据库名：studentdb
  • 表格名：stutable
    字段值