学生成绩管理系统

基于Jsp+sql server数据库的学生成绩管理系统需求分析学生成绩管理系统需求服务对象为在校学生和在校交工校教工. 整体分为学生模块和教师模块.学生模块需求 :1:学生个体通过学号登录系统.2:网页页面显示学生信息(学号,姓名,成绩,年龄,新版)性别,联系方式,居住地)3:学生个体可以修改自己的信息(姓名,性别,年龄,)龄,联系方式,居住地)4:学生无删除与增加自身信息的权限.教师模块需求

libingbojava

13704人浏览 · 2018-06-02 13:58:58

libingbojava · 2018-06-02 13:58:58 发布

基于Jsp+sql server数据库的学生成绩管理系统

需求分析

学生成绩管理系统需求服务对象为在校学生和在校交工
校教工. 整体分为学生模块和教师模块.

学生模块需求 :
1:学生个体通过学号登录系统.

2:网页页面显示学生信息(学号,姓名,成绩,年龄,新版)
性别,联系方式,居住地)
3:学生个体可以修改自己的信息(姓名,性别,年龄,)
龄,联系方式,居住地)
4:学生无删除与增加自身信息的权限.
教师模块需求:

1:教师个体通过教工号登陆系统.
2:教师选择课程后查看所带班全部学生全部信息
3:教师登陆后允许修改学生成绩
4:教师可增加学生个体信息到系统表中
5:教师无删除学生个体信息权限

项目构思

设计学生信息表,包含字段如下:

(学号,课程号,姓名,成绩,年龄,性别,联系方式,居住地)

住地)

设计教工表,包含字段如下:

(课程号,教工号,课程名,教工姓名,职称)

registerForward.jsp判断用户身份

studentDispose.jsp显示学生信息

studentModification.jsp修改学生信息

teacherClass.jsp查找教师所带课程

Showinfo.jsp显示教师所带班级学生信息

modificationScore.jsp显示要修改的学生成绩

finallyScoreModification.jsp修改成绩

addStudentInfo.jsp增添学生用户界面

finallAddinfo.jsp添加学生成绩

详细设计

学生信息表:

