利用控制台完成单机版员工管理系统
数据库
employees表
| 列 | 类型 | 约束 | 说明 |
|---|---|---|---|
| empno | int | Primary Key | 员工编号 |
| ename | varchar | 无 | 员工名字 |
| birthday | date | 无 | 员工生日 |
代码
1. JDBC工具类
public class DBUtil {
private static String url;
private static String username;
private static String password;
private DBUtil() {}
static {
// 1.加载驱动
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//读取配置文件,并完成连接字符串
Properties p = new Properties();
try {
p.load(DBUtil.class.getResourceAsStream("DBConfig.properties"));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
String ip = p.getProperty("IPAdress");
String port = p.getProperty("port");
String db = p.getProperty("database");
String tz = p.getProperty("timezone");
url = "jdbc:mysql://"+ip+":"+port+"/"+db+"?useUnicode=true&characterEncoding=utf-8&serverTimezone="+tz;
username = p.getProperty("username");
password = p.getProperty("password");
}
public static Connection getConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void close(Connection conn, Statement pst, ResultSet rs) {
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(pst != null) {
try {
pst.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void close(Connection conn, Statement pst) {
close(conn, pst, null);
}
public static void close(Connection conn) {
close(conn, null, null);
}
}
2. JDBC配置文件（与工具类在同一包下）
IPAdress：数据库服务器IP地址
port：数据库服务端口号
database：数据库名
username：账号
password：密码
timezone：时区（默认设置为GMT+8）
IPAdress=localhost
port=3306
database=mydb
username=root
password=root
timezone=GMT%2B8
3.pojo类
public class Employee {
private int empno;
private String ename;
private Date birthday;
/**
* 只要empno一致，两个对象就“相等”
*/
@Override
public boolean equals(Object obj) {
if(this == obj) {
return true;
}
if(obj instanceof Employee) {
Employee another = (Employee)obj;
if(this.empno == another.getEmpno()) {
return true;
}
}
return false;
}
@Override
public String toString() {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
String b = sdf.format(birthday);
return "工号:"+empno+",名："+ename+",生日："+b;
}
public java.sql.Date getBirthdaySQLDate(){
//java.util.Date -> java.sql.Date
return new java.sql.Date(birthday.getTime());
}
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 Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
}
4.DAO类
public class EmployeeDAO {
/**
* 新增员工
* @param e
*/
public void insert(Employee e) {
Connection conn = DBUtil.getConnection();
String sql = "insert into employees values (?,?,?)";
PreparedStatement pst = null;
try {
pst = conn.prepareStatement(sql);
pst.setInt(1, e.getEmpno());
pst.setString(2, e.getEname());
pst.setDate(3, e.getBirthdaySQLDate());
pst.executeUpdate();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} finally {
DBUtil.close(conn, pst);
}
}
/**
* 根据编号删除员工
* @param empno
*/
public void delete(int empno) {
Connection conn = DBUtil.getConnection();
String sql = "delete from employees where empno = ?";
PreparedStatement pst = null;
try {
pst = conn.prepareStatement(sql);
pst.setInt(1, empno);
pst.executeUpdate();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} finally {
DBUtil.close(conn, pst);
}
}
public void update(Employee e) {
Connection conn = DBUtil.getConnection();
String sql = "update employees set ename = ?, birthday = ? where empno = ?";
PreparedStatement pst = null;
try {
pst = conn.prepareStatement(sql);
pst.setString(1, e.getEname());
pst.setDate(2, e.getBirthdaySQLDate());
pst.setInt(3, e.getEmpno());
pst.executeUpdate();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} finally {
DBUtil.close(conn, pst);
}
}
/**
* 根据编号查询员工，如果员工不存在返回null
* @param empno
* @return
*/
public Employee selectOne(int empno) {
Connection conn = DBUtil.getConnection();
String sql = "select * from employees where empno = ?";
PreparedStatement pst = null;
ResultSet rs = null;
Employee e = null;
try {
pst = conn.prepareStatement(sql);
pst.setInt(1, empno);
rs = pst.executeQuery();
while(rs.next()) {
e = new Employee();
e.setEmpno(rs.getInt("empno"));
e.setEname(rs.getString("ename"));
e.setBirthday(rs.getDate("birthday"));
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} finally {
DBUtil.close(conn, pst, rs);
}
return e;
}
/**
* 遍历员工列表
* @return
*/
public ArrayList<Employee> selectList(){
Connection conn = DBUtil.getConnection();
String sql = "select empno,ename,birthday from employees";
PreparedStatement pst = null;
ResultSet rs = null;
ArrayList<Employee> list = new ArrayList<Employee>();
try {
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
while(rs.next()) {
Employee e = new Employee();
e.setEmpno(rs.getInt(1));
e.setEname(rs.getString(2));
e.setBirthday(rs.getDate(3));
list.add(e);
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} finally {
DBUtil.close(conn, pst, rs);
}
return list;
}
}
5. View类(主程序)
public class Test {
public static void main(String[] args) {
EmployeeDAO dao = new EmployeeDAO();
while(true) {
System.out.println("1 - 新增员工");
System.out.println("2 - 修改员工");
System.out.println("3 - 删除员工");
System.out.println("4 - 查询所有员工");
System.out.println("5 - 查询单个员工");
System.out.println("6 - 退出");
System.out.println("请选择您的操作");
Scanner sc = new Scanner(System.in);
int n = sc.nextInt();
if(n == 1) {
//新增员工
Employee e = new Employee();
while(true) {
System.out.println("请输入员工工号:");
Scanner sc1 = new Scanner(System.in);
int empno = sc1.nextInt();
e.setEmpno(empno);
Employee temp = dao.selectOne(empno);
if(temp != null) {
System.out.println("工号已被占用");
continue;
}else {
break;
}
}
System.out.println("请输入员工姓名:");
Scanner sc2 = new Scanner(System.in);
String ename = sc2.nextLine();
e.setEname(ename);
System.out.println("请输入员工生日(yyyy-MM-dd):");
Scanner sc3 = new Scanner(System.in);
String b = sc3.nextLine(); //字符串生日
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date birthday = null;
try {
birthday = sdf.parse(b);
} catch (ParseException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.setBirthday(birthday);
dao.insert(e);
}else if(n == 2) {
//修改员工
System.out.println("请输入要修改员工的工号:");
Scanner sc1 = new Scanner(System.in);
int empno = sc1.nextInt();
Employee temp = dao.selectOne(empno);
if(temp != null) {
//有这个员工
System.out.println("请输入新的员工姓名:");
Scanner sc2 = new Scanner(System.in);
String ename = sc2.nextLine();
temp.setEname(ename);
System.out.println("请输入员工生日(yyyy-MM-dd):");
Scanner sc3 = new Scanner(System.in);
String b = sc3.nextLine(); //字符串生日
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date birthday = null;
try {
birthday = sdf.parse(b);
} catch (ParseException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
temp.setBirthday(birthday);
dao.update(temp);
}else {
//集合中没有这个员工
System.out.println("没有找到这个员工");
}
}else if(n == 3) {
//删除员工
System.out.println("请输入要删除员工的工号:");
Scanner sc1 = new Scanner(System.in);
int empno = sc1.nextInt();
Employee temp = dao.selectOne(empno);
if(temp != null) {
//集合中有这个员工
dao.delete(empno);
System.out.println("删除成功");
}else {
//集合中没有这个员工
System.out.println("没有找到这个员工");
}
}else if(n == 4) {
//遍历所有员工
ArrayList<Employee> list = dao.selectList();
for(Employee e : list) {
System.out.println(e);
}
}else if(n == 5) {
System.out.println("请输入要查询员工的工号:");
Scanner sc1 = new Scanner(System.in);
int empno = sc1.nextInt();
Employee temp = dao.selectOne(empno);
if(temp != null) {
//集合中有这个员工
System.out.println(temp);
}else {
//集合中没有这个员工
System.out.println("没有找到这个员工");
}
}else if(n == 6) {
//退出
break;
}else {
System.out.println("您输入的选项不正确");
}
}
}
}
网友评论