本文提供一個基於 **JDBC (SQL Server 2005 驅動)**、**Swing GUI** 的完整考試系統原型:隨機抽取 10 道題 → 存入學生試卷表 (重新編號 1~10) → 逐題作答/顯示答案 → 交卷統計。
-- 題庫表
CREATE TABLE Questions (
QID INT IDENTITY PRIMARY KEY,
Content NVARCHAR(MAX) NOT NULL,
OptionA NVARCHAR(500), OptionB NVARCHAR(500), OptionC NVARCHAR(500), OptionD NVARCHAR(500),
CorrectAnswer CHAR(1) CHECK (CorrectAnswer IN ('A','B','C','D')),
Category NVARCHAR(50), Difficulty TINYINT
);
-- 學生試卷表 (每次考試生成)
CREATE TABLE StudentExam (
ExamID INT IDENTITY PRIMARY KEY,
StudentID INT NOT NULL,
QNum TINYINT NOT NULL, -- 1~10
QID INT NOT NULL, -- 原題庫 ID
UserAnswer CHAR(1) NULL,
IsCorrect BIT NULL,
CONSTRAINT UQ_StudentExam UNIQUE (ExamID, QNum)
);
public class ExamSystem {
// JDBC 配置 (生產環境請用連線池 HikariCP/DBCP)
private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=ExamDB;";
private static final String USER = "sa";
private static final String PASS = "123456";
private static final String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
// 1. 隨機抽題並生成試卷 (核心邏輯)
public void generateExamPaper(int studentId) {
String sql = "SELECT TOP 10 QID, Content, OptionA, OptionB, OptionC, OptionD, CorrectAnswer " +
"FROM Questions ORDER BY NEWID()"; // SQL Server 隨機排序
try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
conn.setAutoCommit(false); // 開啟事務
String insertSql = "INSERT INTO StudentExam (StudentID, QNum, QID, UserAnswer, IsCorrect) VALUES (?, ?, ?, NULL, NULL)";
try (PreparedStatement ips = conn.prepareStatement(insertSql)) {
int qNum = 1;
while (rs.next() && qNum <= 10) {
ips.setInt(1, studentId);
ips.setInt(2, qNum++);
ips.setInt(3, rs.getInt("QID"));
ips.addBatch();
}
ips.executeBatch();
conn.commit();
} catch (Exception e) {
conn.rollback();
throw e;
}
} catch (SQLException e) { e.printStackTrace(); }
}
// 2. 取題顯示 (含選項)
public Question getQuestion(int examId, int qNum) { ... }
// 3. 提交答案並判分
public void submitAnswer(int examId, int qNum, char answer) { ... }
// 4. 交卷統計
public ExamResult finishExam(int examId) { ... }
}
public class ExamFrame extends JFrame {
private JLabel lblQuestion, lblAnswer, lblStatus;
private JButton btnNext, btnShowAnswer, btnSubmit;
private int currentQ = 1;
private ExamSystem system = new ExamSystem();
private int examId;
public ExamFrame(int studentId) {
system.generateExamPaper(studentId);
this.examId = system.getLastExamId(studentId); // 需實現
initUI();
loadQuestion(currentQ);
}
private void loadQuestion(int qNum) {
Question q = system.getQuestion(examId, qNum);
lblQuestion.setText("Q" + qNum + ": " + q.getContent() + "
" +
"A. " + q.getOptA() + "
B. " + q.getOptB() +
"
C. " + q.getOptC() + "
D. " + q.getOptD() + "");
lblAnswer.setVisible(false);
}
// btnNext: currentQ++; if <=10 loadQuestion; else finishExam()
// btnShowAnswer: lblAnswer.setText("正確答案: " + q.getCorrectAnswer()); lblAnswer.setVisible(true);
// btnSubmit: system.finishExam(examId); show score dialog; dispose();
}