数据库课程设计—企业工资管理系统java版+完整代码

1、数据库课程设计报告企业工资管理系统姓名王素文班级软133学号139074224课程名称数据库原理及应用指导教师陈业斌2016年5月20日目录一工资管理系统需求分析1.1功能需求1.1.1 功能划分1.1.2 功能描述1.2性能需求1.3数据流图二. 总体设计2.1数据库概念设计2.2功能模块三. 系统详细设计3.1数据库逻辑设计3.2各模块功能3.2.1 职工信息管理系统3.2.2 职工工资管理系统3.2.3 职工津贴管理系统四. 系统实现4.1界面截图4.1.1 主界面及工资基本信息界面4.1.2 登录界面4.1.3 系统主界面4.1.4 信息录入，修改，删除，查询界面4.2设计代码五. 实

2、验总结1、需求分析1.1功能需求1.1.1功能划分(1) 、员工信息表；及时反映员工的基本信息(2) 、员工津贴表，反映员工津贴(3) 、员工基本工资表1.1.2 功能描述(1) 、基本工资的设定(2) 、津贴的设定(3) 、计算出月工资(4) 、录入员工工资信息(5 )、添加员工工资信息(6)、更改员工工资信息1.2性能需求此工资管理系统对工资数据精度的计算能在默认情况之 下精确到小数点后3位小数，即是精确到分的计算。但在用户使用过程中，能自行根据实际情况进行小数计算精度 的设定，最大能允许保留小数点后5位的精度。在时间特性上，当用户发出命令请求时的服务器的响应时间、对数 据更新处理、工资数

3、据的查询检索等上，同样要求系统响 应时间不会超过 0.5秒时间。系统支持多种操作系统的运 行环境，多不同操作系统，不同文件格式的磁盘上的数据 均能实现信息的互通，及共享。当服务器移植到其他的系 统平台，如：Linux平台下时，同样能和其他的系统进行数 据存取同步，不会出现系统之间互不兼容的情况，系统支 持多系统之间的互连互通，系统有巨大的强健性。本课程 设计是用Java语言编写，mysql数据库。1.3数据流图根据工资管理要求及用户需求调查分析，得到以下数据流图图1.1第一层数据流图图1.2职工信息的载入添加操作用户修改操作津贴表删除操作查询操作2.总体设计2.1数据库概念设计有了数据流图，用

4、E-R图来说明工资信息管理系统的数据库概念模式，如图图2.1实体之间关系E-R图2.2功能模块3.1数据库逻辑设计将以上E-R转换成如下关系模式管理员（姓名，密码，权限）员工（职工号，姓名，密码）工资（职工号，姓名，基本工资，）津贴（职工号，姓名，奖金）其中，标有下划线的字段表示为该数据表的主码，即主关键字。在上面的 实体以及实体之间关系的基础上，形成数据库中的表格以及各个表格之间的关 系。工资信息管理系统数据库中各个表格的设计结果如下面的几个表格所示。 每个表格表示在数据库中的一个表。表一：员工信息表:列名数据类型1可否取空备注说明noChar ( 6 )NOT NULL职工号（主键）nam

