@Data               // getter/setter/toString/equals/hashCode
@NoArgsConstructor  // 无参构造
@AllArgsConstructor // 全参构造
public class Emp {
    private Integer id;
    private String username;
    private String name;
    private Short gender;
    private LocalDate entrydate;       // date → LocalDate
    private LocalDateTime createTime;  // datetime → LocalDateTime
    private Integer deptId;
}

@Mapper  ////也可以在启动类上使用 @MapperScan("com.xxx.mapper") 指定扫描包路径。
public interface EmpMapper {
    // #{id} 占位符，自动生成预编译 SQL，防止 SQL 注入
    @Delete("DELETE FROM emp WHERE id = #{id}")
    void delete(Integer id);
}

@Mapper 
public interface EmpMapper {

    @Insert("INSERT INTO user(name, age, email) VALUES(#{name}, #{age}, #{email})")
    //表示使用数据库自增主键，并将生成的主键值回填到实体对象的 id 属性中
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);
}

@Mapper
public interface EmpMapper {
    @Update("UPDATE emp SET username=#{username}, name=#{name}, gender=#{gender}, " +
            "image=#{image}, job=#{job}, entrydate=#{entrydate}, " +
            "dept_id=#{deptId}, update_time=#{updateTime} WHERE id=#{id}")
    void update(Emp emp);
}

// 方案1：SQL 中起别名（保证别名与属性名一致）
@Select("SELECT id, username, dept_id AS deptId, create_time AS createTime FROM emp WHERE id=#{id}")
Emp getById(Integer id);

// 方案2：@Results 手动映射
@Results({
    @Result(column = "dept_id",    property = "deptId"),
    @Result(column = "create_time",property = "createTime"),
    @Result(column = "update_time",property = "updateTime")
    //id = true：表示该字段为主键。
	//property：Java 对象的属性名。
	//column：数据库查询结果的列名。
})
@Select("SELECT * FROM emp WHERE id=#{id}")
Emp getById(Integer id);

// 方案3：开启驼峰命名自动映射（推荐，在 application.properties 中配置一次即可）
// mybatis.configuration.map-underscore-to-camel-case=true
// dept_id → deptId，create_time → createTime，自动匹配
@Select("SELECT * FROM emp WHERE id=#{id}")
Emp getById(Integer id);

//当方法参数 ≥ 2 个，或想在 SQL 中使用明确的参数名时，用 @Param 声明。
@Select("SELECT * FROM user WHERE name = #{name} AND age = #{age}")
User findByNameAndAge(@Param("name") String name, @Param("age") Integer age);

// ❌ 方式1：${}字符串拼接，存在SQL注入风险，不推荐
@Select("SELECT * FROM emp WHERE name LIKE '%${name}%' AND gender=#{gender}")
List<Emp> list(String name, Short gender);

// ✅ 方式2：concat 函数 + #{}，预编译，安全推荐
@Select("SELECT * FROM emp " +
        "WHERE name LIKE CONCAT('%',#{name},'%') " +
        "AND gender=#{gender} " +
        "AND entrydate BETWEEN #{begin} AND #{end} " +
        "ORDER BY update_time DESC")
List<Emp> list(String name, Short gender, LocalDate begin, LocalDate end);

List<User> listUser(@Param("name") String name, @Param("age") Integer age);

int updateUser(User user);

List<User> listUserByIds(@Param("ids") List<Long> ids);

int batchInsertUser(@Param("userList") List<User> userList);

//@Mapper
public interface DeptMapper {

    @Select("select * from dept")
    public List<Dept> findAll();

    // 根据id删除部门信息
    @Delete("delete from dept where id=${id}")
    public int deleteById(Integer id);

    @Select("select ${col} from ${tbn} where id=#{id}")
    public List<Dept> findByDiy(@Param("col") String col, @Param("tbn") String tbn,@Param("id") Integer id);// 形参数量超过1个,必须使用  @Param(形参名) 指定形参名,sql中通过 #{形参名} 获取参数值

