jdbc se mysql

类似于图书管理系统的小小小项目练习，但是里面好像有一些bug，我不知道怎么改了，比如 在swich中case 9 时一开始使用return结束程序但是不知道为什么结束不了，运行的时候还有bug，希望指正

public class Control {
    public static void main(String[] args) {
        //推出系统标志
        //boolean exitSystem = true;
        Scanner sca = new Scanner(System.in);
        System.out.println("请输入用户名和密码：");
        String s1 = sca.nextLine();
        String s2 = sca.nextLine();

        if (Utile.isLogin(s1, s2)) {
            System.out.println("登录成功");
            Utile2 utile2 = new Utile2();
            Reporter re = new Reporter(Utile.getConn());
            new Thread(re).start();
            Connection conn = Utile.getConn();
            while (true) {
                System.out.println("商品维护模块主要包括:");
                System.out.println("0-添加 1-删除 2-修改 3-列表 4-入库 5-出库 9-退出系统:");
                int choice = sca.nextInt();
                switch (choice) {
                    case 0:
                        Utile2.addProduce(sca, conn);
                        break;
                    case 1:
                        Utile2.deleteProduce(sca, conn);
                        break;
                    case 2:
                        Utile2.updateProduce(sca, conn);
                        break;
                    case 3:
                        Utile2.queryProduce(conn, Producer.class);
                        break;
                    case 4:
                        Utile2.inStock(sca, conn);
                        break;
                    case 5:
                        Utile2.outStock(sca, conn);
                        break;
                    case 9:
                        Utile2.closePrint(re);
                        Utile.closeUtile(null, conn, null);
                        sca.close();
                        //exitSystem = false;
                        //return;为什么不能使用return退出
                        System.exit(0);
                    default:
                        System.out.println("请重新输入正确数字");
                }
            }
        } else {
            System.out.println("用户名或者密码错误");
        }
    }
}

/**
 * 建立一个获取连接工具类 Utile
 */
public class Utile {
    private static DataSource dataSource;

