Connection conn = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/web", "root", "1234");

PreparedStatement pstmt = conn.prepareStatement(
        "select * from user where username = ? and password = ?");
pstmt.setString(1, "daqiao");
pstmt.setString(2, "123456");

ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
    Integer id = rs.getInt("id");
    String username = rs.getString("username");
    String password = rs.getString("password");
    String name = rs.getString("name");
    Integer age = rs.getInt("age");
    System.out.println(id + "," + username + "," + password + "," + name + "," + age);
}

rs.close();
pstmt.close();
conn.close();

PreparedStatement pstmt = conn.prepareStatement(
        "select * from user where username = ? and password = ?");
pstmt.setString(1, username);
pstmt.setString(2, password);
ResultSet rs = pstmt.executeQuery();

Connection conn = DriverManager.getConnection(
        "jdbc:mysql://localhost:3306/web", "root", "1234");

String sql = "update user set password = ?, age = ? where id = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, "123456");
pstmt.setInt(2, 25);
pstmt.setInt(3, 1);

int rows = pstmt.executeUpdate();
System.out.println(rows);

pstmt.close();
conn.close();

@Mapper
public interface UserMapper {
    List<User> findAll();

    int deleteById(Integer id);

    int insert(User user);

    int update(User user);

    User findByUsernameAndPassword(@Param("username") String username,
                                   @Param("password") String password);
}

@Select("select * from user")
List<User> findAll();

@Select("select * from user where username = #{username} and password = #{password}")
User findByUsernameAndPassword(@Param("username") String username,
                               @Param("password") String password);

@Delete("delete from user where id = #{id}")
int deleteById(Integer id);

@Insert("insert into user(username, password, name, age) values(#{username}, #{password}, #{name}, #{age})")
int insert(User user);

@Update("update user set username = #{username}, password = #{password}, name = #{name}, age = #{age} where id = #{id}")
int update(User user);

@Data
public class Emp {
    private Integer id;
    private String username;
    private String password;
    private String name;
    private Integer gender;       // 1:男, 2:女
    private String phone;
    private Integer job;          // 1班主任,2讲师,3学工主管,4教研主管,5咨询师
    private Integer salary;
    private String image;
    private LocalDate entryDate;
    private Integer deptId;      // 部门ID（逻辑外键）
    private LocalDateTime createTime;
    private LocalDateTime updateTime;
    
    private String deptName;     // 部门名称（多表查询结果封装）
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class PageResult<T> {
    private Long total;    // 总记录数
    private List<T> rows; // 当前页数据列表
}

@Mapper
public interface EmpMapper {
    /** 查询所有员工及其部门名称 */
    @Select("select e.*, d.name as deptName 
             from emp e left join dept d on e.dept_id = d.id")
    List<Emp> list();
}

@Mapper
public interface EmpMapper {
    // 查询总记录数
    @Select("select count(*) from emp e left join dept d on e.dept_id = d.id")
    Long count();
    
