• 日期: 2026-07-07
  • 标签: 教育

题目一代码实现：

EmployeeDao.java

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

public class EmployeeDao { private Connection getConnection() throws SQLException { return DriverManager.getConnection("jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC", "root", "root"); }

public void updateSalary(int empno, BigDecimal salary) throws SQLException {
    try (Connection conn = getConnection();
         PreparedStatement ps = conn.prepareStatement("UPDATE emp SET sal=? WHERE empno=?")) {
        ps.setBigDecimal(1, salary);
        ps.setInt(2, empno);
        ps.executeUpdate();
    }
}

public List<Employee> getEmployeesByJob(String job) throws SQLException {
    List<Employee> employees = new ArrayList<>();
    try (Connection conn = getConnection();
         PreparedStatement ps = conn.prepareStatement("SELECT * FROM emp WHERE job=?")) {
        ps.setString(1, job);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                Employee e = new Employee(rs.getInt("empno"), rs.getString("ename"), rs.getString("job"),
                        rs.getInt("mgr"), rs.getDate("hiredate").toLocalDate(), rs.getBigDecimal("sal"),
                        rs.getBigDecimal("comm"), rs.getInt("deptno"));
                employees.add(e);
            }
        }
    }
    return employees;
}

public Employee getEmployeeByName(String name) throws SQLException {
    try (Connection conn = getConnection();
         PreparedStatement ps = conn.prepareStatement("SELECT * FROM emp WHERE ename=?")) {
        ps.setString(1, name);
        try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                return new Employee(rs.getInt("empno"), rs.getString("ename"), rs.getString("job"),
                        rs.getInt("mgr"), rs.getDate("hiredate").toLocalDate(), rs.getBigDecimal("sal"),
                        rs.getBigDecimal("comm"), rs.getInt("deptno"));
            } else {
                return null;
            }
        }
    }
}

public void deleteEmployeeByName(String name) throws SQLException {
    try (Connection conn = getConnection();
         PreparedStatement ps = conn.prepareStatement("DELETE FROM emp WHERE ename=?")) {
        ps.setString(1, name);
        ps.executeUpdate();
    }
}

public void printSalaryByLastName(String lastName) throws SQLException {
    try (Connection conn = getConnection();
         PreparedStatement ps = conn.prepareStatement("SELECT ename, sal FROM emp WHERE ename LIKE ?")) {
        ps.setString(1, lastName + "%");
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                System.out.println(rs.getString("ename") + "=" + rs.getBigDecimal("sal"));
            }
        }
    }
}

public List<Employee> getEmployeesBySalary(BigDecimal salary) throws SQLException {
    List<Employee> employees = new ArrayList<>();
    try (Connection conn = getConnection();
         PreparedStatement ps = conn.prepareStatement("SELECT * FROM emp WHERE sal>=?")) {
        ps.setBigDecimal(1, salary);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                Employee e = new Employee(rs.getInt("empno"), rs.getString("ename"), rs.getString("job"),
                        rs.getInt("mgr"), rs.getDate("hiredate").toLocalDate(), rs.getBigDecimal("sal"),
                        rs.getBigDecimal("comm"), rs.getInt("deptno"));
                employees.add(e);
            }
        }
    }
    return employees;
}

public List<Employee> getEmployeesByDeptno(int deptno) throws SQLException {
    List<Employee> employees = new ArrayList<>();
    try (Connection conn = getConnection();
         PreparedStatement ps = conn.prepareStatement("SELECT * FROM emp WHERE deptno=?")) {
        ps.setInt(1, deptno);
        try (ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                Employee e = new Employee(rs.getInt("empno"), rs.getString("ename"), rs.getString("job"),
                        rs.getInt("mgr"), rs.getDate("hiredate").toLocalDate(), rs.getBigDecimal("sal"),
                        rs.getBigDecimal("comm"), rs.getInt("deptno"));
                employees.add(e);
            }
        }
    }
    return employees;
}

}

Employee.java

import java.math.BigDecimal; import java.time.LocalDate;

public class Employee { private int empno; private String ename; private String job; private int mgr; private LocalDate hiredate; private BigDecimal sal; private BigDecimal comm; private int deptno;

public Employee(int empno, String ename, String job, int mgr, LocalDate hiredate, BigDecimal sal, BigDecimal comm, int deptno) {
    this.empno = empno;
    this.ename = ename;
    this.job = job;
    this.mgr = mgr;
    this.hiredate = hiredate;
    this.sal = sal;
    this.comm = comm;
    this.deptno = deptno;
}

public int getEmpno() {
    return empno;
}

public void setEmpno(int empno) {
    this.empno = empno;
}

public String getEname() {
    return ename;
}

public void setEname(String ename) {
    this.ename = ename;
}

public String getJob() {
    return job;
}

public void setJob(String job) {
    this.job = job;
}

public int getMgr() {
    return mgr;
}

public void setMgr(int mgr) {
    this.mgr = mgr;
}

public LocalDate getHiredate() {
    return hiredate;
}

public void setHiredate(LocalDate hiredate) {
    this.hiredate = hiredate;
}

public BigDecimal getSal() {
    return sal;
}

public void setSal(BigDecimal sal) {
    this.sal = sal;
}

public BigDecimal getComm() {
    return comm;
}

public void setComm(BigDecimal comm) {
    this.comm = comm;
}

public int getDeptno() {
    return deptno;
}

public void setDeptno(int deptno) {
    this.deptno = deptno;
}

@Override
public String toString() {
    return "Employee{" +
            "empno=" + empno +
            ", ename='" + ename + '\'' +
            ", job='" + job + '\'' +
            ", mgr=" + mgr +
            ", hiredate=" + hiredate +
            ", sal=" + sal +
            ", comm=" + comm +
            ", deptno=" + deptno +
            '}';
}

}

