package com.springboot.Mapper;

import com.springboot.Pojo.MyUser;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper //在运行时，会自动生成该接口的实现类对象（代理对象），并且将该对象交给IOC容器管理
public interface UserMapper {
    //查询全部用户信息
    @Select("select * from user")
    public List<MyUser> listUser();
}

package com.springboot.Pojo;


public class MyUser {
    private int id;
    private String name;
    private String password;
    private String createTime;
    private String updateTime;
    private Short status;

    public MyUser() {
    }

    public MyUser(int id, String name, String password, String createTime, String updateTime, Short status) {
        this.id = id;
        this.name = name;
        this.password = password;
        this.createTime = createTime;
        this.updateTime = updateTime;
        this.status = status;
    }

    @Override
    public String toString() {
        return "MyUser{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", createTime=" + createTime +
                ", updateTime=" + updateTime +
                ", status=" + status +
                '}';
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getCreateTime() {
        return createTime;
    }

    public void setCreateTime(String createTime) {
        this.createTime = createTime;
    }

    public String getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(String updateTime) {
        this.updateTime = updateTime;
    }

    public Short getStatus() {
        return status;
    }

    public void setStatus(Short status) {
        this.status = status;
    }
}

package com.springboot.Pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data //get/set/toString/equars/hashCode
@NoArgsConstructor //无参构造
@AllArgsConstructor//全参构造
public class MyUser {
    private int id;
    private String name;
    private String password;
    private String createTime;
    private String updateTime;
    private Short status;
}

@Mapper
public interface EmpMapper {
    //获取所有员工信息
    @Select("select * from emp")
    public List<Emp> findAllEmp();

    //根据id删除员工信息
    //#{}参数占位符
    //执行SQL时，会将#{...}替换为?，生成预编译SQL，会自动设置参数值。
    //使用时机:参数传递，都使用#{...}
    //${...}
//    拼接SQL。直接将参数拼接在SOL语句中，存在SOL注入问题。
//            ·使用时机:如果对表名、列表进行动态设置时使用
    @Delect("delete from emp where id=#{id}")
    public void deleteEmpById(Integer id);

    //新增员工信息
    @Insert("insert into emp(username, password, name, sex, job, entry_date, dept_id, create_time, update_time) values (#{username}, #{password}, #{name}, #{sex}, #{job}, #{entryDate}, #{deptId}, #{createTime}, #{updateTime})")
    public void saveEmp(Emp emp);
}

@Mapper
public interface EmpMapper {
    //新增员工信息
    //主键返回
    @Options(useGeneratedKeys = true, keyProperty = "id")
    @Insert("insert into emp(username, password, name, sex, job, entry_date, dept_id, create_time, update_time) values (#{username}, #{password}, #{name}, #{sex}, #{job}, #{entryDate}, #{deptId}, #{createTime}, #{updateTime})")
    public void saveEmp(Emp emp);
}

@Mapper
public interface EmpMapper {
    //根据id查询员工信息=
    //数据库字段名 别名
    @Select("select id, username, password, name, sex, job, entry_date entryDate, dept_id deptId, create_time createTime, update_time updateTime from emp where id=#{id}")
    public Emp findEmpById(Integer id);
}

@Mapper
public interface EmpMapper {
    //方案二：使用`@Results`和`@Result`注解进行手动封装
    @Results({
            //手动映射 只需要对数据库字段名和实体类属性名不一致的进行映射
            //column 数据库字段名
            //property 实体类属性名
            @Result(column = "entry_date", property = "entryDate"),
            @Result(column = "dept_id", property = "deptId"),
            @Result(column = "create_time", property = "createTime"),
            @Result(column = "update_time", property = "updateTime")
    })
    @Select("select * from emp where id=#{id}")
    public Emp findEmpById(Integer id);
}

@Mapper
public interface EmpMapper {
    //查询符合条件的员工信息
    //name:模糊匹配
    //sex：精准匹配
    //job：精准匹配
    //entry_date:范围匹配
    //根据最后修改时间进行倒序排序
    //在sql中 concat 函数用于将两个或多个字符串连接在一起
    @Select("select * from emp where name like concat('%',#{name},'%') and sex=#{sex} and job=#{job} and entry_date between #{startDate} and #{endDate} order by update_time desc")
    public List<Emp> findEmpByCondition(String name, Short sex, Short job, LocalDateTime startDate, LocalDateTime endDate);
}


//测试类
//查询符合条件的员工
@Test
public void textFindEmpByCondition() {
    List<Emp> empList = empMapper.findEmpByCondition(
            "张三",
            (short) 1,
            (short) 1,
            LocalDateTime.of(2021, 1, 1, 12, 12, 12),
            LocalDateTime.now());
    System.out.println(empList);
}