5、evarChar ( 10)NOT NULL职工姓名mivarchar(IO)NOT NULL登录密码表二：基本工资表:列名数据类型可否取空备注说明noChar(8 )NOT NULL职工号（主键）namevarChar ( 10；NOT NULL职工姓名JbintNOT NULL基本工资表三：管理员表:m名数据类型可否为空备注说明glnamevarChar ( 10 )NOT NULL管理员姓名（主键）glmmvarChar (10)NOT NULL登陆密码powerchar(3)NOT NULL权限|表四：津贴信息表列名数据类型可否为空备注说明noChar(6 )NOT NULL职工号（主

6、键)|namevarChar (10)|NOT NULL职工姓名JtintNOT NULL津贴create table yg(no varchar(6),n ame varchar(IO),jbgz in t(11),jt int(11),mm varchar(10);create table gly(aln ame varchar(10),glmm varchar(10),power char(3);3.2各模块功能3.2.1职工信息管理系统功能为：财务部门相关人员录入、修改、删除、查询员工个人信息322职工工资管理系统功能为：根据工资生成公式，按照员工的考勤情况及各种表现按月生成相 应的工

7、资；财务部门相关人员能录入、修改、删除、查询每个月每个员工的工 资信息以及工资汇总；员工本人能查询自己的工资信息以及工资汇总；3.2.3职工登陆查询系统功能为：员工本人能通过用户名和密码查询自己的信息以及修改自己的密 码；4.系统实现4.1界面截图4.1.1 主界面4.1.2 登录界面4.1.3 管理员管理工资界面4.1.4信息录入,修改,删除,查询界面4.1.5职工信息查询界面4.1.6职工密码修改界面新密码X4.2设计代码注：以下代码只选取了各个页面内与数据库操 作有关的内容进入系统public void action Performed(Acti on Eve nt e)t1 = new

8、 String(username.getText();t2 = new String(password.getPassword();if(e.getSource()=log on Butt on 1)try Connection con;Stateme nt sql;ResultSet rs;Class.forName(com.mysql.jdbc.Driver);Class.forName(com.mysql.jdbc.Driver). newI nsta nce();/驱动con = DriverMa nager.getC onn ecti on( jdbc:mysql:/localhos

9、t:3306/dba,root,123457);sql=c on. createStateme nt();rs=sql.executeQuery(selectgl name,power,glmmfrom gly where gl name=”+t1+ and glmm=+t2+”);/查询power=rs.getStri ng(power);if(rs. next()if(rs.getStri ng(gl name).equals(t1)&rs.getStri ng(glmm).equals(t2)n ame=t1;GZGLZJM yg = new GZGLZJM( name);frame.d

10、ispose();yg.create();/JOpti onPan e.showMessageDialog( newDL(),登录成功！)；if(JOptio nPa ne.YES_OPTION=0)frame.dispose();/ else 输入用户JOptio nPan e.showMessageDialog(this,名或密码错误！)；con. close();sql.close();catch (Excepti on e1) / TODO Auto-ge nerated catch blocke1.pri ntStackTrace();if (e.getSource()=logo n

11、Button2)try Connection con;Stateme nt sql;ResultSet rs;Class.forName(com.mysql.jdbc.Driver)/Class.forName(com.mysql.jdbc.Driver). newI nsta nce();/驱动con = DriverMa nager.getC onn ecti on(jdbc:mysql:/localhost:3306/dba,root,123457);sql=c on. createStateme nt();* from yg where查询rs=sql.executeQuery(sel

12、ectname=+t1+ and mm=+t2+”);/if(rs. next()if(rs.getStri ng(” name).equals(t1)&rs.getStri ng(mm).equals(t2)n ame=t1;YGDLJM yg = new YGDLJM( name);frame.dispose();yg.create();DL(),JOptio nPan e.showMessageDialog( new 录成功！)；if(JOptio nPan e.YES_OPTION=0)frame.dispose();else JOptionPane.showMessageDialog

13、(this,输入用户名或密码错误！)；con. close();sql.close();catch (Excepti on e1) / TODO Auto-ge nerated catch blocke1.pri ntStackTrace();if (can celButton .equals(e.getSource() /退出System.exit(0);public void chax un()Connection con;Stateme nt sql;ResultSet rs;/Vector vector = new Vector();try Class.forName(com.mysq

14、l.jdbc.Driver); catch (ClassNotFou ndException e1) JOptionPane.showMessageDialog(this,连接数据库失败！)； try con=DriverMa nager.getC onn ectio n(jdbc:mysql:/localhost:3306/d ba,root,123457);sql= con .createStateme nt(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.TYPE_SCROLL_SENSITIVE);/if (b4.equals(查询所有)for (i

15、nt i = 0; i 50; i+) for (i nt j = 0; j 4; j+) table.setValueAt(, i, j);rs=sql.executeQuery(selectno,n amejbgzjt fromyg);/disti netint k = -1;while (rs. next() +k;Stri ng no = rs.getStri ng(1);Stri ng n ame = rs.getStri ng(2);String jbgz = rs.getStri ng(3);Stri ng jt = rs.getStri ng(4);/int sum= rs.g

16、etI nt(3)+rs.get In t(4); table.setValueAt(no, k, 0);table.setValueAt(name, k, 1);table.setValueAt(jbg z, k, 2);table.setValueAt(jt, k, 3); table.setValueAt(sum,k,4);con. close(); sql.close();catch (SQLExceptio n e1) if(chax un. equals(chax un) IJOptionPane.showMessageDialog(this, 查询失败); 1elseJOptio

17、nPane.showMessageDialog(this,刷新失败！)；public void actio nPerformed(Actio nEve nt e) if (b1.equals(e.getSource() /录入 Connection con;Stateme nt sql;ResultSet rs;try Class.forName(org.gjt.mm.mysql.Driver); catch (ClassNotFou ndException e1) System.out.pri ntln ( + e1); try con=DriverMa nager.getCo nn ect

18、io n(jdbc:mysql:/localhost :3306/dba,root,123457);sql = con .createStateme nt();rs=sql.executeQuery(select* from gly wheregln ame=+name+;);while(rs. next()power=rs.getStri ng(power); Irs=n ull;if(power.equals(aa)tryStri ngin sertStr= INSERT INTO yg(no,n ame,jbgz,jt)VALUES(+tf1.getText()+,+tf2.getTex

19、t()+,+tf4.getText()+,+tf3.getText()+);sql.executeUpdate(i nsertStr);JOptionPane.showMessageDialog(this,录入成功！)；catch(SQLExcepti on e2)JOptionPane.showMessageDialog(this,输入的数值有误);Ltf1.setText();tf2.setText();tf3.setText();tf4.setText();chax un();elseJOptionPane.showMessageDialog(this,权限不足,不能操作！ )； _tf

20、1.setText();tf2.setText();tf3.setText();tf4.setText();con .close(); sql.close();catch (SQLExceptio n e1) JOptionPane.showMessageDialog(this,录入失败!）；if (b2.equals(e.getSource() / Connection con;Stateme nt sql;ResultSet rs;try Class.forName(com.mysql.jdbc.Driver); catch (ClassNotFou ndException e1) Sys

21、tem.out.pri ntln ( + e1); try con:DriverMa nager.getC onn ectio n(jdbc:mysql:/localhost:3306/d ba,root,123457);sql = con .createStateme nt();rs=sql.executeQuery(select* from gly wheregln ame=+name+;);while(rs. next()power=rs.getStri ng(power);rs=n ull;if(power.equals(aa)rs=sql.executeQuery(select* f

22、rom yg whereno=+tf1.getText()+”);if(rs. next()Stri ng updateStr = UPDATE y g SET jt=+tf3.getText()+,jbgz=+tf4.getText()+, name=”+tf2.getText()+where n o=”+tf1.getText()+;sql.executeUpdate(updateStr);JOptionPane.showMessageDialog(this,修改成功！)；tf1.setText();tf2.setText();tf3.setText();tf4.setText();cha

23、x un();/chax un();con. close();elseJOptionPane.showMessageDialog(this,查无此人)；tf1.setText();tf2.setText();tf3.setText();tf4.setText();elseJOptionPane.showMessageDialog(this,权限不足，不能操作！)；tf1.setText();tf2.setText();tf3.setText();tf4.setText();con. close();sql.close(); catch (SQLExcepti on e1) JOptionPan

24、e.showMessageDialog(this,信息不错误或不完整！)；if (b3.equals(e.getSource() /删 除Connection con;Stateme nt sql;ResultSet rs;try Class.forName(com.mysql.jdbc.Driver); catch (ClassNotFou ndException e1) System.out.pri ntln ( + e1); try con=DriverMa nager.getC onn ectio n(jdbc:mysql:/localhost:3306/d ba,root,12345

25、7);sql=con .createStateme nt(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.TYPE_SCROLL_SENSITIVE);rs=sql.executeQuery(select* from gly wheregln ame=+name+;);System.out.println(”+n ame);while(rs. next()power二rs.getStri ng(power); rs二null;if(power.equals(aa)rs=sql.executeQuery(select* from yg whereno=”+tf

26、6.getText()+”);if(rs. next()sql.executeUpdate(DELETEFROM yg whereno=+tf6.getText()+;);sql.executeUpdate(DELETEFROM yg whereno=+tf6.getText()+;);JOptionPane.showMessageDialog(this,删除成功！)；tf6.setText();chax un();elseJOptio nPan e.showMessageDialog(this,查无此人);tf6.setText();elseJOptionPane.showMessageDi

27、alog(this,权限不足，不能操作！)；tf6.setText();con. close();sql.close(); catch (SQLExcepti on e1) JOptionPane.showMessageDialog(this,删除失if (b4.equals(e.getSource() /查 询全部chax un=chax un;chax un();if(b6.equals(e.getSource()查询单 条信息Connection con;Stateme nt sql;ResultSet rs;/Vector vector = new Vector();try Class

28、.forName(com.mysql.jdbc.Driver); catch (ClassNotFou ndException e1) 连接数据JOptio nPan e.showMessageDialog(this,库失败！）； try conDriverMa nager.getC onn ectio n(jdbc:mysql:/localhost:3306/d ba,root,123457);sqlcon .createStateme nt(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.TYPE_SCROLL_SENSITIVE);from yg wh

29、erers=sql.executeQuery(select no=+tf6.getText()+”);if(rs. next()for (int i = 0; i 50; i+)for (i nt j = 0; j 4; j+)table.setValueAt(, i, j);rs=sql.executeQuery(selectno,n ame,jbgz,jt no=+tf6.getText()+;);/disti netfromygwhereint k = 0;while (rs. next() Stri ng no = rs.getStri ng(1);Stri ng n ame = rs

30、.getStri ng(2);String jbgz = rs.getStri ng(3);Stri ng jt = rs.getStri ng(4);/int sum= rs.getI nt(3)+rs.get In t(4); table.setValueAt(no, k, 0);table.setValueAt(name, k, 1);table.setValueAt(jbg z, k, 2);table.setValueAt(jt, k, 3);tf6.setText();elseJOptio nPan e.showMessageDialog(this,查无此人);tf6.setTex

31、t(); Itable.setValueAt(sum,k,4);/con. close();sql.close(); catch (SQLExcepti on e1) JOptionPane.showMessageDialog(this,查询失败！)；if (b5.equals(e.getSource() /返 回GZGLZJM gl=new GZGLZJM( name);public void actio nPerformed(Actio nEve nt e) if (b1.equals(e.getSource() /查询个 人工资Connection con;Stateme nt sql;

32、ResultSet rs;try Class.forName(com.mysql.jdbc.Driver); catch (ClassNotFou ndException e1) System.out.pri ntln ( + e1); try conDriverMa nager.getC onn ectio n(jdbc:mysql:/localhost:3306/d ba,root,123457);sqlcon .createStateme nt(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);rs = sql.ex

33、ecuteQuery(selectno,n amejtjbgzfrom yg where n ame=+yg name+;);/dist inctint k = 0;for (int i = 0; i 50; i+) for (i nt j = 0; j 4; j+) table.setValueAt(, i, j);/System.out.pri ntln (t1);while (rs. next() Stri ng no = rs.getStri ng(1);Stri ng n ame = rs.getStri ng(2);String jbgz = rs.getStri ng(4);St

34、ri ng jt = rs.getStri ng(3);int sum=rs.getl nt(3)+rs.getl nt(4);table.setValueAt(no, k, 0);table.setValueAt(name, k, 1); table.setValueAt(jbg z, k, 3); table.setValueAt(jt, k, 2); table.setValueAt(sum,k,4);if (b2.equals(e.getSource()/ 修改密码XGMM xgmm=new XGMM(yg name);败！查询失 con. close(); sql.close();

35、catch (SQLExcepti on e1) JOptio nPan e.showMessageDialog(this,xgmm.create() ;if (b3.equals(e.getSource() /返 回DL dl=new DL(); dl.create(); f.dispose();void chax un()Conn ecti on con;Stateme nt sql;ResultSet rs;try Class.forName(com.mysql.jdbc.Driver); catch (ClassNotFou ndException e1) JOptionPane.sh

36、owMessageDialog(this,连接数据库失败！)；try con=DriverMa nager.getC onn ectio n(jdbc:mysql:/localhost:3306/d ba,root,123457);sqlcon .createStateme nt(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);for (int i = 0; i 50; i+)for (i nt j = 0; j 6; j+)table.setValueAt(, i, j);rs=sql.executeQuery(sel

37、ectno,name,jbgz,jt,mmfrom yg order by jbgz+jt);/distinctint k = -1;while (rs.n ext() k+;String no = rs.getStri ng(1); Stri ng n ame = rs.getStri ng(2); Stri ng jt = rs.getStri ng(4); String jbgz = rs.getStri ng(3);int sum=rs.get In t(3)+rs.getI nt(4);Stri ng mm = rs.getStri ng(5);table.setValueAt(no

38、, k, 0);table.setValueAt(name, k, 1);table.setValueAt(jt, k, 2);table.setValueAt(jbg z, k, 3);table.setValueAt(sum,k,4);table.setValueAt(mm,k,5); Icon. close();sql.close(); catch (SQLException e1) if(chax un. equals(chax un)JOptionPane.showMessageDialog(this,查询失败！)；else败！ 添加新员工信息public void action P

39、erformed(Acti on Eve nt e) if (b1.equals(e.getSource() /if(power.equals(aa) Connection con;Stateme nt sql;ResultSet rs;try Class.forName(com.mysql.jdbc.Driver); catch (ClassNotFou ndException e1) JOptionPane.showMessageDialog(this,连接数据库失败！)； try con=DriverMa nager.getC onn ectio n(jdbc:mysql:/localh

40、ost:3306/d ba,root,123457);sql=con .createStateme nt(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);rs=sql.executeQuery(selectpower from glywhere gln ame=+name+;);while(rs. next()power=rs.getStri ng(power);if(power.equals(aa)tryStri ngin sertStr= INSERT INTO yg(n o, namejtjbg z,m m)VALU

41、ES(+tf1.getText()+,+tf2.getT ext()+,+tf3.getText()+,+tf4.getText()+,+tf5.ge tText()+);sql.executeUpdate(i nsertStr);JOptio nPan e.showMessageDia log(this, 添加成功！); I.catch(SQLExcepti on e2)JOptionPane.showMessageDialog(this,输入数值有误); tf1.setText();tf2.setText();tf3.setText();tf4.setText();tf5.setText(

42、);chax un();con. close();elseJOptionPane.showMessageDialog(this,管理员权限不足！);tf1.setText();tf2.setText();tf3.setText();tf4.setText();tf5.setText();_J con .close();败!）；sql.close(); catch (SQLExcepti on e1) JOptio nPan e.showMessageDialog(this,添加失if (b2.equals(e.getSource() /Connection con;Stateme nt sql

43、;ResultSet rs;try Class.forName(com.mysql.jdbc.Driver); catch (ClassNotFou ndException e1) JOptio nPan e.showMessageDialog(this,库失败！)； try conDriverMa nager.getC onn ectio n(jdbc:mysql:/localhost:3306/d ba,root,123457);sqlcon .createStateme nt(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_O

44、NLY);rs=sql.executeQuery(selectpower from glywhere gln ame=+name+;);while(rs. next()power=rs.getStri ng(power);rs=n ull;if(power.equals(aa)rs=sql.executeQuery(selectwhere n o=”+tf1.getText()+;);if(rs. next()Stri ng修改员工信息连接数据* from ygupdateStr jt=+tf3.getText()+,mm=+tf5.getText()+,jbgz=+tf4. getText(

45、)+, name=+tf2.getText()+where n o=+tf1.getText()+;UPDATE yg SETsql.executeUpdate(updateStr);JOptio nPan e.showMessageDialog(this,修改成功！tf1.setText();tf2.setText();tf3.setText();tf4.set Text();tf5.setText();chaxu n();con. close();else)；JOpti onPan e.showMessageDialog(this,查无此人！)；tf1.setText();tf2.setT

46、ext();tf3.setText();tf4.setText();tf5.setText(); elseJOptionPane.showMessageDialog(this,管理员权限不足！);tf1.setText();tf2.setText();tf3.setText();tf4.setText();tf5.setText();con. close(); sql.close(); catch (SQLExcepti on e1) JOptionPane.showMessageDialog(this,请再确认你要更改的信息！)；if (b3.equals(e.getSource() /删除

47、员工信息if(power.equals(aa)Connection con;Stateme nt sql;ResultSet rs; try Class.forName(com.mysql.jdbc.Driver); catch (ClassNotFou ndException e1) JOptionPane.showMessageDialog(this,连接数据库失败！)； r try con=DriverMa nager.getC onn ectio n(jdbc:mysql:/localhost:3306/d ba,root,123457);sql=con .createStateme

48、nt(ResultSet.TYPE_SCROLL_SENSITIVE,Result Set.CONCUR_READ_ONLY);rs=sql.executeQuery(select* from gly wheregln ame=+name+;);while(rs. next()power二rs.getStri ng(power); Irs=n ull;if(power.equals(aa)rs=sql.executeQuery(selectno FROM yg whereno=”+tf6.getText()+;);if(rs. next()sql.executeUpdate(DELETEFRO

49、M yg whereno=+tf6.getText()+;);JOptionPane.showMessageDialog(this,删除成功！ );tf6.setText();chaxun();con. close(); |elseJOptionPane.showMessageDialog(this,请输入正确的员工工号！)；tf6.setText();elseJOptio nPan e.showMessageDialog(this,管理员权 限不足);tf6.setText(); Icon. close();sql.close(); catch (SQLExcepti on e1) JOptionPane.showMessageDialog(this,请 输入员工的 工号！ );tf6.setText();/else/JOptionPane.showMessageDialog(this,管理员权限不足！)；/ if (b4.equals(e.getSource() /查询全部员 工信息chax un=chax un;chax un();if (b5.equals(e.getSource() /返 回GZGLZJM gl=new GZGLZJM( name);gl.create();f.dispose();/查询单条