Java商店管理系统

#java商店管理系统
#IEDA + MySQL数据库
其中数据库表字段有：ID、name、introduce、price

以下为java程序：
1、my_JDBC.java

import java.sql.*;

public class my_JDBC {
    static Connection conn = null;
    static Statement stmt = null;

    /**
     * jdbc的登录方法
     */
    public static void jdbcLogin(String url, String UserName, String password) {
        try {
            // 动态导入数据库的驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 获取数据库链接
            conn = DriverManager.getConnection(url, UserName, password);
            // ||||这里是数据库名字，用户名，密码
            // 创造SQL语句
            ResultSet rs = null;
            System.out.println("数据库连接成功");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 添加数据
     */
    public static void addData(String tableName, String listValue1, String listValue2, int listValue3) {
        try {
            String sql = "INSERT INTO " + tableName + " ( goodsName,introduce,price ) VALUES ( ' " + listValue1 + "','" + listValue2 + "'," + listValue3 + ")";
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);
            System.out.println("数据已插入");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 删除goodsName对应的内容
     */
    public static void deleteData(String tableName, String goodsName) {
        int goodsid = searchID(tableName, goodsName);
        System.out.println(goodsid);
        if (goodsid != 0) {
            System.out.println("goodsName = '" + goodsName + "'已经找到，其id为" + goodsid);
            System.out.println("正在删除......");
            my_JDBC.deleteData(tableName, goodsid); //调用删除id的方法
        } else System.out.println("你要删除的 goodsName='" + goodsName + "'未找到，请检查是否输入有误");
    }

    /**
     * 功能：搜索goodsName是否存在，若存在则返回id,否则返回0
     */
    public static int searchID(String tableName, String goodsName) {
        int goodsid = 0;
        ResultSet rs = null;
        try {
            String sql = "SELECT * FROM " + tableName;
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                goodsid = rs.getInt(1);//第一列
                String getedName = rs.getString(2);//第二列
                getedName = getedName.trim();            //去掉空格
                if (goodsName.equals(getedName)) {
                    return goodsid;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 通过主键id来删除表内容，仅作为过程
     */
    public static void deleteData(String tableName, int id) {
        try {
            String sql = "DELETE FROM " + tableName + " WHERE id = " + id;
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);
            System.out.println("id为" + id + "的数据已删除");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 查询表的所有内容
     */
    public static void traversalData(String tableName) {
        ResultSet rs = null;
        try {
            String sql = "SELECT * FROM " + tableName;
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            System.out.println();
            System.out.println("查询结果如下:");
            System.out.println("  id  -   goodsName   -   introduce   -   price");
            while (rs.next()) {
                String id = rs.getString(1);//第二列
                String goodsName = rs.getString(2);//第二列
                String introduce = rs.getString(3);//第三列
                String price = rs.getString(4);    //第四列
                System.out.println(id + "    -   " + goodsName + "    -   " + introduce + "   -   " + price);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**
     * 查询单个商品的信息，若不存在则提示不存在
     */

    public static void selectData(String tableName, String goodsName) {
        ResultSet rs = null;
        try {
            if (my_JDBC.searchID(tableName, goodsName) == 0) {
                System.out.println("查无此物");
                return;
            }
            String sql = "SELECT * FROM " + tableName;
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            System.out.println();
            System.out.println("查询结果如下:");
            System.out.println("  id  -   goodsName   -   introduce   -   price");
            while (rs.next()) {
                String id = rs.getString(1);//第二列
                String getedName = rs.getString(2);//第二列
                getedName = getedName.trim();            //去掉空格
                String introduce = rs.getString(3);//第三列
                String price = rs.getString(4);    //第四列
                if (goodsName.equals(getedName))
                    System.out.println(id + "    -   " + getedName + "    -   " + introduce + "   -   " + price);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    /**
     * 重排序ID
     */
    public static void resort(String tableName) {
        try {
            String sql = "ALTER TABLE commodity_management_system DROP id";
            String sql2 = "ALTER TABLE commodity_management_system ADD id INT NOT NULL PRIMARY KEY auto_increment first";
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);
            stmt = conn.createStatement();
            stmt.executeUpdate(sql2);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2、managementSystem.java

import java.util.Scanner;

import static java.lang.Integer.parseInt;

public class managementSystem {

    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/znh";
        String UserName = "root";
        String password = "123456";
        String tableName = "commodity_management_system";

        System.out.println("#########程序已启动#########");
        System.out.println("当前操作路径为：" + url);
        System.out.println("用户名为：" + UserName);
        System.out.println("当前操作的表为：" + tableName);
        my_JDBC.jdbcLogin(url, UserName, password);

        while (true) {
            System.out.println("#################商店管理系统#################");
            System.out.println("请选择你要的执行功能");
            System.out.println("    1(添加商品)             2(删除商品)");
            System.out.println("    3(查看所有商品）         4(查看单个商品信息)");
            System.out.println("    5(ID重排序)             6(退出系统)");
            //接收键盘的数据
            Scanner s1 = new Scanner(System.in);
            String sca = s1.next();
            if (sca.equals("6")) {//exit
                System.out.println("欢迎下次使用");
                break;
            } else
                switch (sca) {
                    //add
                    case "1": {
                        System.out.print("请输入商品的名称：");
                        Scanner add1 = new Scanner(System.in);
                        String goodsName = add1.next();

                        System.out.print("请介绍该商品：");
                        Scanner add2 = new Scanner(System.in);
                        String introduce = add2.next();

                        System.out.print("请输入该商品的价格：");
                        Scanner add3 = new Scanner(System.in);
                        int price = parseInt(add3.next());  //String强转int
                        my_JDBC.addData(tableName, goodsName, introduce, price);
                        System.out.println();
                    }
                    //delete
                    break;
                    case "2": {
                        System.out.println("请输入要删除的商品名称：");
                        System.out.println("可输入'menu'返回菜单");
                        Scanner del = new Scanner(System.in);
                        String goodsName = del.next();
                        if (goodsName.equals("menu"))
                            break;
                        else
                            my_JDBC.deleteData(tableName, goodsName);
                    }
                    break;
                    //traversal
                    case "3": {
                        my_JDBC.traversalData(tableName);
                    }
                    break;
                    //select
                    case "4": {
                        System.out.print("请输入您要查询的商品名称：");
                        Scanner sel = new Scanner(System.in);
                        String goodsName = sel.next();
                        my_JDBC.selectData(tableName, goodsName);
                    }
                    break;
                    //resort
                    case "5": {
                        my_JDBC.resort(tableName);
                    }
                    break;
                    default:
                        System.out.println("您的输入有误，请重新输入");
                        break;
                }
        }
    }
}

3、测试效果
（1）启动程序

（2）插入商品信息

（3）遍历数据库所有商品

（4）删除已存在的“牙膏”商品

（5）再删除一次

（6）查询“java课本”属性

（7）ID重排序

经过测试，因为删除商品后其ID无法恢复，只会随着插入而不断递增ID,为此，设计实现ID重排序，消除已经删除商品原来占用的ID，其数据库语句为：

#  ALTER TABLE commodity_management_system DROP id   //删除ID
#  ALTER TABLE commodity_management_system ADD id INT NOT NULL PRIMARY KEY auto_increment first  //再添加ID，并设为第一列

#至于IDEA如何与MySQL建立连接，有时间我再更新好了