学生管理系统Sql Server+Eclipse（增删改查）

2022-12-18 13:25:55

一、应用名称：STD学生管理系统（Javaweb）

二、使用工具：sql server数据库，Eclipse软件

三、实现的功能：多表的综合运用，学生信息的增删改查、条件查询、模糊查询、精确查询等

四、步骤：

1、新建数据库：student

2、新建表：

①学生表：student（sno，sname,ssex,ssdept,sgrade）

属性含义：（学号，姓名，性别，所在系，年级）

②宿舍表：home(sno,shome)

属性含义：（学号，宿舍号）

create table student(
sno int primary key,
sname varchar(10) not null,
ssex varchar(10),
ssdept varchar(10),
sgrade varchar(10));

create table home(
sno int,
shome varchar(10) not null,
primary key(sno),
foreign key(sno) references student(sno));

3、在eclipse新建web工程并导入连接sql server数据库的jar包：sqljdbc4.jar

4、新建index.jsp，实现加载学生所有信息，以及提供增加信息和查询功能

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<link href="css/bootstrap.min.css" rel="stylesheet">
<title>学生管理系统</title>
</head>
<script type="text/javascript">
    function add() {
        location.href = "add.jsp";
    }
</script>
<style>
.form-control {
    width: 90%;
}
</style>
<body>
    <table class="table table-hover table-striped table-bordered table-sm"
        id="resultshow">
        <tr>
            <td colspan="7" align="center">
                <h1>STD学生管理系统</h1>
            </td>
        </tr>
        <tr>
            <td colspan="3" align="center">
                <button type="button" class="btn btn-info" onclick="add()">添加信息</button>
            </td>
            <td colspan="4" align="center">
                <form action="select.jsp" method="post">
                    <table >
                        <tr>
                            <td><input type="text" class="form-control"
                                name="content"></td>
                            <td><select name="way" class="form-control"  >
                                    <option value="">--请选择查询方式--</option>
                                    <option value="1">学号</option>
                                    <option value="2">性别</option>
                                    <option value="3">所在系</option>
                                    <option value="4">年级</option>
                                    <option value="5">宿舍</option>
                                    <option value="6">姓名</option>
                            </select></td>
                            <td>
                            <button type="submit" class="btn btn-info">查询</button></td>
                        </tr>
                    </table>
                </form>
            </td>
        </tr>
        <tr>
            <td align="center">学号</td>
            <td align="center">姓名</td>
            <td align="center">性别</td>
            <td align="center">所在系</td>
            <td align="center">年级</td>
            <td align="center">宿舍号</td>
            <td align="center">操作</td>
        </tr>
        <%
            String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //加载JDBC驱动  
            String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=student";//连接服务器和数据库sample   
            String userName = "sa"; //默认用户名   
            String userPwd = "123456"; //密码      
            Class.forName(driverName);
            Connection dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
            Statement stmt = dbConn.createStatement();
            String sql = "select student.sno,sname,ssex,ssdept,sgrade,shome from student join home on student.sno=home.sno";
            ResultSet rs = stmt.executeQuery(sql); //执行查询语句
            //从结果集中读取各字段并输出
            while (rs.next()) {
                String id = rs.getString(1);
                out.println("<tr><td align='center'>" + rs.getString(1) + "</td><td align='center'>" + rs.getString(2)
                        + "</td><td align='center'>" + rs.getString(3) + "</td><td align='center'>" + rs.getString(4)
                        + "</td><td align='center'>" + rs.getString(5) + "</td><td align='center'>" + rs.getString(6)
                        + "</td><td align='center'><a href='update.jsp?id=" + id
                        + "'>修改</a>&nbsp;&nbsp;&nbsp;<a href='del.jsp?id=" + id
                        + "' onclick = 'return del()'>删除</a></td></tr>");
            }
            //关闭操作
            rs.close();
            stmt.close();
            dbConn.close();
        %>
    </table>
    <script>
        //删除确认
        function del() {
            if (!window.confirm('是否要删除数据??'))
                return false;
        }
    </script>
</body>
</html>

index.jsp

5、add.jsp以及addsave.jsp用来保存学生基本信息

<%@ page contentType="text/html; charset=utf-8" import="java.sql.*"%>
<html>
<head>
<meta charset="UTF-8">
<title>添加学生信息</title>
</head>
<link rel="stylesheet"
    href="https://cdn.staticfile.org/twitter-bootstrap/4.3.1/css/bootstrap.min.css">
<style>
.container {
    width: 100%;
    height: 700px;
    position: relative;
    background: url("img/2.png") no-repeat center/cover;
}

