期中

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;
}

}