Exam Management System Database and Java Code
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;
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();
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_name = ? ORDER BY RAND() LIMIT 1';
PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1, subject);
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.setString(2, subject);
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:
// 查看问题页面逻辑
viewQuestions();
break;
case 2:
// 添加问题页面逻辑
break;
case 3:
// 删除问题页面逻辑
break;
case 4:
// 更新问题页面逻辑
break;
case 5:
System.out.println('返回主页面');
break;
default:
System.out.println('无效的选项！');
break;
}
}
public 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();
String subject = '';
if (subjectChoice == 1) {
subject = 'Mathematics';
} else if (subjectChoice == 2) {
subject = 'English';
} else if (subjectChoice == 3) {
subject = 'Science';
}
try (Connection conn = DatabaseConnector.getConnection()) {
String sql = 'SELECT * FROM questions';
if (!subject.equals('')) {
sql += ' WHERE course_name = ?';
}
PreparedStatement statement = conn.prepareStatement(sql);
if (!subject.equals('')) {
statement.setString(1, subject);
}
ResultSet result = statement.executeQuery();
while (result.next()) {
int questionId = result.getInt('question_id');
String courseName = result.getString('course_name');
String questionText = 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 correctAnswer = result.getString('correct_answer');
System.out.println('Question ID: ' + questionId);
System.out.println('Course Name: ' + courseName);
System.out.println('Question Text: ' + questionText);
System.out.println('Option A: ' + optionA);
System.out.println('Option B: ' + optionB);
System.out.println('Option C: ' + optionC);
System.out.println('Option D: ' + optionD);
System.out.println('Correct Answer: ' + correctAnswer);
System.out.println('');
}
} catch (SQLException e) {
e.printStackTrace();
System.out.println('Failed to retrieve questions!');
}
System.out.println('Do you want to go back to the Admin page now?');
System.out.println('1. Yes');
System.out.println('2. No');
int backChoice = scanner.nextInt();
if (backChoice == 1) {
teacherLogin();
} else {
System.out.println('Returning to Main Menu');
}
}
}
The viewQuestions() function in the Java code connects to the MySQL database to retrieve and display all questions, or only questions for a specific course. It prompts the teacher to choose a course to view, then retrieves the relevant questions from the questions table in the database.
Database Connector:
The code uses a DatabaseConnector class to connect to the database. This class should contain the necessary logic for establishing the connection, including username, password, and database name. It is not included here but should be implemented as part of the application.
Improvements:
- Error Handling: The code should be improved with more robust error handling for database operations and user input validation.
- User Interface: A more user-friendly interface with menus and input validation would enhance the application's usability.
- Question Management: Implement the functionality for adding, deleting, and updating questions in the database.
原文地址: https://www.cveoy.top/t/topic/f0Jm 著作权归作者所有。请勿转载和采集!