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)+"	"+rs.getString(2)+"	"+rs.getString(3)+"		"+rs.getString(4)+"	"+rs.getString(5));
                flag=true;
                System.out.println("你确定要删除吗？
 [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)+"	"+rs.getString(2)+"	"+rs.getString(3)+"		"+rs.getString(4)+"	"+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)+"	"+rs.getString(2)+"	"+rs.getString(3)+"		"+rs.getString(4)+"	"+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();
    }

}