第七章 JDBC实现员工管理

数据库表的准备

drop table if exists t_employee;

create table t_employee(
    id bigint primary key auto_increment,
    name varchar(255),
job varchar(255),
hiredate char(10),
salary decimal(10,2),
address varchar(255)
);

insert into t_employee(name,job,hiredate,salary,address) values('张三','销售员','1999-10-11',5000.0,'北京朝阳');
insert into t_employee(name,job,hiredate,salary,address) values('李四','编码人员','1998-02-12',5000.0,'北京海淀');
insert into t_employee(name,job,hiredate,salary,address) values('王五','项目经理','2000-08-11',5000.0,'北京大兴');
insert into t_employee(name,job,hiredate,salary,address) values('赵六','产品经理','2022-09-11',5000.0,'北京东城');
insert into t_employee(name,job,hiredate,salary,address) values('钱七','测试员','2024-12-11',5000.0,'北京西城');

commit;

select * from t_employee;

实现效果

查看员工列表

查看员工详情

新增员工

修改员工

删除员工

退出系统

 

public class JDBCTest22 {
    public static void main(String[] args) {
        System.out.println("欢迎使用员工信息管理，请认真阅读使用说明：");
        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("[5]删除员工");
        System.out.println("[0]退出系统");

        Scanner scanner = new Scanner(System.in);

        while(true){
            System.out.print("请输入功能编号：");
            int no = scanner.nextInt();
            if(1 == no){
                // 查看员工列表
                System.out.println("员工信息列表如下：");
                doList();
            } else if(2 == no){
                // 查看某个员工的详细信息
                doList();
                System.out.print("请输入员工的id：");
                long id = scanner.nextLong();
                System.out.println("员工[" + id + "]的详细信息如下：");
                doDetail(id);
            } else if(3 == no){
                // 接收员工的信息
                System.out.print("请输入员工姓名：");
                String name = scanner.next();
                System.out.print("请输入员工岗位：");
                String job = scanner.next();
                System.out.print("请输入员工月薪：");
                Double salary = scanner.nextDouble();
                System.out.print("请输入员工入职日期：");
                String hiredate = scanner.next();
                System.out.print("请输入员工住址：");
                String address = scanner.next();
                // 新增员工
                doSave(name, job, salary, hiredate, address);
                System.out.println("新增员工[" + name + "]成功!!!");
                doList();
            } else if(4 == no){
                // 显示员工列表
                doList();
                // 显示员工详细信息
                System.out.print("请输入您要修改的员工id：");
                long id = scanner.nextLong();
                doDetail(id);
                // 接收新的信息（注意：修改员工时，id不能修改）
                System.out.print("请输入员工姓名：");
                String name = scanner.next();
                System.out.print("请输入员工岗位：");
                String job = scanner.next();
                System.out.print("请输入员工月薪：");
                Double salary = scanner.nextDouble();
                System.out.print("请输入员工入职日期：");
                String hiredate = scanner.next();
                System.out.print("请输入员工住址：");
                String address = scanner.next();
                // 修改员工
                doModify(id, name, job, salary, hiredate, address);
                System.out.println("员工[" + id + "]的信息更新成功!!!!");
            } else if(5 == no){
                doList();
                System.out.print("请输入要删除的员工id：");
                Long id = scanner.nextLong();
                // 删除员工
                doDel(id);
                System.out.println("删除员工[" + id + "]成功了！");
                doList();
            } else if(0 == no){
                System.out.println("下次再见！");
                System.exit(0);
            } else {
                System.out.println("对不起，您输入的功能暂不支持！");
            }
        }
    }

    private static void doDel(Long id) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "delete from t_employee where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            // 执行删除SQL
            int count = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, ps, null);
        }
    }

    private static void doModify(Long id, String name, String job, Double salary, String hiredate, String address) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "update t_employee set name=?, job=?, salary=?, hiredate=?, address=? where id=?";
            ps = conn.prepareStatement(sql);
            // 给 ? 传值
            ps.setString(1, name);
            ps.setString(2, job);
            ps.setDouble(3, salary);
            ps.setString(4, hiredate);
            ps.setString(5, address);
            ps.setLong(6, id);
            // 执行更新SQL
            int count = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, ps, null);
        }
    }

    private static void doSave(String name, String job, Double salary, String hiredate, String address) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "insert into t_employee(name,job,salary,hiredate,address) values(?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            // 给 ? 传值
            ps.setString(1, name);
            ps.setString(2, job);
            ps.setDouble(3, salary);
            ps.setString(4, hiredate);
            ps.setString(5, address);
            // 执行保存SQL
            int count = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, ps, null);
        }
    }

    private static void doDetail(Long id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "select * from t_employee where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            rs = ps.executeQuery();
            if(rs.next()){
                String name = rs.getString("name");
                String job = rs.getString("job");
                String hiredate = rs.getString("hiredate");
                Double salary = rs.getDouble("salary");
                String address = rs.getString("address");
                System.out.println("id = " + id);
                System.out.println("姓名 = " + name);
                System.out.println("岗位 = " + job);
                System.out.println("入职日期 = " + hiredate);
                System.out.println("月薪 = " + salary);
                System.out.println("住址 = " + address);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, ps, rs);
        }
    }

    private static void doList() {
        // 编写JDBC代码，连接数据库，查询所有的员工信息，展示
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "select id,name,job from t_employee";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            System.out.println("id\tname\tjob");
            System.out.println("----------------------------");
            while(rs.next()){
                Long id = rs.getLong("id");
                String name = rs.getString("name");
                String job = rs.getString("job");
                System.out.println(id + "\t" + name + "\t" + job);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DbUtils.close(conn, ps, rs);
        }
    }
}