1、具有菜单,包括如下菜单:
员工管理:录入员工信息、删除员工信息、修改员工信息、查询员工信息
工资管理:录入员工工资、修改员工工资、查询员工工资、统计工资总额(某个时间段的)
关于:帮助,帮助中写明开发者和开发日期,开发者是你本人。
主界面如图所示:
2、数据库包括2个主要表
Employee(employeeID,employeeName,sex,workage,gangweui),即包括员工编号、员工姓名、性别、工龄、岗位,其中employeeID为主键。
Wage(employeeD,year,month,gangweiwage,salary,baoxian,gongjijin,yingfa,shifa),即员工编号,年份,月份,岗位工资,奖金,扣的保险,扣的公积金,应发工资,实发工资。其中应发工资=岗位工资+奖金,实发工资=应发工资-保险-公积金。其中employeeD,year,month是主键。
为了显示员工工资时能够显示员工的姓名,可以建立视图vWage,从Employee表中选择employeeID,employeeName,从wage表中选择除了employeeD外的其他列组成视图vWage。
注意,wage表中的employeeD是外键。
3、类的设计:
应该采用分层开发,分为界面层、业务逻辑层、数据访问层、数据库联接层。
数据库联接层,可以命名为DBConnection,负责连接数据库,得到Connection对象
数据访问层,是针对每个表或视图建立一个类,负责完成对该表的插入、删除、修改、查询操作,其中查询可以查询所有的数据,可以按照主键查询。
业务逻辑层,负责根据实际业务和界面的功能要求,进行编写,需要读写数据时,调用数据访问层类来实现。业务逻辑层不应该再出现sql语句,这里感觉不到数据库的存在,感觉数据需要数据时只是从数据访问层类中获得即可。
界面层,负责画出图形界面,根据界面事件调用业务逻辑层类来实现。
数据访问层类一般以 表名(或视图)+DAL的格式来命名。
业务逻辑层类一般以 表名(或视图)+BLL的格式来命名。
另外,一般还把每个表和视图还建立一个实体类,以表或试图的名字来命名,类中只有属性和构造方法以及set/get方法。
Staff_Manage类:
import java.awt.BorderLayout;
import java.awt.Container;
import java.awt.FlowLayout;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.JButton;
import javax.swing.JDialog;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JMenu;
import javax.swing.JMenuBar;
import javax.swing.JMenuItem;
import javax.swing.JOptionPane;
import javax.swing.JScrollPane;
import javax.swing.JTabbedPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;
public class Staff_Manage extends JFrame{
Container c,addPane,serachPane,appearPane,salaryPane,xunzhaoPane,xianshiPane,salarysearchPane,salaryapperaPane;
int iyingfa,ishifa;
JMenuBar jmb;
JMenu staff;
JMenu salary;
JMenu about;
JMenuItem add_staff;
JMenuItem delete_staff;
JMenuItem alter_staff;
JMenuItem select_staff;
JMenuItem enter_salary;
JMenuItem alter_salary;
JMenuItem select_salary;
JMenuItem count_salary;
JMenuItem help;
JTabbedPane jtp;//选项卡
JScrollPane staffPanel;//滚动面板
JScrollPane salaryPanel;
DefaultTableModel dftstaff;
DefaultTableModel dftsalary;
JTable tablestaff;
JTable tablesalary;
JDialog staffsavedialog;
JDialog staffsearchdialog;
JDialog staffapperadialog;
JDialog salarysavedialog;
JDialog salaryfsearchdialog;
JDialog salaryapperadialog;
JDialog salaychaxundialog;
JDialog salarycountdialog;
JButton jbsave=new JButton("确认");
JButton jbxiugai=new JButton("修改");
JButton jbno=new JButton("取消");
JButton jbtnsearch=new JButton("确认");
JButton jbtnxunzhao=new JButton("确认");
JButton jbtncount=new JButton("确认");
JButton jbtsalarysave=new JButton("确认");
JButton jbtsalaryxiugai=new JButton("修改");
JButton jbtsalaryquxiao=new JButton("取消");
JLabel jlsearch;
JLabel jlmonth;
JLabel jlsyingfa;
JLabel jlsshifa;
JTextField jtfid;
JTextField jtfname;
JTextField jtfsex;
JTextField jtfyear;
JTextField jtfgangwei;
JTextField jtfsid;
JTextField jtfsyear;
JTextField jtfsmonth;
JTextField jtfsgangweigongzi;
JTextField jtfsjiangjin;
JTextField jtfsbaoxian;
JTextField jtfsgongjijin;
staff staffinfo=new staff();
salary salaryinfo=new salary();
String[][] data_staff;
String[] columNames_staff;
String[][] data_salary;
String[] columNames_salary;
JTextField jtfsearch;
JTextField jtfmonth;
staffDB staffDb=new staffDB();
salaryDB salaryDb=new salaryDB();
public void init(){
c=this.getContentPane();
c.setLayout(new BorderLayout());
jmb=new JMenuBar();
staff=new JMenu("员工管理");salary=new JMenu("工资管理");about=new JMenu("关于");
add_staff=new JMenuItem("添加新员工");
//添加新员工
add_staff.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
Staff_Manage sm=new Staff_Manage();
staffsavedialog=new JDialog(sm,"添加新员工",true);
addPane=staffsavedialog.getContentPane();
addPane.setLayout(new GridLayout(6,2));
staffDialog("add");
staffsavedialog.setSize(300,250);
staffsavedialog.setVisible(true);
}
});
//添加按钮
jbsave.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
boolean flag=staffDb.staffSave(getStaffInfo());
if(flag){
JOptionPane.showConfirmDialog(null, "插入成功");
staffsavedialog.dispose();
data_staff=staffDb.getstaffInfo();
dftstaff.setDataVector(data_staff, columNames_staff);
}
else{
JOptionPane.showConfirmDialog(null, "插入失败");
}
}
});
//取消按钮
jbno.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
staffsavedialog.dispose();//关闭窗口
}
});
delete_staff=new JMenuItem("删除员工");
//删除员工
delete_staff.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
int delrow=tablestaff.getSelectedRow();
String staffid=(String)tablestaff.getValueAt(delrow, 0);
staffinfo.setStaffid(staffid);
boolean flag=staffDb.staffDelete(staffinfo);
if(flag){
JOptionPane.showConfirmDialog(null, "删除成功");
data_staff=staffDb.getstaffInfo();
dftstaff.setDataVector(data_staff, columNames_staff);
}
else{
JOptionPane.showConfirmDialog(null, "删除失败");
}
}
});
alter_staff=new JMenuItem("修改员工信息");
//修改员工信息
alter_staff.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
Staff_Manage sm=new Staff_Manage();
staffsavedialog=new JDialog(sm,"修改新员工",true);
addPane=staffsavedialog.getContentPane();
addPane.setLayout(new GridLayout(6,2));
staffDialog("update");
staffsavedialog.setSize(300,250);
staffsavedialog.setVisible(true);
}
});
//修改按钮
jbxiugai.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
boolean flag=staffDb.staffUpdate(getStaffInfo());
if(flag){
JOptionPane.showConfirmDialog(null, "修改成功");
staffsavedialog.dispose();
data_staff=staffDb.getstaffInfo();
dftstaff.setDataVector(data_staff, columNames_staff);
}
else{
JOptionPane.showConfirmDialog(null, "修改失败");
}
}
});
select_staff=new JMenuItem("查询员工");
//查询员工信息
select_staff.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
Staff_Manage sm=new Staff_Manage();
staffsearchdialog=new JDialog(sm,"查询新员工",true);
serachPane=staffsearchdialog.getContentPane();
serachPane.setLayout(new FlowLayout());
jlsearch=new JLabel("请输入要查询的员工编号:");
jtfsearch=new JTextField(10);
serachPane.add(jlsearch);serachPane.add(jtfsearch);serachPane.add(jbtnsearch);
staffsearchdialog.setSize(200,200);
staffsearchdialog.setVisible(true);
}
});
//查询按钮
jbtnsearch.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
String searchid=jtfsearch.getText().toString();
String info[]=staffDb.staffQuery(searchid);
Staff_Manage sm=new Staff_Manage();
staffapperadialog=new JDialog(sm,"员工信息",true);
appearPane=staffapperadialog.getContentPane();
appearPane.setLayout(new GridLayout(5,2));
JLabel jlid=new JLabel("员工编号:");
JLabel jlname=new JLabel("员工姓名:");
JLabel jlsex=new JLabel("性 别 : ");
JLabel jlyear=new JLabel("工 龄: ");
JLabel jlgangwei=new JLabel("岗 位 : ");
JLabel jlaid=new JLabel(info[0]);
JLabel jlaname=new JLabel(info[1]);
JLabel jlasex=new JLabel(info[2]);
JLabel jlayear=new JLabel(info[3]);
JLabel jlagangwei=new JLabel(info[4]);
appearPane.add(jlid);appearPane.add(jlaid);
appearPane.add(jlname);appearPane.add(jlaname);
appearPane.add(jlyear);appearPane.add(jlayear);
appearPane.add(jlsex);appearPane.add(jlasex);
appearPane.add(jlgangwei);appearPane.add(jlagangwei);
staffapperadialog.setSize(300,250);
staffapperadialog.setVisible(true);
}
});
enter_salary=new JMenuItem("录入员工工资");
//录入员工工资
enter_salary.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
Staff_Manage sm=new Staff_Manage();
salarysavedialog=new JDialog(sm,"录入员工工资",true);
salaryPane=salarysavedialog.getContentPane();
salaryPane.setLayout(new GridLayout(8,2));
salaryDialog("add");
salarysavedialog.setSize(300,250);
salarysavedialog.setVisible(true);
}
});
//录入按钮
jbtsalarysave.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
boolean flag=salaryDb.salarySave(getSalaryInfo());
if(flag){
JOptionPane.showConfirmDialog(null, "插入成功");
salarysavedialog.dispose();
data_salary=salaryDb.getsalaryInfo();
dftsalary.setDataVector(data_salary, columNames_salary);
}
else{
JOptionPane.showConfirmDialog(null, "插入失败");
}
}
});
//取消按钮
jbtsalaryquxiao.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
salarysavedialog.dispose();//关闭窗口
}
});
alter_salary=new JMenuItem("修改员工工资");
//修改工资信息
alter_salary.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
Staff_Manage sm=new Staff_Manage();
salarysavedialog=new JDialog(sm,"修改员工工资",true);
salaryPane=salarysavedialog.getContentPane();
salaryPane.setLayout(new GridLayout(8,2));
salaryDialog("update");
salarysavedialog.setSize(300,250);
salarysavedialog.setVisible(true);
}
});
//修改按钮
jbtsalaryxiugai.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
boolean flag=salaryDb.salaryUpdate(getSalaryInfo());
if(flag){
JOptionPane.showConfirmDialog(null, "修改成功");
salarysavedialog.dispose();
data_salary=salaryDb.getsalaryInfo();
dftsalary.setDataVector(data_salary, columNames_salary);
}
else{
JOptionPane.showConfirmDialog(null, "修改失败");
}
}
});
select_salary=new JMenuItem("查询员工工资");
//查询员工工资
select_salary.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
Staff_Manage sm=new Staff_Manage();
salarysavedialog=new JDialog(sm,"查询员工工资",true);
xunzhaoPane=salarysavedialog.getContentPane();
xunzhaoPane.setLayout(new FlowLayout());
jlsearch=new JLabel("请输入要查询的员工编号:");
jtfsearch=new JTextField(10);
xunzhaoPane.add(jlsearch);xunzhaoPane.add(jtfsearch);xunzhaoPane.add(jbtnxunzhao);
salarysavedialog.setSize(200,200);
salarysavedialog.setVisible(true);
}
});
//查询按钮
jbtnxunzhao.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
String searchid=jtfsearch.getText().toString();
String info[]=salaryDb.salaryQuery(searchid);
Staff_Manage sm=new Staff_Manage();
salaryapperadialog=new JDialog(sm,"员工工资信息",true);
xianshiPane=salaryapperadialog.getContentPane();
xianshiPane.setLayout(new GridLayout(10,2));
JLabel jlid=new JLabel("员工编号:");
JLabel jlname=new JLabel("员工姓名:");
JLabel jlyear=new JLabel("年 份 : ");
JLabel jlmonth=new JLabel("月 份: ");
JLabel jlgangweiwage=new JLabel("岗位工资 : ");
JLabel jlsalary=new JLabel("奖 金 : ");
JLabel jlbaoxian=new JLabel("保 险 : ");
JLabel jlgongjijin=new JLabel("公积金 : ");
JLabel jlyingfa=new JLabel("应发工资 : ");
JLabel jlshifa=new JLabel("实发工资 : ");
JLabel jlsid=new JLabel(info[0]);
JLabel jlsname=new JLabel(info[9]);
JLabel jlsyear=new JLabel(info[1]);
JLabel jlsmonth=new JLabel(info[2]);
JLabel jlsgangweiwage=new JLabel(info[3]);
JLabel jlssalary=new JLabel(info[4]);
JLabel jlsbaoxian=new JLabel(info[5]);
JLabel jlsgongjijin=new JLabel(info[6]);
JLabel jlsyingfa=new JLabel(info[7]);
JLabel jlsshifa=new JLabel(info[8]);
xianshiPane.add(jlid);xianshiPane.add(jlsid);
xianshiPane.add(jlname);xianshiPane.add(jlsname);
xianshiPane.add(jlyear);xianshiPane.add(jlsyear);
xianshiPane.add(jlmonth);xianshiPane.add(jlsmonth);
xianshiPane.add(jlgangweiwage);xianshiPane.add(jlsgangweiwage);
xianshiPane.add(jlsalary);xianshiPane.add(jlssalary);
xianshiPane.add(jlbaoxian);xianshiPane.add(jlsbaoxian);
xianshiPane.add(jlgongjijin);xianshiPane.add(jlsgongjijin);
xianshiPane.add(jlyingfa);xianshiPane.add(jlsyingfa);
xianshiPane.add(jlshifa);xianshiPane.add(jlsshifa);
salaryapperadialog.setSize(300,250);
salaryapperadialog.setVisible(true);
}
});
count_salary=new JMenuItem("统计工资总额");
//统计工资总额
count_salary.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
Staff_Manage sm=new Staff_Manage();
salaychaxundialog=new JDialog(sm,"统计工资总额",true);
salarysearchPane=salaychaxundialog.getContentPane();
salarysearchPane.setLayout(new GridLayout(3,2));
jlsearch=new JLabel("请输入要查询的年份:");
jlmonth=new JLabel("请输入要查询的月份");
jtfsearch=new JTextField(10);
jtfmonth=new JTextField(10);
salarysearchPane.add(jlsearch);salarysearchPane.add(jtfsearch);
salarysearchPane.add(jlmonth);salarysearchPane.add(jtfmonth);
salarysearchPane.add(jbtncount);
salaychaxundialog.setSize(300,200);
salaychaxundialog.setVisible(true);
}
});
//统计按钮
jbtncount.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
String searchyear=jtfsearch.getText().toString();
String searchmonth=jtfmonth.getText().toString();
int count=salaryDb.salaryCount(searchyear,searchmonth);
Staff_Manage sm=new Staff_Manage();
salarycountdialog=new JDialog(sm,searchyear+"年"+searchmonth+"月的工资总额",true);
salaryapperaPane=salarycountdialog.getContentPane();
salaryapperaPane.setLayout(new FlowLayout());
JLabel jlcount=new JLabel(searchyear+"年"+searchmonth+"月的工资总额");
JLabel jlans=new JLabel(String.valueOf(count));
salaryapperaPane.add(jlcount);salaryapperaPane.add(jlans);
salarycountdialog.setSize(200,100);
salarycountdialog.setVisible(true);
}
});
help=new JMenuItem("帮助");
help.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
JOptionPane.showConfirmDialog(about, "开发者:李鸿超 开发日期:2014年5月18日");
}
});
c.add(jmb,BorderLayout.NORTH);
staff.add(add_staff);staff.add(delete_staff);staff.add(alter_staff);staff.add(select_staff);
salary.add(enter_salary);salary.add(alter_salary);salary.add(select_salary);salary.add(count_salary);
about.add(help);
jmb.add(staff);jmb.add(salary);jmb.add(about);
jtp=new JTabbedPane();
jtp.setTabPlacement(JTabbedPane.TOP);
data_staff=new String[50][5];
columNames_staff=new String[]{"员工编号","员工姓名","性别","工龄","岗位"};
data_staff=staffDb.getstaffInfo();
dftstaff=new DefaultTableModel(data_staff,columNames_staff);
tablestaff=new JTable(data_staff,columNames_staff);
staffPanel=new JScrollPane (tablestaff);//添加员工信息选项卡的表格
data_salary=new String[50][9];
columNames_salary=new String[]{"员工编号","年份","月份","岗位工资","奖金","扣的保险","扣的公积金","应发工资","实发工资"};
data_salary=salaryDb.getsalaryInfo();
tablesalary=new JTable(data_salary,columNames_salary);
salaryPanel=new JScrollPane(tablesalary);//添加工资信息选项卡的表格
jtp.add("员工信息",staffPanel);//添加员工信息选项卡
jtp.add("工资信息",salaryPanel);//添加工资信息选项卡
c.add(jtp);
this.setSize(800,700);
this.setVisible(true);
}
//添加和修改员工界面
public void staffDialog(String s){
JLabel jlid=new JLabel("员工编号:");
JLabel jlname=new JLabel("员工姓名:");
JLabel jlsex=new JLabel("性 别 : ");
JLabel jlyear=new JLabel("工 龄: ");
JLabel jlgangwei=new JLabel("岗 位 : ");
jtfid=new JTextField(15);
jtfname=new JTextField(15);
jtfsex=new JTextField(15);
jtfyear=new JTextField(15);
jtfgangwei=new JTextField(15);
addPane.add(jlid);addPane.add(jtfid);
addPane.add(jlname);addPane.add(jtfname);
addPane.add(jlyear);addPane.add(jtfyear);
addPane.add(jlsex);addPane.add(jtfsex);
addPane.add(jlgangwei);addPane.add(jtfgangwei);
if(s.equals("add")){
addPane.add(jbsave);
addPane.add(jbno);
}
if(s.equals("update")){
addPane.add(jbxiugai);
addPane.add(jbno);
}
}
//录入和修改工资界面
public void salaryDialog(String s){
JLabel jlid=new JLabel("员工编号:");
JLabel jlyear=new JLabel("年 份:");
JLabel jlmonth=new JLabel("月 份 : ");
JLabel jlgangweigongzi=new JLabel("岗位工资: ");
JLabel jljiangjin=new JLabel("奖 金 : ");
JLabel jlbaoxian=new JLabel("保 险: ");
JLabel jlgongjijin=new JLabel("公积金: ");
JLabel jlyingfa=new JLabel("应发工资 : ");
JLabel jlshifa=new JLabel("实发工资 : ");
jlsyingfa=new JLabel();
jlsshifa=new JLabel();
jtfsid=new JTextField(15);
jtfsyear=new JTextField(15);
jtfsmonth=new JTextField(15);
jtfsgangweigongzi=new JTextField(15);
jtfsjiangjin=new JTextField(15);
jtfsbaoxian=new JTextField(15);
jtfsgongjijin=new JTextField(15);
jlsyingfa.setText(String.valueOf(iyingfa));
jlsshifa.setText(String.valueOf(ishifa));
salaryPane.add(jlid);salaryPane.add(jtfsid);
salaryPane.add(jlyear);salaryPane.add(jtfsyear);
salaryPane.add(jlmonth);salaryPane.add(jtfsmonth);
salaryPane.add(jlgangweigongzi);salaryPane.add(jtfsgangweigongzi);
salaryPane.add(jljiangjin);salaryPane.add(jtfsjiangjin);
salaryPane.add(jlbaoxian);salaryPane.add(jtfsbaoxian);
salaryPane.add(jlgongjijin);salaryPane.add(jtfsgongjijin);
if(s.equals("add")){
salaryPane.add(jbtsalarysave);
salaryPane.add(jbtsalaryquxiao);
}
if(s.equals("update")){
salaryPane.add(jbtsalaryxiugai);
salaryPane.add(jbtsalaryquxiao);
}
}
//获得员工信息
public staff getStaffInfo(){
staffinfo.setStaffid(jtfid.getText());
staffinfo.setStaffname(jtfname.getText());
staffinfo.setStaffsex(jtfsex.getText());
staffinfo.setStaffage(Integer.parseInt(jtfyear.getText()));
staffinfo.setStaffgangwei(jtfgangwei.getText());
return staffinfo;
}
//获得工资信息
public salary getSalaryInfo(){
iyingfa=Integer.parseInt(jtfsgangweigongzi.getText().toString())+Integer.parseInt(jtfsjiangjin.getText().toString());
ishifa=iyingfa-Integer.parseInt(jtfsbaoxian.getText())-Integer.parseInt(jtfsgongjijin.getText());
salaryinfo.setStaffid(jtfsid.getText());
salaryinfo.setYear(Integer.parseInt(jtfsyear.getText()));
salaryinfo.setMonth(Integer.parseInt(jtfsmonth.getText()));
salaryinfo.setGangweigongzi(Integer.parseInt(jtfsgangweigongzi.getText()));
salaryinfo.setSalary(Integer.parseInt(jtfsjiangjin.getText()));
salaryinfo.setBaoxian(Integer.parseInt(jtfsbaoxian.getText()));
salaryinfo.setGongjijin(Integer.parseInt(jtfsgongjijin.getText()));
salaryinfo.setYingfa(iyingfa);
salaryinfo.setShifa(ishifa);
return salaryinfo;
}
public Staff_Manage(){
}
public Staff_Manage(String s){
super(s);
init();
}
public static void main(String[] args) {
Staff_Manage sm=new Staff_Manage("员工管理系统");
}
}
staff类:
public class staff {
String staffid;
String staffname;
String staffsex;
int staffage;
String staffgangwei;
public String getStaffid() {
return staffid;
}
public void setStaffid(String staffid) {
this.staffid = staffid;
}
public String getStaffname() {
return staffname;
}
public void setStaffname(String staffname) {
this.staffname = staffname;
}
public String getStaffsex() {
return staffsex;
}
public void setStaffsex(String staffsex) {
this.staffsex = staffsex;
}
public int getStaffage() {
return staffage;
}
public void setStaffage(int staffage) {
this.staffage = staffage;
}
public String getStaffgangwei() {
return staffgangwei;
}
public void setStaffgangwei(String staffgangwei) {
this.staffgangwei = staffgangwei;
}
}
staffDB类:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class staffDB {
static JDBC jdbc=null;
public staffDB(){
if(jdbc==null)jdbc=new JDBC();
}
//维护
public String[][] getstaffInfo(){
String dataVector[][]=new String[50][5];
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
String query="";
try {
conn=jdbc.getConnection();
query="select * from Employee";
ps=conn.prepareStatement(query);
rs=ps.executeQuery();
int num=0;
while(rs.next()){
dataVector[num][0]=rs.getString("employeeID");
dataVector[num][1]=rs.getString("employeeName");
dataVector[num][2]=rs.getString("sex");
dataVector[num][3]=rs.getString("workage");
dataVector[num++][4]=rs.getString("gangwei");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return dataVector;
}
//查询
public String[] staffQuery(String searchid){
String info[]=new String[5];
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
String query="select * from Employee where employeeID="+searchid;
System.out.println(query);
try {
conn = jdbc.getConnection();
ps=conn.prepareStatement(query);
rs=ps.executeQuery();
while(rs.next()){
info[0]=searchid;
info[1]=rs.getString("employeeName");
info[2]=rs.getString("sex");
info[3]=rs.getString("workage");
info[4]=rs.getString("gangwei");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return info;
}
//添加
public boolean staffSave(staff staffinfo){
Connection conn=null;
PreparedStatement ps=null;
boolean flag=false;
try {
conn=jdbc.getConnection();//获取连接
String insert="insert into Employee(employeeID,employeeName,sex,workage,gangwei) values (?,?,?,?,?)";
ps=conn.prepareStatement(insert);
ps.setString(1, staffinfo.getStaffid());
ps.setString(2, staffinfo.getStaffname());
ps.setString(3, staffinfo.getStaffsex());
ps.setInt(4, staffinfo.getStaffage());
ps.setString(5, staffinfo.getStaffgangwei());
ps.executeUpdate();
flag=true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return flag;
}
//删除
public boolean staffDelete(staff staffinfo){
boolean flag=false;
Connection conn=null;
PreparedStatement ps=null;
try {
conn=jdbc.getConnection();
String delete="delete from Employee where employeeID=?";
ps=conn.prepareStatement(delete);
ps.setString(1, staffinfo.getStaffid());
ps.executeUpdate();
flag=true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return flag;
}
//更新
public boolean staffUpdate(staff staffinfo){
Connection conn=null;
PreparedStatement ps=null;
boolean flag=false;
try {
conn=jdbc.getConnection();
String update="update Employee set employeeID=?,employeeName=?,sex=?,workage=?,gangwei=? where employeeID=?";
ps=conn.prepareStatement(update);
ps.setString(1, staffinfo.getStaffid());
ps.setString(2, staffinfo.getStaffname());
ps.setString(3, staffinfo.getStaffsex());
ps.setInt(4, staffinfo.getStaffage());
ps.setString(5, staffinfo.getStaffgangwei());
ps.setString(6, staffinfo.getStaffid());
ps.executeUpdate();
flag=true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return flag;
}
}
salary类:
public class salary {
String staffid;
int year;
int month;
int gangweigongzi;
int salary;
int baoxian;
int gongjijin;
int yingfa;
int shifa;
public String getStaffid() {
return staffid;
}
public void setStaffid(String staffid) {
this.staffid = staffid;
}
public int getYear() {
return year;
}
public void setYear(int year) {
this.year = year;
}
public int getMonth() {
return month;
}
public void setMonth(int month) {
this.month = month;
}
public int getGangweigongzi() {
return gangweigongzi;
}
public void setGangweigongzi(int gangweigongzi) {
this.gangweigongzi = gangweigongzi;
}
public int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public int getBaoxian() {
return baoxian;
}
public void setBaoxian(int baoxian) {
this.baoxian = baoxian;
}
public int getGongjijin() {
return gongjijin;
}
public void setGongjijin(int gongjijin) {
this.gongjijin = gongjijin;
}
public int getYingfa() {
return yingfa;
}
public void setYingfa(int yingfa) {
this.yingfa = yingfa;
}
public int getShifa() {
return shifa;
}
public void setShifa(int shifa) {
this.shifa = shifa;
}
}
salaryDB类:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class salaryDB {
static JDBC jdbc=null;
public salaryDB(){
if(jdbc==null)jdbc=new JDBC();
}
//维护
public String[][] getsalaryInfo(){
String dataVector[][]=new String[50][9];
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
String query="";
try {
conn=jdbc.getConnection();
query="select * from Wage";
System.out.println(query);
ps=conn.prepareStatement(query);
rs=ps.executeQuery();
int num=0;
while(rs.next()){
System.out.println(dataVector[num][0]);
dataVector[num][0]=rs.getString("employeeID");
dataVector[num][1]=rs.getString("year");
dataVector[num][2]=rs.getString("month");
dataVector[num][3]=rs.getString("gangweiwage");
dataVector[num][4]=rs.getString("salary");
dataVector[num][5]=rs.getString("baoxian");
dataVector[num][6]=rs.getString("gongjijin");
dataVector[num][7]=rs.getString("yingfa");
dataVector[num++][8]=rs.getString("shifa");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return dataVector;
}
//查询
public String[] salaryQuery(String searchid){
String info[]=new String[10];
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
String query="select * from Wage where employeeID="+searchid;
try {
conn = jdbc.getConnection();
ps=conn.prepareStatement(query);
rs=ps.executeQuery();
while(rs.next()){
info[0]=searchid;
info[1]=rs.getString("year");
info[2]=rs.getString("month");
info[3]=rs.getString("gangweiwage");
info[4]=rs.getString("salary");
info[5]=rs.getString("baoxian");
info[6]=rs.getString("gongjijin");
info[7]=rs.getString("yingfa");
info[8]=rs.getString("shifa");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
Connection conn1=null;
PreparedStatement ps1=null;
ResultSet rs1=null;
String query1="select * from Employee where employeeID="+searchid;
try {
conn1 = jdbc.getConnection();
ps1=conn1.prepareStatement(query1);
rs1=ps1.executeQuery();
while(rs1.next()){
info[9]=rs1.getString("employeeName");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return info;
}
//添加
public boolean salarySave(salary salaryinfo){
Connection conn=null;
PreparedStatement ps=null;
boolean flag=false;
try {
conn=jdbc.getConnection();//获取连接
String insert="insert into Wage(employeeID,year,month,gangweiwage,salary,baoxian,gongjijin,yingfa,shifa) values (?,?,?,?,?,?,?,?,?)";
ps=conn.prepareStatement(insert);
ps.setString(1, salaryinfo.getStaffid());
ps.setInt(2, salaryinfo.getYear());
ps.setInt(3, salaryinfo.getMonth());
ps.setInt(4, salaryinfo.getGangweigongzi());
ps.setInt(5, salaryinfo.getSalary());
ps.setInt(6, salaryinfo.getBaoxian());
ps.setInt(7, salaryinfo.getGongjijin());
ps.setInt(8, salaryinfo.getYingfa());
ps.setInt(9, salaryinfo.getShifa());
ps.executeUpdate();
flag=true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return flag;
}
//更新
public boolean salaryUpdate(salary salaryinfo){
Connection conn=null;
PreparedStatement ps=null;
boolean flag=false;
try {
conn=jdbc.getConnection();
String update="update Wage set employeeID=?,year=?,month=?,gangweiwage=?,salary=?,baoxian=?,gongjijin=?,yingfa=?,shifa=? where employeeID=?";
ps=conn.prepareStatement(update);
ps.setString(1, salaryinfo.getStaffid());
ps.setInt(2, salaryinfo.getYear());
ps.setInt(3, salaryinfo.getMonth());
ps.setInt(4, salaryinfo.getGangweigongzi());
ps.setInt(5, salaryinfo.getSalary());
ps.setInt(6, salaryinfo.getBaoxian());
ps.setInt(7, salaryinfo.getGongjijin());
ps.setInt(8, salaryinfo.getYingfa());
ps.setInt(9, salaryinfo.getShifa());
ps.setString(10, salaryinfo.getStaffid());
ps.executeUpdate();
flag=true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return flag;
}
//统计总额
public int salaryCount(String year,String month){
int count=0;
int yearint=Integer.parseInt(year);
int monthint=Integer.parseInt(month);
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn=jdbc.getConnection();
String query="select * from Wage where year="+yearint+"and month="+monthint;
System.out.println(query);
ps=conn.prepareStatement(query);
rs=ps.executeQuery();
while(rs.next()){
count+=rs.getInt("shifa");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return count;
}
}
JDBC类:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBC {
Statement st;
String url="jdbc:sqlserver://localhost:1433;DatabaseName=Staff_Manage";
String user="sa";
String password="a123456";
static String driverName="com.microsoft.sqlserver.jdbc.SQLServerDriver";
Connection conn;
static {
try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public Connection getConnection() throws SQLException{
conn=DriverManager.getConnection(url,user,password);
return conn;
}
public static void main(String[] args) throws ClassNotFoundException,SQLException{
JDBC jd=new JDBC();
}
}