使用Java和SQL的方式查询某个员工所在部门的信息

查询某个员工所在部门的信息

表结构

员工表 employees

部门表 departments

  

方式一: JDBC

public static void queryDeptInfoById(int employeeId) {
        PreparedStatement ps = null;
        Connection connection = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            // 查询 特定employee_id职员所在部门的全部信息
            String sql = "SELECT d.* " +
                    "FROM departments d " +
                    "WHERE d.department_id = (" +
                    "    SELECT department_id " +
                    "    FROM employees e " +
                    "    WHERE e.employee_id = ?" +
                    ")";
            ps = connection.prepareStatement(sql);
            ps.setInt(1, employeeId);
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            if (rs.next()) {
                int row = rsmd.getColumnCount();
                int i = row;
                while (row-- > 0) {
                    String columnName = rsmd.getColumnName(i - row);
                    System.out.print(columnName + ": " + rs.getObject((i - row)) + '\t');
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
                if (ps != null) {
                    ps.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

运行结果 

查询103号员工所在部门的信息

 方式二: 基于MySQL

delimiter //
create procedure queryDeptInfoById`(in emp_id int)
begin
    declare dept_name varchar(15);
    declare dept_id int;
	declare manager_id int;
	declare location_id int;

    declare dept_cursor cursor for 
        select d.department_id, d.department_name, d.manager_id, d.location_id
        from departments d 
        where d.department_id = (
            select department_id
            from employees e
            where e.employee_id = emp_id
        );

    -- 打开游标
    open dept_cursor;
    fetch dept_cursor into dept_id, dept_name, manager_id, location_id;
    select dept_id, dept_name, manager_id, location_id;
    -- 关闭游标
    close dept_cursor;
end //
delimiter ;

 运行结果

        上述内容如果有错误的地方，希望大佬们可以指正。我一直在学习的路上，您的帮助使我收获更大！觉得对您有帮助的话，还请点赞支持！我也会不断更新文章！