请写一个sql,查询部门id list的所有部门信息
时间: 2024-05-11 09:17:03 浏览: 3
可以使用以下SQL语句查询部门id list中所有部门的信息:
```
SELECT * FROM departments
WHERE department_id IN (id1, id2, id3, ...);
```
将 `id1, id2, id3, ...` 替换为实际的部门id即可。如果是在程序中动态生成部门id list,可以使用变量或参数代替。
相关问题
java 查询一个用户并且显示他的部门树结构
首先需要明确的是,部门树结构是由部门之间的上下级关系构成的,而一个用户只属于一个部门,因此查询用户并显示其所在的部门树结构需要分两步进行:
1. 查询用户所在的部门
2. 根据部门的上下级关系构建部门树结构,并将用户所在的部门及其祖先部门一起显示出来
下面是一个示例代码,假设有如下的部门表:
| id | name | parent_id |
| ---- | -------- | --------- |
| 1 | 公司 | null |
| 2 | 技术部 | 1 |
| 3 | 开发组 | 2 |
| 4 | 测试组 | 2 |
| 5 | 市场部 | 1 |
| 6 | 销售组 | 5 |
以及如下的用户表:
| id | name | department_id |
| ---- | ------ | ------------- |
| 1 | 张三 | 3 |
| 2 | 李四 | 4 |
| 3 | 王五 | 6 |
代码如下:
```java
import java.sql.*;
import java.util.*;
public class UserDepartmentTree {
private static final String DB_URL = "jdbc:mysql://localhost:3306/test";
private static final String DB_USER = "root";
private static final String DB_PASSWORD = "root";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD)) {
// 查询用户所在的部门
String queryUserSql = "SELECT u.name, d.id, d.name AS department_name, d.parent_id FROM user u JOIN department d ON u.department_id = d.id WHERE u.id = ?;";
PreparedStatement queryUserStmt = conn.prepareStatement(queryUserSql);
queryUserStmt.setInt(1, 1); // 假设查询用户id为1
ResultSet userResult = queryUserStmt.executeQuery();
if (!userResult.next()) {
System.out.println("User not found!");
return;
}
String userName = userResult.getString("name");
int departmentId = userResult.getInt("id");
String departmentName = userResult.getString("department_name");
int parentDepartmentId = userResult.getInt("parent_id");
// 根据部门的上下级关系构建部门树结构
String queryDepartmentSql = "SELECT id, name, parent_id FROM department;";
Statement queryDepartmentStmt = conn.createStatement();
ResultSet departmentResult = queryDepartmentStmt.executeQuery(queryDepartmentSql);
Map<Integer, Department> departmentMap = new HashMap<>();
while (departmentResult.next()) {
int id = departmentResult.getInt("id");
String name = departmentResult.getString("name");
int parentId = departmentResult.getInt("parent_id");
departmentMap.put(id, new Department(id, name, parentId));
}
Department department = departmentMap.get(departmentId);
List<String> departmentNames = new ArrayList<>();
departmentNames.add(department.getName());
while (department.getParentId() != 0) {
department = departmentMap.get(department.getParentId());
departmentNames.add(0, department.getName());
}
// 将用户所在的部门及其祖先部门一起显示出来
StringBuilder sb = new StringBuilder();
sb.append(userName).append("所在的部门:");
for (String name : departmentNames) {
sb.append(name).append(" -> ");
}
sb.delete(sb.length() - 4, sb.length());
System.out.println(sb.toString());
} catch (SQLException e) {
e.printStackTrace();
}
}
static class Department {
private int id;
private String name;
private int parentId;
public Department(int id, String name, int parentId) {
this.id = id;
this.name = name;
this.parentId = parentId;
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public int getParentId() {
return parentId;
}
}
}
```
执行结果:
```
张三所在的部门:公司 -> 技术部 -> 开发组
```
使用Java程序写一个获取部门组织树,并统计各部门人员数量
好的,我可以为您提供一些思路和代码示例。
首先,需要从数据库或其他数据源获取部门组织结构数据。假设我们使用以下表结构:
```
departments表:
id int
name varchar
parent_id int
employees表:
id int
name varchar
department_id int
```
其中,departments表存储部门信息,包括部门id、名称和父部门id;employees表存储员工信息,包括员工id、姓名和所属部门id。
我们可以使用Java的JDBC API连接数据库并查询数据,示例代码如下:
```java
import java.sql.*;
public class DepartmentTree {
public static void main(String[] args) throws SQLException {
// 连接数据库
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "password");
Statement stmt = conn.createStatement();
// 查询所有部门和员工信息
ResultSet deptRs = stmt.executeQuery("SELECT id, name, parent_id FROM departments");
ResultSet empRs = stmt.executeQuery("SELECT id, name, department_id FROM employees");
// 构建部门树
DepartmentNode root = new DepartmentNode("root");
while (deptRs.next()) {
int id = deptRs.getInt("id");
String name = deptRs.getString("name");
int parentId = deptRs.getInt("parent_id");
DepartmentNode node = new DepartmentNode(name);
root.addChild(id, parentId, node);
}
// 统计各部门人员数量
while (empRs.next()) {
int id = empRs.getInt("id");
String name = empRs.getString("name");
int deptId = empRs.getInt("department_id");
root.addEmployee(deptId, name);
}
// 输出部门组织树及人员数量
root.printTree(0);
root.printEmployeeCount();
}
}
```
上述代码中,我们首先连接数据库并查询部门和员工数据。然后,我们使用DepartmentNode类表示部门节点,并根据部门id和父部门id构建部门树。在添加员工数据时,我们遍历部门树,找到对应的部门节点,然后将员工信息添加到该节点中。最后,我们输出部门组织树及各部门人员数量。
DepartmentNode类的实现如下:
```java
import java.util.*;
public class DepartmentNode {
private String name;
private List<DepartmentNode> children;
private Map<Integer, DepartmentNode> childMap;
private Set<String> employees;
public DepartmentNode(String name) {
this.name = name;
this.children = new ArrayList<>();
this.childMap = new HashMap<>();
this.employees = new HashSet<>();
}
public void addChild(int id, int parentId, DepartmentNode child) {
if (parentId == 0) {
this.children.add(child);
} else {
DepartmentNode parent = childMap.get(parentId);
parent.children.add(child);
}
childMap.put(id, child);
}
public void addEmployee(int deptId, String name) {
DepartmentNode node = childMap.get(deptId);
node.employees.add(name);
}
public void printTree(int indent) {
for (int i = 0; i < indent; i++) {
System.out.print(" ");
}
System.out.println(name);
for (DepartmentNode child : children) {
child.printTree(indent + 1);
}
}
public void printEmployeeCount() {
System.out.println("各部门人员数量:");
for (DepartmentNode child : children) {
int count = child.getEmployeeCount();
System.out.println(child.name + ": " + count);
}
}
public int getEmployeeCount() {
int count = employees.size();
for (DepartmentNode child : children) {
count += child.getEmployeeCount();
}
return count;
}
}
```
在DepartmentNode类中,我们使用children列表和childMap映射表存储子部门节点,并使用employees集合存储员工信息。在addChild方法中,我们根据父部门id将部门节点添加到对应的父节点中。在addEmployee方法中,我们根据部门id将员工添加到对应的部门节点中。在printTree方法中,我们使用递归方式输出部门组织树。在printEmployeeCount和getEmployeeCount方法中,我们使用递归方式统计各部门人员数量。
希望这个示例能对您有所帮助!