JDBC+HTML+AJAX实现登陆和单表的CRUD

JDBC+HTML+AJAX实现登陆和单表的CRUD

导入maven依赖

  
  4.0.0  
  com.nie  
  Test  
  1.0-SNAPSHOT  
  war
   
    17  
    17  
    UTF-8 
  
  
    
      jakarta.servlet
      jakarta.servlet-api
      6.0.0
      provided
    
    
      mysql
      mysql-connector-java
      8.0.33
    
    
      com.google.code.gson
      gson
      2.10.1
    
  

登陆页面

    
    用户登录
    
    
        .container {
            width: 300px;
            margin: 100px auto;
            padding: 20px;
            border: 1px solid #ccc;
            border-radius: 5px;
        }
        .form-group {
            margin-bottom: 15px;
        }
        .form-group label {
            display: block;
            margin-bottom: 5px;
        }
        .form-group input {
            width: 100%;
            padding: 8px;
            border: 1px solid #ddd;
            border-radius: 4px;
        }
        .btn-login {
            background-color: #007bff;
            color: white;
            padding: 10px 20px;
            border: none;
            border-radius: 4px;
            cursor: pointer;
        }
        .error-message {
            color: red;
            margin-top: 10px;
            display: none;
        }
    



    
        

            用户名：
            
        
        

            密码：
            
        
        登录
    
    




    $(document).ready(function () {
        $("#loginForm").submit(function () {
            var username = $("#username").val();
            var password = $("#password").val();
            $.ajax({
                type: "POST",
                url: "/login",
                dataType: "text",
                data: { username: username, password: password },
                success: function (response) {
                    if (response === "success") {
                        window.location.href = "Test1.html";
                    }
                }
            })
        })
    })

 

学生信息页面

    
    
    
    学生信息表
    




    
    查询姓名
    查询所有
    
学号
            
姓名
            
性别
            
邮箱
        

    学号:
    姓名:
    性别:
    邮箱:

    新增数据


    function deleteStudent(id) {
        $.ajax({
            url: '/delete',
            type: "POST",
            dataType: "text",
            data: { id: id },
            success: function (data) {
                if (data === "success") {
                    alert("数据删除成功！");
                    $("#all").click()
                } else {
                    alert("删除数据失败!");
                }
            },
            error: function () {
                alert("删除数据失败!");
            }
        })
    }
    function updateStudent(id) {
        let id1=document.getElementById("id").value
        let name=document.getElementById("name").value
        let sex=document.getElementById("sex").value
        let email=document.getElementById("email").value
        $.ajax({
            url: '/update',
            type: "POST",
            dataType: "text",
            data: { id: id ,id1:id1,name:name,sex:sex,email:email},
            success: function (data) {
                if (data === "success") {
                    alert("数据修改成功！");
                    $("#all").click()
                } else {
                    alert("数据修改失败!");
                }
            },
            error: function () {
                alert("数据修改失败!");
            }
        })
    }
    $(document).ready(function () {
        $("#all").click(function () {
            $.ajax({
                url: './api/students',
                type: "GET",
                dataType: "json",
                success: function (data) {
                    console.log("Received data:", data);
                    var html = "";
                    for (var i = 0; i " +
                            "" + ls.id + "" +
                            "" + ls.name + "" +
                            "" + ls.sex + "" +
                            "" + ls.email + "" +
                            "" +
                            "删除" +
                            "修改" +
                            "" +
                            "";
                    }
                    $("#test").html(html);
                },
                error: function (xhr, status, error) {
                    console.error("Error details:", {
                        status: xhr.status,
                        statusText: xhr.statusText,
                        responseText: xhr.responseText
                    });
                    alert("获取数据失败，请检查控制台错误信息");
                }
            });
        });
        $("#chaxun").click(function () {
            var names = document.getElementById("names").value;
            $.ajax({
                url: './api/studentsByName',
                type: "GET",
                dataType: "json",
                data: { name: names },
                success: function (data) {
                    console.log("Received data:", data);
                    var html = "";
                    for (var i = 0; i " +
                            "" + ls.id + "" +
                            "" + ls.name + "" +
                            "" + ls.sex + "" +
                            "" + ls.email + "" +
                            "";
                    }
                    $("#test").html(html);
                },
                error: function () {
                    alert("获取数据失败");
                }
            });
        });
        $("#insert").click(function (){
            var id = document.getElementById("id").value; // 获取输入框的值
            var name = document.getElementById("name").value; // 获取输入框的值
            var sex = document.getElementById("sex").value; // 获取输入框的值
            var email = document.getElementById("email").value; // 获取输入框的值
            $.ajax({
                type: "POST",
                url: '/insert',
                dataType: "text",
                data: { id: id, name: name, sex: sex, email: email },
                success: function (response) {
                    if (response === "success") {
                        alert("数据新增成功！");
                        $("#all").click();
                    } else {
                        alert("新增数据失败!");
                    }
                },
                error: function () {
                    alert("新增数据失败!");
                }
            });
        });
    });

