第 1 至 6 周自测答案与关键示例

private long id;
private String title;

public Ticket(long id, String title) {
    if (id <= 0) {
        throw new IllegalArgumentException("编号必须大于0");
    }
    String validTitle = normalizeTitle(title);
    this.id = id;
    this.title = validTitle;
}

private static String normalizeTitle(String value) {
    if (value == null) {
        throw new IllegalArgumentException("标题不能为空");
    }
    String normalized = value.trim();
    if (normalized.isEmpty() || normalized.length() > 50) {
        throw new IllegalArgumentException("标题长度必须为1至50");
    }
    return normalized;
}

public void rename(String newTitle) {
    this.title = normalizeTitle(newTitle);
}

Ticket ticket = new Ticket(1L, "  投影仪无法开机  ");
try {
    ticket.rename("   ");
    throw new AssertionError("这里本应拒绝空标题");
} catch (IllegalArgumentException expected) {
    System.out.println(expected.getMessage());
}
System.out.println(ticket.getTitle()); // 投影仪无法开机

public enum TicketStatus {
    OPEN, IN_PROGRESS, RESOLVED, CLOSED
}

private TicketStatus status = TicketStatus.OPEN;
private Long assigneeId; // null表示还没有处理人

public void assign(long engineerId) {
    if (engineerId <= 0) {
        throw new IllegalArgumentException("工程师编号必须大于0");
    }
    if (status != TicketStatus.OPEN) {
        throw new IllegalStateException("只有待派单工单可以派单");
    }
    assigneeId = engineerId;
    status = TicketStatus.IN_PROGRESS;
}

List<Ticket> openTickets = new ArrayList<>();
for (Ticket ticket : tickets) {
    if (ticket.getStatus() == TicketStatus.OPEN) {
        openTickets.add(ticket);
    }
}

Path path = Paths.get("data", "tickets.properties");
Files.createDirectories(path.toAbsolutePath().getParent());
System.out.println(path.toAbsolutePath());

Properties data = new Properties();
data.setProperty("count", "1");
data.setProperty("ticket.0.id", Long.toString(ticket.getId()));
data.setProperty("ticket.0.title", ticket.getTitle());
data.setProperty("ticket.0.status", ticket.getStatus().name());
data.setProperty("ticket.0.assigneeId",
    ticket.getAssigneeId() == null ? "" : ticket.getAssigneeId().toString());

try (Writer writer = Files.newBufferedWriter(path, StandardCharsets.UTF_8)) {
    data.store(writer, "ops-ticket training data");
}
System.out.println("保存成功");

Properties loaded = new Properties();
try (Reader reader = Files.newBufferedReader(path, StandardCharsets.UTF_8)) {
    loaded.load(reader);
}
String rawId = loaded.getProperty("ticket.0.id");
long id = Long.parseLong(rawId);
String title = loaded.getProperty("ticket.0.title");
TicketStatus status = TicketStatus.valueOf(
    loaded.getProperty("ticket.0.status"));
String rawAssignee = loaded.getProperty("ticket.0.assigneeId");
if (rawAssignee == null) {
    throw new IllegalArgumentException("第0条缺少assigneeId字段");
}
Long assignee = rawAssignee.isEmpty() ? null : Long.valueOf(rawAssignee);

public static Ticket restore(long id, String title,
        TicketStatus status, Long assigneeId) {
    if (status == null) {
        throw new IllegalArgumentException("状态不能为空");
    }
    if (status == TicketStatus.OPEN && assigneeId != null) {
        throw new IllegalArgumentException("OPEN工单不能已有处理人");
    }
    if (status != TicketStatus.OPEN
            && (assigneeId == null || assigneeId <= 0)) {
        throw new IllegalArgumentException("非OPEN工单必须有正数处理人");
    }
    Ticket ticket = new Ticket(id, title);
    ticket.status = status;
    ticket.assigneeId = assigneeId;
    return ticket;
}

public class TicketService {
    private final TicketRepository repository;

    public TicketService(TicketRepository repository) {
        this.repository = java.util.Objects.requireNonNull(repository);
    }

