题目二35分创建员工表的sql语句如下SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for employee-- ----------------------------DROP TABLE IF EXISTS employee;CREATE TABLE

1. 查询部门员工

import java.sql.*;

public class EmployeeQuery {
    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            // 1. 加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");

            // 2. 建立数据库连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8", "username", "password");

            // 3. 创建Statement对象
            stmt = conn.createStatement();

            // 4. 执行SQL查询语句
            String sql = "SELECT * FROM employee WHERE dname = '市场部'";
            rs = stmt.executeQuery(sql);

            // 5. 处理查询结果集
            while (rs.next()) {
                int eno = rs.getInt("eno");
                String ename = rs.getString("ename");
                float salary = rs.getFloat("salary");
                String dname = rs.getString("dname");
                Date hiredate = rs.getDate("hiredate");

                System.out.println("员工编号: " + eno);
                System.out.println("员工姓名: " + ename);
                System.out.println("员工薪资: " + salary);
                System.out.println("所属部门: " + dname);
                System.out.println("入职日期: " + hiredate);
                System.out.println("---------------------");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 6. 关闭资源
            try {
                if (rs != null) {
                    rs.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

2. 办理员工入职

import java.sql.*;

public class EmployeeEntry {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            // 1. 加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");

            // 2. 建立数据库连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8", "username", "password");

            // 3. 创建PreparedStatement对象
            String sql = "INSERT INTO employee (eno, ename, salary, dname, hiredate) VALUES (?, ?, ?, ?, ?)";
            pstmt = conn.prepareStatement(sql);

            // 4. 设置参数
            pstmt.setInt(1, 1010);
            pstmt.setString(2, "员工1010");
            pstmt.setFloat(3, 4500.00);
            pstmt.setString(4, "销售部");
            pstmt.setDate(5, Date.valueOf("2023-02-10"));

            // 5. 执行SQL语句
            int rows = pstmt.executeUpdate();
            if (rows > 0) {
                System.out.println("员工入职办理成功");
            } else {
                System.out.println("员工入职办理失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 6. 关闭资源
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

3. 调整员工薪资

import java.sql.*;

public class EmployeeSalaryAdjustment {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            // 1. 加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");

            // 2. 建立数据库连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8", "username", "password");

            // 3. 创建PreparedStatement对象
            String sql = "UPDATE employee SET salary = ? WHERE eno = ?";
            pstmt = conn.prepareStatement(sql);

            // 4. 设置参数
            pstmt.setFloat(1, 5500.00);
            pstmt.setInt(2, 1001);

            // 5. 执行SQL语句
            int rows = pstmt.executeUpdate();
            if (rows > 0) {
                System.out.println("员工薪资调整成功");
            } else {
                System.out.println("员工薪资调整失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 6. 关闭资源
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

4. 员工离职

import java.sql.*;

public class EmployeeResignation {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            // 1. 加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");

            // 2. 建立数据库连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8", "username", "password");

            // 3. 创建PreparedStatement对象
            String sql = "DELETE FROM employee WHERE eno = ?";
            pstmt = conn.prepareStatement(sql);

            // 4. 设置参数
            pstmt.setInt(1, 1009);

            // 5. 执行SQL语句
            int rows = pstmt.executeUpdate();
            if (rows > 0) {
                System.out.println("员工离职办理成功");
            } else {
                System.out.println("员工离职办理失败");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 6. 关闭资源
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

5. 分页查询员工

import java.sql.*;

public class EmployeePagination {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            // 1. 加载数据库驱动
            Class.forName("com.mysql.jdbc.Driver");

            // 2. 建立数据库连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database_name?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8", "username", "password");

            // 3. 创建PreparedStatement对象
            String sql = "SELECT * FROM employee LIMIT ?, ?";
            pstmt = conn.prepareStatement(sql);

            // 4. 设置参数
            int offset = 0; // 开始位置
            int rowCount = 5; // 每页行数
            pstmt.setInt(1, offset);
            pstmt.setInt(2, rowCount);

            // 5. 执行SQL查询语句
            rs = pstmt.executeQuery();

            // 6. 处理查询结果集
            while (rs.next()) {
                int eno = rs.getInt("eno");
                String ename = rs.getString("ename");
                float salary = rs.getFloat("salary");
                String dname = rs.getString("dname");
                Date hiredate = rs.getDate("hiredate");

                System.out.println("员工编号: " + eno);
                System.out.println("员工姓名: " + ename);
                System.out.println("员工薪资: " + salary);
                System.out.println("所属部门: " + dname);
                System.out.println("入职日期: " + hiredate);
                System.out.println("---------------------");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 7. 关闭资源
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
``