JDBC连接数据库

package com.nie.utils;
import java.sql.*;
public class GetConnection {
    private static String url="jdbc:mysql://127.0.0.1:3306/student";
    private static String user="root";
    private static String password="123456";
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }
    public void close(Connection conn, PreparedStatement stmt, ResultSet rs) throws SQLException {
        conn.close();
        stmt.close();
        rs.close();
    }
}

登陆实体类

package com.nie.pojo;
public class loginStudent {
    private String username;
    private String password;
    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;
    }
}

学生实体类

package com.nie.pojo;
public class student {
    private String id;
    private String name;
    private String sex;
    private String email;
    public String getId() {
        return id;
    }
    public void setId(String 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 age) {
        this.sex = age;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    @Override
    public String toString() {
        return "student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age='" + sex + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

dao层

package com.nie.dao;
import com.nie.pojo.loginStudent;
import com.nie.pojo.student;
import com.nie.utils.GetConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class StudentDao {
    static Connection conn = null;
    static PreparedStatement pstmt = null;
    static ResultSet rs = null;
    public static List selectAll() throws SQLException {
        conn= GetConnection.getConnection();
        List list = new ArrayList();
        pstmt=conn.prepareStatement("select * from student");
        rs=pstmt.executeQuery();
        while (rs.next()) {
            student student = new student();
            student.setId(rs.getString("id"));
            student.setName(rs.getString("name"));
            student.setSex(rs.getString("sex"));
            student.setEmail(rs.getString("email"));
            list.add(student);
        }
        return list;
    }
    public static List selectByName(String name) throws SQLException {
        conn=GetConnection.getConnection();
        pstmt=conn.prepareStatement("select  * from student where name = ?");
        pstmt.setString(1, name);
        rs=pstmt.executeQuery();
        List list=new ArrayList();
        while (rs.next()) {
            student student = new student();
            student.setId(rs.getString("id"));
            student.setName(rs.getString("name"));
            student.setSex(rs.getString("sex"));
            student.setEmail(rs.getString("email"));
            list.add(student);
        }
        return list;
    }
    public static int insertStudent(student student) throws SQLException {
        conn=GetConnection.getConnection();
        pstmt=conn.prepareStatement("insert into student (id,name,sex,email) values(?,?,?,?)");
        pstmt.setString(1,student.getId());
        pstmt.setString(2,student.getName());
        pstmt.setString(3,student.getSex());
        pstmt.setString(4,student.getEmail());
        int i = pstmt.executeUpdate();
        return i;
    }
    public static List  selectslogin(loginStudent loginStudent) throws SQLException {
        conn=GetConnection.getConnection();
        pstmt=conn.prepareStatement("select * from login where username = ? and password = ?");
        pstmt.setString(1,loginStudent.getUsername());
        pstmt.setString(2,loginStudent.getPassword());
        rs=pstmt.executeQuery();
        List list=new ArrayList();
        if(rs.next()) {
            loginStudent login = new loginStudent();
            login.setUsername(rs.getString("username"));
            login.setPassword(rs.getString("password"));
            list.add(login);
        }
        return list;
    }
    public static int DeleteStudent(String id) throws SQLException {
        conn=GetConnection.getConnection();
        pstmt=conn.prepareStatement("delete from student where id = ?");
        pstmt.setString(1,id);
        int i = pstmt.executeUpdate();
        return i;
    }
    public static int UpdateStudent(String id,String id1,String name,String sex,String email) throws SQLException {
        conn=GetConnection.getConnection();
        pstmt=conn.prepareStatement("update student set id=?,name=?,sex=?,email=? where id=?");
        pstmt.setString(1,id1);
        pstmt.setString(2,name);
        pstmt.setString(3,sex);
        pstmt.setString(4,email);
        pstmt.setString(5,id);
        int i = pstmt.executeUpdate();
        return i;
    }
}

业务层

删除

package com.nie.server;
import com.nie.dao.StudentDao;
import com.sun.net.httpserver.HttpsServer;
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.SQLException;
@WebServlet("/delete")
public class DeleteStudent extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req,resp);
    }
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String id = req.getParameter("id");
        try {
            int i = StudentDao.DeleteStudent(id);
            if(i>0){
                resp.getWriter().write("success");
            }else {
                resp.getWriter().write("fail");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

添加

package com.nie.server;
import com.nie.dao.StudentDao;
import com.nie.pojo.student;
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.SQLException;
@WebServlet("/insert")
public class InsertStudent extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req, resp);
    }
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String id = req.getParameter("id");
        String name = req.getParameter("name");
        String sex = req.getParameter("sex");
        String email = req.getParameter("email");
        student student = new student();
        student.setId(id);
        student.setName(name);
        student.setSex(sex);
        student.setEmail(email);
        try {
            int i = StudentDao.insertStudent(student);
            if (i > 0) {
                resp.getWriter().write("success");
            } else {
                resp.getWriter().write("fail");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            resp.getWriter().write("fail");
        }
    }
}