    public void rename(long id, String title) {
        Ticket ticket = repository.findById(id)
            .orElseThrow(() -> new IllegalArgumentException("工单不存在"));
        ticket.rename(title);
        repository.update(ticket);
    }
}

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.example</groupId>
  <artifactId>ops-ticket</artifactId>
  <version>0.1.0</version>
  <properties>
    <maven.compiler.release>21</maven.compiler.release>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>
  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.16.0</version>
      </plugin>
    </plugins>
  </build>
</project>

mvn -v
mvn package
java -cp target/ops-ticket-0.1.0.jar com.example.opsticket.ConsoleApp

CREATE DATABASE IF NOT EXISTS ops_ticket_dev
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE ops_ticket_dev;

CREATE TABLE app_user (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(64) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(16) NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  CONSTRAINT ck_user_role CHECK (role IN ('REPORTER','ENGINEER','ADMIN'))
) ENGINE=InnoDB;

CREATE TABLE device (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  location VARCHAR(200) NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT TRUE
) ENGINE=InnoDB;

CREATE TABLE ticket (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(50) NOT NULL,
  description VARCHAR(2000) NOT NULL DEFAULT '',
  device_id BIGINT NOT NULL,
  reporter_id BIGINT NOT NULL,
  assignee_id BIGINT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'OPEN',
  version BIGINT NOT NULL DEFAULT 0,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
    ON UPDATE CURRENT_TIMESTAMP(6),
  CONSTRAINT fk_ticket_device FOREIGN KEY (device_id) REFERENCES device(id),
  CONSTRAINT fk_ticket_reporter FOREIGN KEY (reporter_id) REFERENCES app_user(id),
  CONSTRAINT fk_ticket_assignee FOREIGN KEY (assignee_id) REFERENCES app_user(id),
  CONSTRAINT ck_ticket_title CHECK (CHAR_LENGTH(TRIM(title)) BETWEEN 1 AND 50),
  CONSTRAINT ck_ticket_status CHECK (
    status IN ('OPEN','IN_PROGRESS','RESOLVED','CLOSED')),
  CONSTRAINT ck_ticket_assignment CHECK (
    (status = 'OPEN' AND assignee_id IS NULL) OR
    (status IN ('IN_PROGRESS','RESOLVED','CLOSED') AND assignee_id IS NOT NULL)),
  CONSTRAINT ck_ticket_version CHECK (version >= 0)
) ENGINE=InnoDB;

INSERT INTO app_user (id,username,password_hash,role,enabled) VALUES
 (1,'reporter_a','NOT_A_LOGIN_HASH','REPORTER',TRUE),
 (2,'engineer_a','NOT_A_LOGIN_HASH','ENGINEER',TRUE),
 (3,'admin_a','NOT_A_LOGIN_HASH','ADMIN',TRUE),
 (4,'reporter_b','NOT_A_LOGIN_HASH','REPORTER',TRUE);

INSERT INTO device (id,code,name,location,enabled) VALUES
 (1,'PJ-001','投影仪','A101',TRUE),
 (2,'AC-001','空调','A102',TRUE),
 (3,'PR-001','打印机','办公室',TRUE);

INSERT INTO ticket
 (id,title,description,device_id,reporter_id,assignee_id,status,version,created_at,updated_at)
VALUES
 (101,'投影仪无法开机','按电源键无响应',1,1,NULL,'OPEN',0,
  '2026-11-02 09:00:00','2026-11-02 09:00:00'),
 (102,'空调不制冷','运行十分钟仍无冷风',2,1,2,'IN_PROGRESS',0,
  '2026-11-02 10:00:00','2026-11-02 10:00:00'),
 (103,'投影画面闪烁','更换输入源仍闪烁',1,4,NULL,'OPEN',0,
  '2026-11-02 11:00:00','2026-11-02 11:00:00');

SELECT id,title FROM ticket WHERE status='OPEN' ORDER BY id;
SELECT id,title FROM ticket WHERE reporter_id=1 ORDER BY id;
SELECT id,title FROM ticket WHERE assignee_id IS NULL ORDER BY id;
SELECT id,title FROM ticket WHERE title LIKE '%投影%' ORDER BY id;

SELECT id,name,location FROM device WHERE id=1;
UPDATE device SET location='A103' WHERE id=1;
UPDATE device SET enabled=FALSE WHERE id=1;
SELECT id,device_id FROM ticket WHERE device_id=1;