.login {
    width: 500px;
    height: auto;
    background-color: white;
    position: absolute;
    top: 100px;
    left: 350px;
    border-radius: 8px;
}

label {
    float: left;
    width: 100px;
    margin-top: 7px;
    margin-right: 5px;
}

.form-control {
    width: 60%;
}

.logtip {
    padding-top: 20px;
    padding-bottom: 20px;
    border-bottom: 2px solid red;
    text-align: center;
}

.form-group {
    margin-left: 40px;
    margin-top: 20px;
}

.btn {
    height: 50px;
    width: 100px;
    float: left;
    border-radius: 10px;
}

.btnbag {
    margin-left: 200px;
    margin-right: 200px;
    height: 50px;
    overflow: hidden;
    margin-top: 30px;
    margin-bottom: 40px;
}
</style>
<body>
    <div class="container">
        <div class="login">
            <h2 class="logtip">学生信息</h2>
            <form action="addsave.jsp" method="post" onsubmit="return check()">
                <div class="form-group">
                    <label for="sno">学号</label> <input type="text"
                        class="form-control" id="sno" name="sno">
                </div>
                <div class="form-group">
                    <label for="sname">姓名</label> <input type="text"
                        class="form-control" id="sname" name="sname">
                </div>
                <div class="form-group">
                    <label for="ssex">性别</label> <select name="ssex" id="ssex"  class="form-control" >   
                          <option value="">--请选择--</option>   
                          <option value="男">男</option>   
                          <option value="女">女</option>   
                      </select>
                </div>
                <div class="form-group">
                    <label for="ssdept">所在系</label> <input type="text"
                        class="form-control" id="ssdept" name="ssdept">
                </div>
                <div class="form-group">
                    <label for="sgrade">年级</label> <select name="sgrade" id="sgrade"  class="form-control" >   
                          <option value="">--请选择--</option>   
                          <option value="2017级">2017级</option>   
                          <option value="2018级">2018级</option>   
                          <option value="2019级">2019级</option> 
                          <option value="2020级">2020级</option>     
                      </select>
                </div>
                <div class="btnbag">
                    <input type="submit" class="btn btn-primary logbtn" value="确定">
                </div>
            </form>
        </div>
    </div>
</body>
<script type="text/javascript">
    function check() {
        var sno = document.getElementById("sno").value;
        var sname = document.getElementById("sname").value;
        if ((sno == "") || (sname == "")) {
            alert("学号/姓名必填!");
            return false;
        }
        return true;
    }
</script>
</html>

add.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<html>
<head>
<title>添加学生信息</title>
</head>
<body>
    <%
        request.setCharacterEncoding("utf-8");
        String sno = request.getParameter("sno");
        session.setAttribute("sno", sno);
        String sname = request.getParameter("sname");
        String ssex = request.getParameter("ssex");
        String ssdept = request.getParameter("ssdept");
        String sgrade = request.getParameter("sgrade");
        String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //加载JDBC驱动  
        String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=student";//连接服务器和数据库sample   
        String userName = "sa"; //默认用户名   
        String userPwd = "123456"; //密码      
        Class.forName(driverName);
        Connection dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
        String sql = "insert into student(sno,sname,ssex,ssdept,sgrade) values(?,?,?,?,?)";
        PreparedStatement stmt = dbConn.prepareStatement(sql);
        stmt.setString(1, sno);
        stmt.setString(2, sname);
        stmt.setString(3, ssex);
        stmt.setString(4, ssdept);
        stmt.setString(5, sgrade);
        int i = stmt.executeUpdate();
        if (i == 1) {
            out.println("<script>location.href = 'addhome.jsp';</script>");

        } else {
            out.println("<script>alert('添加失败');location.href = 'add.jsp';</script>");
        }
        stmt.close();
        dbConn.close();
    %>
</body>
</html>

addsave.jsp

6、addhome.jsp以及addhomesave.jsp用来保存学生宿舍信息

<%@ page contentType="text/html; charset=utf-8" import="java.sql.*"%>
<html>
<head>
<meta charset="UTF-8">
<title>添加学生信息</title>
</head>
<link rel="stylesheet"
    href="https://cdn.staticfile.org/twitter-bootstrap/4.3.1/css/bootstrap.min.css">
<style>
.container {
    width: 100%;
    height: 700px;
    position: relative;
    background: url("img/2.png") no-repeat center/cover;
}

.login {
    width: 500px;
    height: auto;
    background-color: white;
    position: absolute;
    top: 100px;
    left: 350px;
    border-radius: 8px;
}

