jdbc实现人力资源管理系统

我利用oracle中的scott用户里的表来制作简易的人力资源管理系统，练习使用jdbc和编程，希望观看这篇博客的人能有收获。

1、我将用到的jdbc代码连接进行了封装，方便在程序中使用，创建一个util包来封装。
package com.neusoft.hrsystem.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JdbcUtil {
public static Connection getConnection() throws Exception {
// 数据库驱动
Class.forName(“oracle.jdbc.driver.OracleDriver”);
// 连接数据库
Connection conn = DriverManager.getConnection(“jdbc:oracle:thin:” + “@localhost:1521:oracle”, “scott”,
“123456”);
return conn;
}

// 关闭接口，释放资源
public static void close(Connection conn, PreparedStatement ptmt, ResultSet rs) throws SQLException {
    if (conn != null) {
        conn.close();
    }
    if (ptmt != null) {
        ptmt.close();
    }
    if (rs != null) {
        rs.close();
    }
}

}

2、注册登录系统的界面ui，先创建userr表来储存账户的账户名和密码（创建表过程在此省略。。）：
package com.neusoft.hrsystem.ui;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;

import javax.imageio.spi.RegisterableService;

import com.neusoft.hrsystem.dao.DeptDao;
import com.neusoft.hrsystem.dao.EmpDao;
import com.neusoft.hrsystem.util.JdbcUtil;

public class TeahUi {

JdbcUtil jd = new JdbcUtil();
Scanner sc = new Scanner(System.in);

// 注册
public void registe() throws Exception {
    System.out.println("注册账号:");
    String s = sc.next();
    System.out.println("注册密码:");
    String s1 = sc.next();
    String sql = "insert into userr(username,pass)  values(?,?)";
    PreparedStatement ptmt = jd.getConnection().prepareStatement(sql);
    ptmt.setString(1, s);
    ptmt.setString(2, s1);
    ptmt.executeUpdate();
    System.out.println("注册成功！！");

}

// 登陆
public void login() throws Exception {
    System.out.println("请输入账号:");
    String s = sc.next();
    System.out.println("请输入密码:");
    String s1 = sc.next();
    String sql = "select * from userr where username=? and pass=?";
    PreparedStatement ptmt = jd.getConnection().prepareStatement(sql);
    ptmt.setString(1, s);
    ptmt.setString(2, s1);
    ResultSet rs = ptmt.executeQuery();
    if (rs.next()) {
        System.out.println("登陆成功！");
        System.out.println("请选择人力资源系统模块：1、员工管理 2、部门管理");
        int a = sc.nextInt();
        if (a == 1) {
            EmpDao e = new EmpDao();
            e.Emp();
        } else if (a == 2) {
            DeptDao d = new DeptDao();
            d.Dept();
        }

    } else {
        System.out.println("密码或账号错误！");
    }

}

}

3、创建vo表下的对象
(1).员工成员Emp.java的属性，进行封装
package com.neusoft.hrsystem.vo;

public class Emp {
private int empno;
private String ename;
private String job;
private int mgr;
private String hiredate;
private double sal;
private double comm;
private int deptno;

public int getEmpno() {
    return empno;
}

public void setEmpno(int empno) {
    this.empno = empno;
}

public String getEname() {
    return ename;
}

public void setEname(String ename) {
    this.ename = ename;
}

public String getJob() {
    return job;
}

public void setJob(String job) {
    this.job = job;
}

public int getMgr() {
    return mgr;
}

public void setMgr(int mgr) {
    this.mgr = mgr;
}

public String getHiredate() {
    return hiredate;
}

public void setHiredate(String hiredate) {
    this.hiredate = hiredate;
}

public double getSal() {
    return sal;
}

public void setSal(double sal) {
    this.sal = sal;
}

public double getComm() {
    return comm;
}

public void setComm(double comm) {
    this.comm = comm;
}

public int getDeptno() {
    return deptno;
}

public void setDeptno(int deptno) {
    this.deptno = deptno;
}

public Emp(int empno, String ename, String job, int mgr, String hiredate, double sal, double comm, int deptno) {
    super();
    this.empno = empno;
    this.ename = ename;
    this.job = job;
    this.mgr = mgr;
    this.hiredate = hiredate;
    this.sal = sal;
    this.comm = comm;
    this.deptno = deptno;
}

public Emp() {
    super();
}

@Override
public String toString() {
    return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate=" + hiredate
            + ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + "]";
}

}
(2).部门属性，进行封装
package com.neusoft.hrsystem.vo;

