数据库课程设计报告(戴智青)

1、湖南科技大学计算机科学与工程学院数据库课程设计报告题目：人事管理系统学 号：姓 名： 戴智青指导老师： 唐 兵完成时间： 2013.1.2一、 系统需求分析：1、 系统功能分析：本系统主要有以下几项功能要求l 新员工资料的输入.l 分配员工号，并且设置初始的用户密码l 人事变更详细记录，包括岗位和部门的调整l 员工信息的查询和修改，包括员工个人信息和密码2、 系统功能模块设计：1.管理者模块：对员工信息的各项操作：增加、修改、删除、查询；2.员工模块：查询个人信息、查询工作信息、修改个人密码人事管理系统新员工档案输入人事变更员工档案查询修改3、 数据流程图：人事管理系统的数据流程图如下图所示，

2、所有数据由人事科管理人员输入人事科招聘新员工分配员工号员工信息数据岗位调整员工辞退人事变更记录个人信息查询个人信息查询结果二、 数据库设计： 人事变更记录PKFK1记录编号员工变动详细描述员工号FK1员工密码权限姓名性别生日职位学历专业联系电话Email所属部门当前状态员工PK部门PK部门编号FK1部门名称简介部门主管三、1、 数据库需求分析：根据数据流程图，可以列出以下数据项和数据结构：l 员工信息：员工号、密码、权限、姓名、性别、生日、专业、通讯地址、电话、email、备注。l 人事变动：记录号、员工、变动 所需外部数据支持 l 部门设置：部门编号、名称2、 数据库逻辑设计：根据系统的E-

3、R图，需要设计6个数据表来存放员工的信息department_ifo 部门信息表Personnel 人事变更表edu_level 教育程度代码表Person_ifo员工信息表job_code 职务代码表personnel_change_code 职务变更代码表3、 数据库的建立：此次课程设计所用数据库是MY SQL，编程语言是Java表格的建立：如员工信息表的创建CREATE TABLE person_ifo ( p_no char(10) NOT NULL, p_pwd char(20) NOT NULL default , authority bit(1) NOT NULL default

4、 b1, p_name char(5) default NULL, p_sex char(1) default NULL, p_brith date default NULL, dt_id smallint(6) NOT NULL, j_id smallint(6) NOT NULL, edu_id smallint(6) NOT NULL, skill char(10) default NULL, address char(10) default NULL, telp char(15) default NULL, email char(30) default NULL, remark cha

5、r(30) default NULL, PRIMARY KEY (p_no), KEY dt_id (dt_id), KEY j_id (j_id), KEY edu_id (edu_id), CONSTRAINT person_ifo_ibfk_1 FOREIGN KEY (dt_id) REFERENCES department_ifo (dt_id), CONSTRAINT person_ifo_ibfk_2 FOREIGN KEY (j_id) REFERENCES job_code (j_id), CONSTRAINT person_ifo_ibfk_3 FOREIGN KEY (e

6、du_id) REFERENCES edu_level (edu_id) ENGINE=InnoDB DEFAULT CHARSET=gbk;其他的表格都是使用同样的语言格式创建。三、 各模块的设计与实现：功能代码说明1、 增加新员工public void btn_add_actionPerformed(ActionEvent e) Connection conn = db.getconn();PreparedStatement pstm = null;String sql = insert into person_ifo values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)

