使用数据库改造学生管理系统

1.StudentDaoJDBCImpl

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;


/**
 * 该实现类运行需要MySql数据库test和表students
 * CREATE TABLE `students` (
  `id` int NOT NULL AUTO_INCREMENT,
  `stuno` char(10) NOT NULL,
  `name` char(20) NOT NULL,
  `gender` char(1) NOT NULL,
  `age` int DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `major` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
)
	
 * @author zhrb
 *
 */
public class StudentDaoJDBCImpl implements StudentDao {

	@Override
	public boolean writeStudent(Student student) {
		String sql = "insert into students(stuno,name,gender,age,major,gpa) values(?,?,?,?,?,?) ";//表中有id和name这列
		int result = -1;
		
		try(Connection conn = JDBCUtil.getConnection();
			PreparedStatement pstat = conn.prepareStatement(sql);) {
			pstat.setString(1, new Random().nextInt(100000)+"");
			pstat.setString(2, student.getName());
			pstat.setString(3, student.getGender());
			pstat.setInt(4, student.getAge());
			pstat.setString(5, student.getMajor());
			pstat.setDouble(6,student.getGpa());
			result = pstat.executeUpdate();
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
		return result>0?true:false;
	}

	public boolean deleteStudent(Student student){
		String sql = "delete from students where id=?";//表中有id和name这列
		int result = -1;

		try(Connection conn = JDBCUtil.getConnection();
			PreparedStatement pstat = conn.prepareStatement(sql);) {
			pstat.setString(1, student.getId());

			result = pstat.executeUpdate();
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
		return result>0?true:false;
	}





	@Override
	public List<Student> getStudentsByName(String name) {
		String sql = "select * from students where name like ?";
		List<Student> studentList = new ArrayList<>();
		try(Connection conn = JDBCUtil.getConnection();
			PreparedStatement pstat = conn.prepareStatement(sql);) {
			pstat.setString(1, name);
			ResultSet rs = pstat.executeQuery();
			while(rs.next()){
				Student op=new Student(rs.getString("name"),rs.getInt("age"),rs.getString("gender"),String.valueOf(rs.getInt("id")),rs.getString("major"),rs.getDouble("gpa"));

				studentList.add(op);
			}
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
		return studentList;
	}

	public List<Student> getStudentsByMajor(String major) {
		String sql = "select * from students where major like ?";
		List<Student> studentList = new ArrayList<>();
		try(Connection conn = JDBCUtil.getConnection();
			PreparedStatement pstat = conn.prepareStatement(sql);) {
			pstat.setString(1, major);
			ResultSet rs = pstat.executeQuery();
			while(rs.next()){
				Student op=new Student(rs.getString("name"),rs.getInt("age"),rs.getString("gender"),String.valueOf(rs.getInt("id")),rs.getString("major"),rs.getDouble("gpa"));



				studentList.add(op);
			}
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
		return studentList;
	}

	public List<Student> getStudentsByGpa(double gpa) {
		String sql = "select * from students where gpa = ?";
		List<Student> studentList = new ArrayList<>();
		try(Connection conn = JDBCUtil.getConnection();
			PreparedStatement pstat = conn.prepareStatement(sql);) {
			pstat.setFloat(1, (float) gpa);
			ResultSet rs = pstat.executeQuery();
			while(rs.next()){
				Student op=new Student(rs.getString("name"),rs.getInt("age"),rs.getString("gender"),String.valueOf(rs.getInt("id")),rs.getString("major"),rs.getDouble("gpa"));

				studentList.add(op);
			}
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}

		return studentList;
	}


	@Override
	public void diplayAllStudent() {
		String sql = "select * from students";//表中有id和name这列
		try(Connection conn = JDBCUtil.getConnection();
			PreparedStatement pstat = conn.prepareStatement(sql);) {
			ResultSet rs = pstat.executeQuery(sql);
			while(rs.next()){
				Student op=new Student(rs.getString("name"),rs.getInt("age"),rs.getString("gender"),String.valueOf(rs.getInt("id")),rs.getString("major"),rs.getDouble("gpa"));

				System.out.format(op.toString());
				System.out.println();
			}
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
	}

	/**
	 * 还未实现
	 */
	@Override
	public List<Student> getAllStudents() {
		String sql = "select * from students";
		List<Student> s=new ArrayList<>();
		try(Connection conn = JDBCUtil.getConnection();
			PreparedStatement pstat = conn.prepareStatement(sql);) {
			ResultSet rs = pstat.executeQuery(sql);
			while(rs.next()){
				Student op=new Student(rs.getString("name"),rs.getInt("age"),rs.getString("gender"),String.valueOf(rs.getInt("id")),rs.getString("major"),rs.getDouble("gpa"));
				s.add(op);
			}
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}

		return s;


	}
	

}

2.JDBCUtil

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

public class JDBCUtil {

	//private static final String driverName = "com.mysql.cj.jdbc.Driver";// jdbc4.0以后不需要
	private static final String url = "jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Chongqing";
	private static final String userName = "root";
	private static final String password = "1234";

	/*public static void registerDriver() { //JDBC 4.0以后不需要调用该方法
		try {
			Class.forName(driverName);// JDBC 4.0以前需要这句进行驱动注册
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.out.println("找不到驱动");
		}
	}*/

	public static Connection getConnection() throws SQLException {
		Connection con = null;
		con = DriverManager.getConnection(url, userName, password);
		return con;
	}

	/**
	 * 有时仅需要关闭Connection，就可以调用此方法
	 * @param con
	 */
	public static void closeConnection(Connection con) {
		if (con != null) {
			try {
				con.close();
				con = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	public static void realeaseAll(ResultSet rs, Statement st, Connection con) {
		if (rs != null) {
			try {
				rs.close();
				rs = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (st != null) {
			try {
				st.close();
				st = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		closeConnection(con);
	}
}

3.Main

import java.util.List;
import java.util.Scanner;

public class Main {
	public static void main(String[] args) {

		/**
		 * This program is a student management system that allows users to add, remove,
		 * and search for students by name, major, and GPA. It uses the Student and
		 * StudentManagementSystem classes to store and manage student data.
		 */
		// Initialize the scanner
		Scanner scanner = new Scanner(System.in);
		StudentManagementSystem sms = new StudentManagementSystem();
		boolean running = true;
		while (running) {
			// Prompt user for input
			System.out.println("Enter 1 to add a student");
			System.out.println("Enter 2 to remove a student");
			System.out.println("Enter 3 to search for a student by name");
			System.out.println("Enter 4 to search for a student by major");
			System.out.println("Enter 5 to search for a student by GPA");
			System.out.println("Enter 6 to show all students");
			System.out.println("Enter 7 to exit");

			// Get user input
			int choice = scanner.nextInt();

			// Process user input
			switch (choice) {
			case 1:
				// Add a student
				System.out.println("Enter student name:");
				String name = scanner.next();
				System.out.println("Enter student age:");
				int age = scanner.nextInt();
				System.out.println("Enter student gender:");
				String gender = scanner.next();
				System.out.println("Enter student ID:");
				String id = scanner.next();
				System.out.println("Enter student major:");
				String major = scanner.next();
				System.out.println("Enter student GPA:");
				double gpa = scanner.nextDouble();
				Student student = new Student(name, age, gender, id, major, gpa);
				sms.addStudent(student);
				System.out.println("Student added successfully!\n");
				break;
			case 2:
				// Remove a student
				System.out.println("Enter student ID to remove:");
				String removeId = scanner.next();
				List<Student> students = sms.getStudents();
				boolean removed = false;
				for (Student s : students) {
					if (s.getId().equals(removeId)) {
						sms.removeStudent(s);
						removed = true;
						System.out.println("Student removed successfully!\n");
						break;
					}
				}
				if (!removed) {
					System.out.println("Student not found!");
				}
				break;
			case 3:
				// Search for a student by name
				System.out.println("Enter student name to search:");
				String searchName = scanner.next();
				List<Student> searchResults = sms.searchByName(searchName);
				if (searchResults.isEmpty()) {
					System.out.println("No students found!");
				} else {
					System.out.println("Search results:");
					for (Student s : searchResults) {
						System.out.println(s);
					}
				}
				break;
			case 4:
				// Search for a student by major
				System.out.println("Enter student major to search:");
				String searchMajor = scanner.next();
				searchResults = sms.searchByMajor(searchMajor);
				if (searchResults.isEmpty()) {
					System.out.println("No students found!");
				} else {
					System.out.println("Search results:");
					for (Student s : searchResults) {
						System.out.println(s);
					}
				}
				break;
			case 5:
				// Search for a student by GPA
				System.out.println("Enter student GPA to search:");
				double searchGpa = scanner.nextDouble();
				searchResults = sms.searchByGpa(searchGpa);
				if (searchResults.isEmpty()) {
					System.out.println("No students found!");
				} else {
					System.out.println("Search results:");
					for (Student s : searchResults) {
						System.out.println(s);
					}
				}
				break;
			case 6:
				// Show all Students
				List<Student> studentList = sms.getStudents();
				if (studentList.size() == 0) {
					System.out.println("The System Data is empty Now!");
				}else {
					for (Student studentItem : studentList) {
						System.out.println(studentItem.toString());
					}
				}
				break;
			case 7:
				// Exit the program
				running = false;
				System.out.println("Exit Successfully!");
				break;
			default:
				// Invalid input
				System.out.println("Invalid choice!");
				break;
			}
		}
		// Close scanner
		scanner.close();
	}
}

4.Student

public class Student {
    private String name;
    private int age;
    private String gender;
    private String id;
    private String major;
    private double gpa;

    public Student(String name, int age, String gender, String id, String major, double gpa) {
        this.name = name;
        this.age = age;
        this.gender = gender;
        this.id = id;
        this.major = major;
        this.gpa = gpa;
    }

    public Student() {

    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getMajor() {
        return major;
    }

    public void setMajor(String major) {
        this.major = major;
    }

    public double getGpa() {
        return gpa;
    }

    public void setGpa(double gpa) {
        this.gpa = gpa;
    }

    @Override
    public String toString() {
        return "Student{" +
                "name='" + name + '\'' +
                ", age=" + age +
                ", gender='" + gender + '\'' +
                ", id='" + id + '\'' +
                ", major='" + major + '\'' +
                ", gpa=" + gpa +
                '}';
    }
}

5.StudentDao

import java.util.List;


public interface StudentDao {
	public boolean writeStudent(Student student);
	public List<Student> getStudentsByName(String name);
	public List<Student> getAllStudents();
	public void diplayAllStudent();
}

6.StudentManagementSystem

import java.util.ArrayList;
import java.util.List;

public class StudentManagementSystem {

    StudentDaoJDBCImpl sdji=new StudentDaoJDBCImpl();
    public StudentManagementSystem() {

    }

    public void addStudent(Student student) {
        sdji.writeStudent(student);

    }

    public void removeStudent(Student student) {
        sdji.deleteStudent(student);
    }

    public List<Student> getStudents() {
        return sdji.getAllStudents();
    }

    public List<Student> searchByName(String name) {
            return sdji.getStudentsByName(name);
    }

    public List<Student> searchByMajor(String major) {
            return sdji.getStudentsByMajor(major);
    }

    public List<Student> searchByGpa(double gpa) {
            return sdji.getStudentsByGpa(gpa);

    }

    public void showAllStudents(){

        sdji.diplayAllStudent();


    }


}

7.运行截图

（1）添加学生

（2）删除学生

（3）查找学生

(4)展示所有学生