    /**
     * 加载 Druid 连接池
     */
    static {
        try (InputStream in = Utile.class.getClassLoader().getResourceAsStream("db.properties")) {
            Properties pros = new Properties();
            pros.load(in);
            dataSource = DruidDataSourceFactory.createDataSource(pros);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接Utile
     */
    public static Connection getConn() {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            return conn;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        throw new RuntimeException("获取连接时失败");
    }

    /**
     * 看是不是登录成功
     */
    public static Boolean isLogin(Object... obj) {
        String sql = "select * from users where user_name = ? and password = ?";
        PreparedStatement stat = null;
        ResultSet res = null;
        try {
            stat = getConn().prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                stat.setObject(i + 1, obj[i]);
            }
            res = stat.executeQuery();
            return res.next();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        throw new RuntimeException("验证登录时出错");
    }

    /**
     * 字符串转化 UTile
     */
    public static String change(String col) {
        StringBuilder sb = new StringBuilder();
        //检查_在开头或者结尾的情况
        if (col.startsWith("_")) {
            col = col.substring(1);
        }
        if (col.endsWith("_")) {
            col = col.substring(0, col.length() - 1);
        }
        //_在中间的情况
        String[] strings = col.split("_");
        if (strings.length > 1) {
            sb.append(strings[0]);
            for (int i = 1; i < strings.length; i++) {
                sb.append(strings[i].substring(0, 1).toUpperCase()).append(strings[i].substring(1));
            }
        } else {
            return col;
        }
        return sb.toString();
    }

    /**
     * 关闭资源 Utile
     */
    public static void closeUtile(ResultSet res, Connection conn, Statement stat) {
        if (res != null) {
            try {
                res.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (stat != null) {
            try {
                stat.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}

public class Producer {
    private int id;
    private String idNumber;
    private String name;
    private BigDecimal price;
    private Integer num;

    @Override
    public String toString() {
        return "商品编码: " + idNumber +
                ", 商品名称: " + name +
                ", 商品价格: " + price +
                ", 商品数量: " + num
                ;
    }
}

public class Utile2 {
    /**
     * 添加
     *
     * @param sca
     * @param conn
     */
    public static void addProduce(Scanner sca, Connection conn) {
        System.out.println("输入商品id:");
        String idNumber = sca.next();

        //看商品是不是存在 存在加1 不存在添加
        if (isExists(conn, idNumber)) {
            System.out.println("该商品数量增加1");
            update(conn, idNumber, 1);
        } else {
            System.out.println("商品不存在，现在添加该商品");
            System.out.println("商品名称:");
            String name = sca.next();

            System.out.println("商品价格:");
            sca.nextLine();
            BigDecimal price = sca.nextBigDecimal();

            if (insert(conn, idNumber, name, price, 1)) {
                System.out.println("商品添加成功");
            }
        }
    }

    /**
     * 删除
     *
     * @param sca
     * @param conn
     */
    public static void deleteProduce(Scanner sca, Connection conn) {
        System.out.println("输入商品id");
        String idNumber = sca.next();
        //商品存在删除 不存在提醒
        if (isExists(conn, idNumber)) {
            if (delete(conn, idNumber)) {
                System.out.println("该商品删除成功");
            } else {
                System.out.println("商品未删除成功，请检查商品id是否正确");
            }
        } else {
            System.out.println("该商品目前不存在");
        }
    }

    /**
     * 更新商品价格
     *
     * @param sca
     * @param conn
     */
    public static void updateProduce(Scanner sca, Connection conn) {
        System.out.println("输入商品id:");
        String idNumber = sca.next();
        if (isExists(conn, idNumber)) {
            System.out.println("输入更改价格:");
            BigDecimal price = sca.nextBigDecimal();
            if (update2(conn, price, idNumber)) {
                System.out.println("该商品价格修改成功");
            } else {
                System.out.println("未修改成功");
            }
        } else {
            System.out.println("该商品目前不存在");
        }
    }

    /**
     * 列表
     *
     * @param conn
     * @param clz
     * @param <T>
     */
    public static <T> void queryProduce(Connection conn, Class<T> clz) {
        List<T> list = new ArrayList<>();
        String sql = "select * from produce";
        PreparedStatement stat = null;
        ResultSet res = null;
        try {
            stat = conn.prepareStatement(sql);
            res = stat.executeQuery();
            ResultSetMetaData metaData = res.getMetaData();
            int count = metaData.getColumnCount();
            while (res.next()) {
                T t = clz.newInstance();
                for (int i = 0; i < count; i++) {
                    Field field = clz.getDeclaredField(Utile.change(metaData.getColumnName(i + 1)));
                    field.setAccessible(true);
                    field.set(t, res.getObject(i + 1));
                }
                list.add(t);
            }
            for (T t : list) {
                System.out.println(t);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
    }

    /**
     * @param sca
     * @param conn
     */
    public static void outStock(Scanner sca, Connection conn) {
        System.out.println("请输入出库商品的id：");
        String idNumber = sca.next();
        //先判断是不是在库中,在库中看出库的数量满不满足,满足更新 stock1
        if (isExistsStock(conn, idNumber)) {
            System.out.println("请输入想要出库的数量：");
            int out = sca.nextInt();
            if (isSatisfy(conn, out, idNumber)) {
                System.out.println(updateStock1(conn, -out, idNumber) ? "出库成功" : "出库失败");
                //看一下库存数量，数量为0的话将商品信息清除
                clearStock1(conn, idNumber);
            } else {
                System.out.println("库存数量不足，请重新输入想要出库数量");
            }
        } else {
            System.out.println("该商品目前不在 stock1 中");
        }
    }

    public static void clearStock1(Connection conn, String idNumber) {
        String sql = "select num from stock1 where id_number = ?";
        ResultSet res = null;
        PreparedStatement stat = null;
        int num = 0;
        try {
            stat = conn.prepareStatement(sql);
            stat.setString(1, idNumber);
            res = stat.executeQuery();
            while (res.next()) {
                num = res.getInt(1);
            }
            if (num == 0) {
                sql = "delete from stock1 where id_number = ?";
                stat = conn.prepareStatement(sql);
                stat.setString(1, idNumber);
                stat.execute();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Utile.closeUtile(res, null, stat);
        }
    }

    /**
     * 看是不是满足出库条件
     *
     * @param conn
     * @param out
     * @param idNumber
     * @return
     */
    public static Boolean isSatisfy(Connection conn, int out, String idNumber) {
        String sql = "select num from stock1 where id_number = ?";
        PreparedStatement stat = null;
        ResultSet res = null;
        int sum = 0;
        try {
            stat = conn.prepareStatement(sql);
            stat.setString(1, idNumber);
            res = stat.executeQuery();
            while (res.next()) {
                sum = res.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Utile.closeUtile(res, null, stat);
        }
        return out <= sum;
    }


    /**
     * 入库操作
     */
    public static void inStock(Scanner sca, Connection conn) {
        System.out.println("请输入入库的产品id");
        String idNumber = sca.next();
        if (isExists(conn, idNumber)) {
            System.out.println("请输入入库的数量:");
            int in = sca.nextInt();
            if (in <= produceNum(conn, idNumber)) {
                //将produce中的该商品数量更新
                update(conn, idNumber, -in);
                //查看原仓库中有没有当前商品，没有插入，有的话更新数量
                if (isExistsStock(conn, idNumber)) {
                    System.out.println(updateStock1(conn, in, idNumber) ? "入库成功" : "入库失败");
                } else {
                    insertStock1(conn, idNumber, in);
                }
                /*update(conn,idNumber,-in);
                //更新stock中的数量总价和平均价格
                updateStock1(conn,in,idNumber);*/
            } else {
                System.out.println("产品数量不足无法入库");
            }
        } else {
            System.out.println("该商品不存在");
        }
    }

    /**
     * 更新 stock1 中的数量
     *
     * @param conn
     * @param obj
     */
    public static Boolean updateStock1(Connection conn, Object... obj) {
        String sql = "update stock1 set num = num + ? where id_number = ?";
        PreparedStatement stat = null;
        Boolean b = false;
        try {
            stat = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                stat.setObject(i + 1, obj[i]);
            }
            b = stat.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Utile.closeUtile(null, null, stat);
        }
        return !b;
    }

    /**
     * 入库 stock1
     *
     * @param conn
     * @param idNumber
     * @param num
     */
    public static void insertStock1(Connection conn, String idNumber, int num) {
        String sql = "insert into stock1(id_number , price ,num) values (?,?,?)";
        //将 produce 中的价格取出来
        String sql1 = "select price from produce where id_number = ?";
        PreparedStatement stat = null;
        ResultSet res = null;
        BigDecimal price = null;
        Boolean b = false;
        try {
            conn.setAutoCommit(false);
            stat = conn.prepareStatement(sql1);
            stat.setString(1, idNumber);
            res = stat.executeQuery();
            while (res.next()) {
                price = res.getBigDecimal(1);
            }
            stat = conn.prepareStatement(sql);
            stat.setString(1, idNumber);
            stat.setBigDecimal(2, price);
            stat.setInt(3, num);
            b = stat.execute();
            System.out.println(!b ? "入库成功" : "入库失败");
            conn.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } finally {
            Utile.closeUtile(res, null, stat);
            try {
                conn.setAutoCommit(true);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

    /**
     * 查看 stock 中是不是存在该商品
     *
     * @param conn
     * @param idNumber
     * @return
     */
    public static Boolean isExistsStock(Connection conn, String idNumber) {
        String sql = "select num from stock1 where id_number = ?";
        PreparedStatement stat = null;
        ResultSet res = null;
        Boolean b = false;
        int i = 0;
        try {
            stat = conn.prepareStatement(sql);
            stat.setString(1, idNumber);
            res = stat.executeQuery();
            while (res.next()) {
                i = res.getInt(1);
            }
            if (i > 0) {
                b = true;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Utile.closeUtile(res, null, stat);
        }
        return b;
    }


    /**
     * 获取 idNumber 的产品数量
     *
     * @param conn
     * @param idNumber
     * @return
     */
    public static int produceNum(Connection conn, String idNumber) {
        String sql = "select num from produce where id_number = ?";
        PreparedStatement stat = null;
        ResultSet res = null;
        int num = 0;
        try {
            stat = conn.prepareStatement(sql);
            stat.setObject(1, idNumber);
            res = stat.executeQuery();
            while (res.next()) {
                num = res.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Utile.closeUtile(res, null, stat);
        }
        return num;
    }


    /**
     * 更新商品数量，用于 addProduce 方法中
     *
     * @param conn
     * @param idNumber
     * @param num
     * @return
     */
    public static Boolean update(Connection conn, String idNumber, int num) {
        PreparedStatement stat = null;
        try {
            stat = conn.prepareStatement("update produce set num = num + ? where id_number = ?");
            stat.setObject(1, num);
            stat.setObject(2, idNumber);
            return !stat.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Utile.closeUtile(null, null, stat);
        }
        throw new RuntimeException("更新商品数量时出错");
    }

    /**
     * 判断商品是否存在
     *
     * @param conn
     * @param idNumber
     * @return
     */
    public static Boolean isExists(Connection conn, String idNumber) {
        String query = "SELECT * FROM produce WHERE id_number = ?";
        PreparedStatement stat = null;
        ResultSet res = null;
        Boolean b = false;
        try {
            stat = conn.prepareStatement(query);
            stat.setObject(1, idNumber);
            res = stat.executeQuery();
            b = res.next();
            return b;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Utile.closeUtile(res, null, stat);
        }
        throw new RuntimeException("判断是否存在时出错");
    }

    /**
     * 插入新的商品
     *
     * @param conn
     * @param obj
     * @return
     */
    public static Boolean insert(Connection conn, Object... obj) {
        String sql = "insert into produce(id_number,name,price,num) values(?,?,?,?)";
        PreparedStatement stat = null;
        try {
            stat = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                stat.setObject(i + 1, obj[i]);
            }
            return !stat.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Utile.closeUtile(null, null, stat);
        }
        throw new RuntimeException();
    }

    /**
     * 将商品信息全部删除 用于 deleteProduce 方法
     *
     * @param conn
     * @param idNumber
     * @return
     */
    public static Boolean delete(Connection conn, String idNumber) {
        String sql = "delete from produce where id_number = ?";
        PreparedStatement stat = null;
        try {
            stat = conn.prepareStatement(sql);
            stat.setObject(1, idNumber);
            return !stat.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Utile.closeUtile(null, null, stat);
        }
        throw new RuntimeException("删除商品时出错");
    }

    /**
     * 修改商品价格 用于 updateProduce
     *
     * @param conn
     * @param obj
     * @return
     */
    public static Boolean update2(Connection conn, Object... obj) {
        String sql = "update produce set price = ? where id_number = ?";
        PreparedStatement stat = null;
        Boolean b = false;
        try {
            stat = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                stat.setObject(i + 1, obj[i]);
            }
            b = stat.execute();
            return !b;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Utile.closeUtile(null, null, stat);
        }
        throw new RuntimeException("修改价格时出错");
    }

    /**
     * 建立关闭打印工具类
     *
     * @param
     */
    public static void closePrint(Reporter reporter) {
        reporter.setStop(false);
    }
}

/**
 * 使用该类进行每隔一分钟的打印操作
 */
public class Reporter implements Runnable {
    private ResultSet res ;
    private Connection conn ;
    private PreparedStatement stat = null;

    private  Boolean isStop = true;

    public  void setStop(Boolean stop) {
        this.isStop = stop;
    }

    public Reporter(Connection conn) {
        this.conn = conn;
    }

    @Override
    public void run() {
        if (isStop) {
            print(conn);
        } else {
            Utile.closeUtile(res, conn, stat);
        }

    }

    public void print(Connection conn) {
        while (true) {
            synchronized (this) {

                System.out.println("-----------仓库信息--------------");

                BigDecimal sumPrice = BigDecimal.ZERO;
                BigDecimal avgPrice = null;
                //获取stock1 中的商品总数总价平均价格
                String sql1 = "select num from stock1 ";
                int sum = 0;
                String sql2 = "select price from stock1";

                try {
                    //获取sum
                    stat = conn.prepareStatement(sql1);
                    res = stat.executeQuery();
                    while (res.next()) {
                        sum += res.getInt(1);
                    }
                    stat = conn.prepareStatement(sql2);
                    res = stat.executeQuery();
                    while (res.next()) {
                        if (res.getBigDecimal(1) != null) {
                            sumPrice = sumPrice.add(res.getBigDecimal(1));
                        }
                    }
                    avgPrice = sumPrice.divide(BigDecimal.valueOf(sum));
                    System.out.println("商品总数：" + sum + "||商品总价值：" + sumPrice + "||商品平均价格：" + avgPrice);

                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
                try {
                    Thread.sleep(60000);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

自己复习忘了看的