Java面向對象練習-實作員工管理系統（MySQL資料庫存儲）

需求詳見：需求内容

本次變更：

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為例）：

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.6</version>
        </dependency>

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 List<Employee> em = new ArrayList<Employee>();


    //操作入口
    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();
    }

}

小夥看完記得給個👍

Java面向對象練習-實作員工管理系統（MySQL資料庫存儲）

繼續閱讀

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的簡單使用

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

登入plsql 報錯 the account is locked --使用者被鎖

Effective Java 8:通用程式設計

SequoiaDB巨杉資料庫C++驅動概述

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method