本文共 12847 字,大约阅读时间需要 42 分钟。
需求详见:
本次变更: 1新建DBConnection类,用于获取/关闭数据库连接 2新建SQL工具类,存放数据库增删改查方法 3Test.java修改存储方式,调用SQL工具类第一步:建库建表
Mysql中新建数据库名为:“EmployeeManager”,在该数据库下新建一个员工表:Employee,建表SQL如下:--创建表Employee create table Employee( id varchar(50) primary key, name varchar(50), position varchar(50),holiday int,salary decimal(8,2));--插入两条数据insert into Employee(id,name,position,holiday,salary) values('1','张三','普通员工',3,6700.00);insert into Employee(id,name,position,holiday,salary) values('2','李四','普通员工',3,6700.00);
第二部:引入数据库驱动包
原有项目中导入数据库的驱动Jar包。 Eclipse引入方式:右键项目-build path-Configure Build path 弹出的对话框选择Libraries选项卡,点击右方的add External jar 然后选择你的驱动包点击确定就行。 Maven引入方式:pom中添加如下依赖(以Mysql为例):mysql mysql-connector-java 6.0.6
PS:仓库地址推荐https://mvnrepository.com/
第三步:创建数据库连接类
接下来项目中新增数据库连接类DBConnection.java,源码如下 注意:若Mysql版本为8.0以上,url后缀增加 “&useSSL=false&serverTimezone=UTC” 并将驱动包替换为8.0以上版本package jingshenxiaohuo.com.test;/** * @program: testApplication * @description:连接工具类 * @version: V1 **/import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class DBConnection { private static Connection conn = null; /* * SQLServer: * classname:com.microsoft.sqlserver.jdbc.SQLServerDriver * url:jdbc:sqlserver://localhost:1433;DatabaseName=EmployeeManager;user=sa;password=123 * Oralce: * classname:oracle.jdbc.driver.OracleDriver * url:jdbc:oracle:thin:@localhost:1521:orcl * Postgres: * classname:org.postgresql.Driver * url:jdbc:postgresql://localhost/myDB */ //使用MYSql private static final String classname="com.mysql.Driver"; private static final String url="jdbc:mysql://localhost/EmployeeManager?user=root&password=Abc123++"; //获取连接 public static Connection getConnection(){ try { Class.forName(classname); conn = DriverManager.getConnection(url); } catch (Exception e) { System.out.println("连接异常,异常信息:"+e.getMessage()); } return conn; } //关闭连接 public static void close(){ try { conn.close(); } catch (SQLException e) { System.out.println("关闭失败,异常信息:"+e.getMessage()); } }}
第四步:创建数据库操作类
接下来项目中新增数据库操作类DBOption.java,实现对员工数据的增删改查操作,源码如下package jingshenxiaohuo.com.test;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;/** * @program: testApplication * @description: * @version: V1 **/public class DBOption { //添加 public static boolean add(Employee employee){ boolean isSaved = false; try { Connection conn = DBConnection.getConnection(); //?为占位符 String sql = "insert into Employee(id,name,position,holiday,salary) values(?,?,?,?,?)"; PreparedStatement ps = null; ps = conn.prepareStatement(sql); //指sql中第一个?为ID ps.setString(1, employee.ID+""); ps.setString(2, employee.name); ps.setString(3, employee.position); ps.setInt(4, employee.holiday); ps.setDouble(5, employee.salary); isSaved = ps.execute(); ps.close(); conn.close(); } catch (Exception e) { System.out.println("存储异常,异常原因:"+e.getMessage()); } return isSaved; } //修改 public static boolean update(Employee employee){ boolean isUpdate = false; try { Connection conn = DBConnection.getConnection(); //?为占位符 String sql = "update Employee set id=?,name=?,position=?,holiday=?,salary=? where name=?"; PreparedStatement ps = null; ps = conn.prepareStatement(sql); //指sql中第一个?为ID ps.setString(1, employee.ID+""); ps.setString(2, employee.name); ps.setString(3, employee.position); ps.setInt(4, employee.holiday); ps.setDouble(5, employee.salary); ps.setString(6, employee.name); isUpdate = ps.execute(); ps.close(); conn.close(); } catch (Exception e) { System.out.println("更新异常,异常原因:"+e.getMessage()); } return isUpdate; } //删除 public static boolean delete(String name){ boolean isDelete = false; try { Connection conn = DBConnection.getConnection(); //?为占位符 String sql = "delete from Employee where name=?"; PreparedStatement ps = null; ps = conn.prepareStatement(sql); //指sql中第一个?为ID ps.setString(1, name); isDelete = ps.execute(); ps.close(); conn.close(); } catch (Exception e) { System.out.println("删除异常,异常原因:"+e.getMessage()); } return isDelete; } //查询 public static void query(){ try { 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) + "\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t\t" + rs.getString(4) + "\t" + rs.getString(5)); } rs.close(); ps.close(); conn.close(); } catch (Exception e) { System.out.println("查询异常,异常原因:"+e.getMessage()); } } //查询单个人根据人名 public Employee selectEmployeeByName(String name){ Employee employee = null; try { Connection conn = DBConnection.getConnection(); String sql = "select * from Employee where name = '"+name+"'"; PreparedStatement ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery(); if(rs.next()) { employee = new Employee(rs.getString(1), rs.getString(2), rs.getString(3), rs.getInt(4), rs.getDouble(5)); } rs.close(); ps.close(); conn.close(); } catch (Exception e) { System.out.println("查询异常,异常原因:"+e.getMessage()); } return employee; }}
最后,就是进入操作类TestEMD.java的编写了,代码如下:
package jingshenxiaohuo.com.test;import java.util.ArrayList;import java.util.List;import java.util.Scanner;/** * @program: testApplication * @description: * @version: V1 **/public class Test { private static Scanner sc = new Scanner(System.in); private static Listem = new ArrayList (); //操作入口 public static void opt() { 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("请输入你要选择的操作:"); Scanner sc = new Scanner(System.in); String s = sc.next(); switch (s) { case "1": addEmployee(); break; case "2": delEmployee(); break; case "3": updateEmployee(); break; case "4": queryEmployee(); break; case "0": System.out.println("谢谢使用"); break; default: System.out.println("指令错误请重新输入!"); opt(); break; } } //新增员工 public static void addEmployee() { 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(); Employee a = null; switch (position) { case "普通员工": a = new CommonEmployee(id,name,position,holiday,salary); break; case "经理": a = new Manager(id,name,position,holiday,salary); break; case "董事长": a = new Director(id,name,position,holiday,salary); break; default: System.out.println("不存在此职务,请重新输入!"); addEmployee(); break; } if (a != null) { a.sumSalary(); em.add(a); //数据库存储 DBOption.add(a); System.out.println("添加成功!"); } opt(); } //删除员工 public static void delEmployee() { System.out.println("----------删除员工---------"); System.out.println("请输入员工姓名:"); String n = sc.next(); for (int i = 0; i < em.size(); i++) { if (em.get(i).name.equals(n)) { System.out.println("你要删除的是:"); em.get(i).display(); System.out.println("你确定要删除吗?\n [Y]确定,[N]取消"); String s = sc.next(); if (s.toLowerCase().equals("y")) { em.remove(i); //删除员工 boolean isDeleted = DBOption.delete(n); if(isDeleted){ System.out.println("删除成功!"); } opt(); } else if (s.toLowerCase().equals("n")) { opt(); } else { System.out.println("输入指令不正确,请重新输入!"); delEmployee(); } } else { if (i != em.size() - 1) { continue; } else { System.out.println("你输入的账号不存在!请重新输入!"); delEmployee(); } } } } //修改员工 public static void updateEmployee(){ System.out.println("--------------修改员工资料-------------"); System.out.println("请输入你要修改的姓名:"); String s = sc.next(); out: for (int i = 0; i < em.size(); i++) { if (em.get(i).name.equals(s)) { System.out.println("你要修改的是:"); em.get(i).display(); 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(); if (em.get(i).position.equals(position)) { em.get(i).ID = id; em.get(i).name = name; em.get(i).position = position; em.get(i).holiday = holiday; em.get(i).salary = salary; em.get(i).sumSalary(); System.out.println("修改成功!"); em.get(i).display(); }else{ Employee a = null; switch (position) { case "普通员工": a = new CommonEmployee(id,name,position,holiday,salary); break; case "经理": a = new Manager(id,name,position,holiday,salary); break; case "董事长": a = new Director(id,name,position,holiday,salary); break; default: System.out.println("不存在此职务,请重新输入!"); addEmployee(); break; } if (a != null) { a.sumSalary(); em.set(i, a); DBOption.update(a); } } opt(); } else { if (i != em.size() - 1) { continue out; } else { System.out.println("你输入的员工不存在!请重新输入!"); updateEmployee(); } } } } //查询员工 public static void queryEmployee() { //查询所有并展示 DBOption.query(); //继续操作 opt(); } public static void main(String[] args) { Test.opt(); }}
小伙看完记得给个👍
转载地址:http://cgdrf.baihongyu.com/