9.12将仓库管理系统改为数据库实现

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

//11111111 华为 华为公司 20230101 20230102 111 00001121 10 5
//22222222 小米 小米公司 20230102 20230202 222 00001122 11 4
//33333333 魅族 魅族公司 20230103 20230302 333 00001123 12 3
//44444444 京东 京东公司 20230104 20230402 444 00001124 13 2
//55555555 淘宝 淘宝公司 20230105 20230502 555 00001125 14 1

public class Main {
public static void main(String[] args) throws SQLException {
Scanner sc = new Scanner(System.in);
while (true) {
show();
System.out.print("请输入数字：");
int choice = sc.nextInt();
switch (choice) {
case 1 -> addshop();
case 2 -> setshop();
case 3 -> outshop();
case 4 -> showAllshop();
case 5 -> deleteshop();
case 6 -> System.exit(0);
default -> System.out.println("该选项不存在");
}
}
}

public static void show() {
System.out.println("***********************************************************");
System.out.println(" 石家庄铁道大学前进22软件开发有限公司 ");
System.out.println(" 仓库管理系统2022版 ");
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("**********************************************************");
}

public static void show1(String itemno1, String itemname1, String shipmenttime1, String suppliername1, String warehousenumber1, String warehouseplace1, int itemnumber1) {
System.out.println("***********************************************************");
System.out.println(" 石家庄铁道大学前进22软件开发有限公司 ");
System.out.println(" 仓库管理系统2022版 ");
System.out.println("***********************************************************");
System.out.println(" 1商品编号：" + itemno1);
System.out.println(" 商品名称：" + itemname1);
System.out.println(" 供货商信息：" + shipmenttime1);
System.out.println(" 入库时间：" + suppliername1);
System.out.println(" 存放仓库号：" + warehousenumber1);
System.out.println(" 存放位置信息：" + warehouseplace1);
System.out.println(" 入库商品数量：" + itemnumber1);
}

public static void show2() {
System.out.println("***********************************************************");
System.out.println(" 石家庄铁道大学前进22软件开发有限公司 ");
System.out.println(" 仓库管理系统2022版 ");
System.out.println("***********************************************************");
System.out.println(" 请输入商品编号：XXXXXXXX ");
System.out.println("***********************************************************");
}


public static int addshop() throws SQLException {
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/数据库名称", "用户名", "密码");
Scanner sc = new Scanner(System.in);
while (true) {
System.out.println("请输入相关信息:");
System.out.println("1商品编号2商品名称3供货商名称4入库时间5出库时间6仓库编号7存放商品的具体位置8入库商品的数量9出库商品的数量");
String itemno1 = sc.next();
String itemname1 = sc.next();
String suppliername1 = sc.next();
String warehousingtime1 = sc.next();
String shipmenttime1 = sc.next();
String warehousenumber1 = sc.next();
String warehouseplace1 = sc.next();
int itemnumber1 = sc.nextInt();
int outnumber1 = sc.nextInt();
if (!itemno1.matches("\\d{8}") || !warehousingtime1.matches("\\d{8}") || !warehousenumber1.matches(".{3}") || !shipmenttime1.matches("\\d{8}")) {
System.out.println("信息错误，请重新输入");
continue;
}
if (shipmenttime1.compareTo(warehousingtime1) < 0 || warehouseplace1.matches("^\\d{4}YY\\d{2}$")) {
System.out.println("信息错误，请重新输入");
continue;
}
String insertQuery0 = "Select itemno,count(*) from shop where itemno = ? ";
PreparedStatement preparedStatement0 = connection.prepareStatement(insertQuery0);
preparedStatement0.setString(1, itemno1);
ResultSet resultSet = preparedStatement0.executeQuery();
if (resultSet.next()) {
int itemCount = resultSet.getInt(2);
System.out.println("找到了 " + itemCount + " 条符合要求的数据。");
if (itemCount != 0) {
System.out.println("商品编号存在请重新输入");
continue;
}
}
show1(itemno1, itemname1, shipmenttime1, suppliername1, warehousenumber1, warehouseplace1, itemnumber1);
System.out.println("该商品入库操作已完成，是否提交（Y/N）");
System.out.println("**********************************************************");
String choice1 = sc.next();
if (choice1.equals("N")) {
continue;
}
if (choice1.equals("Y")) {
String insertQuery = "INSERT INTO shop (itemno, itemname, suppliername, warehousingtime, shipmenttime, warehousenumber, warehouseplace, itemnumber, outnumber) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);
preparedStatement.setString(1, itemno1);
preparedStatement.setString(2, itemname1);
preparedStatement.setString(3, suppliername1);
preparedStatement.setString(4, warehousingtime1);
preparedStatement.setString(5, shipmenttime1);
preparedStatement.setString(6, warehousenumber1);
preparedStatement.setString(7, warehouseplace1);
preparedStatement.setInt(8, itemnumber1);
preparedStatement.setInt(9, outnumber1);
int rowsAffected = preparedStatement.executeUpdate();
if (rowsAffected > 0) {
System.out.println("商品录入成功");
} else {
System.out.println("商品录入失败");
}
return 0;
}
}
}

public static int setshop() throws SQLException {
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/数据库名称", "用户名", "密码");
Scanner sc = new Scanner(System.in);
while (true) {
show2();
System.out.println("请输入八位商品编号:");
String itemno2 = sc.next();
String insertQuery0 = "Select itemno,count(*) from shop where itemno = ? ";
PreparedStatement preparedStatement0 = connection.prepareStatement(insertQuery0);
preparedStatement0.setString(1, itemno2);
ResultSet resultSet = preparedStatement0.executeQuery();
if (resultSet.next()) {
int itemCount = resultSet.getInt(2);
System.out.println("找到了 " + itemCount + " 条符合要求的数据。");
if (itemCount == 0) {
System.out.println("请重新输入");
continue;
}
}
resultSet.close();
preparedStatement0.close();
System.out.println(" 请选择需要修改的信息编号（1-7）：");
System.out.println("***********************************************************");
int choice2 = sc.nextInt();
if (choice2 > 7 || choice2 < 1) {
System.out.println("该选项不存在");
break;
}
String choice3[] = {"商品编号", "商品名称", "供货商信息", "入库时间", "存放仓库号", "存放位置信息", "商品数量"};
System.out.print("请输入修改后的" + choice3[choice2 - 1] + ":");
String s = sc.next();
System.out.println("***********************************************************");
System.out.println("请输入Y/N:");
String s2 = sc.next();
String shop[] = {"itemno", "itemname", "suppliername", "warehousingtime", "warehousenumber", "warehouseplace", "itemnumber"};
if (s2.equals("Y")) {
String insertQuery1 = "UPDATE shop SET " + shop[choice2 - 1] + " = ? where itemno = ? ";
PreparedStatement preparedStatement1 = connection.prepareStatement(insertQuery1);
if (choice2 != 7) {
preparedStatement1.setString(1, s);
} else {
preparedStatement1.setInt(1, Integer.parseInt(s));
}
preparedStatement1.setString(2, itemno2);
preparedStatement1.executeUpdate();
preparedStatement1.close();
System.out.println("商品修改成功");
break;
}
}
return 0;
}


public static int outshop() throws SQLException {
Scanner sc = new Scanner(System.in);
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/数据库名称", "用户名", "密码");
while (true) {
System.out.println("请输入商品编号");
String itemno2 = sc.next();
String insertQuery0 = "Select itemno,count(*) from shop where itemno = ? ";
PreparedStatement preparedStatement0 = connection.prepareStatement(insertQuery0);
preparedStatement0.setString(1, itemno2);
ResultSet resultSet = preparedStatement0.executeQuery();
if (resultSet.next()) {
int itemCount = resultSet.getInt(2);
System.out.println("找到了 " + itemCount + " 条符合要求的数据。");
if (itemCount == 0) {
System.out.println("没有符合要求的编号请重新输入");
continue;
}
}
preparedStatement0.close();
System.out.println("请输入出库时间和数量：");
String outtime = sc.next();
int outshop = sc.nextInt();
String selectQuery = "SELECT warehousingtime,outnumber,itemnumber from shop where itemno = ?";
PreparedStatement preparedStatement1 = connection.prepareStatement(selectQuery);
preparedStatement1.setString(1, itemno2);
ResultSet resultSet1 = preparedStatement1.executeQuery();
preparedStatement1.close();
if (resultSet1.next()) {
int column1Value9 = resultSet1.getInt("outnumber");
int column1Value10 = resultSet1.getInt("itemnumber");
String column1Value11 = resultSet1.getString("warehousingtime");
if (column1Value9 + outshop - column1Value10 > 0) {
System.out.println("出库数量过多请重新输入全部信息");
continue;
}
if (column1Value11.compareTo(outtime) > 0) {
System.out.println("时间不正常请重新输入全部信息");
continue;
}
String insertQuery1 = "update shop set outnumber = ?,shipmenttime = ? where itemno = ? ";
PreparedStatement preparedStatement2 = connection.prepareStatement(insertQuery1);
preparedStatement2.setInt(1, column1Value9 + outshop);
preparedStatement2.setString(2, outtime);
preparedStatement2.setString(3, itemno2);
preparedStatement2.executeUpdate();
preparedStatement2.close();
System.out.println("出库成功");
}
return 0;
}
}


public static void showAllshop() {
System.out.println("数据如下");
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 建立数据库连接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/数据库名称", "用户名", "密码");
// 创建并执行查询语句
String selectQuery = "SELECT * FROM shop";
preparedStatement = connection.prepareStatement(selectQuery);
resultSet = preparedStatement.executeQuery();
// 遍历结果集并打印内容
while (resultSet.next()) {
String column1Value1 = resultSet.getString("itemno"); // 替换成实际的列名
String column1Value2 = resultSet.getString("itemname"); // 替换成实际的列名
String column1Value3 = resultSet.getString("suppliername"); // 替换成实际的列名
String column1Value4 = resultSet.getString("warehousingtime"); // 替换成实际的列名
String column1Value5 = resultSet.getString("shipmenttime"); // 替换成实际的列名
String column1Value6 = resultSet.getString("warehousenumber"); // 替换成实际的列名
String column1Value7 = resultSet.getString("warehouseplace"); // 替换成实际的列名
int column1Value8 = resultSet.getInt("itemnumber"); // 替换成实际的列名
int column1Value9 = resultSet.getInt("outnumber"); // 替换成实际的列名
System.out.println(column1Value1 + " " + column1Value2 + " " + column1Value3 + " " + column1Value4 + " " + column1Value5 + " " + column1Value6 + " " + column1Value7 + " " + column1Value8 + " " + column1Value9 + " ");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭资源
try {
if (resultSet != null) resultSet.close();
if (preparedStatement != null) preparedStatement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}

public static void deleteshop() throws SQLException {
Scanner sc = new Scanner(System.in);
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/数据库名称", "用户名", "密码");
PreparedStatement preparedStatement0;
while (true) {
System.out.println("请输入商品编号");
String itemno2 = sc.next();
String deleteQuery0 = "DELETE FROM shop WHERE itemno = ?";
preparedStatement0 = connection.prepareStatement(deleteQuery0);
preparedStatement0.setString(1, itemno2);
int rowsAffected = preparedStatement0.executeUpdate();
if (rowsAffected > 0) {
System.out.println("成功删除 " + rowsAffected + " 条符合要求的数据。");
break;
} else {
System.out.println("没有找到符合要求的数据，请检查查询条件。");
}
}
preparedStatement0.close();
}
}