一、应用名称: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> <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>
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>
<%@ 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>
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>
<%@ 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>
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>
<%@ 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>
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>
五、运行截图
index.jsp
添加信息
查询通过宿舍号模糊查询
修改