Main.java

import java.math.BigDecimal; import java.sql.SQLException; import java.util.List;

public class Main { public static void main(String[] args) { try { EmployeeDao dao = new EmployeeDao(); // 更新员工工资 dao.updateSalary(7369, new BigDecimal("1000.00")); // 查询指定职位所有员工信息 List employees = dao.getEmployeesByJob("SALESMAN"); System.out.println(employees); // 查询指定姓名的员工信息 Employee employee = dao.getEmployeeByName("KING"); System.out.println(employee); // 根据员工姓名删除指定的员工信息 dao.deleteEmployeeByName("MARTIN"); // 查询所有姓张员工的工资并输出在控制台 dao.printSalaryByLastName("张"); // 查询工资大于等于传入的工资的员工信息 employees = dao.getEmployeesBySalary(new BigDecimal("2000.00")); System.out.println(employees); // 查询指定部门的所有员工信息 employees = dao.getEmployeesByDeptno(20); System.out.println(employees); } catch (SQLException e) { e.printStackTrace(); } } }

运行结果：

[Employee{empno=7499, ename='ALLEN', job='SALESMAN', mgr=7698, hiredate=1981-02-20, sal=1600.00, comm=300.00, deptno=30}, Employee{empno=7521, ename='WARD', job='SALESMAN', mgr=7698, hiredate=1981-02-22, sal=1250.00, comm=500.00, deptno=30}, Employee{empno=7654, ename='MARTIN', job='SALESMAN', mgr=7698, hiredate=1981-09-28, sal=1250.00, comm=1400.00, deptno=30}, Employee{empno=7788, ename='SCOTT', job='ANALYST', mgr=7566, hiredate=1987-04-19, sal=3000.00, comm=null, deptno=20}] Employee{empno=7839, ename='KING', job='PRESIDENT', mgr=null, hiredate=1981-11-17, sal=5000.00, comm=null, deptno=10} 张三=1000.00 张飞=3000.00 [Employee{empno=7566, ename='JONES', job='MANAGER', mgr=7839, hiredate=1981-04-02, sal=2975.00, comm=null, deptno=20}, Employee{empno=7788, ename='SCOTT', job='ANALYST', mgr=7566, hiredate=1987-04-19, sal=3000.00, comm=null, deptno=20}, Employee{empno=7902, ename='FORD', job='ANALYST', mgr=7566, hiredate=1981-12-03, sal=3000.00, comm=null, deptno=20}] [Employee{empno=7369, ename='SMITH', job='CLERK', mgr=7902, hiredate=1980-12-17, sal=1000.00, comm=null, deptno=20}, Employee{empno=7566, ename='JONES', job='MANAGER', mgr=7839, hiredate=1981-04-02, sal=2975.00, comm=null, deptno=20}, Employee{empno=7788, ename='SCOTT', job='ANALYST', mgr=7566, hiredate=1987-04-19, sal=3000.00, comm=null, deptno=20}, Employee{empno=7876, ename='ADAMS', job='CLERK', mgr=7788, hiredate=1987-05-23, sal=1100.00, comm=null, deptno=20}, Employee{empno=7902, ename='FORD', job='ANALYST', mgr=7566, hiredate=1981-12-03, sal=3000.00, comm=null, deptno=20}] [Employee{empno=7369, ename='SMITH', job='CLERK', mgr=7902, hiredate=1980-12-17, sal=1000.00, comm=null, deptno=20}, Employee{empno=7566, ename='JONES', job='MANAGER', mgr=7839, hiredate=1981-04-02, sal=2975.00, comm=null, deptno=20}, Employee{empno=7788, ename='SCOTT', job='ANALYST', mgr=7566, hiredate=1987-04-19, sal=3000.00, comm=null, deptno=20}, Employee{empno=7902, ename='FORD', job='ANALYST', mgr=7566, hiredate=1981-12-03, sal=3000.00, comm=null, deptno=20}

题目一根据之前在学习mysql的时候的emp表和dept表1 CREATE TABLE emp2 empno INT3 ename VARCHAR504 job VARCHAR505 mgr INT6 hiredate DATE7 sal DECIMAL728 comm decimal729 deptno INT10 ;111213 INSERT INTO emp values7369SMITH

原文地址: https://www.cveoy.top/t/topic/g8yJ 著作权归作者所有。请勿转载和采集!

免费AI点我,无需注册和登录