数据库表的准备
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);
}
}
}