label {
    float: left;
    width: 100px;
    margin-top: 7px;
    margin-right: 5px;
}

.form-control {
    width: 60%;
}

.logtip {
    padding-top: 20px;
    padding-bottom: 20px;
    border-bottom: 2px solid red;
    text-align: center;
}

.form-group {
    margin-left: 40px;
    margin-top: 20px;
}

.btn {
    height: 50px;
    width: 100px;
    float: left;
    border-radius: 10px;
}

.btnbag {
    margin-left: 200px;
    margin-right: 200px;
    height: 50px;
    overflow: hidden;
    margin-top: 30px;
    margin-bottom: 40px;
}
</style>
<body>
    <div class="container">
        <div class="login">
            <h2 class="logtip">宿舍</h2>
            <form action="addhomesave.jsp" method="post" onsubmit="return check()">
                <div class="form-group">
                    <label for="sno">学号</label> <input type="text"
                        class="form-control" id="sno" name="sno" value=<%=(String)(session.getAttribute("sno"))%> readonly="readonly">
                </div>
                    <div class="form-group">
                    <label for="shome">宿舍</label> <input type="text"
                        class="form-control" id="shome" name="shome" placeholder="1栋001">
                </div>
                <div class="btnbag">
                    <input type="submit" class="btn btn-primary logbtn" value="确定">
                </div>
            </form>
        </div>
    </div>
</body>
<script type="text/javascript">
    function check() {
        var shome = document.getElementById("shome").value;
        if (shome == "") {
            alert("宿舍必填!");
            return false;
        }
        return true;
    }
</script>
</html>

addhome.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<html>
<head>
<title>添加学生信息</title>
</head>
<body>
    <%
        request.setCharacterEncoding("utf-8");
        String sno = request.getParameter("sno");
        String shome = request.getParameter("shome");
        String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //加载JDBC驱动  
        String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=student";//连接服务器和数据库sample   
        String userName = "sa"; //默认用户名   
        String userPwd = "123456"; //密码      
        Class.forName(driverName);
        Connection dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
        String sql = "insert into home(sno,shome) values(?,?)";
        PreparedStatement stmt = dbConn.prepareStatement(sql);
        stmt.setString(1, sno);
        stmt.setString(2, shome);
        int i = stmt.executeUpdate();
        if (i == 1) {
            out.println("<script>alert('添加成功');location.href = 'index.jsp';</script>");

        } else {
            out.println("<script>alert('添加失败');location.href = 'add.jsp';</script>");
        }
        stmt.close();
        dbConn.close();
    %>
</body>
</html>

addhomesave.jsp

7、update.jsp以及updatesave.jsp实现信息的修改

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<html>
<head>
<meta charset="UTF-8">
<title>修改学生信息</title>
</head>
<link rel="stylesheet"
    href="https://cdn.staticfile.org/twitter-bootstrap/4.3.1/css/bootstrap.min.css">
<style>
.container {
    width: 100%;
    height: 700px;
    position: relative;
    background: url("img/2.png") no-repeat center/cover;
}

.login {
    width: 500px;
    height: auto;
    background-color: white;
    position: absolute;
    top: 100px;
    left: 350px;
    border-radius: 8px;
}

label {
    float: left;
    width: 100px;
    margin-top: 7px;
    margin-right: 5px;
}

.form-control {
    width: 60%;
}

.logtip {
    padding-top: 20px;
    padding-bottom: 20px;
    border-bottom: 2px solid red;
    text-align: center;
}

.form-group {
    margin-left: 40px;
    margin-top: 20px;
}

.btn {
    height: 50px;
    width: 100px;
    float: left;
    border-radius: 10px;
}

.logbtn {
    margin-right: 20px;
}

.btnbag {
    margin-left: 140px;
    margin-right: 140px;
    height: 50px;
    overflow: hidden;
    margin-top: 30px;
    margin-bottom: 40px;
}
</style>

