大一java实训题（java编写）

数据统计系统
设计目的：
1、掌握基本运算
2、掌握循环结构和选择结构
3、掌握数组链表的基本操作
设计要求：
1、录入商品信息，包含编号、名称、单价、类别四种数据
2、将单价大于用户输入的数值的所有商品打印出来
3、按商品类别，统计每种商品的个数，并打印出来

我帮你写

创建表的sql如下

CREATE TABLE `GOODS`(
    `ID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '编号',
    `GOODS_NAME` VARCHAR(50) NOT NULL COMMENT '商品名称',
    `PRICE` DOUBLE COMMENT '价格',
    `TYPE` VARCHAR(50) NULL COMMENT '类别'
    PRIMARY KEY(`ID` ASC)
)COMMENT = '商品信息' ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入数据的sql如下：

insert into GOODS(`ID`, `GOODS_NAME`, `PRICE`, `TYPE`) values(1, '鼠标', 20, '配件');
insert into GOODS(`ID`, `GOODS_NAME`, `PRICE`, `TYPE`) values(2, '鼠标', 50, '配件');
insert into GOODS(`ID`, `GOODS_NAME`, `PRICE`, `TYPE`) values(3, '硬盘', 100, '大件');
insert into GOODS(`ID`, `GOODS_NAME`, `PRICE`, `TYPE`) values(4, '光驱', 400, '大件');

完成的程序代码如下：

    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/goods?useUnicode=true&characterEncoding=utf8";
        System.out.print("请输入商品价格: ");
        Scanner scanner = new Scanner(System.in);
        double price = scanner.nextDouble();
        try (Connection connection = DriverManager.getConnection(url, "root", "123456")) {
            String sql1 = "select * from GOODS where price>?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setDouble(1, price);
            ResultSet rs = preparedStatement.executeQuery();
            System.out.println("大于"+price+"的商品有：");
            while(rs.next()) {
                System.out.print(rs.getString("ID") + " ");
                System.out.print(rs.getString("GOODS_NAME") + " ");
                System.out.print(rs.getString("PRICE") + " ");
                System.out.print(rs.getString("TYPE") + " ");
            }
            System.out.println();

            String sql2 = "select t.TYPE, count(*) from GOODS t group by t.TYPE";
            ResultSet rs2 = connection.createStatement().executeQuery(sql2);
            System.out.println("商品类别，统计每种商品的个数:");
            while(rs.next()) {
                System.out.print(rs.getString(1) + " ");
                System.out.print(rs.getInt(2));
            }
            System.out.println();
        }
    }

如有帮助，请采纳，十分感谢！

有人写了吗？