    // 分页查询（limit 起始索引, 每页条数）
    @Select("select e.*, d.name deptName from emp e left join dept d on e.dept_id = d.id 
             limit #{start}, #{pageSize}")
    List<Emp> list(Integer start, Integer pageSize);
}

public PageResult page(Integer page, Integer pageSize) {
    Long total = empMapper.count();           // 1. 查总数
    Integer start = (page - 1) * pageSize;     // 2. 计算起始索引
    List<Emp> empList = empMapper.list(start, pageSize); // 3. 查列表
    return new PageResult(total, empList);     // 4. 封装结果
}

@Mapper
public interface EmpMapper {
    @Select("select e.*, d.name deptName 
             from emp e left join dept d on e.dept_id = d.id")
    List<Emp> list();  // 无需传分页参数
}

public PageResult page(Integer page, Integer pageSize) {
    // 1. 设置分页参数（紧跟着的第一条SQL会被分页）
    PageHelper.startPage(page, pageSize);
    
    // 2. 执行查询（PageHelper自动追加count和limit）
    List<Emp> empList = empMapper.list();
    Page<Emp> p = (Page<Emp>) empList;
    
    // 3. 封装结果
    return new PageResult(p.getTotal(), p.getResult());
}

@GetMapping
public Result page(
    @RequestParam(defaultValue = "1") Integer page,
    @RequestParam(defaultValue = "10") Integer pageSize,
    String name,                    // 姓名（模糊匹配）
    Integer gender,                 // 性别（精确匹配）
    @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin,  // 开始日期
    @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end) {  // 结束日期
    
    PageResult pageResult = empService.page(page, pageSize, name, gender, begin, end);
    return Result.success(pageResult);
}

@Mapper
public interface EmpMapper {
    List<Emp> list(String name, Integer gender, LocalDate begin, LocalDate end);
}

@Data
public class EmpQueryParam {
    private Integer page = 1;
    private Integer pageSize = 10;
    private String name;
    private Integer gender;
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private LocalDate begin;
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private LocalDate end;
}

@GetMapping
public Result page(EmpQueryParam param) {  // 一个对象接收所有参数
    PageResult pageResult = empService.page(param);
    return Result.success(pageResult);
}

List<Emp> list(EmpQueryParam param);  // 传递整个参数对象

@Data
public class Result {
    private Integer code;   // 1成功，0失败
    private String msg;     // 提示信息
    private Object data;    // 响应数据

    public static Result success() { ... }         // 无数据返回
    public static Result success(Object data) { ... }  // 有数据返回
    public static Result error(String msg) { ... }    // 返回错误信息
}

@GetMapping("/depts")
public Result list(){
    ArrayList<Dept> data = deptService.findAll();
    return Result.success(data);
}

@Select("select id, name, create_time, update_time from dept")
ArrayList<Dept> findAll();

@DeleteMapping("/depts")
public Result deleteById(Integer id){
    deptService.deleteById(id);
    return Result.success();
}

@Delete("delete from dept where id=#{id}")
void deleteById(Integer id);

@PostMapping("/depts")
public Result insert(@RequestBody Dept dept){
    deptService.insert(dept);
    return Result.success();
}

public void insert(Dept dept) {
    dept.setCreateTime(LocalDateTime.now());
    dept.setUpdateTime(LocalDateTime.now());
    deptMapper.insert(dept);
}

@Insert("insert into dept(name,create_time,update_time) values(#{name},#{createTime},#{updateTime})")
void insert(Dept dept);

@GetMapping("/depts/{id}")
public Result getById(@PathVariable("id") Integer id){
    Dept dept = deptService.getById(id);
    return Result.success(dept);
}

@PutMapping("/depts")
public Result update(@RequestBody Dept dept){
    deptService.update(dept);
    return Result.success();
}

public void update(Dept dept) {
    dept.setUpdateTime(LocalDateTime.now());
    deptMapper.update(dept);
}

// 方式一：@RequestParam
public Result delete(@RequestParam("id") Integer id)

// 方式二：省略注解（参数名与形参名一致）
public Result delete(Integer id)

public Result save(@RequestBody Dept dept)

@GetMapping("/depts/{id}")
public Result getById(@PathVariable("id") Integer id)

@RequestMapping("/depts")
@RestController
public class DeptController {
    
    @GetMapping          // 完整路径：/depts
    public Result list() { ... }
    
    @DeleteMapping       // 完整路径：/depts
    public Result delete(Integer id) { ... }
    
    @PostMapping         // 完整路径：/depts
    public Result insert(@RequestBody Dept dept) { ... }
    
    @GetMapping("/{id}") // 完整路径：/depts/{id}
    public Result getById(@PathVariable Integer id) { ... }
    