7、;Date brith = null;try if(txt_pid.getText().equalsIgnoreCase()JOptionPane.showMessageDialog(this, 员工号不能为空！, 错误, JOptionPane.ERROR_MESSAGE);return;if(txt_brithY.getText().equalsIgnoreCase()txt_brithY.setText(1);if(txt_brithM.getText().equalsIgnoreCase()txt_brithM.setText(1);if(txt_brithD.getText().eq

8、ualsIgnoreCase()txt_brithD.setText(1);conn.setAutoCommit(false);pstm = conn.prepareStatement(sql);pstm.setString(1, txt_pid.getText();pstm.setString(2,);pstm.setByte(3, (byte) cmPower.getSelectedIndex();pstm.setString(4, txt_name.getText();if(rabF.isSelected()pstm.setString(5,rabF.getText();elsepstm

9、.setString(5,rabM.getText();brith = new Date(Integer.parseInt(txt_brithY.getText()-1900,Integer.parseInt(txt_brithM.getText()-1,Integer.parseInt(txt_brithD.getText();pstm.setDate(6, new java.sql.Date(brith.getTime();pstm.setShort(7, (short) cmDepart.getSelectedIndex();pstm.setShort(8, (short) cmJob.

10、getSelectedIndex();pstm.setShort(9, (short) cmEdu.getSelectedIndex();pstm.setString(10, txt_skill.getText();pstm.setString(11, txt_adress.getText();pstm.setString(12, txt_telp.getText();pstm.setString(13, txt_email.getText();pstm.setShort(14, (short) cmRemark.getSelectedIndex();int len = pstm.execut

11、eUpdate();if(len0)mit();JOptionPane.showMessageDialog(this, 添加数据成功！,提示,JOptionPane.INFORMATION_MESSAGE);elseJOptionPane.showMessageDialog(this, 添加数据失败！,提示,JOptionPane.ERROR_MESSAGE);conn.rollback(); catch (SQLException e1) / TODO Auto-generated catch blocke1.printStackTrace();try JOptionPane

12、.showMessageDialog(this, 输入格式错误，请检查数据是否合法！, ERROR, JOptionPane.ERROR_MESSAGE);conn.rollback(); catch (SQLException e2) / TODO Auto-generated catch blocke2.printStackTrace();finallydb.free(pstm, conn);2、 修改员工信息public void btn_alter_actionPerformed(ActionEvent e) Connection conn = db.getconn();Prepare

13、dStatement pstm = null;String p_no = rowDate.get(0);String sql = update person_ifo set + p_no=?,p_pwd=?,authority=?,p_name=?, +p_sex=?,p_brith=?,dt_id=?,j_id=?,edu_id=?,skill=?,address=?,telp=?,email=?,pc_id=? where p_no=?;Date brith = null;try if(txt_pid.getText().equalsIgnoreCase()JOptionPane.show

14、MessageDialog(this, 员工号不能为空！, 错误, JOptionPane.ERROR_MESSAGE);return;if(txt_brithY.getText().equalsIgnoreCase()txt_brithY.setText(1);if(txt_brithM.getText().equalsIgnoreCase()txt_brithM.setText(1);if(txt_brithD.getText().equalsIgnoreCase()txt_brithD.setText(1);conn.setAutoCommit(false);pstm = conn.pr

15、epareStatement(sql);pstm.setString(1, txt_pid.getText();pstm.setString(2, rowDate.get(1);pstm.setByte(3, (byte) cmPower.getSelectedIndex();pstm.setString(4, txt_name.getText();if(rabF.isSelected()pstm.setString(5,rabF.getText();elsepstm.setString(5,rabM.getText();brith = new Date(Integer.parseInt(tx

16、t_brithY.getText()-1900,Integer.parseInt(txt_brithM.getText()-1,Integer.parseInt(txt_brithD.getText();pstm.setDate(6, new java.sql.Date(brith.getTime();pstm.setShort(7, (short) cmDepart.getSelectedIndex();pstm.setShort(8, (short) cmJob.getSelectedIndex();pstm.setShort(9, (short) cmEdu.getSelectedInd

17、ex();pstm.setString(10, txt_skill.getText();pstm.setString(11, txt_adress.getText();pstm.setString(12, txt_telp.getText();pstm.setString(13, txt_email.getText();pstm.setShort(14, (short)cmRemark.getSelectedIndex();pstm.setString(15,p_no);int len = pstm.executeUpdate();if(len0)mit();JOptionPa

18、ne.showMessageDialog(this, 修改数据成功！,提示,JOptionPane.INFORMATION_MESSAGE);this.dispose();elseJOptionPane.showMessageDialog(this, 修改数据失败！,提示,JOptionPane.ERROR_MESSAGE);conn.rollback(); catch (SQLException e1) / TODO Auto-generated catch blocke1.printStackTrace();try JOptionPane.showMessageDialog(this, 输

19、入格式错误，请检查数据是否合法！, ERROR, JOptionPane.ERROR_MESSAGE);conn.rollback(); catch (SQLException e2) / TODO Auto-generated catch blocke2.printStackTrace();finallydb.free(pstm, conn);3、员工信息删除private void deleteDate(String towerid) Connection conn = db.getconn();PreparedStatement pstm = null;String sql = dele

20、te from person_ifo where p_no=?;try pstm = conn.prepareStatement(sql);pstm.setString(1, towerid);int len = pstm.executeUpdate();if(len 0)elseJOptionPane.showMessageDialog(this,towerid+ 该项删除失败); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();JOptionPane.showMessageDialog(

21、this,出现未知错误404，删除失败,ERROR, JOptionPane.ERROR_MESSAGE);4、员工信息查询private void setTableData(int cm) Connection conn = null;PreparedStatement pstm = null;ResultSet rs = null;String sql = SELECT p_no,p_pwd,authority, +edu__level,p_name,p_sex,p_brith, +department_ifo.dt_name,skill, +job_code.j_nam

22、e,address,telp,email,pchange + FROM person_ifo,edu_level,department_ifo,job_code,personnel_change_code + WHERE person__id=edu__id + AND person_ifo.dt_id=department_ifo.dt_id + AND person_ifo.j_id=job_code.j_id +AND person_ifo.dt_id=? + AND person_ifo.pc_id=personnel_change_code.pc_id

23、;Vector data = new Vector();Vector row = null;try conn = db.getconn();pstm = conn.prepareStatement(sql);pstm.setInt(1, cm);rs = pstm.executeQuery();while(rs.next()row = new Vector();row.add(rs.getString(p_no);if(db.power = 0)row.add(rs.getString(p_pwd);row.add(rs.getString(authority);row.add(rs.getS

24、tring(edu_level); /row.add(rs.getString(p_name);row.add(rs.getString(p_sex);row.add(rs.getString(p_brith);row.add(rs.getString(dt_name); /row.add(rs.getString(j_name); row.add(rs.getString(skill);/row.add(rs.getString(address);row.add(rs.getString(telp);row.add(rs.getString(email);row.add(rs.getStri

25、ng(pchange);data.add(row); catch (SQLException e) / TODO Auto-generated catch blocke.printStackTrace();finallydb.free(rs, pstm, conn);Vector title = new Vector();title.add(员工号);title.add(姓名);title.add(性别);title.add(生日);title.add(所在部门);title.add(职务);title.add(专业技能);title.add(家庭住址);title.add(联系电话);tit

26、le.add(电子邮箱);title.add(当前状态);if(db.power = 0)title.add(1,密码);title.add(2,权限);title.add(3,教育程度);dtmView = new DefaultTableModel(data,title);this.tabView.setModel(dtmView);5、员工密码修改String sql1=UPDATE person_ifo SET p_pwd=? WHERE p_no=?;String sql2=select p_pwd,p_name from person_ifo where p_no=?;Result

27、Set rs = null;try conn.setAutoCommit(false);pstm1 = conn.prepareStatement(sql2);pstm1.setString(1, txt_no.getText();rs = pstm1.executeQuery();while(rs.next()if(rs.getString(p_pwd).equals(new String(txt_pwd1.getPassword() & rs.getString(p_name).equals(txt_name.getText() )bool = true;break;elseJOption

28、Pane.showMessageDialog(this, 对不起！员工号或姓名、密码不匹配，请检查数据的正确性！,ERROE,JOptionPane.ERROR_MESSAGE);conn.rollback();return ;if (bool) pstm2 = conn.prepareStatement(sql1);if (new String(txt_pwd2.getPassword().equals(new String(txt_pwd3.getPassword() pstm2.setString(1, new String(txt_pwd2.getPassword(); else JO

29、ptionPane.showMessageDialog(this, 两次输入密码不匹配,ERROE, JOptionPane.ERROR_MESSAGE);conn.rollback();return;pstm2.setString(2, txt_no.getText();int len = pstm2.executeUpdate();if (len 0) JOptionPane.showMessageDialog(this, 密码修改成功，请您牢记！,修改成功,JOptionPane.INFORMATION_MESSAGE);mit();txt_no.setText();txt_name.setText();txt_pwd1.setText();txt_pwd2.setText();txt_pwd3.setText();elseJOptionPane.showMessageDialog(this, 错误代码404，你懂得！,ERROR,JOptionPane.ERROR_MESSAGE);conn.rollback();elseJOptionPane.showMessageDialog(this, 错误代码404，你懂得！,ERROR,JOptionPane.ERROR_MESSAGE);conn.rollbac