《学生基本信息管理系统文档1》

计算机科学与工程学院 课程设计报告北方民族大学课程设计报告 系（部、中心） 计算机科学与工程学院 姓 名 李昌玲 学 号 20082546 专 业 网络工程 班 级 081 同组人员 陆巍 艾安健 设计题目名称 学生基本信息管理系统 起止时间 2011-5-17-2011-5-25 成 绩 指导教师签名 北方民族大学教务处制学生基本信息管理系统开发摘要：学生基本信息管理系统的开发主要是为了学院能够更好的管理各个班级，同时也间接地方便了学校对各个学院的管理。该系统可实现多种条件下的对班级和学生的信息更改，也可实现按不同条件统计学生的人数。关键字：学生基本信息管理系统 信息更改 统计The Development of Students' basic Information Management SystemAbstract：Class information management system mainly function is that can better manage each class, also can provides convenience to school to manage each college. This system can realize change class and students' information with various conditions, also can achieve statistical the number of the students according to different conditions.Keywords：Students' basic Information Management SystemInformation change statistic目录1项目概述02 需求分析02.1 业务需求分析022系统功能模块划分02.3 系统流程图02.4 任务划分03 数据模型分析03.1 E-R模型13.2 关系表13.3 数据字典23.4 数据库实现主要程序代码34 系统实现54.1登录模块与查询统计实现54.1.1算法流程图54.1.2主要程序代码54.2班级信息管理实现54.2.1算法流程图54.2.2主要程序代码54.3学生信息管理实现54.3.1算法流程图54.3.2主要程序代码55 系统测试55.1 系统实现部分截图55.2 测试数据和结果56 结束语5参考文献524第 24 页 共 28 页1项目概述班级信息管理系统的开发主要是为了学院能够更好的管理各个班级，同时也间接地方便了学校对各个学院的管理。该系统预期实现以下功能：1.按班级名、系名、院名对班级信息进行增删改查；2.按学号和姓名对学生信息进行增删改查；3.按不同要求（班级、系、学院、年龄、性别）统计人数以及按学号和姓名查询该学生所属班级、系和学院。2 需求分析2.1 业务需求分析22系统功能模块划分 2.3 系统流程图2.4 任务划分3 数据模型分析3.1 E-R模型3.2 关系表 学生表（student）学号姓名性别年龄所在班级所在系别所在学院snosnamesexagecnamednameconame班级表（class）班级名所在系别所在学院cnamednameconame系表（department）系名所在学院dnameconame院表（college）院名coname管理员表（admin）用户账号用户名字用户密码usernousernamepassword3.3 数据字典 Student字段名称数据类型长度主/外键约束备注snonumber4主键非空snamevarchar220非空sexvarchar220非空agenumber4非空cnamevarchar220外键非空参照“class”中的“cname”dnamevarchar220外键非空参照“department”中的“dname”conamevarchar220外键非空参照“college”中的“coname”class字段名称数据类型长度主/外键约束备注cnamevarchar220主键非空dnamevarchar220外键非空参照“department”中的“dname”conamevarchar220外键非空参照“college”中的“coname”department字段名称数据类型长度主/外键约束备注dnamevarchar220主键非空conamevarchar220外键非空参照“college”中的“coname”college字段名称数据类型长度主/外键约束备注conamevarchar220主键非空admin 字段名称数据类型长度主/外键约束备注usernoNumber6主键非空usernamevarchar220/非空passwordvarchar210/非空3.4 数据库实现主要程序代码创建用户 lal 密码 lal：SQL> create user lal identified by lal default tablespace users quota 10M on users account unlock;为lal授权：SQL> grant create session,create view,create table,create any index to lal with admin option;连接用户：SQL> conn lal/lalorcl创建college表SQL> create table college ( 2 coname varchar(20) constraint co_pk primary key 3 );创建dept表SQL> create table dept ( dname varchar(20) constraint d_pk primary key, coname varchar2(20) constraint d_fk3 references college (coname) );创建class表SQL> create table class( cname varchar(20) constraint c_pk primary key, dname varchar2(20) constraint c_fk2 references dept (dname), coname varchar2(20) constraint c_fk3 references college (coname) );创建student表SQL> create table student ( sno number(6) constraint s_pk primary key, sname varchar2(20) not null, sex varchar2(8) constraint s_ck1 check(sex in('男','女'), age number(4) not null , cname varchar2(20) constraint s_fk1 foreign key(cname) references class(cname), dname varchar2(20) constraint s_fk2 foreign key(dname) references dept (dname), coname varchar2(20) constraint s_fk3 foreign key(coname) references college (coname) );为college表添加数据insert into college values('计算机');insert into college values('法学院');insert into college values('商学院');insert into college values('材料学院');insert into college values('信计学院');为dept表添加数据insert into dept values('网络工程','计算机');insert into dept values('软件工程','计算机');insert into dept values('计算机系','计算机');insert into dept values('国际系','法学院');insert into dept values('金融系','计算机');为class表添加数据insert into class values('08网络一班','网络工程','计算机');insert into class values('08软件一班','软件工程','计算机');insert into class values('08计算机二班','计算机系','计算机');insert into class values('08国际法一班','国际系','法学院');insert into class values('08金融二班','金融系','商学院');为student表添加数据insert into student values(080001,'王伟','男',21,'08计算机二班','计算机系','计算机');insert into student values(080002,'张蕊','女',21,'08计算机二班','计算机系','计算机');insert into student values(080005,'李泽','男',20,'08网络一班','网络工程','计算机');insert into student values(080007,'张迪','男',22,'08网络一班','网络工程','计算机');insert into student values(080012,'艾薇','女',21,'08网络一班','网络工程','计算机');insert into student values(080035,'王帅','男',21,'08软件一班','软件工程','计算机');insert into student values(080053,'徐国玺','女',21,'08国际法一班','国际系','法学院');insert into student values(080058,'卢娟','女',22,'08金融二班','金融系','商学院');创建管理员admin表create table admin (userno number(6) constraint ad_pk primary key,username varchar2(20) not null,password varchar2(10) not null)为admin添加数据insert into admin values(00001234,'王东','111111');insert into admin values(00001235,'谢锋','111112');事物提交commit;4 系统实现4.1 登录模块与查询统计实现4.1.1 主要程序代码用户登录<% String Userno=request.getParameter("Userno"); String Password=request.getParameter("Password"); Connection con; Statement sql; ResultSet rs; try Class.forName("oracle.jdbc.driver.OracleDriver"); catch(Exception e) out.print(e); try con=DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl","lal","lal"); sql=con.createStatement(); rs=sql.executeQuery( "select * from admin where userno='" + Userno + "' and password = '" + Password + "'"); /如果记录集非空，表明有匹配的用户名和密码，登陆成功 if ( rs.next ( ) ) session.setAttribute("no", Userno); session.setAttribute("pass", Password); out.print ( "<br>登录成功！</br>" ); out.print("<br> 您的用户ID是："+Userno ); out.print("<br> 您的密码是："+Password); %> <br /> <a href="updateuser.jsp">修改密码</a> <br /> <a href="insertuser.jsp">新建账户</a> <br /> <a href="deleteuser.jsp">删除账户</a> <br /><a href="index1.1.jsp">欢迎登录到班级信息管理系统_统计信息 </a><br /><a href="selectwhere.jsp">欢迎登录到班级信息管理系统_查询归属 </a> <% else out.print( "用户名不存在或密码错误！" ); %><br /><a href="login2.jsp">返回 </a> <% rs.close ( ); sql.close ( ); con.close ( );catch(SQLException e1) out.print(e1); %>修改密码<% String idno=request.getParameter("id"); String pass1=request.getParameter("pass1");String pass2=request.getParameter("pass2"); Connection con; Statement sql; try Class.forName("oracle.jdbc.driver.OracleDriver"); catch(Exception e) out.print(e); try con=DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl","lal","lal"); sql=con.createStatement(); String updateCondition="update admin set password= '"+pass2+"'"+ "where userno='"+idno+"'"+"and password='"+pass1+"'" int m=sql.executeUpdate(updateCondition); if ( m!=0 ) out.print("修改成功!");%><br /><p align="center"><a href="login2.jsp">测试登录 </a></p><%elseout.print("更新失败!"); sql.close ( ); con.close ( );catch(SQLException e1) out.print(e1); %>新建账户<% String newid=request.getParameter("newid"); String newname=request.getParameter("newname"); try byte bb=newname.getBytes("ISO-8859-1"); newname=new String(bb,"gb2312"); catch(Exception e) String newpass=request.getParameter("newpass"); Connection con; Statement sql; try Class.forName("oracle.jdbc.driver.OracleDriver"); catch(Exception e) out.print(e); try con=DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl","lal","lal"); sql=con.createStatement(); String insertCondition="insert into admin values('"+newid+"','"+newname+"','"+newpass+"')" int m=sql.executeUpdate(insertCondition); if ( m!=0 ) out.print("注册成功!");%><br /><p align="center"><a href="login2.jsp">测试登录</a></p><%elseout.print("注册失败!"); sql.close ( ); con.close ( );catch(SQLException e1) out.print(e1); %>删除账户<% String idno=request.getParameter("dno"); Connection con; Statement sql; try Class.forName("oracle.jdbc.driver.OracleDriver"); catch(Exception e) out.print(e); try con=DriverManager.getConnection("jdbc:oracle:thin:127.0.0.1:1521:orcl","lal","lal"); sql=con.createStatement(); String deleteCondition="delete from admin where userno='"+idno+"'" int m=sql.executeUpdate(deleteCondition); if ( m!=0 ) out.print("删除成功!");%><br /><p align="center"><a href="login2.jsp">测试登录 </a></p><%elseout.print("删除失败!"); sql.close ( ); con.close ( );catch(SQLException e1) out.print(e1); %>4.2 院系以及班级信息管理实现4.2.1 算法流程图4.2.2 主要程序代码AddClassBean.java /添加班级信息package tom.jiafei;import java.sql.*;public class AddClassBean String cname="", /班级名 dname="", /系名 coname="" /学院名 String addMessage="" public AddClassBean() try Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); catch(ClassNotFoundException e) public void setCname(String s) cname=s.trim(); /从当前 String 对象移除所有前导空白字符和尾部空白字符。 try byte bb=cname.getBytes("ISO-8859-1"); cname=new String(bb,"gb2312"); catch(Exception e) public void setDname(String s) dname=s.trim(); try byte bb=dname.getBytes("ISO-8859-1"); dname=new String(bb,"gb2312"); catch(Exception e) public void setConame(String s) coname=s.trim(); try byte bb=coname.getBytes("ISO-8859-1"); coname=new String(bb,"gb2312"); catch(Exception e) public String getAddMessage() String insertCondition="INSERT INTO class VALUES( '"+cname+"', '"+dname+"','"+coname+"')" String str="" Connection con=null; Statement sql=null; try con=DriverManager.getConnection("jdbc:odbc:jsp_oracle","lal","lal"); sql=con.createStatement(); catch(SQLException e) str="aa"+e; try if(cname.length()>0) int m=sql.executeUpdate(insertCondition); if(m!=0) str="对表中添加"+m+"条记录成功" else str="添加记录失败" else str="请输入班级名" con.close(); catch(SQLException e) str="输入的班级名不允许重复"+e; return str; ConditionDelete.java /删除班级信息package tom.jiafei;import java.sql.*;public class ConditionDelete String cname ; /学号 String dname; /系名 String coname; String deleteResultByCname, /删除结果 deleteResultByDname, deleteResultByConame; public ConditionDelete() try Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); catch(ClassNotFoundException e) public void setCname(String s) cname=s.trim(); try byte bb=cname.getBytes("ISO-8859-1"); cname=new String(bb,"gb2312"); catch(Exception e) public String getCname() return cname; public void setDname(String s) dname=s.trim(); try byte bb=dname.getBytes("ISO-8859-1"); dname=new String(bb,"gb2312"); catch(Exception e) public String getDname() return dname; public void setConame(String s) coname=s.trim(); try byte bb=coname.getBytes("ISO-8859-1"); coname=new String(bb,"gb2312"); catch(Exception e) public String getConame() return coname; public String getDeleteResultByCname() String condition="Delete FROM class Where cname= '"+cname+"'" deleteResultByCname=f(condition); return deleteResultByCname; public String getDeleteResultByDname() String condition="Delete FROM class Where dname= '"+dname+"'" deleteResultByDname=f(condition); return deleteResultByDname; public String getDeleteResultByConame() String condition="Delete FROM class Where dname= '"+coname+"'" deleteResultByConame=f(condition); return deleteResultByConame; public String f(String condition) String delCondition=condition; String str="" Connection con; Statement sql; try con=DriverManager.getConnection("jdbc:odbc:jsp_oracle","lal","lal"); sql=con.createStatement(); int m=sql.executeUpdate(delCondition); if(m!=0) str="对表中删除"+m+"条记录成功" else str="必须指定删除记录" con.close(); catch(SQLException e) str="班级不存在" return str; RenewBean.java /更新班级信息package tom.jiafei;import java.sql.*;public class RenewBean String cname="", /班级名 dname="", /专业名 coname="" /学院名 String renewMessage="" public RenewBean() try Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); catch(ClassNotFoundException e) public void setCname(String s) cname=s.trim(); try byte bb=cname.getBytes("ISO-8859-1"); cname=new String(bb,"gb2312"); catch(Exception e) public void setDname(String s) dname=s.trim(); try byte bb=dname.getBytes("ISO-8859-1"); dname=new String(bb,"gb2312"); catch(Exception e) public void setConame(String s) coname=s.trim(); try byte bb=coname.getBytes("ISO-8859-1"); coname=new String(bb,"gb2312"); catch(Exception e) public String getRenewMessage() String updateCondition="UPDATE class SET dname = '"+dname+ "', coname = '"+coname+ "' WHERE cname= '"+cname+"'" String str="" Connection con; Statement sql; try con=DriverManager.getConnection("jdbc:odbc:jsp_oracle","lal","lal"); sql=con.createStatement(); int m=sql.executeUpdate(updateCondition); if(m!=0) str="对表中第"+m+"条记录更新成功" else str="更新失败" con.close(); catch(SQLException e) str="你还没有提交更新的数据或"+e; return str; ConditionQuery.java /按不同字段查询班级信息 package tom.jiafei;import java.sql.*;public class ConditionQuery String cname ; /学号 String dname; /系名 String coname; StringBuffer queryResultByCname, /查询结果 queryResultByDname, queryResultByConame; public ConditionQuery() queryResultByCname=new StringBuffer(); queryResultByDname=new StringBuffer(); queryResultByConame=new StringBuffer(); try Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); catch(ClassNotFoundException e) public void setCname(String s) cname=s.trim(); try byte bb=cname.getBytes("ISO-8859-1"); cname=new String(bb,"gb2312"); catch(Exception e) public String getCname() return cname; public void setDname(String s) dname=s.trim(); try byte bb=dname.getBytes("ISO-8859-1"); dname=new String(bb,"gb2312"); catch(Exception e) public String getDname() return dname; public void setConame(String s) coname=s.trim(); try byte bb=coname.getBytes("ISO-8859-1"); coname=new String(bb,"gb2312"); catch(Exception e) public String getConame() return coname; public StringBuffer getQueryResultByCname() String condition="SELECT * FROM class Where cname= '"+cname+"'" queryResultByCname=f(condition); return queryResultByCname; public StringBuffer getQueryResultByDname() String condition="SELECT * FROM class Where dname= '"+dname+"'" queryResultByDname=f(condition); return queryResultByDname; public StringBuffer getQueryResultByConame() String condition="SELECT * FROM class Where coname= '"+coname+"'" queryResultByConame=f(condition); return queryResultByConame; private StringBuffer f(String condition) StringBuffer str=new StringBuffer(); Connection con=null; Statement sql=null; ResultSet rs=null; try con=DriverManager.getConnection("jdbc:odbc:jsp_oracle","lal","lal"); sql=con.createStatement(); rs=sql.executeQuery(condition); str.append("<table border=1>"); str.append("<th width=100>"+"班级名称"); str.append("<th width=100>"+"所属系名"); str.append("<th width=100>"+"所属学院"); while(rs.next() str.append("<tr>"); str.append("<th>"+rs.getString(1)+"</td>"); str.append("<th>"+rs.getString(2)+"</td>"); str.append("<th>"+rs.getString(3)+"</td>"); str.append("</tr>"); str.append("<table border=1>"); con.close(); catch(SQLException e) str.append(e); return str; Oracle_Bean.java /数据库连接package tom.jiafei;import java.sql.*;public class Oracle_Bean private Statement stmt=null; private ResultSet rs=null; private Connection conn=null; public Oracle_Bean(); public void OpenConn()throws Exception try Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); conn=DriverManager.getConnection("jdbc:odbc:jsp_oracle","lal","lal"); catch(SQLException e)System.out.println(e); public ResultSet executeQuery(String sql) rs=null; try stmt=conn.cre