public class Dept {

private int deptno;
private String dname;
private String loc;

public int getDeptno() {
    return deptno;
}

public void setDeptno(int deptno) {
    this.deptno = deptno;
}

public String getDname() {
    return dname;
}

public void setDname(String dname) {
    this.dname = dname;
}

public String getLoc() {
    return loc;
}

public void setLoc(String loc) {
    this.loc = loc;
}

public Dept(int deptno, String dname, String loc) {
    super();
    this.deptno = deptno;
    this.dname = dname;
    this.loc = loc;
}

public Dept() {
    super();
}

@Override
public String toString() {
    return "Dept [deptno=" + deptno + ", dname=" + dname + ", loc=" + loc + "]";
}

}

4、这是最重要的，也是系统里的主体部分，数据交流dao层，即对数据进行增删改查操作：
(1).EmpDap.java
package com.neusoft.hrsystem.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

import com.neusoft.hrsystem.util.JdbcUtil;
import com.neusoft.hrsystem.vo.Emp;

public class EmpDao {

Emp e = new Emp();
Scanner sc = new Scanner(System.in);

public void Emp() throws SQLException, Exception {
    while (true) {
        System.out.println("请输入选择的操作：1、员工添加 2、员工修改 3、员工删除 4、员工查询");
        int a = sc.nextInt();
        switch (a) {
        case 1:
            insertEmp();
            break;
        case 2:
            updateEmp();
            break;
        case 3:
            deleteEmp();
            break;
        case 4:
            selectEmp();
            break;

        default:
            System.out.println("不存在的操作！！");
            break;
        }
    }
}

public void deleteEmp() throws SQLException, Exception {
    selectEmp();
    System.out.println("请输入删除的员工号:");
    e.setEmpno(sc.nextInt());
    String sql = "delete  from emp where empno=?";
    PreparedStatement ptmt = JdbcUtil.getConnection().prepareStatement(sql);
    ptmt.setInt(1, e.getEmpno());
    int a=ptmt.executeUpdate();
    if(a!=0){
    System.out.println("删除成功！");}
    else{
        System.out.println("删除失败！");
    }
    selectEmp();
    JdbcUtil.getConnection().close();
    ptmt.close();
}

public void insertEmp() throws SQLException, Exception {
    System.out.println("员工号");
    e.setEmpno(sc.nextInt());
    System.out.println("员工名");
    e.setEname(sc.next());
    System.out.println("职位");
    e.setJob(sc.next());
    System.out.println("经理号");
    e.setMgr(sc.nextInt());
    System.out.println("入职时间");
    e.setHiredate(sc.next());
    System.out.println("工资");
    e.setSal(sc.nextDouble());
    System.out.println("奖金");
    e.setComm(sc.nextDouble());
    System.out.println("部门号");
    e.setDeptno(sc.nextInt());
    String sql = "insert into emp values(?,?,?,?,?,?,?,?) ";
    PreparedStatement ptmt = JdbcUtil.getConnection().prepareStatement(sql);
    ptmt.setInt(1, e.getEmpno());
    ptmt.setString(2, e.getEname());
    ptmt.setString(3, e.getJob());
    ptmt.setInt(4, e.getMgr());
    ptmt.setString(5, e.getHiredate());
    ptmt.setDouble(6, e.getSal());
    ptmt.setDouble(7, e.getComm());
    ptmt.setInt(8, e.getDeptno());
    int a=ptmt.executeUpdate();
    if(a!=0){
    System.out.println("添加成功！！");}
    else{
        System.out.println("添加失败！");
    }
    selectEmp();
    JdbcUtil.getConnection().close();
    ptmt.close();
}

public void updateEmp() throws SQLException, Exception {
    selectEmp();
    System.out.println("请输入修改的员工号:");
    int s = sc.nextInt();
    String sql = "update Emp set Ename = ? ,Job=? ,Mgr=?,Hiredate=?,Sal=?,Comm=?,Deptno=? where Empno = ?";
    PreparedStatement ptmt = JdbcUtil.getConnection().prepareStatement(sql);
    ResultSet rs = ptmt.executeQuery();
        ptmt.setInt(8,s);
        System.out.println("修改员工名:");
        ptmt.setString(1, sc.next());
        System.out.println("修改职位:");
        ptmt.setString(2, sc.next());
        System.out.println("修改经理号:");
        ptmt.setInt(3, sc.nextInt());
        System.out.println("修改入职时间:");
        ptmt.setString(4, sc.next());
        System.out.println("修改工资:");
        ptmt.setDouble(5, sc.nextDouble());
        System.out.println("修改奖金:");
        ptmt.setDouble(6,sc.nextDouble());
        System.out.println("修改部门号:");
        ptmt.setInt(7,sc.nextInt());
        int a=ptmt.executeUpdate();
        if(a!=0){
        System.out.println("修改成功！");}
        else{
            System.out.println("修改失败");
        }
        selectEmp();

    JdbcUtil.close(JdbcUtil.getConnection(), ptmt, rs);

}

public void selectEmp() throws SQLException, Exception {
    String sql = "select * from emp";
    PreparedStatement ptmt = JdbcUtil.getConnection().prepareStatement(sql);
    ResultSet rs = ptmt.executeQuery();
    while (rs.next()) {
        System.out.println("员工号:" + rs.getString(1) + " 员工名:" + rs.getString(2) + " 职位:" + rs.getString(3) + " 经理号:"
                + rs.getString(4) + " 入职时间:" + rs.getString(5) + " 工资:" + rs.getString(6) + " 奖金:" + rs.getString(7)
                + " 部门号:" + rs.getString(8));
    }
    System.out.println();
    JdbcUtil.close(JdbcUtil.getConnection(), ptmt, rs);

}

}

