Java实现员工管理系统（数据库存储版）

需求详见http://blog.csdn.net/hahaha_sxm/article/details/48169711
这次将存储方式变为数据库存储，相较上文，添加了一个DBConnection类，TestEMD.java重写（代码在下方贴出），另外父类Employee中添加了一个返回工资的方法（用于获取计算后的工资存于数据库）：

public Double getSalary(){        return salary;    }

先在SQLServer 2008 R2 中新建数据库名为：“EmployeeManager”，在该数据库下新建查询建一个员工表：Employee，属性为了方便些，都设为verchar(50)不推荐这么干,原谅我︿(￣︶￣)︿，顺带把把几个操作语句也粘上，好久没动数据库了，基本的语句都忘了，憋了半天憋出来的。。原谅我︿(￣︶￣)︿。

create table Employee(  id varchar(50) primary key,  name varchar(50) unique,  position varchar(50),holiday varchar(50),salary varchar(50),)insert into Employee(id,name,position,holiday,salary) values('1','张三','普通员工','3','6700.0')insert into Employee(id,name,position,holiday,salary) values('2','李四','普通员工','3','6700.0')delete from Employee where name='李四';update Employee set id='1',name='李华',position='普通员工',holiday='3',salary='7555.0' where name='张三'

数据库建完后第一步，项目中导入SQLDrive的Jar包。右键项目-build path-Configure Build path 弹出的对话框选择Libraries选项卡，点击右方的add External jar 然后选择你的驱动包点击确定就行。

接下来项目中写数据库访问类DBConnection.java

