第7至13周自测答案与关键实现提示

package com.example.opsticket.controller;

import java.util.Map;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class PingController {
    @GetMapping("/api/ping")
    public Map<String, String> ping() {
        return Map.of("message", "ok");
    }
}

.\mvnw.cmd -v
.\mvnw.cmd spring-boot:run

$ticketBody = @{
    title = '教学楼投影仪无信号'
    description = '通电后指示灯亮，但无法显示输入画面'
    deviceId = 1
} | ConvertTo-Json

$createdTicket = Invoke-RestMethod -Uri 'http://localhost:8080/api/tickets' -Method Post -ContentType 'application/json; charset=utf-8' -Body ([System.Text.Encoding]::UTF8.GetBytes($ticketBody))
$createdTicket
Invoke-RestMethod -Uri ('http://localhost:8080/api/tickets/' + $createdTicket.id)

server.address=127.0.0.1
spring.datasource.url=${OPS_DB_URL}
spring.datasource.username=${OPS_DB_USERNAME}
spring.datasource.password=${OPS_DB_PASSWORD}
mybatis.configuration.map-underscore-to-camel-case=true
mybatis.mapper-locations=classpath:mapper/*.xml

.\mvnw.cmd dependency:tree '-Dincludes=com.mysql:mysql-connector-j'

@Select("""
    SELECT id, title, description, device_id, reporter_id,
           assignee_id, status, version, created_at, updated_at
    FROM ticket
    WHERE id = #{id}
    """)
TicketRow findById(@Param("id") long id);

@Insert("""
    INSERT INTO ticket
      (title, description, device_id, reporter_id, assignee_id,
       status, version, created_at, updated_at)
    VALUES
      (#{title}, #{description}, #{deviceId}, #{reporterId}, NULL,
       'OPEN', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
    """)
@Options(useGeneratedKeys = true, keyProperty = "id")
int insert(TicketRow ticket);

SELECT id, code, name, location, enabled FROM device ORDER BY id;
INSERT INTO device (code, name, location, enabled) VALUES (?, ?, ?, TRUE);
UPDATE device SET enabled = ? WHERE id = ?;

@Update("""
    UPDATE ticket
    SET assignee_id = #{engineerId}, status = 'IN_PROGRESS',
        version = version + 1, updated_at = CURRENT_TIMESTAMP
    WHERE id = #{id} AND status = 'OPEN' AND version = #{expectedVersion}
    """)
int assign(@Param("id") long id,
           @Param("engineerId") long engineerId,
           @Param("expectedVersion") long expectedVersion);

公开的@Transactional方法 assign(id, command)
  1. 取服务器当前操作者，检查其为ADMIN
  2. 查工单，不存在则404
  3. 查目标工程师，要求存在、启用、角色为ENGINEER
  4. 校验OPEN与expectedVersion
  5. 执行带状态和版本条件的UPDATE
  6. 影响行数不是1则抛ConflictException（继承RuntimeException）
  7. INSERT ticket_action_log
       (ticket_id,actor_id,action,from_status,to_status,note,created_at)
       写入(id,操作者id,'ASSIGN','OPEN','IN_PROGRESS',说明,当前时间)
  8. 正常结束提交；异常越过方法时回滚

package com.example.opsticket.dto;

import jakarta.validation.constraints.NotBlank;
import jakarta.validation.constraints.NotNull;
import jakarta.validation.constraints.Positive;
import jakarta.validation.constraints.Size;

public record CreateTicketRequest(
        @NotBlank @Size(max = 50) String title,
        @NotBlank @Size(max = 2000) String description,
        @NotNull @Positive Long deviceId) {
    public CreateTicketRequest {
        title = title == null ? null : title.trim();
        description = description == null ? null : description.trim();
    }
}

// Controller收到page与size后，交给Service前执行的局部代码。
if (page < 1 || size < 1 || size > 100) {
    throw new IllegalArgumentException("page至少为1，size必须在1到100之间");
}
long offset = ((long) page - 1) * size;

<sql id="ticketFilters">
  <where>
    <if test="status != null">
      AND status = #{status}
    </if>
    <if test="deviceId != null">
      AND device_id = #{deviceId}
    </if>
  </where>
</sql>

<select id="count" resultType="long">
  SELECT COUNT(*) FROM ticket
  <include refid="ticketFilters"/>
</select>

<select id="findPage" resultType="com.example.opsticket.model.TicketRow">
  SELECT id, title, description, device_id, reporter_id, assignee_id,
         status, version, created_at, updated_at
  FROM ticket
  <include refid="ticketFilters"/>
  ORDER BY created_at DESC, id DESC
  LIMIT #{size} OFFSET #{offset}
</select>

async function requestJson(url, method = "GET", data) {
  const headers = { Accept: "application/json" };
  const options = { method, headers, credentials: "same-origin" };
  if (data !== undefined) {
    headers["Content-Type"] = "application/json";
    options.body = JSON.stringify(data);
  }
  const response = await fetch(url, options);
  const text = await response.text();
  let result = null;
  if (text) {
    try { result = JSON.parse(text); }
    catch { throw new Error("服务器返回格式异常，请检查Network响应"); }
  }
  if (!response.ok) {
    throw new Error(result?.message ?? `请求失败：${response.status}`);
  }
  return result;
}

const page = await requestJson("/api/tickets?page=1&size=10");
const list = document.getElementById("tickets");
list.replaceChildren();
for (const ticket of page.items) {
  const item = document.createElement("li");
  item.textContent = `${ticket.id} ${ticket.title} ${ticket.status}`;
  list.append(item);
}

SELECT id, username, password_hash, role, enabled
FROM app_user
WHERE username = ?;

@Bean
UserDetailsService userDetailsService(AppUserMapper users) {
    return username -> {
        AppUserRow row = users.findByUsername(username);
        if (row == null) {
            throw new UsernameNotFoundException("账号或密码错误");
        }
        return User.withUsername(row.getUsername())
                .password(row.getPasswordHash())
                .roles(row.getRole())
                .disabled(!row.isEnabled())
                .build();
    };
}

package com.example.opsticket.security;

import java.io.IOException;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.security.authentication.dao.DaoAuthenticationProvider;
import org.springframework.security.config.Customizer;
import org.springframework.security.config.annotation.web.builders.HttpSecurity;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.crypto.factory.PasswordEncoderFactories;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.security.web.SecurityFilterChain;
import org.springframework.security.web.csrf.CsrfException;

@Configuration
public class SecurityConfig {
    @Bean
    PasswordEncoder passwordEncoder() {
        return PasswordEncoderFactories.createDelegatingPasswordEncoder();
    }

    @Bean
    SecurityFilterChain securityFilterChain(
            HttpSecurity http, UserDetailsService users,
            PasswordEncoder passwords) throws Exception {
        var provider = new DaoAuthenticationProvider(users);
        provider.setPasswordEncoder(passwords);
        http.authenticationProvider(provider);
        http.csrf(Customizer.withDefaults());
        http.requestCache(cache -> cache.disable());
        http.authorizeHttpRequests(auth -> auth
                .requestMatchers("/", "/index.html", "/login.html",
                        "/app.js", "/auth.js", "/style.css", "/favicon.ico",
                        "/api/csrf", "/error").permitAll()
                .requestMatchers("/api/**").authenticated()
                .anyRequest().denyAll());
        http.formLogin(form -> form
                .loginPage("/login.html")
                .loginProcessingUrl("/api/login")
                .successHandler((request, response, auth) -> response.setStatus(204))
                .failureHandler((request, response, error) -> writeError(response, 401,
                        "{\"code\":\"LOGIN_FAILED\",\"message\":\"账号或密码错误\"}"))
                .permitAll());
        http.logout(logout -> logout
                .logoutUrl("/api/logout")
                .deleteCookies("JSESSIONID")
                .logoutSuccessHandler((request, response, auth) -> response.setStatus(204))
                .permitAll());
        http.exceptionHandling(errors -> errors
                .authenticationEntryPoint((request, response, error) ->
                        writeError(response, 401,
                                "{\"code\":\"UNAUTHENTICATED\",\"message\":\"请先登录\"}"))
                .accessDeniedHandler((request, response, error) -> {
                    String body = error instanceof CsrfException
                            ? "{\"code\":\"CSRF_INVALID\",\"message\":\"请求令牌失效，请刷新页面\"}"
                            : "{\"code\":\"FORBIDDEN\",\"message\":\"没有操作权限\"}";
                    writeError(response, 403, body);
                }));
        return http.build();
    }

    private static void writeError(HttpServletResponse response, int status,
                                   String fixedJson) throws IOException {
        response.setStatus(status);
        response.setContentType("application/json;charset=UTF-8");
        response.getWriter().write(fixedJson);
    }
}

package com.example.opsticket.controller;

import org.springframework.http.CacheControl;
import org.springframework.http.ResponseEntity;
import org.springframework.security.web.csrf.CsrfToken;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class CsrfController {
    public record TokenView(String headerName, String token) { }

    @GetMapping("/api/csrf")
    public ResponseEntity<TokenView> token(CsrfToken csrf) {
        var view = new TokenView(csrf.getHeaderName(), csrf.getToken());
        return ResponseEntity.ok().cacheControl(CacheControl.noStore()).body(view);
    }
}

let csrfInfo;

async function readResponse(response) {
  const text = await response.text();
  let data = null;
  if (text) {
    try { data = JSON.parse(text); }
    catch { throw new Error(`响应格式异常：${response.status}`); }
  }
  if (!response.ok) {
    const error = new Error(data?.message ?? `请求失败：${response.status}`);
    error.status = response.status;
    error.code = data?.code;
    throw error;
  }
  return data;
}

async function refreshCsrf() {
  const response = await fetch("/api/csrf", {
    credentials: "same-origin", cache: "no-store"
  });
  csrfInfo = await readResponse(response);
}

async function login(username, password) {
  await refreshCsrf();
  const response = await fetch("/api/login", {
    method: "POST",
    credentials: "same-origin",
    headers: { [csrfInfo.headerName]: csrfInfo.token },
    body: new URLSearchParams({ username, password })
  });
  await readResponse(response);
  await refreshCsrf();
}

async function apiJson(url, method = "GET", data) {
  const headers = { Accept: "application/json" };
  const options = { method, credentials: "same-origin", headers };
  const modifies = !["GET", "HEAD", "OPTIONS"].includes(method.toUpperCase());
  if (modifies) {
    if (!csrfInfo) await refreshCsrf();
    headers[csrfInfo.headerName] = csrfInfo.token;
  }
  if (data !== undefined) {
    headers["Content-Type"] = "application/json";
    options.body = JSON.stringify(data);
  }
  return readResponse(await fetch(url, options));
}

async function logout() {
  await apiJson("/api/logout", "POST");
  csrfInfo = undefined;
  await refreshCsrf();
}

boolean visible = switch (actor.role()) {
    case "ADMIN" -> true;
    case "REPORTER" -> ticket.getReporterId() == actor.id();
    case "ENGINEER" -> java.util.Objects.equals(ticket.getAssigneeId(), actor.id());
    default -> false;
};
if (!visible) {
    throw new ForbiddenException("没有访问这张工单的权限");
}

<choose>
  <when test="role == 'ADMIN'">
    AND 1 = 1
  </when>
  <when test="role == 'REPORTER'">
    AND reporter_id = #{currentUserId}
  </when>
  <when test="role == 'ENGINEER'">
    AND assignee_id = #{currentUserId}
  </when>
  <otherwise>
    AND 1 = 0
  </otherwise>
</choose>