期中

Jacksoner發表於2024-10-28

package com.zlj.ks;

import java.sql.*;
import java.util.Scanner;

public class t1 {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
Scanner sc = new Scanner(System.in);
while (true) {
//列印選單
System.out.println("-------Mes系統-------");
System.out.println("1.提交日報");
System.out.println("2.刪除日報");
System.out.println("3.修改日報");
System.out.println("4.查詢日報");
System.out.println("5.退出系統");

        System.out.println("請輸入你想執行的功能序號");
        int choice = sc.nextInt();
        if (choice == 1) {
            System.out.println("請輸入生產批次：");
            int lot = sc.nextInt();
            System.out.println("請輸入工號：");
            int id = sc.nextInt();
            System.out.println("請輸入工人姓名：");
            String name = sc.next();
            System.out.println("請輸入轉出總數：");
            int outnum = sc.nextInt();
            System.out.println("請輸入轉出合格數：");
            int goodnum = sc.nextInt();
            System.out.println("請輸入次品數：");
            int badnum = sc.nextInt();
            System.out.println("請輸入丟失數：");
            int lostnum = sc.nextInt();


            // 檢查押平規則
            if (outnum == (goodnum + badnum + lostnum)) {
                try {
                    //載入驅動
                    Class.forName("com.mysql.cj.jdbc.Driver");
                    //獲取連線
                    String url = "jdbc:mysql://127.0.0.1:3306/kaoshi?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
                    String username = "root";
                    String password = "root";
                    Connection conn = DriverManager.getConnection(url, username, password);
                    //建立會話
                    Statement sta = conn.createStatement();
                    //傳送SQL
                    int n = sta.executeUpdate("insert into t_report (id, name, outnum, goodnum, badnum, lostnum, lot) values(" + id + ", '" + name + "', " + outnum + ", " + goodnum + ", " + badnum + ", " + lostnum + ", " + lot + ")");
                    if (n > 0) {
                        System.out.println("提交成功！");
                    } else {
                        System.out.println("提交失敗！");
                    }
                    //關閉資料庫資源
                    sta.close();
                    conn.close();
                } catch (ClassNotFoundException | SQLException e) {
                    e.printStackTrace();
                }
            } else {
                System.out.println("上報資料有誤，請重新提交");
            }
        }

        if (choice == 2) {
            System.out.println("請輸入你要刪除工序的生產批次");
            int lno = sc.nextInt();
            int n = delByLno(lno);
            if (n <= 0) {
                System.out.println("刪除失敗！");
            } else {
                System.out.println("刪除成功！");
            }
        }
        if (choice == 3) {
            System.out.println("請輸入要修改工序的生產批次：");
            int lno = sc.nextInt();
            System.out.println("請選擇要修改的欄位：1.工號 2.工人姓名 3.轉出總數 4.轉出合格數 5.次品數 6.丟失數");
            int fieldChoice = sc.nextInt();
            System.out.println("請輸入新值：");
            String newValue = sc.next();

            String updateField = "";
            switch (fieldChoice) {
                case 1:
                    updateField = "id";
                    break;
                case 2:
                    updateField = "name";
                    break;
                case 3:
                    updateField = "outnum";
                    break;
                case 4:
                    updateField = "goodnum";
                    break;
                case 5:
                    updateField = "badnum";
                    break;
                case 6:
                    updateField = "lostnum";
                    break;
            }

            try {
                //載入驅動
                Class.forName("com.mysql.cj.jdbc.Driver");
                //獲取連線
                String url = "jdbc:mysql://127.0.0.1:3306/kaoshi?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
                String username = "root";
                String password = "root";
                Connection conn = DriverManager.getConnection(url, username, password);
                //建立會話
                Statement sta = conn.createStatement();
                //傳送SQL
                int n = sta.executeUpdate("update t_report set " + updateField + " = '" + newValue + "' where lot = " + lno);
                if (n > 0) {
                    System.out.println("修改成功！");
                } else {
                    System.out.println("修改失敗！");
                }
                //關閉資料庫資源
                sta.close();
                conn.close();
            } catch (ClassNotFoundException | SQLException e) {
                e.printStackTrace();
            }
        }
        if (choice == 4) {
            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("6.按次品數查詢");
            System.out.println("7.按丟失數查詢");
            int queryChoice = sc.nextInt();
            String queryField = "";
            int queryValue = 0;
            String queryValueStr = "";
            switch (queryChoice) {
                case 1:
                    queryField = "lot";
                    System.out.println("請輸入生產批次：");
                    queryValue = sc.nextInt();
                    break;
                case 2:
                    queryField = "id";
                    System.out.println("請輸入工號：");
                    queryValue = sc.nextInt();
                    break;
                case 3:
                    queryField = "name";
                    System.out.println("請輸入工人姓名：");
                    queryValueStr = sc.next();
                    break;
                case 4:
                    queryField = "outnum";
                    System.out.println("請輸入轉出總數：");
                    queryValue = sc.nextInt();
                    break;
                case 5:
                    queryField = "goodnum";
                    System.out.println("請輸入轉出合格數：");
                    queryValue = sc.nextInt();
                    break;
                case 6:
                    queryField = "badnum";
                    System.out.println("請輸入次品數：");
                    queryValue = sc.nextInt();
                    break;
                case 7:
                    queryField = "lostnum";
                    System.out.println("請輸入丟失數：");
                    queryValue = sc.nextInt();
                    break;
            }

            try {
                //載入驅動
                Class.forName("com.mysql.cj.jdbc.Driver");
                //獲取連線
                String url = "jdbc:mysql://127.0.0.1:3306/kaoshi?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
                String username = "root";
                String password = "root";
                Connection conn = DriverManager.getConnection(url, username, password);
                //建立會話
                Statement sta = conn.createStatement();
                String sql = "";
                if (queryValueStr.equals("")) {
                    sql = "select * from t_report where " + queryField + " = " + queryValue;
                } else {
                    sql = "select * from t_report where " + queryField + " = '" + queryValueStr + "'";
                }
                ResultSet rs = sta.executeQuery(sql);
                if (rs.next()) {
                    Report r = new Report();
                    int lot = rs.getInt("lot");
                    int id = rs.getInt("id");
                    String name = rs.getString("name");
                    int outnum = rs.getInt("outnum");
                    int goodnum = rs.getInt("goodnum");
                    int badnum = rs.getInt("badnum");
                    int lostnum = rs.getInt("lostnum");
                    r.setLot(lot);
                    r.setId(id);
                    r.setName(name);
                    r.setOutnum(outnum);
                    r.setBadnum(badnum);
                    r.setGoodnum(goodnum);
                    r.setLostnum(lostnum);
                    System.out.println(r);
                    System.out.println("生產批次" + r.getLot() + ' ' + "工號" + r.getId() + ' ' +
                            "工人姓名" + r.getName() + ' ' + "轉出總數" + r.getOutnum() + ' ' +
                            "轉出合格數" + r.getGoodnum() + ' ' + "次品數" + r.getBadnum() + ' ' +
                            "丟失數" + r.getLostnum());
                } else {
                    System.out.println("未找到符合條件的記錄");
                }
                //關閉資料庫資源
                sta.close();
                conn.close();
            } catch (ClassNotFoundException | SQLException e) {
                e.printStackTrace();
            }
        }
        if (choice == 5) {
            System.out.println("即將退出系統");
            break;
        }
    }
}