(s# , c# ,sname , score,age,sex,telephone,home)

教工表:

(c#,cname,title,t#,tname)

分设两个入口:教师入口和学生入口

教师入口由教师工号字段进入

学生入口由学生学号字段进入

RegisterForward.jsp功能:

接收由register.html提交的字段,并依submit的value值判断提交按钮,并依此刷新到不同界面

studentDispose.jap功能:(由registerForward.jsp

跳转)

链接数据库学生信息表,查询字段等于s#的元组,

将其显示在网页上,并设有修改按钮(提交至stud

entModification.jsp),查询后关闭数据库

studentModification.jsp功能:

接收由studentDispose.jsp传入的元组所有字段,

链接数据库,查询到对应s#,修改学生信息表属性

值,显示修改成功或失败,返回登录入口.

teacherClass.jsp功能:

为教师提供课程选择功能.

链接数据库教工表,查询t#等于字段的所有c#,

页面显示教工的tname,title,并设有下拉列表框,

显示cname,设进入按钮,跳转页面并提交cname

字段.关闭数据库.

ShowInfo.jsp功能:

接收cname字段.链接数据库教工表,由cname字

段匹配c#,查询学生信息表,匹配c#字段,页面显

示对应行记录,标头显示tname,title,cname,行信

息右侧显示设修改超链接(超链接传入s#)

底部设有增加学生信息超链接.

Modification.jsp功能:

接收S#字段,链接数据库学生信息表,读取匹配s

#的字段行,页面显示s#,sname,score,设有修改按

钮.关闭数据库.

finallyScoreModification.jsp功能:

接收由modificationscore.jsp传入的s#,score.

链接数据库学生信息表,匹配s#,更新score,页面

显示修改成功或失败.

addStudentInfo.html功能:

学生信息填入表单(接收cname,由showinfo提交)

用以录入学生信息,设提交按钮.

finallAddInfo.jsp功能:

接收addStudentInfo.html的字段.

链接数据库教工表,由cname查询c#.

将接收的字段与c#作为记录插入到学生信息表

中,显示插入成功或失败.

关闭数据库.5秒后跳转到showInfo页面.

代码部分:

<%@ page  pageEncoding="UTF-8" %>
<html>
<head><title>登录入口</title>
<script type="text/javascript" src="registerForward.js"></script>
</head>
<body>
<form   name="form1" method="post"   action=""   >
<table  align="center"  width="300" height="100">
	<tr><th>工号:</th><td><input type="text"  name="jobNumber" ></td></tr>
	<tr><th colspan="2"><input  type="submit"  value="教师入口"  onClick="return teacherCheck()" />
	<input type="submit"  value="学生入口"  onClick="return studentCheck()" /></th></tr>
</table>
</form>
</body>
</html>

registerForward.js

function teacherCheck()
{
	
	if(document.form1.jobNumber.value=='')
	{
		alert("工号不能为空!");
		return false;
	}
	
	document.form1.mehtod="post";
	document.form1.action="teacherClass.jsp";
	document.form1.submit();
	return true; 
}

function studentCheck()
{
	if(document.form1.jobNumber.value=='')
	{
		alert("工号不能为空!");
		return false;
	}
	
	document.form1.action="studentDispose.jsp";
	document.form1.submit();

}

studentDispose.jsp

<%@ page  pageEncoding="UTF-8"  import ="java.sql.*"  import="java.util.*" %>
<html>
<head><title>显示学生信息</title></head>
<body>
	<%! String sNumber , sname , sex ,telephone , home ; int age ,score;%>
	<%
		String jobNumber = request.getParameter("jobNumber");
		Connection con;
		PreparedStatement pstmt;
		ResultSet rs=null;
		String sql=null;
		String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		String url = "jdbc:sqlserver://localhost:1433;DatabaseName=LCBCom";
		String user = "sa";
		String userPassword = "123";
		Class.forName(driver);
		con = DriverManager.getConnection(url , user , userPassword);
		sql="select s# , sname , score,age,sex,telephone,home  from S  where s#=? ";
		pstmt = con.prepareStatement(sql);
		pstmt.setString(1 , jobNumber);
		rs = pstmt.executeQuery();
		if(rs.next())
		{ 

		  sNumber= rs.getString("s#");   //String 是 s#报错重复的变量s# , 为什么?
		  sname = rs.getString("sname");
		  score = rs.getInt("score");
		  age = rs.getInt("age");
		  sex = rs.getString("sex");
		  telephone = rs.getString("telephone");
		  home  = rs.getString("home");
		if(sex.equals("M"))
		{
			sex="男";
		}
		else
		{
			sex="女";
		}
		rs.close();
		pstmt.close();
		con.close();

		//将学生用户信息存储在session中
		session.setAttribute("sNumber" ,  sNumber );  //直接写变量,不用放在表达式里
		session.setAttribute("sname"  , sname );
		session.setAttribute("score"  , score );
		session.setAttribute("age"  , age );
		session.setAttribute("sex" , sex );
		session.setAttribute("telephone" ,   telephone);
		session.setAttribute("home" ,  home );
	}
	else{


	%>
	<jsp:forward page="numberError.jsp" >
		<jsp:param name="jobNumber" value="<%= jobNumber %>"  />
	</jsp:forward>
	<%
		}
	%>
	<h2 align="center">你的信息如下:</h2>
	<form   method="post" name=form2 action="studentModification.jsp"  >
	<table  align="center">
		<tr><th>学号:</th><td><%= sNumber %></td></tr>
		<tr><th>姓名:</th><td><%= sname  %></td></tr>
		<tr><th>年龄:</th><td><%= age %></td></tr>
		<tr><th>成绩:</th><td><%= score %></td></tr>
		<tr><th>性别:</th><td><%= sex  %></td></tr>
		<tr><th>联系方式:</th><td><%= telephone %></td></tr>
		<tr><th>居住地:</th><td><%= home %></td></tr>
		<tr><th>修改个人信息:</th><td><input type="submit" value="提交" ></td></tr> 
</body>
</html>

numberError.jsp

<%@ page  pageEncoding="UTF-8" %>
<html>
<body>
<%
	String errorNumber = request.getParameter("jobNumber");
%>
<p>无法识别学号<%= errorNumber %>,请检查学号是否输入正确!</p>
<p>5秒后将返回登录界面</p>
<% response.setHeader("Refresh"   , "5;url=register.jsp");  %>
</body>
</html>

studentModification.jsp

<%@ page  pageEncoding="UTF-8"   import ="java.sql.*"  import="java.util.*" %>
<html>
<body>
	<%
		session = request.getSession();
		String sNumber = (String)session.getAttribute("sNumber");
		String sname  = (String)session.getAttribute("sname");
		Object o;
		o  = session.getAttribute("score");
		String score = o.toString();
		o = session.getAttribute("age");
		String age = o.toString();
		String sex = (String)session.getAttribute("sex");
		String telephone  = (String)session.getAttribute("telephone");
		String home  = (String)session.getAttribute("home");

		if(sex.equals("男"))
		{
			sex = "M";
		}
		else
		{
			sex = "W";
		}
		Connection con;
		PreparedStatement pstmt;

		String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		String url = "jdbc:sqlserver://localhost:1433;DatabaseName=LCBCom";
		String user = "sa";
		String userPassword = "123";
		Class.forName(driver);
		con = DriverManager.getConnection(url , user , userPassword);

		String sql = "update S  set s#=? , sname = ? , score = ? , age = ? , sex = ? , telephone = ? , home = ?  where s# = ? ";
		pstmt = con.prepareStatement(sql);
		pstmt.setString(1 , sNumber);
		pstmt.setString(2 , sname);
		pstmt.setInt(3 , Integer.parseInt(score));
		pstmt.setInt(4 , Integer.parseInt(age));
		pstmt.setString(5 , sex);
		pstmt.setString(6 , telephone);
		pstmt.setString(7 , home);
		pstmt.setString(8 , sNumber);
		pstmt.executeUpdate();

		pstmt.close();
		con.close();

		
	%>
	<p>数据更新完成,将在5秒后返回主界面</p>
	<% response.setHeader("Refresh" , "5;url=register.jsp"); %>
	<p>已更新的数据如下:</p>
	<p>成绩:<%= score %></p>
	<p>姓名:<%= sname  %></p>
	<p>学号:<%= sNumber %></p>
	<p>性别:<%= sex %></p>
	<p>年龄:<%= age %></p>
	<p>联系方式:<%= telephone %></p>
	<p>居住地:<%= home %></p>
</body>
</html>

teacherClass.jsp

<%@ page  pageEncoding="UTF-8"  import ="java.sql.*"  import="java.util.*"  %>
<html>
<body>
	<%! String tname , title; %>
	<%
		String jobNumber = request.getParameter("jobNumber");
		jobNumber.trim();
		session.setAttribute("t#" ,jobNumber);
		Connection con;
		PreparedStatement pstmt;
		ResultSet rs=null;
		String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		String url = "jdbc:sqlserver://localhost:1433;DatabaseName=LCBCom";
		String user = "sa";
		String userPassword = "123";
		Class.forName(driver);
		con = DriverManager.getConnection(url , user , userPassword);

		String sql = "select c# , cname , tname , title  from T where t# = ?";
		pstmt = con.prepareStatement(sql);
		pstmt.setString(1 , jobNumber);
		rs = pstmt.executeQuery();
		String ArrayCname[] = new  String[10];
		String ArrayCnumber[] = new String[10];
		int i=0;
		while(rs.next())
		{
			tname = rs.getString("tname");
			title = rs.getString("title");
			ArrayCname[i] = rs.getString("cname");
			ArrayCnumber[i] = rs.getString("c#");
			//原来的设计里没有获取c#的想法,跳转到showinfo.jsp页面后,是根据cname的值查找c# ,然后又根据c#找的学生表的数据.这时要对学生表和教工表做外连接,不太好.
			//所以现在直接把c#也请求过来,然后单选框的value是c#的值,而对应的显示的是cname. 之后只需要查找学生表即可.
			i++;
		}

		rs.close();
		pstmt.close();
		con.close();
		
	%>

	<form  name = "form3"  action="showInfo.jsp" >
	<table align="center">
	<h2 align = "center" > 选择你的课程进入系统</h2>
	<h3 align="center" ><%= tname %><%= title %>,请您选课</h3>
	<%
		int u=0;
		while(u<i)
		{
			out.println(" "+" "+" "+" "+" "+"<h4 align=\"center \"><input type=\"radio\" name = \"Cnumber\" value="+ArrayCnumber[u]+">"+ ArrayCname[u]+"</h4>");
			u++;
		}
	%>
	<input type = "submit" value="进入" align="center">
	</table>
	</form>
</body>
</html>

showInfo.jsp

<%@ page  pageEncoding="UTF-8"  import ="java.sql.*"  import="java.util.*"  %>
<html>
<body>
	<form  name="form4"  >
	<table  align="center"  border="8" width="600" height="200">
	<h3 align="center"> 学生信息:</h3>
	<tr><td>学号</td><td>姓名</td><td>年龄</td><td>性别</td><td>联系方式</td><td>成绩</td><td>修改成绩</td></tr>
	<%! String cnumber  , snumber;  %>
	<%
		session = request.getSession();
		Object  o = session.getAttribute("t#");
		String c = o.toString();

	    cnumber = request.getParameter("Cnumber");  //接收到的是实际的c#值  , 变量写c# 报错说变量c重复定义,估计是#特殊字符


		Connection con;
		PreparedStatement pstmt;
		ResultSet rs=null;
		String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		String url = "jdbc:sqlserver://localhost:1433;DatabaseName=LCBCom";
		String user = "sa";
		String userPassword = "123";
		Class.forName(driver);
		con = DriverManager.getConnection(url , user , userPassword);

		String sql = "select s# ,sname ,score ,age ,sex , telephone  , home   from S  where c# = ?";
		pstmt = con.prepareStatement(sql);
		pstmt.setString(1 , cnumber);
		rs = pstmt.executeQuery();

		while(rs.next())
			{
	%>
			<tr><td><%= rs.getString("s#") %></td>
						<%
							 snumber = rs.getString("s#");
						%>
				<td><%= rs.getString("sname")%></td>
				<td><%= rs.getInt("age") %></td>
				
				<%
					String sex = rs.getString("sex");
					if(sex.equals("M"))
					{
						sex = "男";
					}
					else
					{
						sex = "女";
					}


					session.setAttribute("showInfo_snumber" , snumber);
					session.setAttribute("showInfo_cnumber" , cnumber);
				%>
				<td><%= sex %></td>
				<td><%= rs.getString("telephone") %></td>
				<td><%= rs.getInt("score")  %></td>
				<td><a href = "Modification.jsp" >修改成绩</a></td>
			</tr>
			
	<%
		}
	%>
	</table>
	</form>
	<h2 align="center"><a href = "addStudentInfo.jsp">创建学生身份信息</a></h2>
</body>
</html>

addStudentInfo.jsp

<%@ page  pageEncoding="UTF-8"  import ="java.sql.*"  import="java.util.*"  %>
<html>
<body>
<%
	 	session = request.getSession();
		Object o = session.getAttribute("showInfo_cnumber");
		String cnumber = o.toString();
%>
<form name = "form6"  action = "finallAddInfo.jsp">
	<table align="center" border="7" width="300" height="400">
	<tr><td>姓名:</td><td><input type="text"  name="sname"></td></tr>
	<tr><td>年龄:</td><td><input type="text"  name="age" ></td></tr>
	<tr><td>性别:</td></tr>
	<tr><td><input type="radio"  name="sex" value="M" checked>男</td><td><input type="radio" name="sex" value="W" >女</tr>
	<tr><td>学号:</td><td><input type="text"  name="snumber"></td></tr>
	<tr><td>联系方式:</td><td><input type="text" name="telephone" ></td></tr>
	<tr><td>居住地:</td><td><input type="text" name="home" ></td></tr>
	<tr><td>成绩:</td><td><input type = "text" name = "score" ></td></tr>
	<tr><td><input type = "submit"  value="添加"> </td></tr>
	<tr><td><%= cnumber %></td></tr>
</body>
</html>

finallAddInfo.jsp

<%@ page  pageEncoding="UTF-8"  import ="java.sql.*"  import="java.util.*"  %>
<html>
<body>
	<%
		String sname = request.getParameter("sname");
		String age =  request.getParameter("age");
		String snumber =  request.getParameter("snumber");
		String telephone = request.getParameter("telephone");
		String home = request.getParameter("home");
		String sex = request.getParameter("sex");
		String score = request.getParameter("score");

		int  age_new = Integer.parseInt(age);
		int score_new = Integer.parseInt(score);

		session = request.getSession();
		Object o = session.getAttribute("showInfo_cnumber");
		String cnumber = o.toString();
		Connection con;
		PreparedStatement pstmt;
		String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		String url = "jdbc:sqlserver://localhost:1433;DatabaseName=LCBCom";
		String user = "sa";
		String userPassword = "123";
		Class.forName(driver);
		con = DriverManager.getConnection(url , user , userPassword);

		String sql = "insert into S(s# ,c# ,age ,sex ,home ,telephone , score ,sname) values(?, ? ,? ,? ,?,? , ? ,?)";
		pstmt = con.prepareStatement(sql);
		pstmt.setString(1 , snumber);
		pstmt.setString(2 , cnumber);
		pstmt.setInt(3 , age_new);
		pstmt.setString(4 , sex);
		pstmt.setString(5 , home);
		pstmt.setString(6 , telephone);
		pstmt.setInt(7 , score_new);
		pstmt.setString(8 ,sname);
		pstmt.executeUpdate();
	%>
	<h2 align="center">学生成绩插入成功!将在5秒后返回登录界面</h2>
	<%  response.setHeader("Refresh" , "5;url=register.jsp");  %>
</body>
</html>

modification.jsp

<%@ page  pageEncoding="UTF-8"  import ="java.sql.*"  import="java.util.*"  %>
<html>
<body>
	<% 
		session = request.getSession();
		Object o = session.getAttribute("showInfo_snumber");
		String snumber = o.toString();
		Connection con;
		PreparedStatement pstmt;
		ResultSet rs=null;
		String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		String url = "jdbc:sqlserver://localhost:1433;DatabaseName=LCBCom";
		String user = "sa";
		String userPassword = "123";
		Class.forName(driver);
		con = DriverManager.getConnection(url , user , userPassword);

		String  sql = "select sname ,score from S where s# = ? ";
		pstmt = con.prepareStatement(sql);
		pstmt.setString(1 , snumber);
		rs = pstmt.executeQuery();
		rs.next();
		String sname = rs.getString("sname");
		int  score = rs.getInt("score");
		session.setAttribute("Modification_snumber" ,  snumber );
		rs.close();
		pstmt.close();
		con.close();
		
	%>
	
	<form  name = "form5"  action = "finallyScoreModification.jsp" >
		<table  align = "center" >
		<h1 align="center">成绩修订</h1>
		<tr><th>姓名:</th><td><%= sname %></td></tr>
		<tr><th>学号:</th><td><%= snumber %></td></tr>
		<tr><th>当前成绩:</th><td><%= score %></td></tr>
		<tr><th>修订后的成绩:</th><td><input type = "text"  name = "score"  ></td></tr>
		<tr><td  align="center" colspan = "2"><input type = "submit"  value = "修改" ></td></tr>
		</table>
	</form>
</body>
</html>

finallyScoreModification.jsp

<%@ page  pageEncoding="UTF-8"  import ="java.sql.*"  import="java.util.*"  %>
<html>
<body>
	<%! String snumber; %>
	<%
		session = request.getSession();
		String score = request.getParameter("score");
		int int_score = Integer.parseInt(score);
		Object o = session.getAttribute("Modification_snumber");
		snumber = o.toString();
		
		Connection con;
		PreparedStatement pstmt;
		
		String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
		String url = "jdbc:sqlserver://localhost:1433;DatabaseName=LCBCom";
		String user = "sa";
		String userPassword = "123";
		Class.forName(driver);
		con = DriverManager.getConnection(url , user , userPassword);

		String sql = "update S set score = ? where s# = ?" ;
		pstmt = con.prepareStatement(sql);
		pstmt.setInt(1 , int_score);
		pstmt.setString(2 , snumber);
		pstmt.executeUpdate();
	%>
	<h2 align="center">修改成绩成功!将在5秒后返回登录界面</h2>
	<%  response.setHeader("Refresh" , "5;url=register.jsp");  %>
</body>
</html>

数据库建表代码:

use LCBCom
--s# , c# ,sname , score,age,sex,telephone,home
create table S
(
	id int identity(1,1),
	s# varchar(20)  constraint PK_S_s# primary key,
	c# varchar(20) ,
	sname  char(6) default '',
	score  int  check(score<100  and score >0),
	age    int  check(age<50 and age>0),
	telephone  char(20) default '',
	home  varchar(40)  default ''
)

use LCBCom
alter table S
add sex char(1) 
go      -- 忘加了sex


use LCBCom
--s# , c# ,sname , score,age,sex,telephone,home
alter table S
add foreign key(c#) references T(c#)
on update cascade
on delete no action
go
--on 之后没格式没有{} , 直接写关键字,而且在创建外键之前必须已近存在外键



use LCBCom
--c#,cname,title,t#,tname
create table T
(
	c# varchar(20) constraint PK_T_c# primary key,
	cname char(10) ,
	tname char(4) , 
	t# varchar(20),
	title char(6)
)


use LCBCom
insert into  dbo.S(s# , c# , sname , score , age ,sex,telephone , home)
values ('201665748339' , '0010' ,'王佳 ',34 ,21 ,'W ' ,'18865673382' ,  '哈尔滨江北区学海街1006室')
go

use LCBCom
insert into T(c# ,cname ,tname ,t# , title)
values('0000' ,'javaWeb开发教程 ',  '张娜' , '20160000' ,'教授')
go

选取部分源数据:

T表源数据示例:
c#             cname                                 tname                t#                                            title
0000           javaWeb开发教程                       张娜                 20160000                                      教授
0001           数据结构                              严为民               20160001                                      副教授
0010           c语言程序设计                         谭浩强               20160010                                      讲师
0011           python程序设计教程                    林兴良               20160011                                      讲师

S表源数据示例:
s#                 c#                   sname         score      age     sex      telephone                         home
201662025056       0000                 张三          78         20      M        18846791756                       哈尔滨江北区学海街1006室
201662979907       0001                 李四          88         19      W        18846782389                       哈尔滨江北区学海街1006室
201666387629       0001                 王五          89         19      M        18837893338                       哈尔滨江北区学海街1006室

201665748339 0010 王佳 34 21 W 18865673382 哈尔滨江北区学海街1006室

最后,链接数据库用到的用户名是 sa 密码:123 可以自行按需求更改

欢迎使用若依开源

快速构建 Web 应用程序

更多推荐

eclipse安装若依框架

1、首先下载maven，下载地址为：http://maven.apache.org/download.cgi，然后选择apache-maven-3.6.0-bin.tar.gz，如下图所示：2、然后新建一个maven目录，如下图所示：3、将下载的maven包解压到该目录中，（注意此时maven的位置，与压缩包的位置相同）如下图所示：4、配置环境变量，新建一个MAVEN_HOME，值为：D:\...

RuoYi 若依

开源框架若依实战Demo学习笔记

若依是个不错的框架，下面是我用他写的一个简单demo，以后会在此基础上进行进一步的开发，下面是整合的过程。我在整合的过程中可谓是一波三折，大家如果不想走弯路，那么这篇文章将对你很有帮助。首先，项目代码地址写列出来，方便大家下载使用，这是整理后的代码https://gitee.com/schjava/heros_separ.git启动项目访问localhost（默认80端口）配置测试用户先创建角色为