    @PutMapping          // 完整路径：/depts
    public Result update(@RequestBody Dept dept) { ... }
}

// 方式一：声明Logger对象
private static final Logger log = LoggerFactory.getLogger(ClassName.class);

// 方式二：使用Lombok @Slf4j注解
@Slf4j
public class ClassName {
    // 自动生成log对象
}

log.debug("调试信息");
log.info("运行信息");
log.warn("警告信息");
log.error("错误信息");

@DeleteMapping
public Result delete(Integer[] ids) {   // 前端参数名必须与方法形参名一致
    log.info("批量删除员工: ids={}", Arrays.asList(ids));
    empService.deleteByIds(ids);
    return Result.success();
}

@DeleteMapping
public Result delete(@RequestParam List<Integer> ids) {
    log.info("批量删除员工: ids={}", ids);
    empService.deleteByIds(ids);
    return Result.success();
}

@Transactional
@Override
public void deleteByIds(List<Integer> ids) {
    // 1. 根据ID批量删除员工基本信息
    empMapper.deleteByIds(ids);
    // 2. 根据员工ID批量删除工作经历信息
    empExprMapper.deleteByEmpIds(ids);
}

@GetMapping("/{id}")
public Result getInfo(@PathVariable Integer id) {
    log.info("根据id查询员工的详细信息");
    Emp emp = empService.getInfo(id);
    return Result.success(emp);
}

@Transactional
@Override
public void update(Emp emp) {
    // 1. 根据ID更新员工基本信息
    emp.setUpdateTime(LocalDateTime.now());
    empMapper.updateById(emp);

    // 2. 根据员工ID删除员工的工作经历信息 【删除老的】
    empExprMapper.deleteByEmpIds(Arrays.asList(emp.getId()));

    // 3. 新增员工的工作经历数据 【新增新的】
    Integer empId = emp.getId();
    List<EmpExpr> exprList = emp.getExprList();
    if (!CollectionUtils.isEmpty(exprList)) {
        exprList.forEach(empExpr -> empExpr.setEmpId(empId));
        empExprMapper.insertBatch(exprList);
    }
}

@PutMapping
public Result update(@RequestBody Emp emp) {
    log.info("修改员工信息, {}", emp);
    empService.update(emp);
    return Result.success();
}

@RestControllerAdvice
public class GlobalExceptionHandler {

    // 处理异常：方法形参中指定能够处理的异常类型
    @ExceptionHandler
    public Result ex(Exception e) {
        e.printStackTrace();  // 打印堆栈中的异常信息
        // 捕获到异常之后，响应一个标准的Result
        return Result.error("对不起,操作失败,请联系管理员");
    }
}

@Slf4j
@RestControllerAdvice
public class GlobalExceptionHandler {

    // 通用异常兜底
    @ExceptionHandler
    public Result handleException(Exception e) {
        log.error("全局异常处理：{}", e.getMessage());
        return Result.error("出错了,请联系管理员");
    }

    // 针对主键/唯一约束冲突的异常单独处理
    @ExceptionHandler
    public Result handleException(DuplicateKeyException e) {
        log.error("全局异常处理：{}", e.getMessage());
        String message = e.getMessage();
        int index = message.indexOf("Duplicate entry");   // 定位重复字段位置
        String duplicateKey = message.substring(index).split(" ")[2]; // 提取重复的值
        return Result.error(duplicateKey + "已存在");       // 例如：13309090027已存在
    }
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class JobOption {
    private List jobList;   // X轴：职位名称列表
    private List dataList;  // Y轴：对应人数列表
}

@RestController
@RequestMapping("/report")
public class ReportController {
    @Autowired
    private ReportService reportService;

    @GetMapping("/empJobData")
    public Result getEmpJobData() {
        log.info("统计各个职位的员工人数");
        JobOption jobOption = reportService.getEmpJobData();
        return Result.success(jobOption);
    }
}

@Service
public class ReportServiceImpl implements ReportService {
    @Autowired
    private EmpMapper empMapper;

