Exam Management System Database and Java Code Example

{ 'title': 'Exam Management System Database and Java Code Example', 'description': 'This code implements an Exam Management System with database schema and Java code. Students can register, login, take exams, and view scores. Teachers can manage questions and update the database.', 'keywords': 'exam management system, database, java, sql, students, teachers, questions, answers, results, scores', 'content': '```sql 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'; select *from students; 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); select *from students;

```java
package exam;

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

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

        switch (langChoice) {
            case 1:
                System.out.println('欢迎使用考试管理系统！');
                System.out.println('请选择您是学生还是老师：');
                System.out.println('1. 学生');
                System.out.println('2. 老师');
                int choice = scanner.nextInt();

                switch (choice) {
                    case 1:
                        System.out.println('请选择您要进行的操作：');
                        System.out.println('1. 学生注册');
                        System.out.println('2. 学生登录');
                        System.out.println('3. 返回主页面');
                        int studentChoice = scanner.nextInt();

                        switch (studentChoice) {
                            case 1:
                                studentRegistration();
                                break;
                            case 2:
                                studentLogin();
                                break;
                            case 3:
                                System.out.println('返回主页面');
                                break;
                            default:
                                System.out.println('无效的选项！');
                                break;
                        }
                        break;
                    case 2:
                        System.out.println('请输入姓名：');
                        String teacherName = scanner.nextLine();
                        System.out.println('请输入密码：');
                        String teacherPassword = scanner.nextLine();
                        System.out.println('是否登录？');
                        System.out.println('1. 是');
                        System.out.println('2. 不是');
                        int teacherLoginChoice = scanner.nextInt();

                        switch (teacherLoginChoice) {
                            case 1:
                                teacherLogin();
                                break;
                            case 2:
                                System.out.println('返回主页面');
                                break;
                            default:
                                System.out.println('无效的选项！');
                                break;
                        }
                        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();

                switch (Choice) {
                    case 1:
                        System.out.println('Please choose your option:');
                        System.out.println('1. Student Registration');
                        System.out.println('2. Student Login');
                        System.out.println('3. Return to Main Menu');
                        int studentChoice = scanner.nextInt();

                        switch (studentChoice) {
                            case 1:
                                studentRegistration();
                                break;
                            case 2:
                                studentLogin();
                                break;
                            case 3:
                                System.out.println('Returning to Main Menu');
                                break;
                            default:
                                System.out.println('Invalid option!');
                                break;
                        }
                        break;
                    case 2:
                        System.out.println('Please enter your name:');
                        String teacherName = scanner.nextLine();
                        System.out.println('Please enter your password:');
                        String teacherPassword = scanner.nextLine();
                        System.out.println('Do you want to login?');
                        System.out.println('1. Yes');
                        System.out.println('2. No');
                        int teacherLoginChoice = scanner.nextInt();

                        switch (teacherLoginChoice) {
                            case 1:
                                teacherLogin();
                                break;
                            case 2:
                                System.out.println('Returning to Main Menu');
                                break;
                            default:
                                System.out.println('Invalid option!');
                                break;
                        }
                        break;
                    default:
                        System.out.println('Invalid option!');
                        break;
                }
                break;
            default:
                System.out.println('Invalid option!');
                break;
        }
    }
    
    // 学生注册
    public static void studentRegistration() {
        Scanner scanner = new Scanner(System.in);
        System.out.println('请输入学号：');
        int studentId = scanner.nextInt();
        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();
        System.out.println('请输入密码：');
        String password = scanner.next();
        
        // 查询数据库验证学生登录信息
        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('登录成功！');
                System.out.println('请选择您要进行的操作：');
                System.out.println('1. 开始考试');
                System.out.println('2. 查看成绩');
                System.out.println('3. 返回主页面');
                int choice = scanner.nextInt();
 
                switch (choice) {
                    case 1:
                        studentStartExam();
                        break;
                    case 2:
                        // 查看成绩页面逻辑
                        break;
                    case 3:
                        System.out.println('返回主页面');
                        break;
                    default:
                        System.out.println('无效的选项！');
                        break;
                }

            } else {
                System.out.println('学号或密码错误，请重新登录！');
                studentLogin(); // 返回登录页面
            }
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println('登录失败，请重新尝试！');
        }
    }
    
    // 学生开始考试页面
    public static void studentStartExam() {
        Scanner scanner = new Scanner(System.in);
        System.out.println('请输入您的学号：');
        int studentId = scanner.nextInt();
        System.out.println('请选择您要考试的学科：');
        System.out.println('1. Mathematics');
        System.out.println('2. English');
        System.out.println('3. Science');
        int subjectChoice = scanner.nextInt();
        
        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.next();
                    
                    if (studentAnswer.equalsIgnoreCase(answer)) {
                        score += 10;
                        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();
                
                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('请选择您要进行的操作：');
        System.out.println('1. 查看问题');
        System.out.println('2. 添加问题');
        System.out.println('3. 删除问题');
        System.out.println('4. 更新问题');
        System.out.println('5. 返回主页面');
        int choice = scanner.nextInt();

        switch (choice) {
            case 1:
                // 查看问题页面逻辑
                break;
            case 2:
                // 添加问题页面逻辑
                break;
            case 3:
                // 删除问题页面逻辑
                break;
            case 4:
                // 更新问题页面逻辑
                break;
            case 5:
                System.out.println('返回主页面');
                break;
            default:
                System.out.println('无效的选项！');
                break;
        }
    }
}

This code demonstrates a basic structure for an Exam Management System. It includes database creation, user management, student registration and login, exam taking, and score saving. To make this code functional, you need to create a DatabaseConnector class that handles establishing a connection to the database and executing SQL statements. Remember to replace placeholders like // 查看成绩页面逻辑 with actual code for the respective functionalities.