jdbc笔试题

公司组织机构图：

员工表：
其中UserId为用户帐号，UserName为姓名，DepartmentId为部门id。

部门表：
其中id为部门Id，DepartmentName为部门名称，ParentDepartmentId为上一级部门id，Layer为在组织机构图中的级别。

兼职表：
其中MapId为orguser表中id，departmentid为所兼职的部门id。

1、编写sql语句实现分页查询显示员工信息，每页显示4条记录，显示第二页的员工，要求显示序号，员工号，姓名，月薪，按照月薪降序排列

2、通过编写java程序实现当在main方法中输入任意一个用户的UserId时候返回当前部门中所有人员的UserId

说明：公司只有第一级是部门，部门下的都是组。如图：如果输入会计B组人员的账号，要输出财务部所有人员的账号。
例如输入”005”会返回”001 002 003 004 005 015 006 007”返回的顺序可能不一样，但是返回的人数必须完整，
由于用户有兼职，所以如果同一个用户出现了2次或以上只返回一次。

答案

1

SELECT (@row_number:=5) AS 序号,
       e.departmentid AS 员工号,
       e.username AS 姓名,
       e.sal AS 月薪
FROM (
         SELECT departmentid, username, sal
         FROM orguser
         ORDER BY sal DESC
         LIMIT 4 OFFSET 4
     ) AS e
         CROSS JOIN (SELECT @row_number:=0) AS dummy;

2

package src.com;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/*
select userid from orguser where departmentid in (6279,6278,6274,6270,6275);
*/
public class code2 {
    public static void main(String[] args) throws Exception {
        // 注册驱动,获取连接
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/practice";
        String username = "root";
        String password = "1234";
        Connection connection = DriverManager.getConnection(url, username, password);

        /*
        * 根据员工id获取顶层部门id
        * 先获取员工所在部门，再递归获取部门的父部门，直到获取到顶层部门
        * 输入:员工id
        * 输出:顶层部门id
        * */
        String topLevelDepartmentId = getTopLevelDepartmentId(connection, "005");//输入员工id
        /*
        * 根据顶层部门查询其每个(子部门)并获取每个子部门下的(员工id)
        * 先根据顶层部门获取子部门，再递归获取子部门的子部门直到没有下一级，然后根据子部门查询里面的员工id
        * 输入:顶层部门id
        * 返回:员工id
        * */
        List<String> employeeIds = getEmployeesInDepartment(connection, topLevelDepartmentId);
        for (String employeeId : employeeIds) {
            System.out.println("员工id: "+employeeId);
        }

        connection.close();
    }



    /*
    * 查询当前部门id，并递归查询顶层部门的ID
    * 输入:员工id
    * 调用:递归方法getTopLevelDepartment()
    * 输出:员工所在顶级部门id
    */
    private static String getTopLevelDepartmentId(Connection connection, String userId) throws Exception {
        // 从员工表查询用户所在的部门
        String query = "SELECT departmentid FROM orguser WHERE userid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(query);
        preparedStatement.setString(1, userId);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            // 当前用户所在部门id
            String departmentId = resultSet.getString("departmentid");
            // 顶层id
            // 调用递归函数getTopLevelDepartment()获取每一个父级部门id
            String topLevelDepartmentId = getTopLevelDepartment(connection, departmentId);
            return topLevelDepartmentId;
        }
        // 用户不存在
        return null;
    }

    /*
    * 递归方法:递归查询顶层部门的ID
    * 输入:当前部门ID
    * 输出:当前部门的顶级部门的ID
    */
    private static String getTopLevelDepartment(Connection connection, String departmentId) throws Exception {
        //根据部门id查询父部门id和当前部门层级
        String query = "SELECT parentdepartmentid, layer FROM orgdepartment WHERE id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(query);
        preparedStatement.setString(1, departmentId);
        ResultSet resultSet = preparedStatement.executeQuery();

        if (resultSet.next()) {

            // 父部门id
            String parentDepartmentId = resultSet.getString("parentdepartmentid");
            // 当前部门层级
            int layer = resultSet.getInt("layer");


            if (layer == 2) {
                // 顶层部门
                return departmentId;
            } else {
                // 层级没有到顶级，继续递归
                return getTopLevelDepartment(connection, parentDepartmentId);
            }
        }

        // 部门不存在，返回null
        return null;
    }


    /*
    * 递归查询指定部门及其子部门下的所有员工ID
    * 输入:顶级部门ID
    * 输出:部门下(包括子部门)的所有员工ID
    *
    */
    private static List<String> getEmployeesInDepartment(Connection connection, String departmentId) throws Exception {
        List<String> employeeIds = new ArrayList<>();

        // 查询部门下的员工ID
        List<String> departmentEmployeeIds = getEmployeesInGroup(connection, departmentId);
        employeeIds.addAll(departmentEmployeeIds);


        // 查询指定部门下的子部门
        List<String> subDepartmentIds = getSubDepartments(connection, departmentId);


        // 对于每个子部门，递归查询员工ID
        for (String subDepartmentId : subDepartmentIds) {
            List<String> subDepartmentEmployeeIds = getEmployeesInDepartment(connection, subDepartmentId);
            employeeIds.addAll(subDepartmentEmployeeIds);
        }


        return employeeIds;
    }


    /*
    * 查询指定组下的员工ID
    * 输入:员工部门ID
    * 输入:部门下的员工ID
    *
    */
    private static List<String> getEmployeesInGroup(Connection connection, String groupId) throws Exception {
        List<String> employeeIds = new ArrayList<>();
        String query = "SELECT ou.userid FROM orguser ou WHERE ou.departmentid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(query);
        preparedStatement.setString(1, groupId);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            employeeIds.add(resultSet.getString("userid"));
        }

        return employeeIds;
    }


    /*
    * 查询指定部门下的子部门ID
    * 需要递归调用到每个子部门的子部门
    * 输入:部门ID
    * 输出:子部门ID
    */
    private static List<String> getSubDepartments(Connection connection, String departmentId) throws Exception {
        List<String> subDepartmentIds = new ArrayList<>();
        String query = "SELECT id FROM orgdepartment WHERE parentdepartmentid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(query);
        preparedStatement.setString(1, departmentId);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            subDepartmentIds.add(resultSet.getString("id"));
        }

        return subDepartmentIds;
    }

}