    @Override
    public JobOption getEmpJobData() {
        List<Map<String,Object>> list = empMapper.countEmpJobData();
        // 用Stream分别提取"职位名"和"人数"两个列表
        List<Object> jobList = list.stream().map(dataMap -> dataMap.get("pos")).toList();
        List<Object> dataList = list.stream().map(dataMap -> dataMap.get("total")).toList();
        return new JobOption(jobList, dataList);
    }
}

@MapKey("pos")   // 指定返回Map的唯一标识字段【也可以不指定】
List<Map<String,Object>> countEmpJobData();

@MapKey("name")
List<Map> countEmpGenderData();

@Data
public class Emp {
    ...
    private String deptName;        // 部门名称
    private List<EmpExpr> exprList; // 员工工作经历信息（新增）
}

// 主键返回：将数据库自动生成的主键回填到emp对象的id属性中
@Options(useGeneratedKeys = true, keyProperty = "id")
@Insert("insert into emp(username, name, gender, phone, job, salary, image, entry_date, dept_id, create_time, update_time) " +
        "values (#{username},#{name},#{gender},#{phone},#{job},#{salary},#{image},#{entryDate},#{deptId},#{createTime},#{updateTime})")
void insert(Emp emp);

@Mapper
public interface EmpExprMapper {
    void insertBatch(List<EmpExpr> exprList);  // 批量插入
}

@Override
public void save(Emp emp) {
    // 1. 补全基础属性
    emp.setCreateTime(LocalDateTime.now());
    emp.setUpdateTime(LocalDateTime.now());
    // 2. 保存员工基本信息（insert后emp.getId()有值）
    empMapper.insert(emp);
    // 3. 保存工作经历 - 批量
    Integer empId = emp.getId();
    List<EmpExpr> exprList = emp.getExprList();
    if (!CollectionUtils.isEmpty(exprList)) {
        exprList.forEach(expr -> expr.setEmpId(empId));  // 给每段经历补上empId
        empExprMapper.insertBatch(exprList);
    }
}

@Transactional
@Override
public void save(Emp emp) {
    empMapper.insert(emp);
    int i = 1/0;  // 异常→回滚
    empExprMapper.insertBatch(exprList);
}

// 模拟编译时异常：事务没有回滚，数据保存成功
@Transactional
public void save(Emp emp) throws Exception {
    empMapper.insert(emp);
    throw new Exception("出现异常了~~~");  // 不会回滚！
}

@Transactional(rollbackFor = Exception.class)  // 所有异常都回滚
public void save(Emp emp) throws Exception {
    empMapper.insert(emp);
    throw new Exception("出现异常了~~~");  // 现在会回滚
}

@Service
public class EmpLogServiceImpl implements EmpLogService {
    @Transactional(propagation = Propagation.REQUIRES_NEW)  // 独立新事务
    @Override
    public void insertLog(EmpLog empLog) {
        empLogMapper.insert(empLog);
    }
}

@Slf4j
@RestController
public class UploadController {
    @PostMapping("/upload")
    public Result upload(String username, Integer age, MultipartFile file) throws Exception {
        log.info("上传文件：{}, {}, {}", username, age, file);
        if (!file.isEmpty()) {
            file.transferTo(new File("D:\\images\\" + file.getOriginalFilename()));
        }
        return Result.success();
    }
}

// 生成唯一文件名：UUID + 原文件后缀
String originalFilename = file.getOriginalFilename();
String extName = originalFilename.substring(originalFilename.lastIndexOf("."));
String uniqueFileName = UUID.randomUUID().toString().replace("-", "") + extName;

@Component
public class AliyunOSSOperator {
    private String endpoint = "https://oss-cn-beijing.aliyuncs.com";
    private String bucketName = "java-ai";
    private String region = "cn-beijing";

    public String upload(byte[] content, String originalFilename) throws Exception {
        // 从环境变量获取凭证
        EnvironmentVariableCredentialsProvider credentialsProvider = 
            CredentialsProviderFactory.newEnvironmentVariableCredentialsProvider();
        // 按日期分目录 + UUID唯一文件名
        String dir = LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy/MM"));
        String newFileName = UUID.randomUUID() + originalFilename.substring(originalFilename.lastIndexOf("."));
        String objectName = dir + "/" + newFileName;
        // 创建客户端
        OSS ossClient = OSSClientBuilder.create()
                .endpoint(endpoint)
                .credentialsProvider(credentialsProvider)
                .region(region)
                .build();
        try {
            ossClient.putObject(bucketName, objectName, new ByteArrayInputStream(content));
        } finally {
            ossClient.shutdown();
        }
        // 拼接返回可访问的URL
        return endpoint.split("//")[0] + "//" + bucketName + "." + endpoint.split("//")[1] + "/" + objectName;
    }
}