    @Insert("insert into dept (name, create_time, update_time) values (#{name},#{createTime},#{updateTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    public int insertDept(Dept dept);

    @Update("update dept set name=#{name}, create_time=#{createTime}, update_time=#{updateTime} where id=#{id}")
    public int updateDept(Dept dept);

    @Select("select * from dept where id=#{id}")
    public Dept findById(Integer id);

    /*@Select("select id id2,name name2, create_time createTime2, update_time updateTime2 from dept where id=#{id}")
    // 通过给列名起别名的方式,只能针对这一个方法生效,其他的sql也得写别名,扩展性很差
    public Dept2 findById2(Integer id);*/

    @Select("select * from dept where id=#{id}")
    // 手动添加,映射关系  我们自己指定哪个字段对应哪个属性
    @Results({
            @Result(id = true,column = "id",property = "id2"),
            @Result(column = "name",property = "name2"),
            @Result(column = "create_time",property = "createTime2"),
            @Result(column = "update_time",property = "updateTime2")
    })
    public Dept2 findById2(Integer id);

    public List<Dept> findAll2();

    public List<Dept> findAll3(Dept dept);
    public List<Dept2> findAll4();

    public int updateDept2(Dept dept);

    public int deleteByIds(@Param("abc") List<Integer> ids);

    public int insertBeach(List<Dept> depts);
}

@SpringBootTest
class Day28Mybatis01ApplicationTests {

	// 获取一个UserDao接口的实现类对象  直接找 springboot要对象即可,不要直接创建!!!!
	@Autowired  // spring 会自动从它的容器中,找 UserDao 类型的对象,并给这个属性赋值
	private UserDao userDao ;

	@Autowired
	private DeptMapper deptMapper;

	@Test
	void contextLoads() {
		List<User> all = userDao.findAll();
		System.out.println(all);
	}
	@Test
	void testDeptMapperSelectAll() {
		List<Dept> all = deptMapper.findAll();
		System.out.println(all);
	}

	@Test
	void testDeptMapperDelById() {
		int i = deptMapper.deleteById(3);
		System.out.println("影响"+i+"行数据");
	}

	@Test
	void testDeptMapperfindByDiy() {
		List<Dept> byDiy = deptMapper.findByDiy("name", "dept", 2);
		System.out.println(byDiy);
	}

	@Test
	void testDeptMapperInsert() {
		Dept dept = new Dept(null, "测试的数据4", new Date(), new Date());
		int i = deptMapper.insertDept(dept);
		System.out.println("添加完成后,dept="+dept);

		//  开启主键返回即可
		System.out.println(i);
	}

	@Test
	void testDeptMapperInsertBeach() {
		Dept dept = new Dept(null, "测试的数据6", new Date(), new Date());
		Dept dept2 = new Dept(null, "测试的数据7", new Date(), new Date());
		Dept dept3 = new Dept(null, "测试的数据8", new Date(), new Date());

		List<Dept> dept1 = List.of(dept, dept2, dept3);

		int i = deptMapper.insertBeach(dept1);
		System.out.println(i);
	}

	@Test
	void testDeptMapperUpdate() {
		Dept dept = new Dept(9, "嘿嘿", new Date(), new Date());
		int i = deptMapper.updateDept(dept);
		//  开启主键返回即可
		System.out.println(i);
	}

	@Test
	void testDeptMapperUpdate2() {
		Dept dept = new Dept(9, "吼吼", null, null);
		int i = deptMapper.updateDept2(dept);
		//  开启主键返回即可
		System.out.println(i);
	}

	@Test
	void testDeptMapperSelectById() {
		Dept byId = deptMapper.findById(9);
		System.out.println(byId);
	}

	@Test
	void testDeptMapperSelectById2() {
		Dept2 byId = deptMapper.findById2(9);
		System.out.println(byId);
	}

	@Test
	void testDeptMapperSelectAll2() {
		List<Dept> all2 = deptMapper.findAll2();
		System.out.println(all2);
	}

	@Test
	void testDeptMapperSelectAll3() {

		Dept dept = new Dept(3, "2", new Date(0), null);

		List<Dept> all3 = deptMapper.findAll3(dept);
		System.out.println(all3);
	}

	@Test
	void testDeptMapperSelectAll4() {


		List<Dept2> all3 = deptMapper.findAll4();
		System.out.println(all3);
	}

	@Test
	void testDeptMapperDelByIds() {

		List<Integer> list = List.of(1, 5, 8);
		int i = deptMapper.deleteByIds(list);
		System.out.println("影响"+i+"行数据");
	}

}