萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

Java + SQL Server 2005 隨機抽題考試系統:JDBC 連線池、事務控制與 Swing 界面完整實現

從題庫隨機抽取、持久化試卷到桌面答題流程的完整代碼

本文提供一個基於 **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)
);

核心 Java 類別結構

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) { ... }
}

Swing GUI 關鍵片段

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(); }

關鍵技術點總結

  1. 隨機抽樣:`ORDER BY NEWID()` (資料量大時改用 `TABLESAMPLE` 或應用層隨機 ID)
  2. 事務保證原子性:批次插入 10 題要么全成要么全滾
  3. 參數化預處理:防注入、重用執行計畫
  4. 連線池化**:生產必須替換 `DriverManager` 為 `HikariDataSource`
  5. 異常處理與資源關閉**:`try-with-resources` 確保連線/語句/結果集關閉

擴展方向

  • Web 化:Spring Boot + Thymeleaf/React + REST API
  • 題型擴展:多選、填空、判斷、編程題 (代碼運行沙箱)
  • 防作弊:題目/選項亂序、螢幕水印、瀏覽器全屏監控
  • 統計分析:正確率、區分度、難度系數 (IRT 模型)
copyright © 萬盛學電腦網 all rights reserved