<body>
    <%
        String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //加载JDBC驱动  
        String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=student";//连接服务器和数据库sample   
        String userName = "sa"; //默认用户名   
        String userPwd = "123456"; //密码      
        Class.forName(driverName);
        Connection dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
        Statement stmt = dbConn.createStatement();
        String sql = "select student.sno,sname,ssdept,shome from student join home on student.sno=home.sno";
        ResultSet rs = stmt.executeQuery(sql); //执行查询语句
        rs.next();
    %>
    <div class="container">
        <div class="login">
            <h2 class="logtip">修改信息</h2>
            <form action="updatesave.jsp" method="post" onsubmit="return check()">
                <div class="form-group">
                    <label for="sno">学号</label> <input type="text" class="form-control"
                        id="sno" name="sno" value="<%=rs.getString(1)%>" readonly="readonly">
                </div>
                <div class="form-group">
                    <label for="sname">姓名</label> <input type="text"
                        class="form-control" id="sname" name="sname" value="<%=rs.getString(2)%>">
                </div>
                <div class="form-group">
                    <label for="ssex">性别</label> <select name="ssex" id="ssex"
                        class="form-control">
                        <option value="">--请选择--</option>
                        <option value="男">男</option>
                        <option value="女">女</option>
                    </select>
                </div>
                <div class="form-group">
                    <label for="ssdept">所在系</label> <input type="text"
                        class="form-control" id="ssdept" name="ssdept" value="<%=rs.getString(3)%>">
                </div>
                <div class="form-group">
                    <label for="sgrade">年级</label> <select name="sgrade" id="sgrade"
                        class="form-control">
                        <option value="">--请选择--</option>
                        <option value="2017级">2017级</option>
                        <option value="2018级">2018级</option>
                        <option value="2019级">2019级</option>
                        <option value="2020级">2020级</option>
                    </select>
                </div>
                <div class="form-group">
                    <label for="shome">宿舍</label> <input type="text"
                        class="form-control" id="shome" name="shome"  value="<%=rs.getString(4)%>" readonly="readonly">
                </div>
                <div class="btnbag">
                    <input type="submit" class="btn btn-primary logbtn" value="确定">
                    <input type="reset" class="btn btn-primary mangbtn" value="重置">
                </div>
            </form>
        </div>
    </div>
</body>
<script type="text/javascript">
    function check() {
        var sno = document.getElementById("sno").value;
        var sname = document.getElementById("sname").value;
        if ((sno == "") || (sname == "")) {
            alert("学号/姓名必填!");
            return false;
        }
        return true;
    }
</script>
</html>

update.jsp

<%@ page language="java" import="java.sql.*" pageEncoding="utf-8"%>
<html>
<head>
<title>修改完成</title>
<link rel="stylesheet" type="text/css" href="css/style.css">
</head>
<body>
    <%
    request.setCharacterEncoding("utf-8");
    String sno = request.getParameter("sno");
    String sname = request.getParameter("sname");
    String ssex = request.getParameter("ssex");
    String ssdept = request.getParameter("ssdept");
    String sgrade = request.getParameter("sgrade");
    String shome = request.getParameter("shome");
    String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //加载JDBC驱动  
    String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=student";//连接服务器和数据库sample   
    String userName = "sa"; //默认用户名   
    String userPwd = "123456"; //密码      
    Class.forName(driverName);
    Connection dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
        String sql = "update student set sname='" + sname + "',ssex='" + ssex + "',ssdept='" + ssdept + "',sgrade='" + sgrade
                + "' where sno=" + sno;
        PreparedStatement stmt = dbConn.prepareStatement(sql);
        int i = stmt.executeUpdate();
        if (i == 1) {
            out.println("<script>alert('修改成功');location.href = 'index.jsp';</script>");
        } else {
            out.println("<script>alert('修改失败');location.href = 'update.jsp?id='"+sno+";</script>");
        }
        stmt.close();
        dbConn.close();
    %>
</body>
</html>

updatesave.jsp

8、del.jsp实现删除功能

<%@ page contentType="text/html; charset=utf-8" language="java"
    import="java.sql.*" pageEncoding="utf-8"%>
<html>
<head>
<title>删除学生信息</title>
<link rel="stylesheet" type="text/css" href="css/style.css">
</head>
<body>
    <%
        request.setCharacterEncoding("utf-8");
        String id = request.getParameter("id");
        String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver"; //加载JDBC驱动  
        String dbURL = "jdbc:sqlserver://localhost:1433; DatabaseName=student";//连接服务器和数据库sample   
        String userName = "sa"; //默认用户名   
        String userPwd = "123456"; //密码
        Class.forName(driverName);
        Connection dbConn = DriverManager.getConnection(dbURL, userName, userPwd);
        String sql = "delete from home from home JOIN student on home.sno=student.sno where home.sno="+id;
//         out.println(sql);
        PreparedStatement stmt = dbConn.prepareStatement(sql);
        int i = stmt.executeUpdate();
        if (i == 1) {
            out.println("<script>alert('删除成功');;location.href = 'index.jsp';</script>");
        } else {
            out.println("<script>alert('删除成功');;location.href = 'index.jsp';</script>");
        }
        stmt.close();
        dbConn.close();
    %>
</body>
</html>

del.jsp

五、运行截图

index.jsp