import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class DBConnection {    private static Connection conn = null;    private static final String classname="com.microsoft.sqlserver.jdbc.SQLServerDriver";    private static final String url="jdbc:sqlserver://localhost:1433;DatabaseName=EmployeeManager;user=sa;password=123";    public static Connection getConnection(){        try {            Class.forName(classname);        } catch (ClassNotFoundException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        try {            conn = DriverManager.getConnection(url);        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        return conn;    }    public static void close(){        try {            conn.close();        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }}

接下来就是进入操作类TestEMD.java的编写了，代码如下：

package EmployeeGuanli;import java.io.IOException;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.Scanner;public class TestEMD {    static Scanner sc = new Scanner(System.in);    public static void caoZuo() {        System.out.println("----     工资管理系统                  ----");        System.out.println("-------------------------------");        System.out.println("---        1     增加                        ---");        System.out.println("---        2     删除                        ---");        System.out.println("---        3     修改                        ---");        System.out.println("---        4     查询                        ---");        System.out.println("---        0     退出                        ---");        System.out.println("-------------------------------");        System.out.println("请输入你要选择的操作：");        String s = sc.next();        switch (s) {        case "1":            try {                try {                    addEmployee();                } catch (SQLException e) {                    // TODO Auto-generated catch block                    e.printStackTrace();                }            } catch (IOException e1) {                // TODO Auto-generated catch block                e1.printStackTrace();            }            break;        case "2":            try {                try {                    delEmployee();                } catch (SQLException e) {                    // TODO Auto-generated catch block                    e.printStackTrace();                }            } catch (IOException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }            break;        case "3":            try {                try {                    updateEmployee();                } catch (SQLException e) {                    // TODO Auto-generated catch block                    e.printStackTrace();                }            } catch (IOException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }            break;        case "4":            try {                queryEmployee();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }            break;        case "0":            System.out.println("谢谢使用O(∩_∩)O");            break;        default:            System.out.println("指令错误请重新输入！");            caoZuo();            break;        }    }    public static void addEmployee() throws IOException, SQLException {        Connection conn=DBConnection.getConnection();        String sql="insert into Employee(id,name,position,holiday,salary) values(?,?,?,?,?)";        PreparedStatement ps=null;        ps=conn.prepareStatement(sql);        System.out.println("------增加员工------");        System.out.println("请输入相关信息：");        System.out.print("ID：");        String id = sc.next();        System.out.print("姓名:");        String name = sc.next();        System.out.print("职务:");        String position = sc.next();        System.out.print("请假天数:");        int holiday = sc.nextInt();        System.out.print("基本工资:");        double salary = sc.nextInt();        switch (position) {        case "普通员工":            Employee a = new CommonEmployee();            a.ID = id;            a.name = name;            a.position = "普通员工";            a.holiday = holiday;            a.salary = salary;            a.sumSalary();            ps.setString(1,""+id);            ps.setString(2, name);            ps.setString(3, position);            ps.setString(4, ""+holiday);            ps.setString(5, ""+a.getSalary());            ps.executeUpdate();            ps.close();            conn.close();            System.out.println("添加成功");            break;        case "经理":            Employee b = new Manager();            b.ID = id;            b.name = name;            b.position = "经理";            b.holiday = holiday;            b.salary = salary;            b.sumSalary();            ps.setString(1,""+id);            ps.setString(2, name);            ps.setString(3, position);            ps.setString(4, ""+holiday);            ps.setString(5, ""+b.getSalary());            ps.executeUpdate();            ps.close();            conn.close();            System.out.println("添加成功！");            break;        case "董事长":            Employee c = new Director();            c.ID = id;            c.name = name;            c.position = "董事长";            c.holiday = holiday;            c.salary = salary;            c.sumSalary();            ps.setString(1,""+id);            ps.setString(2, name);            ps.setString(3, position);            ps.setString(4, ""+holiday);            ps.setString(5, ""+c.getSalary());            ps.executeUpdate();            ps.close();            conn.close();            System.out.println("添加成功！");            break;        default:            System.out.println("不存在此职务，请重新输入！");            ps.close();            conn.close();            addEmployee();            break;        }        caoZuo();    }    public static void delEmployee() throws IOException, SQLException {        System.out.println("----------删除员工---------");        System.out.println("请输入员工姓名：");        String n = sc.next();        Connection conn=DBConnection.getConnection();        String sql="select * from Employee";        PreparedStatement ps=conn.prepareStatement(sql);        ResultSet rs=ps.executeQuery();        Boolean flag=false;        while(rs.next()){            String s=rs.getString("name");            if(n.equals(s)){                System.out.println("你要删除的是:");                System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t\t"+rs.getString(4)+"\t"+rs.getString(5));                flag=true;                System.out.println("你确定要删除吗？\n [Y]确定，[N]取消");            }        }        if(!flag){            System.out.println("你输入的用户名不存在！请重新输入！");            rs.close();            ps.close();            conn.close();            delEmployee();        }        String shifou=sc.next();        if(shifou.equals("y")){            rs.close();            ps.close();;            String sql2="delete from Employee where name=?";            PreparedStatement ps2=conn.prepareStatement(sql2);            ps2.setString(1, n);            Boolean bo=ps2.execute();            if(!bo){                System.out.println("删除成功！");            }else{                System.out.println("删除失败！");            }            ps2.close();            conn.close();            caoZuo();        }else if(shifou.equals("n")){            System.out.println("已取消删除！");            rs.close();            ps.close();            conn.close();            caoZuo();        }    }    public static void updateEmployee() throws IOException, SQLException {        System.out.println("--------------修改员工资料-------------");        System.out.println("请输入你要修改的姓名：");        String s = sc.next();        Connection conn=DBConnection.getConnection();        String sql="select * from Employee";        PreparedStatement ps=conn.prepareStatement(sql);        ResultSet rs=ps.executeQuery();        Boolean flag=false;        while(rs.next()){            String nn=rs.getString("name");            if(nn.equals(s)){                System.out.println("你要修改的是:");                System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t\t"+rs.getString(4)+"\t"+rs.getString(5));                flag=true;            }        }        if(!flag){            System.out.println("你输入的用户名不存在！请重新输入！");            rs.close();            ps.close();            conn.close();            delEmployee();        }        rs.close();        ps.close();        String sql2="update Employee set id=?,name=?,position=?,holiday=?,salary=? where name=?";        PreparedStatement ps2=conn.prepareStatement(sql2);        System.out.println("请重新输入相关信息：");        System.out.print("ID：");        String id = sc.next();        System.out.print("姓名:");        String name = sc.next();        System.out.print("职务:");        String position = sc.next();        System.out.print("请假天数:");        int holiday = sc.nextInt();        System.out.print("基本工资:");        double salary = sc.nextDouble();        switch (position) {        case "普通员工":                Employee a = new CommonEmployee();                a.ID = id;                a.name = name;                a.position = "普通员工";                a.holiday = holiday;                a.salary = salary;                a.sumSalary();                ps2.setString(1, id);                ps2.setString(2, name);                ps2.setString(3, position);                ps2.setString(4, ""+holiday);                ps2.setString(5, ""+a.getSalary());                ps2.setString(6, s);                ps2.executeUpdate();                ps2.close();                conn.close();                System.out.println("修改成功！");                a.display();                caoZuo();            break;        case "经理":                Employee b = new Manager();                b.ID = id;                b.name = name;                b.position = "经理";                b.holiday = holiday;                b.salary = salary;                b.sumSalary();                ps2.setString(1, id);                ps2.setString(2, name);                ps2.setString(3, position);                ps2.setString(4, ""+holiday);                ps2.setString(5, ""+b.getSalary());                ps2.setString(6, s);                ps2.executeUpdate();                ps2.close();                conn.close();                System.out.println("修改成功！");                b.display();                caoZuo();            break;        case "董事长":                Employee c = new Director();                c.ID = id;                c.name = name;                c.position = "董事长";                c.holiday = holiday;                c.salary = salary;                c.sumSalary();                ps2.setString(1, id);                ps2.setString(2, name);                ps2.setString(3, position);                ps2.setString(4, ""+holiday);                ps2.setString(5, ""+c.getSalary());                ps2.setString(6, s);                ps2.executeUpdate();                ps2.close();                conn.close();                System.out.println("添加成功！");                c.display();                 caoZuo();            break;        default:            System.out.println("不存在此职务，请重新输入！");            ps2.close();            conn.close();            updateEmployee();            break;        }    }    public static void queryEmployee() throws SQLException {        System.out.println("--------------所有员工信息---------------");        Connection conn=DBConnection.getConnection();        String sql="select * from Employee";        PreparedStatement ps=conn.prepareStatement(sql);        ResultSet rs=ps.executeQuery();        while(rs.next()){            System.out.println(rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3)+"\t\t"+rs.getString(4)+"\t"+rs.getString(5));        }        rs.close();        ps.close();        conn.close();        try {            Thread.sleep(2000);        } catch (InterruptedException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }        caoZuo();    }    public static void main(String[] args) {        // TODO Auto-generated method stub        TestEMD.caoZuo();    }}