Exam Management System Database and Java Code Implementation

CREATE DATABASE exam;
USE exam;

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    password VARCHAR(50)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE questions (
    question_id INT PRIMARY KEY,
    course_id INT,
    question_text VARCHAR(500),
    option_a VARCHAR(100),
    option_b VARCHAR(100),
    option_c VARCHAR(100),
    option_d VARCHAR(100),
    correct_answer VARCHAR(1),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CREATE TABLE exam_results (
    student_id INT,
    course_id INT,
    score INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CREATE TABLE teachers (
    teacher_id INT PRIMARY KEY,
    name VARCHAR(50),
    password VARCHAR(50)
);

CREATE TABLE teacher_courses (
    teacher_id INT,
    course_id INT,
    PRIMARY KEY (teacher_id, course_id),
    FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);


CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON exam.* TO 'new_user'@'localhost';

INSERT INTO courses (course_id, course_name) VALUES (1, 'Mathematics');
INSERT INTO courses (course_id, course_name) VALUES (2, 'English');
INSERT INTO courses (course_id, course_name) VALUES (3, 'Science');

INSERT INTO questions (question_id, course_id, question_text, option_a, option_b, option_c, option_d, correct_answer) VALUES (1, 1, 'What is 2+2?', '3', '4', '5', '6', 'B');
INSERT INTO questions (question_id, course_id, question_text, option_a, option_b, option_c, option_d, correct_answer) VALUES (2, 1, 'What is the square root of 16?', '2', '4', '6', '8', 'B');
INSERT INTO questions (question_id, course_id, question_text, option_a, option_b, option_c, option_d, correct_answer) VALUES (3, 2, 'What is the opposite of 'good'?', 'Bad', 'Nice', 'Happy', 'Sad', 'A');
INSERT INTO questions (question_id, course_id, question_text, option_a, option_b, option_c, option_d, correct_answer) VALUES (4, 2, 'What is the past tense of 'eat'?', 'Eating', 'Ate', 'Eaten', 'Eats', 'B');
INSERT INTO questions (question_id, course_id, question_text, option_a, option_b, option_c, option_d, correct_answer) VALUES (5, 3, 'What is the boiling point of water?', '50°C', '75°C', '100°C', '125°C', 'C');
INSERT INTO questions (question_id, course_id, question_text, option_a, option_b, option_c, option_d, correct_answer) VALUES (6, 3, 'What is the largest organ in the human body?', 'Heart', 'Liver', 'Lungs', 'Skin', 'D');

-- INSERT INTO students (student_id, name,password) VALUES (101, 'John','John123');
-- INSERT INTO students (student_id, name,password) VALUES (102, 'Jane','Jane123');
-- INSERT INTO students (student_id, name,password) VALUES (103, 'Tom','Tom123');
-- INSERT INTO students (student_id, name,password) VALUES (104, 'Mary','Mary123');
-- INSERT INTO students (student_id, name,password) VALUES (105, 'Peter','Peter123');
-- INSERT INTO students (student_id,name,password) VALUES (106, 'Lucy','Lucy123');

-- INSERT INTO exam_results (student_id, course_id, score) VALUES (101, 1, 80);
-- INSERT INTO exam_results (student_id, course_id, score) VALUES (102, 1, 90);
-- INSERT INTO exam_results (student_id, course_id, score) VALUES (103, 2, 75);
-- INSERT INTO exam_results (student_id, course_id, score) VALUES (104, 2, 85);
-- INSERT INTO exam_results (student_id, course_id, score) VALUES (105, 3, 70);
-- INSERT INTO exam_results (student_id, course_id, score) VALUES (106, 3, 80);

-- INSERT INTO teachers (teacher_id, name, password) VALUES (2, 'John Doe', 'password123');
-- INSERT INTO teacher_courses (teacher_id, course_id) VALUES (1, 1);

package exam;

import java.util.Scanner;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ExamManagementSystem {

    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.println('Select Language:');
        System.out.println('1. 中文');
        System.out.println('2. English');
        int langChoice = scanner.nextInt();
        scanner.nextLine(); // Consume newline character
        
        switch (langChoice) {
            case 1:
                System.out.println('欢迎使用考试管理系统！');
                System.out.println('请选择您是学生还是老师：');
                System.out.println('1. 学生');
                System.out.println('2. 老师');
                int choice = scanner.nextInt();
                scanner.nextLine(); // Consume newline character

                switch (choice) {
                    case 1:
                        studentMenu();
                        break;
                    case 2:
                        teacherMenu();
                        break;
                    default:
                        System.out.println('无效的选项！');
                        break;
                }
                break;
            case 2:
                System.out.println('Welcome to Exam Management System!');
                System.out.println('Please choose if you are a student or a teacher:');
                System.out.println('1. Student');
                System.out.println('2. Teacher');
                int Choice = scanner.nextInt();
                scanner.nextLine(); // Consume newline character

                switch (Choice) {
                    case 1:
                        studentMenu();
                        break;
                    case 2:
                        teacherMenu();
                        break;
                    default:
                        System.out.println('Invalid option!');
                        break;
                }
                break;
            default:
                System.out.println('Invalid option!');
                break;
        }
    }
    
    private static void studentMenu() {
        Scanner scanner = new Scanner(System.in);
        System.out.println('请选择您要进行的操作：');
        System.out.println('1. 学生注册');
        System.out.println('2. 学生登录');
        System.out.println('3. 返回主页面');
        int studentChoice = scanner.nextInt();
        scanner.nextLine(); // Consume newline character

        switch (studentChoice) {
            case 1:
                studentRegistration();
                break;
            case 2:
                studentLogin();
                break;
            case 3:
                System.out.println('返回主页面');
                break;
            default:
                System.out.println('无效的选项！');
                break;
        }
    }

    private static void teacherMenu() {
        Scanner scanner = new Scanner(System.in);
        System.out.println('请选择您要进行的操作：');
        System.out.println('1. 查看问题');
        System.out.println('2. 添加问题');
        System.out.println('3. 删除问题');
        System.out.println('4. 更新问题');
        System.out.println('5. 返回主页面');
        int choice = scanner.nextInt();
        scanner.nextLine(); // Consume newline character

        switch (choice) {
            case 1:
                viewQuestions();
                break;
            case 2:
                addQuestion();
                break;
            case 3:
                deleteQuestion();
                break;
            case 4:
                updateQuestion();
                break;
            case 5:
                System.out.println('返回主页面');
                break;
            default:
                System.out.println('无效的选项！');
                break;
        }
    }
    
    // 学生注册
    public static void studentRegistration() {
        Scanner scanner = new Scanner(System.in);
        System.out.println('请输入学号：');
        int studentId = scanner.nextInt();
        scanner.nextLine(); // Consume newline character
        System.out.println('请输入姓名：');
        String name = scanner.nextLine();
        System.out.println('请输入密码：');
        String password = scanner.nextLine();
        System.out.println('请再次输入密码：');
        String confirmPassword = scanner.nextLine();
        
        if (!password.equals(confirmPassword)) {
            System.out.println('两次输入的密码不一致，请重新注册！');
            return;
        }
        
        // 将学生信息插入到数据库中
        try (Connection conn = DatabaseConnector.getConnection()) {
            String sql = 'INSERT INTO students (student_id, name, password) VALUES (?, ?, ?)';
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setInt(1, studentId);
            statement.setString(2, name);
            statement.setString(3, password);
            statement.executeUpdate();
            System.out.println('注册成功！');
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println('注册失败，请重新尝试！');
        }
    }
    
    // 学生登录
    public static void studentLogin() {
        Scanner scanner = new Scanner(System.in);
        System.out.println('请输入学号：');
        int studentId = scanner.nextInt();
        scanner.nextLine(); // Consume newline character
        System.out.println('请输入密码：');
        String password = scanner.nextLine();
        
        // 查询数据库验证学生登录信息
        try (Connection conn = DatabaseConnector.getConnection()) {
            String sql = 'SELECT * FROM students WHERE student_id = ? AND password = ?';
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setInt(1, studentId);
            statement.setString(2, password);
            ResultSet result = statement.executeQuery();
            if (result.next()) {
                System.out.println('登录成功！');
                studentPostLoginMenu();
            } else {
                System.out.println('学号或密码错误，请重新登录！');
                studentLogin(); // 返回登录页面
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println('登录失败，请重新尝试！');
        }
    }
    
    private static void studentPostLoginMenu() {
        Scanner scanner = new Scanner(System.in);
        System.out.println('请选择您要进行的操作：');
        System.out.println('1. 开始考试');
        System.out.println('2. 查看成绩');
        System.out.println('3. 返回主页面');
        int choice = scanner.nextInt();
        scanner.nextLine(); // Consume newline character

        switch (choice) {
            case 1:
                studentStartExam();
                break;
            case 2:
                viewStudentResults();
                break;
            case 3:
                System.out.println('返回主页面');
                break;
            default:
                System.out.println('无效的选项！');
                break;
        }
    }
    
    // 学生开始考试页面
    public static void studentStartExam() {
        Scanner scanner = new Scanner(System.in);
        System.out.println('请输入您的学号：');
        int studentId = scanner.nextInt();
        scanner.nextLine(); // Consume newline character
        System.out.println('请选择您要考试的学科：');
        System.out.println('1. Mathematics');
        System.out.println('2. English');
        System.out.println('3. Science');
        int subjectChoice = scanner.nextInt();
        scanner.nextLine(); // Consume newline character
        
        String subject = '';
        int totalQuestions = 0;
        switch (subjectChoice) {
            case 1:
                subject = 'Mathematics';
                totalQuestions = 10;
                break;
            case 2:
                subject = 'English';
                totalQuestions = 10;
                break;
            case 3:
                subject = 'Science';
                totalQuestions = 10;
                break;
            default:
                System.out.println('无效的选项！');
                return;
        }
        
        int score = 0;
        for (int i = 1; i <= totalQuestions; i++) {
            System.out.println('问题 ' + i + '：');
            // 从数据库中获取题目
            try (Connection conn = DatabaseConnector.getConnection()) {
                String sql = 'SELECT * FROM questions WHERE course_id = ? ORDER BY RAND() LIMIT 1';
                PreparedStatement statement = conn.prepareStatement(sql);
                statement.setInt(1, subjectChoice);
                ResultSet result = statement.executeQuery();
                if (result.next()) {
                    String question = result.getString('question_text');
                    String optionA = result.getString('option_a');
                    String optionB = result.getString('option_b');
                    String optionC = result.getString('option_c');
                    String optionD = result.getString('option_d');
                    String answer = result.getString('correct_answer');
                    
                    System.out.println(question);
                    System.out.println('A. ' + optionA);
                    System.out.println('B. ' + optionB);
                    System.out.println('C. ' + optionC);
                    System.out.println('D. ' + optionD);
                    System.out.println('请选择您的答案：');
                    String studentAnswer = scanner.nextLine();
                    
                    if (studentAnswer.equalsIgnoreCase(answer)) {
                        score += 10;
                        System.out.println('回答正确！');
                    } else {
                        System.out.println('回答错误！');
                    }
                } else {
                    System.out.println('获取题目失败！');
                }
            } catch (SQLException e) {
                e.printStackTrace();
                System.out.println('获取题目失败！');
            }
            
            if (i < totalQuestions) {
                System.out.println('请选择您要进行的操作：');
                System.out.println('1. 下一题');
                System.out.println('2. 完成答题');
                int answerChoice = scanner.nextInt();
                scanner.nextLine(); // Consume newline character
                
                if (answerChoice == 2) {
                    break;
                }
            }
        }
        
        System.out.println('您的得分是：' + score);
        
        // 将学生的分数保存到数据库中
        try (Connection conn = DatabaseConnector.getConnection()) {
            String sql = 'INSERT INTO exam_results (student_id, course_id, score) VALUES (?, ?, ?)';
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setInt(1, studentId);
            statement.setInt(2, subjectChoice);
            statement.setInt(3, score);
            statement.executeUpdate();
            System.out.println('分数已保存！');
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println('保存分数失败！');
        }
    }
    
    // 教师登录
    public static void teacherLogin() {
        Scanner scanner = new Scanner(System.in);
        System.out.println('请输入您的姓名：');
        String teacherName = scanner.nextLine();
        System.out.println('请输入密码：');
        String teacherPassword = scanner.nextLine();
        
        // 查询数据库验证老师登录信息
        try (Connection conn = DatabaseConnector.getConnection()) {
            String sql = 'SELECT * FROM teachers WHERE name = ? AND password = ?';
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setString(1, teacherName);
            statement.setString(2, teacherPassword);
            ResultSet result = statement.executeQuery();
            if (result.next()) {
                System.out.println('登录成功！');
                teacherPostLoginMenu();
            } else {
                System.out.println('用户名或密码错误，请重新登录！');
                teacherLogin(); // 返回登录页面
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println('登录失败，请重新尝试！');
        }
    }
    
    private static void teacherPostLoginMenu() {
        Scanner scanner = new Scanner(System.in);
        System.out.println('请选择您要进行的操作：');
        System.out.println('1. 查看问题');
        System.out.println('2. 添加问题');
        System.out.println('3. 删除问题');
        System.out.println('4. 更新问题');
        System.out.println('5. 返回主页面');
        int choice = scanner.nextInt();
        scanner.nextLine(); // Consume newline character

        switch (choice) {
            case 1:
                viewQuestions();
                break;
            case 2:
                addQuestion();
                break;
            case 3:
                deleteQuestion();
                break;
            case 4:
                updateQuestion();
                break;
            case 5:
                System.out.println('返回主页面');
                break;
            default:
                System.out.println('无效的选项！');
                break;
        }
    }
    
    // 教师查看问题
    private static void viewQuestions() {
        Scanner scanner = new Scanner(System.in);
        System.out.println('请选择您要查看的科目：');
        System.out.println('1. Mathematics');
        System.out.println('2. English');
        System.out.println('3. Science');
        int subjectChoice = scanner.nextInt();
        scanner.nextLine(); // Consume newline character

        try (Connection conn = DatabaseConnector.getConnection()) {
            String sql = 'SELECT * FROM questions WHERE course_id = ?';
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setInt(1, subjectChoice);
            ResultSet result = statement.executeQuery();

            if (result.next()) {
                do {
                    System.out.println('问题 ID：' + result.getInt('question_id'));
                    System.out.println('题目：' + result.getString('question_text'));
                    System.out.println('选项 A：' + result.getString('option_a'));
                    System.out.println('选项 B：' + result.getString('option_b'));
                    System.out.println('选项 C：' + result.getString('option_c'));
                    System.out.println('选项 D：' + result.getString('option_d'));
                    System.out.println('正确答案：' + result.getString('correct_answer'));
                    System.out.println('-----------------------');
                } while (result.next());
            } else {
                System.out.println('该科目没有问题。');
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println('查询问题失败！');
        }
    }
    
    // 教师添加问题
    private static void addQuestion() {
        Scanner scanner = new Scanner(System.in);
        System.out.println('请输入问题文本：');
        String questionText = scanner.nextLine();
        System.out.println('请输入选项 A：');
        String optionA = scanner.nextLine();
        System.out.println('请输入选项 B：');
        String optionB = scanner.nextLine();
        System.out.println('请输入选项 C：');
        String optionC = scanner.nextLine();
        System.out.println('请输入选项 D：');
        String optionD = scanner.nextLine();
        System.out.println('请输入正确答案（A/B/C/D）：');
        String correctAnswer = scanner.nextLine();
        System.out.println('请选择问题所属的科目：');
        System.out.println('1. Mathematics');
        System.out.println('2. English');
        System.out.println('3. Science');
        int subjectChoice = scanner.nextInt();
        scanner.nextLine(); // Consume newline character

        try (Connection conn = DatabaseConnector.getConnection()) {
            String sql = 'INSERT INTO questions (course_id, question_text, option_a, option_b, option_c, option_d, correct_answer) VALUES (?, ?, ?, ?, ?, ?, ?)';
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setInt(1, subjectChoice);
            statement.setString(2, questionText);
            statement.setString(3, optionA);
            statement.setString(4, optionB);
            statement.setString(5, optionC);
            statement.setString(6, optionD);
            statement.setString(7, correctAnswer);
            statement.executeUpdate();
            System.out.println('问题添加成功！');
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println('添加问题失败！');
        }
    }
    
    // 教师删除问题
    private static void deleteQuestion() {
        Scanner scanner = new Scanner(System.in);
        System.out.println('请输入要删除问题的 ID：');
        int questionId = scanner.nextInt();
        scanner.nextLine(); // Consume newline character

        try (Connection conn = DatabaseConnector.getConnection()) {
            String sql = 'DELETE FROM questions WHERE question_id = ?';
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setInt(1, questionId);
            int rowsAffected = statement.executeUpdate();

            if (rowsAffected > 0) {
                System.out.println('问题删除成功！');
            } else {
                System.out.println('没有找到该问题。');
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println('删除问题失败！');
        }
    }
    
    // 教师更新问题
    private static void updateQuestion() {
        Scanner scanner = new Scanner(System.in);
        System.out.println('请输入要更新问题的 ID：');
        int questionId = scanner.nextInt();
        scanner.nextLine(); // Consume newline character

        try (Connection conn = DatabaseConnector.getConnection()) {
            String sql = 'SELECT * FROM questions WHERE question_id = ?';
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setInt(1, questionId);
            ResultSet result = statement.executeQuery();

            if (result.next()) {
                System.out.println('当前问题：');
                System.out.println('问题文本：' + result.getString('question_text'));
                System.out.println('选项 A：' + result.getString('option_a'));
                System.out.println('选项 B：' + result.getString('option_b'));
                System.out.println('选项 C：' + result.getString('option_c'));
                System.out.println('选项 D：' + result.getString('option_d'));
                System.out.println('正确答案：' + result.getString('correct_answer'));
                System.out.println('-----------------------');

                System.out.println('请输入新的问题文本（留空则不修改）：');
                String newQuestionText = scanner.nextLine();
                System.out.println('请输入新的选项 A（留空则不修改）：');
                String newOptionA = scanner.nextLine();
                System.out.println('请输入新的选项 B（留空则不修改）：');
                String newOptionB = scanner.nextLine();
                System.out.println('请输入新的选项 C（留空则不修改）：');
                String newOptionC = scanner.nextLine();
                System.out.println('请输入新的选项 D（留空则不修改）：');
                String newOptionD = scanner.nextLine();
                System.out.println('请输入新的正确答案（留空则不修改）：');
                String newCorrectAnswer = scanner.nextLine();

                sql = 'UPDATE questions SET question_text = ?, option_a = ?, option_b = ?, option_c = ?, option_d = ?, correct_answer = ? WHERE question_id = ?';
                statement = conn.prepareStatement(sql);
                statement.setString(1, newQuestionText.isEmpty() ? result.getString('question_text') : newQuestionText);
                statement.setString(2, newOptionA.isEmpty() ? result.getString('option_a') : newOptionA);
                statement.setString(3, newOptionB.isEmpty() ? result.getString('option_b') : newOptionB);
                statement.setString(4, newOptionC.isEmpty() ? result.getString('option_c') : newOptionC);
                statement.setString(5, newOptionD.isEmpty() ? result.getString('option_d') : newOptionD);
                statement.setString(6, newCorrectAnswer.isEmpty() ? result.getString('correct_answer') : newCorrectAnswer);
                statement.setInt(7, questionId);
                statement.executeUpdate();

                System.out.println('问题更新成功！');
            } else {
                System.out.println('没有找到该问题。');
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println('更新问题失败！');
        }
    }
    
    private static void viewStudentResults() {
        Scanner scanner = new Scanner(System.in);
        System.out.println('请输入您的学号：');
        int studentId = scanner.nextInt();
        scanner.nextLine(); // Consume newline character

        try (Connection conn = DatabaseConnector.getConnection()) {
            String sql = 'SELECT c.course_name, r.score FROM exam_results r JOIN courses c ON r.course_id = c.course_id WHERE r.student_id = ?';
            PreparedStatement statement = conn.prepareStatement(sql);
            statement.setInt(1, studentId);
            ResultSet result = statement.executeQuery();

            if (result.next()) {
                do {
                    System.out.println('科目：' + result.getString('course_name'));
                    System.out.println('分数：' + result.getInt('score'));
                    System.out.println('-----------------------');
                } while (result.next());
            } else {
                System.out.println('您还没有参加过考试。');
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println('查询成绩失败！');
        }
    }
}

package exam;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DatabaseConnector {

    private static final String DB_URL = 'jdbc:mysql://localhost:3306/exam';
    private static final String DB_USER = 'new_user';
    private static final String DB_PASSWORD = 'password';

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
    }
}