(2)DeptDao.java
package com.neusoft.hrsystem.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

import com.neusoft.hrsystem.util.JdbcUtil;

import com.neusoft.hrsystem.vo.Dept;

public class DeptDao {
Dept d = new Dept();
Scanner sc = new Scanner(System.in);

public void Dept() throws SQLException, Exception {
    while (true) {
        System.out.println("请输入选择的操作：1、部门添加 2、部门修改 3、部门删除 4、部门查询");
        int a = sc.nextInt();
        switch (a) {
        case 1:
            insertDept();
            break;
        case 2:
            updateDept();
            break;
        case 3:
            deleteDept();
            break;
        case 4:
            selectDept();
            break;

        default:
            System.out.println("不存在的操作！！");
            break;
        }
    }
}

public void deleteDept() throws SQLException, Exception {
    selectDept();
    System.out.println("请输入删除的部门号:");
    d.setDeptno(sc.nextInt());
    String sql = "delete  from dept where deptno=?";
    PreparedStatement ptmt = JdbcUtil.getConnection().prepareStatement(sql);
    ptmt.setInt(1, d.getDeptno());
    int a =ptmt.executeUpdate();
    if(a!=0){
    System.out.println("删除成功！");}
    else{
        System.out.println("删除失败");
    }
    selectDept();
    JdbcUtil.getConnection().close();
    ptmt.close();
}

public void insertDept() throws SQLException, Exception {
    System.out.println("部门号:");
    d.setDeptno(sc.nextInt());
    System.out.println("部门名:");
    d.setDname(sc.next());
    System.out.println("出生地:");
    d.setLoc(sc.next());
    String sql = "insert into dept values(?,?,?) ";
    PreparedStatement ptmt = JdbcUtil.getConnection().prepareStatement(sql);
    ptmt.setInt(1, d.getDeptno());
    ptmt.setString(2, d.getDname());
    ptmt.setString(3, d.getLoc());
    int a=ptmt.executeUpdate();
    if(a!=0){
    System.out.println("添加成功！");}
    else{
        System.out.println("添加失败！");
    }
    JdbcUtil.getConnection().close();
    ptmt.close();
}

public void updateDept() throws SQLException, Exception {
    selectDept();
    System.out.println("请输入部门号:");
    int a =sc.nextInt();
    System.out.println("修改部门名");
    d.setDname(sc.next());
    System.out.println("修改出生地");
    d.setLoc(sc.next());
    String sql = "update Dept set dname =?,loc =? where deptno=?";
    PreparedStatement ptmt = JdbcUtil.getConnection().prepareStatement(sql);
    ptmt.setString(1,d.getDname());
    ptmt.setString(2,d.getLoc());
    ptmt.setInt(3,a);
     int b=ptmt.executeUpdate();
    if(b!=0){
    System.out.println("修改成功！");}
    else{
        System.out.println("修改失败！");
    }
    selectDept();
    JdbcUtil.getConnection().close();
    ptmt.close();

}

public void selectDept() throws SQLException, Exception {
    String sql = "select * from dept";
    PreparedStatement ptmt = JdbcUtil.getConnection().prepareStatement(sql);
    ResultSet rs = ptmt.executeQuery();
    while (rs.next()) {
        System.out.println("部门号:" + rs.getInt(1) + " 部门名:" + rs.getString(2) + " 出生地:" + rs.getString(3));
    }
    System.out.println();
    JdbcUtil.close(JdbcUtil.getConnection(), ptmt, rs);

}

}

5、最后测试类：
package com.neusoft.hrsystem.test;

import java.util.Scanner;

import com.neusoft.hrsystem.ui.TeahUi;

public class Test {

public static void main(String[] args) throws Exception {

    TeahUi t = new TeahUi();
    Scanner sc = new Scanner(System.in);
    System.out.println("请选择操作：1、注册账户 2、登陆账户");
    int a = sc.nextInt();
    if (a == 1) {
        t.registe();
    } else if (a == 2) {
        t.login();
    }

}

}

这算对知识的小概括吧，巩固巩固。。