JDBC入门 作业4-4

package com.imooc.jdbc.common;

import java.sql.*;

public class DbUtils {
    public static Connection getConnection() throws ClassNotFoundException, SQLException {

        Class.forName("com.mysql.cj.jdbc.Driver");

        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/goods?useSSL=false&useUnicode=true&usecharacterEnconding=utf-8&serverTimezone=Asia/Shanghai&allPublicKeyRetrieval=true",
                "root", "root");
        return conn;
    }

    public static void closeConnection(ResultSet rs, Statement stmt, Connection conn) {
        try {
            //5. 关闭连接,释放资源
            if (rs != null) {
                rs.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

package com.imooc.jdbc;
import com.imooc.jdbc.command.Command;
import com.imooc.jdbc.command.DeleteCommand;
import com.imooc.jdbc.command.InsertCommand;
import com.imooc.jdbc.command.UpdateCommand;

import java.util.Scanner;

public class GoodsRescource {
    public static void main(String[] args) {
        System.out.println("1-添加新的商品");
        System.out.println("2-更新商品信息");
        System.out.println("3-删除商品信息");
        System.out.println("请选择功能：");
        Scanner in = new Scanner(System.in);
        Integer cmd = in.nextInt();

        Command command = null;
        switch (cmd){
            case 1:     //查询部门员工
                command = new InsertCommand();
                command.execute();  //执行
                break;
            case 2:
                command = new UpdateCommand();
                command.execute();
                break;
            case 3:
                command = new DeleteCommand();
                command.execute();
                break;
        }
    }
}

package com.imooc.jdbc.command;

import com.imooc.jdbc.common.DbUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class InsertCommand implements Command {
    @Override
    public void execute() {
        Scanner in = new Scanner(System.in);
        System.out.println("请输入商品名字：");
        String name = in.next();
        System.out.println("请输入商品价格：");
        float price = in.nextFloat();
        System.out.println("请输入商品描述：");
        String desp = in.next();

        Connection conn=null;
        PreparedStatement pstmt = null;

        try {
            conn = DbUtils.getConnection();
            String sql = " insert into  goods(name,price,desp) values (?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,name);
            pstmt.setFloat(2,price);
            pstmt.setString(3,desp);
            int cnt = pstmt.executeUpdate();
            System.out.println(cnt+"商品信息已添加。");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DbUtils.closeConnection(null,pstmt,conn);
        }
    }
}

package com.imooc.jdbc.command;

import com.imooc.jdbc.common.DbUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Scanner;
import java.util.concurrent.ForkJoinPool;

public class UpdateCommand implements Command{
    @Override
    public void execute() {
        Scanner in = new Scanner(System.in);
        System.out.println("请输入商品编号：");
        int id = in.nextInt();
        System.out.println("请输入商品新的价格：");
        Float price = in.nextFloat();

        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DbUtils.getConnection();
            String sql = " Update goods set price=? where id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setFloat(1,price);
            pstmt.setInt(2,id);
            int cnt = pstmt.executeUpdate();
            if ( cnt==1 ){
                System.out.println("已成功更改商品价格。");
            }else{
                System.out.println("未找到"+id+"编号的商品。");
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DbUtils.closeConnection(null,pstmt,conn);
        }

    }
}

package com.imooc.jdbc.command;

import com.imooc.jdbc.common.DbUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class DeleteCommand implements Command{
    @Override
    public void execute() {
        Scanner in = new Scanner(System.in);
        System.out.println("请输入商品编号：");
        int id = in.nextInt();

        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DbUtils.getConnection();
            String sql = " Delete from goods where id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            int cnt = pstmt.executeUpdate();
            if ( cnt==1 ){
                System.out.println("已成功删除商品信息。");
            }else{
                System.out.println("删除失败，未找到"+id+"编号的商品。");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DbUtils.closeConnection(null,pstmt,conn);
        }
    }
}

create table goods(
id int primary key auto_increment COMMENT "商品编号",
name varchar(20) not null COMMENT "商品名称",
price float not null comment"商品价格",
desp varchar(20) not null comment"商品描述");

Insert into goods(name,price,desp) 
values ('手机',2000,'黑色，存储容量'),
('冰箱',1500,'银色，对开门'),
('洗衣机',3000,'滚筒');

运行截图：

前面发的两个问题老师可以帮忙删一下惹 略微刷屏 谢谢老师