java JDBC 封装工具类

封装工具类：

public class DbUtils {
    /**
     * 创建新的数据库连接
     * @return 新的Connection对象
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static Connection getConnection() throws SQLException, ClassNotFoundException {
        //1. 加载并注册JDBC驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2. 创建数据库连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/imooc?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true", "root", "root");
        return conn;
    }

    /**
     * 关闭连接,释放资源
     * @param rs 结果集对象
     * @param stmt Statement对象
     * @param conn Connection对象
     */
    public static void closeConnection(ResultSet rs , Statement stmt , Connection conn){
        try {
            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 e) {
            e.printStackTrace();
        }
    }
}

写入数据：
 

/**
 * 新增员工数据
 */
public class InsertCommand implements Command{
    @Override
    public void execute() {
        Scanner in = new Scanner(System.in);
        System.out.print("请输入员工编号:");
        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();
        System.out.print("请输入入职日期：");
        String strHiredate = in.next();
        //String到java.sql.Date分为两步
        //1.String转为java.util.Date
        java.util.Date udHiredate = null;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            udHiredate = sdf.parse(strHiredate);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        //2.java.util.Date转为java.sql.Date
        long time = udHiredate.getTime();//获取自1970年到现在的毫秒数
        java.sql.Date sdHiredate = new java.sql.Date(time);
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "insert into employee(eno,ename,salary,dname,hiredate ) values(?,?,?,?,?)";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, eno);
            pstmt.setString(2, ename);
            pstmt.setFloat(3, salary);
            pstmt.setString(4,dname);
            pstmt.setDate(5,sdHiredate); //java.sql.Date
            int cnt = pstmt.executeUpdate();//所有写操作都使用executeUpdate
            System.out.println("cnt:" + cnt);
            System.out.println(ename + "员工入职手续已办理");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeConnection(null,pstmt,conn);
        }
    }
}

更新：

/**
 * 员工调薪
 */
public class UpdateCommand implements Command{
    @Override
    public void execute() {
        Scanner in = new Scanner(System.in);
        System.out.print("请输入员工编号:");
        int eno = in.nextInt();
        System.out.print("请输入员工新的薪资:");
        float salary = in.nextFloat();
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "update employee set salary=? where eno=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setFloat(1, salary);
            pstmt.setInt(2, eno);
            int cnt = pstmt.executeUpdate();
            if(cnt == 1){
                System.out.println("员工薪资调整完毕");
            }else{
                System.out.println("未找到" + eno + "编号员工数据");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeConnection(null,pstmt,conn);
        }
    }
}

删除：

/**
 * 删除员工数据
 */
public class DeleteCommand implements Command{
    @Override
    public void execute() {
        Scanner in = new Scanner(System.in);
        System.out.print("请输入员工编号:");
        int eno = in.nextInt();
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = DbUtils.getConnection();
            String sql = "delete from employee where eno = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setFloat(1, eno);
            int cnt = pstmt.executeUpdate();
            if(cnt == 1){
                System.out.println("员工离职手续已完成");
            }else{
                System.out.println("未找到" + eno + "编号员工数据");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DbUtils.closeConnection(null,pstmt,conn);
        }
    }
}