SELECT t.id,t.title,d.name AS device_name,
       r.username AS reporter_name,
       a.username AS assignee_name,t.status
FROM ticket t
JOIN device d ON d.id=t.device_id
JOIN app_user r ON r.id=t.reporter_id
LEFT JOIN app_user a ON a.id=t.assignee_id
ORDER BY t.id;

SELECT status,COUNT(*) AS total
FROM ticket GROUP BY status ORDER BY status;

SELECT d.id,d.name,COUNT(t.id) AS ticket_count
FROM device d
LEFT JOIN ticket t ON t.device_id=d.id
GROUP BY d.id,d.name
ORDER BY d.id;

SELECT id,title FROM ticket
ORDER BY created_at DESC,id DESC LIMIT 2 OFFSET 0;
SELECT id,title FROM ticket
ORDER BY created_at DESC,id DESC LIMIT 2 OFFSET 2;

SELECT location FROM device WHERE id=1;
START TRANSACTION;
UPDATE device SET location='临时位置' WHERE id=1;
SELECT location FROM device WHERE id=1; -- 当前连接看到临时位置
ROLLBACK;
SELECT location FROM device WHERE id=1; -- 恢复实验前的位置

START TRANSACTION;
UPDATE device SET location='新教室' WHERE id=1;
COMMIT;

EXPLAIN SELECT id,title,created_at FROM ticket
WHERE reporter_id=1
ORDER BY created_at DESC,id DESC LIMIT 20;

CREATE INDEX idx_ticket_reporter_created_id
  ON ticket(reporter_id,created_at,id);

EXPLAIN SELECT id,title,created_at FROM ticket
WHERE reporter_id=1
ORDER BY created_at DESC,id DESC LIMIT 20;

<dependencies>
  <dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>9.7.0</version>
  </dependency>
</dependencies>

private static String requiredEnv(String key) {
    String value = System.getenv(key);
    if (value == null || value.isBlank()) {
        throw new IllegalStateException("缺少环境变量：" + key);
    }
    return value;
}

private static Connection openConnection() throws SQLException {
    return DriverManager.getConnection(
        requiredEnv("OPS_DB_URL"),
        requiredEnv("OPS_DB_USERNAME"),
        requiredEnv("OPS_DB_PASSWORD"));
}

try (Connection connection = openConnection();
     PreparedStatement statement = connection.prepareStatement("SELECT 1");
     ResultSet result = statement.executeQuery()) {
    if (result.next()) {
        System.out.println(result.getInt(1)); // 1
    }
}

static long insertTicket(String normalizedTitle, String description,
        long deviceId, long reporterId) throws SQLException {
    String sql = "INSERT INTO ticket "
        + "(title,description,device_id,reporter_id,status,version) "
        + "VALUES (?,?,?,?, 'OPEN',0)";
    try (Connection connection = openConnection();
         PreparedStatement statement = connection.prepareStatement(
             sql, Statement.RETURN_GENERATED_KEYS)) {
        statement.setString(1, normalizedTitle);
        statement.setString(2, description);
        statement.setLong(3, deviceId);
        statement.setLong(4, reporterId);
        if (statement.executeUpdate() != 1) {
            throw new SQLException("创建工单受影响行数不是1");
        }
        try (ResultSet keys = statement.getGeneratedKeys()) {
            if (!keys.next()) {
                throw new SQLException("没有取得生成的工单编号");
            }
            return keys.getLong(1);
        }
    }
}

String sql = "SELECT id,title,assignee_id,status FROM ticket WHERE id=?";
try (Connection connection = openConnection();
     PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setLong(1, 101L);
    try (ResultSet result = statement.executeQuery()) {
        if (result.next()) {
            Long assignee = result.getObject("assignee_id", Long.class);
            TicketStatus status = TicketStatus.valueOf(result.getString("status"));
            System.out.println(result.getLong("id") + " | "
                + result.getString("title") + " | " + assignee + " | " + status);
        } else {
            System.out.println("工单不存在");
        }
    }
}

浏览器
  → GET /api/tickets/101
后端：检查身份和数据范围 → 查询数据库 → 组织响应
  → HTTP 200
  → Content-Type: application/json
  → {"id":101,"title":"投影仪无法开机","status":"OPEN"}
浏览器读取 JSON 并显示标题