public static Report findByLno(int lno) throws ClassNotFoundException, SQLException {
    Report r = null;
    //載入驅動
    Class.forName("com.mysql.cj.jdbc.Driver");
    //獲取連線
    String url = "jdbc:mysql://127.0.0.1:3306/kaoshi?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
    String username = "root";
    String password = "root";
    Connection conn = DriverManager.getConnection(url, username, password);
    //建立會話
    Statement sta = conn.createStatement();
    //傳送SQL
    ResultSet rs = sta.executeQuery("select * from t_report where lot =" + lno);
    //處理結果
    if (rs.next()) {

        int lot = rs.getInt("lot");
        int id = rs.getInt("id");
        String name = rs.getString("name");
        int outnum = rs.getInt("outnum");
        int goodnum = rs.getInt("goodnum");
        int badnum = rs.getInt("badnum");
        int lostnum = rs.getInt("lostnum");
        //封裝
        r = new Report();
        r.setLot(lot);
        r.setId(id);
        r.setName(name);
        r.setOutnum(outnum);
        r.setBadnum(badnum);
        r.setGoodnum(goodnum);
        r.setLostnum(lostnum);
    }
    //關閉資料庫資源
    sta.close();
    conn.close();

    return r;
}

public static int delByLno(int lno) throws ClassNotFoundException, SQLException {
    //載入驅動
    Class.forName("com.mysql.cj.jdbc.Driver");
    //獲取連線
    String url = "jdbc:mysql://127.0.0.1:3306/kaoshi?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
    String username = "root";
    String password = "root";
    Connection conn = DriverManager.getConnection(url, username, password);
    //建立會話
    Statement sta = conn.createStatement();
    //傳送SQL
    int n = sta.executeUpdate("delete from t_report where lot = " + lno);

    //關閉資料庫資源
    sta.close();
    conn.close();

    return n;
}

}

相關文章