-- 創(chuàng)建數(shù)據(jù)庫
CREATE DATABASE IF NOT EXISTS demo;
USE demo;
-- 創(chuàng)建員工表
CREATE TABLE employees (
                           id INT PRIMARY KEY AUTO_INCREMENT,
                           employee_id VARCHAR(20) UNIQUE NOT NULL,
                           name VARCHAR(50) NOT NULL,
                           gender ENUM('男', '女') NOT NULL,
                           age INT,
                           department VARCHAR(50) NOT NULL,
                           position VARCHAR(50) NOT NULL,
                           salary DECIMAL(10,2),
                           hire_date DATE NOT NULL,
                           email VARCHAR(100),
                           phone VARCHAR(20),
                           address VARCHAR(200),
                           created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 插入示例數(shù)據(jù)
INSERT INTO employees (employee_id, name, gender, age, department, position, salary, hire_date, email, phone, address)
VALUES
('EMP001', '張三', '男', 28, '技術部', '軟件工程師', 15000.00, '2020-03-15', 'zhangsan@company.com', '13800138001', '北京市海淀區(qū)'),
('EMP002', '李四', '女', 32, '人力資源部', 'HR經(jīng)理', 12000.00, '2018-06-20', 'lisi@company.com', '13800138002', '北京市朝陽區(qū)'),
('EMP003', '王五', '男', 35, '財務部', '財務主管', 18000.00, '2016-09-10', 'wangwu@company.com', '13800138003', '北京市西城區(qū)'),
('EMP004', '趙六', '女', 26, '市場部', '市場專員', 8000.00, '2021-01-08', 'zhaoliu@company.com', '13800138004', '北京市東城區(qū)'),
('EMP005', '錢七', '男', 30, '技術部', '高級工程師', 20000.00, '2019-11-25', 'qianqi@company.com', '13800138005', '北京市豐臺區(qū)'),
('EMP006', '孫八', '女', 29, '銷售部', '銷售經(jīng)理', 16000.00, '2020-07-30', 'sunba@company.com', '13800138006', '北京市石景山區(qū)'),
('EMP007', '周九', '男', 27, '技術部', '前端開發(fā)', 13000.00, '2021-03-12', 'zhoujiu@company.com', '13800138007', '北京市通州區(qū)'),
('EMP008', '吳十', '女', 33, '行政部', '行政主管', 11000.00, '2017-05-18', 'wushi@company.com', '13800138008', '北京市昌平區(qū)'),
('EMP009', '鄭十一', '男', 31, '財務部', '會計', 10000.00, '2019-08-22', 'zhengshiyi@company.com', '13800138009', '北京市大興區(qū)'),
('EMP010', '王芳', '女', 25, '市場部', '市場助理', 7000.00, '2022-02-14', 'wangfang@company.com', '13800138010', '北京市房山區(qū)');

spring.application.name=demo
#驅動類名稱
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#數(shù)據(jù)庫連接的url
spring.datasource.url=jdbc:mysql://localhost:3306/demo
#連接數(shù)據(jù)庫的用戶名
spring.datasource.username=root
#連接數(shù)據(jù)庫的密碼
spring.datasource.password=1234

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {
    private Integer id; //ID
    private String username; //用戶名
    private String password; //密碼
    private String name; //姓名
    private Short gender; //性別 , 1 男, 2 女
    private String image; //圖像url
    private Short job; //職位 , 1 班主任 , 2 講師 , 3 學工主管 , 4 教研主管 , 5 咨詢師
    private LocalDate entrydate; //入職日期
    private Integer deptId; //部門ID
    private LocalDateTime createTime; //創(chuàng)建時間
    private LocalDateTime updateTime; //修改時間
}

@Mapper
public interface EmpMapper {
    //分頁查詢
    @Select("select * from emp limit #{start},#{pageSize}")
    List<Emp> page(Integer start, Integer pageSize);
    //條件分頁查詢
    List<Emp> list(Integer start, Integer pageSize,String name, Short gender, LocalDate begin, LocalDate end);
    //查詢數(shù)據(jù)總數(shù)
    @Select("select count(*) from emp")
    Long count();
    //批量刪除員工
    void delete(List<Integer> ids);
    //新增員工
    @Insert("insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time) " +
            "value(#{username},#{name},#{gender},#{image},#{job},#{entrydate},#{deptId},#{createTime},#{updateTime})")
    void save(Emp emp);
    //根據(jù)id更新員工信息
    void update(Emp emp);
    //根據(jù)id查詢員工信息
    @Select("select * from emp where id = #{id}")
    Emp getById(Integer id);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="cn.nuist.tlias.mapper.EmpMapper">
    <select id="list" resultType="cn.nuist.tlias.pojp.Emp">
        select *
        from emp
        <where>
            <if test="name != null and name != ''">
                name like concat('%',#{name},'%')
            </if>
            <if test="gender != null">
                and gender = #{gender}
            </if>
            <if test="begin != null and end != null">
                and entrydate between #{begin} and #{end}
            </if>
        </where>
        order by update_time desc
        limit #{start},#{pageSize}
    </select>
    <delete id="delete">
        delete
        from emp
        where id in
        <foreach collection="ids" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    <update id="update">
        update emp
        <set>
            <if test="username != null and username != ''">
                username = #{username}
            </if>
            <if test="name != null and name != ''">
                name = #{name}
            </if>
            <if test="gender != null">
                gender = #{gender}
            </if>
            <if test="image != null and image != ''">
                image = #{image}
            </if>
            <if test="job != null">
                job = #{job}
            </if>
            <if test="entrydate != null">
                entrydate = #{entrydate}
            </if>
            <if test="deptId != null">
                dept_id = #{deptId}
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime}
            </if>
        </set>
        where id = #{id}
    </update>
</mapper>