查询某个员工所在部门的信息
表结构
员工表 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 ;
运行结果
上述内容如果有错误的地方,希望大佬们可以指正。我一直在学习的路上,您的帮助使我收获更大!觉得对您有帮助的话,还请点赞支持!我也会不断更新文章!