package com.imooc.jdbc.good;

import com.imooc.jdbc.hrapp.command.*;

public class Goods {
public static void main(String[] args) {
        Command cmd = new PstmtViewGoods();
        //Command cmd = new AddCommand();
        //Command cmd = new UpdateGoodCommand();
        //Command cmd = new DeleteGoodCommand();
        cmd.execute();
    }
}

package com.imooc.jdbc.hrapp.command;

import com.imooc.jdbc.common.DbUtils;

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

public class AddCommand implements Command{
@Override
    public void execute() {
        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, "耳机");
            pstmt.setFloat(2, 200);
            pstmt.setString(3,"蓝牙耳机");
            int cnd = pstmt.executeUpdate();
            if (cnd == 1){
                Command cmd = new ViewGoods();
                cmd.execute();
            }else{
                System.out.println("添加失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DbUtils.closeConnection(pstmt, null, conn);
        }
    }
}

package com.imooc.jdbc.hrapp.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 DeleteGoodCommand implements Command{
@Override
    public void execute() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "delete from goods where name = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "空调");
            int cnd = pstmt.executeUpdate();
            if (cnd == 1){
                System.out.println("删除空调成功");
            }else{
                System.out.println("删除空调失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DbUtils.closeConnection(pstmt, null, conn);
        }

    }
}

package com.imooc.jdbc.hrapp.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 UpdateGoodCommand implements Command{
@Override
    public void execute() {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "update goods set price = ? where name = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setFloat(1,1500);
            pstmt.setString(2, "手机");
            int cnd = pstmt.executeUpdate();
            if (cnd == 1){
                System.out.println("手机价格调整完毕");
            }else{
                System.out.println("手机价格调整失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DbUtils.closeConnection(pstmt, null, conn);
        }

    }
}

package com.imooc.jdbc.hrapp.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() {
        System.out.print("请输入员工编号: ");
        Scanner in = new Scanner(System.in);
        int eno = in.nextInt();
        System.out.print("请输入员工姓名: ");
        String ename = in.next();
        System.out.print("请输入员工薪资: ");
        float salary = in.nextFloat();
        System.out.print("请输入隶属部门: ");
        String dname = in.next();
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "insert into employee(eno,ename,salary,dname) values (?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, eno);
            pstmt.setString(2, ename);
            pstmt.setFloat(3, salary);
            pstmt.setString(4, dname);
            int cnd = pstmt.executeUpdate();
            if (cnd == 1){
                System.out.println(ename+"员工入职手续已办理成功");
            }else {
                System.out.println(ename + "员工入职手续办理失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DbUtils.closeConnection(pstmt, null, conn);
        }
    }
}

package com.imooc.jdbc.hrapp.command;

import java.sql.*;

public class PstmtViewGoods implements Command{
@Override
    public void execute() {
        String url = "jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimeZone=Asia/shanghai&allowPublicKeyRetrieval=true";
        Connection conn = null;
        //Statement stmt = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
//1.加载并注册JDBC驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.创建数据库连接
            conn = DriverManager.getConnection(url,"root","Ma199722");
            //3.创建Statement对象
            String sql = "select * from goods";
            pstmt = conn.prepareStatement(sql);
            //pstmt.setFloat(1,1500);
            //pstmt.setFloat(2,3500);
            //4.遍历查询结果
            rs = pstmt.executeQuery();
            while(rs.next()){
int id = rs.getInt("id");
                String name = rs.getString("name");
                float price = rs.getFloat("price");
                String desp = rs.getString("desp");
                System.out.println(id + " "+ name + " "+ price + " " +desp);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
//5.关闭连接.释放资源
            try {
if (rs != null){
                    rs.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
try {
if (pstmt != null){
                    pstmt.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
try {
if (conn != null && conn.isClosed() == false){
                    conn.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
}