數(shù)據(jù)庫課程設(shè)計報告(戴智青)

1、湖南科技大學(xué)計算機科學(xué)與工程學(xué)院數(shù)據(jù)庫課程設(shè)計報告題目：人事管理系統(tǒng)學(xué) 號：1005010211姓 名： 戴智青指導(dǎo)老師： 唐 兵完成時間： 2013.1.2一、 系統(tǒng)需求分析：1、 系統(tǒng)功能分析：本系統(tǒng)主要有以下幾項功能要求l 新員工資料的輸入.l 分配員工號，并且設(shè)置初始的用戶密碼l 人事變更詳細(xì)記錄，包括崗位和部門的調(diào)整l 員工信息的查詢和修改，包括員工個人信息和密碼2、 系統(tǒng)功能模塊設(shè)計：1.管理者模塊：對員工信息的各項操作：增加、修改、刪除、查詢；2.員工模塊：查詢個人信息、查詢工作信息、修改個人密碼人事管理系統(tǒng)新員工檔案輸入人事變更員工檔案查詢修改3、 數(shù)據(jù)流程圖：人事管理系統(tǒng)的數(shù)

2、據(jù)流程圖如下圖所示，所有數(shù)據(jù)由人事科管理人員輸入人事科招聘新員工分配員工號員工信息數(shù)據(jù)崗位調(diào)整員工辭退人事變更記錄個人信息查詢個人信息查詢結(jié)果二、 數(shù)據(jù)庫設(shè)計： 人事變更記錄PKFK1記錄編號員工變動詳細(xì)描述員工號FK1員工密碼權(quán)限姓名性別生日職位學(xué)歷專業(yè)聯(lián)系電話Email所屬部門當(dāng)前狀態(tài)員工PK部門PK部門編號FK1部門名稱簡介部門主管三、1、 數(shù)據(jù)庫需求分析：根據(jù)數(shù)據(jù)流程圖，可以列出以下數(shù)據(jù)項和數(shù)據(jù)結(jié)構(gòu)：l 員工信息：員工號、密碼、權(quán)限、姓名、性別、生日、專業(yè)、通訊地址、電話、email、備注。l 人事變動：記錄號、員工、變動 所需外部數(shù)據(jù)支持 l 部門設(shè)置：部門編號、名稱2、 數(shù)據(jù)庫邏輯

3、設(shè)計：根據(jù)系統(tǒng)的E-R圖，需要設(shè)計6個數(shù)據(jù)表來存放員工的信息department_ifo 部門信息表Personnel 人事變更表edu_level 教育程度代碼表Person_ifo員工信息表job_code 職務(wù)代碼表personnel_change_code 職務(wù)變更代碼表3、 數(shù)據(jù)庫的建立：此次課程設(shè)計所用數(shù)據(jù)庫是MY SQL，編程語言是Java表格的建立：如員工信息表的創(chuàng)建CREATE TABLE person_ifo ( p_no char(10) NOT NULL, p_pwd char(20) NOT NULL default 123456, authority bit(1)

4、NOT NULL default 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

5、NULL, remark char(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_

6、3 FOREIGN KEY (edu_id) REFERENCES edu_level (edu_id) ENGINE=InnoDB DEFAULT CHARSET=gbk;其他的表格都是使用同樣的語言格式創(chuàng)建。三、 各模塊的設(shè)計與實現(xiàn)：功能代碼說明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_bri

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

9、abF.getText();elsepstm.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.setS

10、hort(8, (short) cmJob.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()

11、;int len = pstm.executeUpdate();if(len0)mit();JOptionPane.showMessageDialog(this, 添加數(shù)據(jù)成功！,提示,JOptionPane.INFORMATION_MESSAGE);elseJOptionPane.showMessageDialog(this, 添加數(shù)據(jù)失敗！,提示,JOptionPane.ERROR_MESSAGE);conn.rollback(); catch (SQLException e1) / TODO Auto-generated catch blocke1.printStackTrace();t

12、ry JOptionPane.showMessageDialog(this, 輸入格式錯誤，請檢查數(shù)據(jù)是否合法！, 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.get

13、conn();PreparedStatement 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()JO

14、ptionPane.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().equalsIgnoreCase()txt_brithD.setText(1);conn.setAutoCommit(false);

15、pstm = conn.prepareStatement(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(Integ

16、er.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.getSelectedIndex();pstm.setShort(9, (short) cmEdu.

17、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();pstm.setString(15,p_no);int len = pstm.executeUpdate();if(len0)mit();JOp

18、tionPane.showMessageDialog(this, 修改數(shù)據(jù)成功！,提示,JOptionPane.INFORMATION_MESSAGE);this.dispose();elseJOptionPane.showMessageDialog(this, 修改數(shù)據(jù)失??！,提示,JOptionPane.ERROR_MESSAGE);conn.rollback(); catch (SQLException e1) / TODO Auto-generated catch blocke1.printStackTrace();try JOptionPane.showMessageDialog(t

19、his, 輸入格式錯誤，請檢查數(shù)據(jù)是否合法！, 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

20、= delete 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.showMessageD

21、ialog(this,出現(xiàn)未知錯誤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

22、.j_name,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

23、.pc_id;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(r

24、s.getString(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.g

25、etString(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(職務(wù));title.add(專業(yè)技能);title.add(家庭住址);title.add(聯(lián)系電

26、話);title.add(電子郵箱);title.add(當(dāng)前狀態(tài));if(db.power = 0)title.add(1,密碼);title.add(2,權(quán)限);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=?;

27、ResultSet 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;elseJ

28、OptionPane.showMessageDialog(this, 對不起！員工號或姓名、密碼不匹配，請檢查數(shù)據(jù)的正確性！,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(); e

29、lse JOptionPane.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.rollback(