新建员工表包含一下字段:员工编号员工姓名员工性别员工年龄员工籍贯入职时间薪资使用mybatis的mapper接口开发完成以下功能:1查询所有员工信息2新增一名员工信息3根据员工编号修改员工信息4根据员工编号删除员工信息5根据性别分组统计平均薪资6查询入职时间在最近一年的员工信息7查询入职两年到三年之间员工的最高薪资和最低薪资的员工信息8查询年龄为25岁的员工中最高薪资的员工信息PS:关于时间的操作
这里是一个示例的员工表的创建脚本:
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
employee_number INT,
employee_name VARCHAR(255),
gender VARCHAR(10),
age INT,
native_place VARCHAR(255),
entry_time DATE,
salary DECIMAL(10, 2)
);
接下来,你可以创建一个对应该表的实体类,并在Mybatis中创建一个对应的Mapper接口。
- 查询所有员工信息
Mapper接口中定义一个方法:
List<Employee> getAllEmployees();
对应的SQL语句:
<select id="getAllEmployees" resultType="Employee">
SELECT * FROM employee
</select>
- 新增一名员工信息
Mapper接口中定义一个方法:
void addEmployee(Employee employee);
对应的SQL语句:
<insert id="addEmployee">
INSERT INTO employee (employee_number, employee_name, gender, age, native_place, entry_time, salary)
VALUES (#{employeeNumber}, #{employeeName}, #{gender}, #{age}, #{nativePlace}, #{entryTime}, #{salary})
</insert>
- 根据员工编号修改员工信息
Mapper接口中定义一个方法:
void updateEmployee(Employee employee);
对应的SQL语句:
<update id="updateEmployee">
UPDATE employee SET employee_number = #{employeeNumber}, employee_name = #{employeeName}, gender = #{gender},
age = #{age}, native_place = #{nativePlace}, entry_time = #{entryTime}, salary = #{salary}
WHERE id = #{id}
</update>
- 根据员工编号删除员工信息
Mapper接口中定义一个方法:
void deleteEmployee(int id);
对应的SQL语句:
<delete id="deleteEmployee">
DELETE FROM employee WHERE id = #{id}
</delete>
- 根据性别分组统计平均薪资
Mapper接口中定义一个方法:
List<Map<String, Object>> getAverageSalaryByGender();
对应的SQL语句:
<select id="getAverageSalaryByGender" resultType="java.util.Map">
SELECT gender, AVG(salary) AS average_salary
FROM employee
GROUP BY gender
</select>
- 查询入职时间在最近一年的员工信息
Mapper接口中定义一个方法:
List<Employee> getRecentYearEmployees();
对应的SQL语句:
<select id="getRecentYearEmployees" resultType="Employee">
SELECT *
FROM employee
WHERE entry_time >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
</select>
- 查询入职两年到三年之间员工的最高薪资和最低薪资的员工信息
Mapper接口中定义一个方法:
List<Employee> getEmployeesByEntryYears();
对应的SQL语句:
<select id="getEmployeesByEntryYears" resultType="Employee">
SELECT *
FROM employee
WHERE entry_time >= DATE_SUB(CURDATE(), INTERVAL 3 YEAR)
AND entry_time < DATE_SUB(CURDATE(), INTERVAL 2 YEAR)
ORDER BY salary DESC
LIMIT 1
</select>
- 查询年龄为25岁的员工中最高薪资的员工信息
Mapper接口中定义一个方法:
Employee getHighestSalaryEmployeeByAge(int age);
对应的SQL语句:
<select id="getHighestSalaryEmployeeByAge" resultType="Employee">
SELECT *
FROM employee
WHERE age = #{age}
ORDER BY salary DESC
LIMIT 1
</select>
以上是一个示例的使用Mybatis的Mapper接口开发员工信息管理功能的实现。你可以根据需要进行修改和扩展
原文地址: https://www.cveoy.top/t/topic/ihST 著作权归作者所有。请勿转载和采集!