登陆

package com.nie.server;
import com.nie.dao.StudentDao;
import com.nie.pojo.loginStudent;
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.io.PrintWriter;
import java.sql.SQLException;
import java.util.List;
@WebServlet("/login")
public class loginServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String username = req.getParameter("username");
        String password = req.getParameter("password");
        loginStudent loginStudent = new loginStudent();
        loginStudent.setUsername(username);
        loginStudent.setPassword(password);
        try {
            List selectslogin = StudentDao.selectslogin(loginStudent);
            if(selectslogin.size()>0) {
                resp.getWriter().write("success");
            }else {
                resp.getWriter().write("fail");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req, resp);
    }
}

查询

package com.nie.server;
import com.google.gson.Gson;
import com.nie.dao.StudentDao;
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.SQLException;
@WebServlet("/api/students")
public class ServletStudent extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String name = req.getParameter("name");
        Gson gson = new Gson();
        String jsonResponse = null;
        try {
            if (name != null){
                jsonResponse = gson.toJson(StudentDao.selectByName(name));
            }else {
                jsonResponse = gson.toJson(StudentDao.selectAll());
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        // 设置响应头，确保返回JSON
        resp.setContentType("application/json");
        resp.setCharacterEncoding("UTF-8");
        // 添加CORS头，允许跨域访问（如果需要的话）
        resp.setHeader("Access-Control-Allow-Origin", "*");
        resp.getWriter().write(jsonResponse);
    }
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req,resp);
    }
}

根据姓名查询

package com.nie.server;
import com.google.gson.Gson;
import com.nie.dao.StudentDao;
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.SQLException;
@WebServlet("/api/studentsByName")
public class ServletStudentByName extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String name = req.getParameter("name");
        Gson gson = new Gson();
        String jsonResponse = null;
        try {
            jsonResponse = gson.toJson(StudentDao.selectByName(name));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        // 设置响应头，确保返回JSON
        resp.setContentType("application/json");
        resp.setCharacterEncoding("UTF-8");
        // 添加CORS头，允许跨域访问（如果需要的话）
        resp.setHeader("Access-Control-Allow-Origin", "*");
        resp.getWriter().write(jsonResponse);
    }
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req,resp);
    }
}

修改

package com.nie.server;
import com.nie.dao.StudentDao;
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.SQLException;
@WebServlet("/update")
public class UpdateStudent extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req,resp);
    }
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String id = req.getParameter("id");
        String id1 = req.getParameter("id1");
        String name = req.getParameter("name");
        String sex = req.getParameter("sex");
        String email = req.getParameter("email");
        try {
            int i = StudentDao.UpdateStudent(id, id1, name, sex, email);
            if (i>0) {
                resp.getWriter().write("success");
            } else {
                resp.getWriter().write("fail");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

最终效果: