[条目 29] 基于 OpenGauss 数据库设计人力资源管理系统的实验

package Dao;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import Model.*;

import Util.getInformation;

public class operate {

//********************************登录**************************************

//实现登录操作,登录成功返回true

public String login(String staff_id,String password){

if(staff_id.equals("hr001")){

if (password.equals("hr001")){

return staff_id;

}else {

return null;

}

}else {

String sql="select staff_id,password from staffs ";

ResultSet rs=Util.getInformation.executeQuery(sql);

try {

while(rs.next()){ //用户输入的账号密码和数据库中的信息做比较，判断输入是否正确；

Integer id = rs.getInt("staff_id");

String pwd = rs.getString("password");

if(id.equals(new Integer(staff_id)) && pwd.equals(password)){

return staff_id;

}

}

rs.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

return null;

}

//判断该员工是否为部门经理，返回部门编号

public String isManager(String staff_id){

String sql="select section_id,manager_id from sections";

ResultSet rs=Util.getInformation.executeQuery(sql);

try {

while(rs.next()){ //用户输入的账号密码和数据库中的信息做比较，判断输入是否正确；

Integer id = rs.getInt("manager_id");

String section_id = rs.getString("section_id");

if(id.equals(new Integer(staff_id))){

return section_id;

}

}

rs.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return "null";

}

//**********************************员工操作***********************************

//修改电话号码

public void updatePhoneNumber(String phone_number,String staff_id){

String sql = "update staffs set phone_number=? where staff_id=? ";

Util.getInformation.executeUpdate(sql, phone_number, new Integer(staff_id));

}

//**********************************部门经理**********************************

//查询部门所有员工信息(按员工编号升序排列)

public List QuerySectionStaffsOrderByStaffId(Integer section_id)

{

List list=new ArrayList(); //最终返回整个list集合

String sql="select * from staffs where section_id=? order by staff_id asc";

ResultSet rs=getInformation.executeQuery(sql,section_id);

try {

while(rs.next())

{

//保存取出来的每一条记录

staffs staff =new staffs();

staff.setStaff_id(rs.getInt("staff_id"));

staff.setFirst_name(rs.getString("first_name"));

staff.setLast_name(rs.getString("last_name"));

staff.setEmail(rs.getString("email"));

staff.setPhone_number(rs.getString("phone_number"));

staff.setHire_date(rs.getDate("hire_date"));

staff.setEmployment_id(rs.getString("employment_id"));

staff.setSalary(rs.getInt("salary"));

staff.setCommission_pct(rs.getInt("commission_pct"));

staff.setManager_id(rs.getInt("manager_id"));

staff.setSection_id(rs.getInt("section_id"));

staff.setGraduated_name(rs.getString("graduated_name"));

staff.setPassword(rs.getString("password"));

list.add(staff);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return list;

}

//查询部门所有员工信息(按工资降序排列)

public List QuerySectionStaffsOrderBySalary(Integer section_id)

{

List list=new ArrayList(); //最终返回整个list集合

String sql="select * from staffs where section_id=? order by salary desc";

ResultSet rs=getInformation.executeQuery(sql,section_id);

try {

while(rs.next())

{

//保存取出来的每一条记录

staffs staff =new staffs();

staff.setStaff_id(rs.getInt("staff_id"));

staff.setFirst_name(rs.getString("first_name"));

staff.setLast_name(rs.getString("last_name"));

staff.setEmail(rs.getString("email"));

staff.setPhone_number(rs.getString("phone_number"));

staff.setHire_date(rs.getDate("hire_date"));

staff.setEmployment_id(rs.getString("employment_id"));

staff.setSalary(rs.getInt("salary"));

staff.setCommission_pct(rs.getInt("commission_pct"));

staff.setManager_id(rs.getInt("manager_id"));

staff.setSection_id(rs.getInt("section_id"));

staff.setGraduated_name(rs.getString("graduated_name"));

staff.setPassword(rs.getString("password"));

list.add(staff);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return list;

}

//根据员工号查询部门内员工，然后返回该员工信息

public staffs QuerySectionStaffByStaff_id(Integer staff_id,Integer section_id)

{

staffs staff =new staffs();

String sql="select * from staffs where staff_id=? and section_id=?";

ResultSet rs=getInformation.executeQuery(sql, staff_id,section_id);

try {

if(rs.next())

{

staff.setStaff_id(rs.getInt("staff_id"));

staff.setFirst_name(rs.getString("first_name"));

staff.setLast_name(rs.getString("last_name"));

staff.setEmail(rs.getString("email"));

staff.setPhone_number(rs.getString("phone_number"));

staff.setHire_date(rs.getDate("hire_date"));

staff.setEmployment_id(rs.getString("employment_id"));

staff.setSalary(rs.getInt("salary"));

staff.setCommission_pct(rs.getInt("commission_pct"));

staff.setManager_id(rs.getInt("manager_id"));

staff.setSection_id(rs.getInt("section_id"));

staff.setGraduated_name(rs.getString("graduated_name"));

staff.setPassword(rs.getString("password"));

}

} catch (NumberFormatException | SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return staff;

}

//根据员工姓名查询部门内员工，然后返回该员工信息

public staffs QuerySectionStaffByFirstName(String first_name,Integer section_id)

{

staffs staff =new staffs();

String sql="select * from staffs where first_name=? and section_id=?";

ResultSet rs=getInformation.executeQuery(sql, first_name,section_id);

try {

if(rs.next())

{

staff.setStaff_id(rs.getInt("staff_id"));

staff.setFirst_name(rs.getString("first_name"));

staff.setLast_name(rs.getString("last_name"));

staff.setEmail(rs.getString("email"));

staff.setPhone_number(rs.getString("phone_number"));

staff.setHire_date(rs.getDate("hire_date"));

staff.setEmployment_id(rs.getString("employment_id"));

staff.setSalary(rs.getInt("salary"));

staff.setCommission_pct(rs.getInt("commission_pct"));

staff.setManager_id(rs.getInt("manager_id"));

staff.setSection_id(rs.getInt("section_id"));

staff.setGraduated_name(rs.getString("graduated_name"));

staff.setPassword(rs.getString("password"));

}

} catch (NumberFormatException | SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return staff;

}

public List SectionStatistics(String section_id)

{

ArrayList list =new ArrayList(); // 初始化

String sql="select avg(salary),min(salary),max(salary) from staffs where section_id = ?;";

ResultSet rs=getInformation.executeQuery(sql,section_id);

try {

while(rs.next())

{

//保存取出来的每一条记录

list.add(rs.getInt("avg"));

list.add(rs.getInt("max"));

list.add(rs.getInt("min"));

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return list;

}

//******************************人事经理操作*****************************************

//根据员工号查询员工，然后返回该员工信息

public staffs QueryStaffByStaff_id(Integer staff_id)

{

staffs staff =new staffs();

String sql="select * from staffs where staff_id=?";

ResultSet rs=getInformation.executeQuery(sql, staff_id);

try {

if(rs.next())

{

staff.setStaff_id(rs.getInt("staff_id"));

staff.setFirst_name(rs.getString("first_name"));

staff.setLast_name(rs.getString("last_name"));

staff.setEmail(rs.getString("email"));

staff.setPhone_number(rs.getString("phone_number"));

staff.setHire_date(rs.getDate("hire_date"));

staff.setEmployment_id(rs.getString("employment_id"));

staff.setSalary(rs.getInt("salary"));

staff.setCommission_pct(rs.getInt("commission_pct"));

staff.setManager_id(rs.getInt("manager_id"));

staff.setSection_id(rs.getInt("section_id"));

staff.setGraduated_name(rs.getString("graduated_name"));

staff.setPassword(rs.getString("password"));

}

} catch (NumberFormatException | SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return staff;

}

//根据员工姓名查询员工，然后返回该员工信息

public staffs QueryStaffByFirstName(String first_name)

{

staffs staff =new staffs();

String sql="select * from staffs where first_name=?";

ResultSet rs=getInformation.executeQuery(sql, first_name);

try {

if(rs.next())

{

staff.setStaff_id(rs.getInt("staff_id"));

staff.setFirst_name(rs.getString("first_name"));

staff.setLast_name(rs.getString("last_name"));

staff.setEmail(rs.getString("email"));

staff.setPhone_number(rs.getString("phone_number"));

staff.setHire_date(rs.getDate("hire_date"));

staff.setEmployment_id(rs.getString("employment_id"));

staff.setSalary(rs.getInt("salary"));

staff.setCommission_pct(rs.getInt("commission_pct"));

staff.setManager_id(rs.getInt("manager_id"));

staff.setSection_id(rs.getInt("section_id"));

staff.setGraduated_name(rs.getString("graduated_name"));

staff.setPassword(rs.getString("password"));

}

} catch (NumberFormatException | SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return staff;

}

//查询所有员工信息(按员工编号升序排列)

public List QueryAllStaffsOrderByStaffId()

{

List list=new ArrayList(); //最终返回整个list集合

String sql="select * from staffs order by staff_id asc";

ResultSet rs=getInformation.executeQuery(sql);

try {

while(rs.next())

{

//保存取出来的每一条记录

staffs staff =new staffs();

staff.setStaff_id(rs.getInt("staff_id"));

staff.setFirst_name(rs.getString("first_name"));

staff.setLast_name(rs.getString("last_name"));

staff.setEmail(rs.getString("email"));

staff.setPhone_number(rs.getString("phone_number"));

staff.setHire_date(rs.getDate("hire_date"));

staff.setEmployment_id(rs.getString("employment_id"));

staff.setSalary(rs.getInt("salary"));

staff.setCommission_pct(rs.getInt("commission_pct"));

staff.setManager_id(rs.getInt("manager_id"));

staff.setSection_id(rs.getInt("section_id"));

staff.setGraduated_name(rs.getString("graduated_name"));

staff.setPassword(rs.getString("password"));

list.add(staff);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return list;

}

//查询所有员工信息(按工资降序排列)

public List QueryAllStaffsOrderBySalary()

{

List list=new ArrayList(); //最终返回整个list集合

String sql="select * from staffs order by salary desc";

ResultSet rs=getInformation.executeQuery(sql);

try {

while(rs.next())

{

//保存取出来的每一条记录

staffs staff =new staffs();

staff.setStaff_id(rs.getInt("staff_id"));

staff.setFirst_name(rs.getString("first_name"));

staff.setLast_name(rs.getString("last_name"));

staff.setEmail(rs.getString("email"));

staff.setPhone_number(rs.getString("phone_number"));

staff.setHire_date(rs.getDate("hire_date"));

staff.setEmployment_id(rs.getString("employment_id"));

staff.setSalary(rs.getInt("salary"));

staff.setCommission_pct(rs.getInt("commission_pct"));

staff.setManager_id(rs.getInt("manager_id"));

staff.setSection_id(rs.getInt("section_id"));

staff.setGraduated_name(rs.getString("graduated_name"));

staff.setPassword(rs.getString("password"));

list.add(staff);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return list;

}

public List statistics( )

{

ArrayList list =new ArrayList(); // 初始化

String sql="select avg(salary),min(salary),max(salary),section_id from staffs group by section_id;";

ResultSet rs=getInformation.executeQuery(sql);

try {

while(rs.next())

{

//保存取出来的每一条记录

list.add(rs.getInt("section_id"));

list.add(rs.getInt("avg"));

list.add(rs.getInt("max"));

list.add(rs.getInt("min"));

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return list;

}

//查询所有部门信息

public List QuerySectionOrderBySectionId()

{

List list=new ArrayList(); //最终返回整个list集合

String sql="select * from sections order by section_id asc";

ResultSet rs=getInformation.executeQuery(sql);

try {

while(rs.next())

{

//保存取出来的每一条记录

sections sections =new sections();

sections.setSection_id(rs.getInt("section_id"));

sections.setSection_name(rs.getString("section_name"));

sections.setManager_id(rs.getInt("manager_id"));

sections.setPlace_id(rs.getInt("place_id"));

list.add(sections);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return list;

}

//查询所有工作地点信息

public List QueryPlaces()

{

List list=new ArrayList(); //最终返回整个list集合

String sql="select * from places";

ResultSet rs=getInformation.executeQuery(sql);

try {

while(rs.next())

{

//保存取出来的每一条记录

places places = new places();

places.setPlace_id(rs.getInt("place_id"));

places.setStreet_address(rs.getString("street_address"));

places.setPostal_code(rs.getString("postal_code"));

places.setCity(rs.getString("city"));

places.setState_province(rs.getString("state_province"));

places.setState_id(rs.getString("state_id"));

list.add(places);

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return list;

}

//修改部门名称

public void updateSectionName(String section_name,Integer section_id){

String sql = "update sections set section_name=? where section_id=? ";

Util.getInformation.executeUpdate(sql, section_name, section_id);

}

//实现添加新工作地点

public void addPlace(places place)

{

String sql="insert into places (place_id, street_address, postal_code, city, state_province,state_id) values (?,?,?,?,?,?)";

Util.getInformation.executeUpdate(sql, place.getPlace_id(),place.getStreet_address(), place.getPostal_code(), place.getCity(), place.getState_province(), place.getState_id());

}

// 查询员工工作信息

public List QueryStaffEmployment(String staff_id)

{

List list=new ArrayList(); //最终返回整个list集合

String sql="SELECT staff_id,employment_id,section_id\n" +

"FROM staffs\n" +

"WHERE staff_id = ?\n" +

"\n" +

"UNION\n" +

"\n" +

"SELECT staff_id,employment_id,section_id\n" +

"FROM employment_history\n" +

"WHERE staff_id = ?;";

Integer id = new Integer(staff_id);

ResultSet rs=getInformation.executeQuery(sql,id,id);

try {

while(rs.next())

{

//保存取出来的每一条记录

list.add(rs.getString("staff_id"));

list.add(rs.getString("employment_id"));

list.add(rs.getString("section_id"));

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return list;

}

}