jdbc；一、语言和环境A、实现语言JAVA+JDBC+DAO封装B、环境要求JDK 80、MySQL 80、IDEA2020二、功能要求使用JAVA+JDBC+DAO封装等JDBC相关技术实现一个学生信息管理系统。该系统包括查看学生的信息学生姓名、性别、生日、电话、邮箱、所在班级名称、新增学生信息、删除学生信息、修改学生信息、查询指定班级学生信息等功能。学生信息管理系统数据库的表名、字段名、字段

三、代码实现

1. StudentDao 接口

public interface StudentDao {
    // 查看所有学生信息
    List<Student> findAll();

    // 新增学生信息
    void add(Student student);

    // 修改学生信息
    void update(Student student);

    // 删除学生信息
    void delete(int id);

    // 按班级名称查询学生信息
    List<Student> findByClassName(String className);
}

2. StudentDaoImpl 类

public class StudentDaoImpl implements StudentDao {
    private Connection connection;
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;

    // 构造方法，获取数据库连接
    public StudentDaoImpl() {
        connection = JDBCUtils.getConnection();
    }

    // 查看所有学生信息
    @Override
    public List<Student> findAll() {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT s.id, s.name, s.birthday, s.gender, s.telephone, s.email, c.name AS class_name FROM student s JOIN classes c ON s.cId = c.id";
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                Date birthday = resultSet.getDate("birthday");
                String gender = resultSet.getString("gender");
                String telephone = resultSet.getString("telephone");
                String email = resultSet.getString("email");
                String className = resultSet.getString("class_name");
                Student student = new Student(id, name, birthday, gender, telephone, email, className);
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }
        return students;
    }

    // 新增学生信息
    @Override
    public void add(Student student) {
        String sql = "INSERT INTO student(name, birthday, gender, telephone, email, cId) VALUES (?, ?, ?, ?, ?, ?)";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, student.getName());
            preparedStatement.setDate(2, new java.sql.Date(student.getBirthday().getTime()));
            preparedStatement.setString(3, student.getGender());
            preparedStatement.setString(4, student.getTelephone());
            preparedStatement.setString(5, student.getEmail());
            preparedStatement.setInt(6, student.getClassId());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(preparedStatement, connection);
        }
    }

    // 修改学生信息
    @Override
    public void update(Student student) {
        String sql = "UPDATE student SET name = ?, birthday = ?, gender = ?, telephone = ?, email = ?, cId = ? WHERE id = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, student.getName());
            preparedStatement.setDate(2, new java.sql.Date(student.getBirthday().getTime()));
            preparedStatement.setString(3, student.getGender());
            preparedStatement.setString(4, student.getTelephone());
            preparedStatement.setString(5, student.getEmail());
            preparedStatement.setInt(6, student.getClassId());
            preparedStatement.setInt(7, student.getId());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(preparedStatement, connection);
        }
    }

    // 删除学生信息
    @Override
    public void delete(int id) {
        String sql = "DELETE FROM student WHERE id = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(preparedStatement, connection);
        }
    }

    // 按班级名称查询学生信息
    @Override
    public List<Student> findByClassName(String className) {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT s.id, s.name, s.birthday, s.gender, s.telephone, s.email, c.name AS class_name FROM student s JOIN classes c ON s.cId = c.id WHERE c.name = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, className);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                Date birthday = resultSet.getDate("birthday");
                String gender = resultSet.getString("gender");
                String telephone = resultSet.getString("telephone");
                String email = resultSet.getString("email");
                String cName = resultSet.getString("class_name");
                Student student = new Student(id, name, birthday, gender, telephone, email, cName);
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }
        return students;
    }
}

3. Student 类

public class Student {
    private int id; // 学生编号
    private String name; // 学生姓名
    private Date birthday; // 生日
    private String gender; // 性别
    private String telephone; // 电话
    private String email; // E-mail
    private String className; // 所在班级名称

    public Student() {}

    public Student(String name, Date birthday, String gender, String telephone, String email, String className) {
        this.name = name;
        this.birthday = birthday;
        this.gender = gender;
        this.telephone = telephone;
        this.email = email;
        this.className = className;
    }

    public Student(int id, String name, Date birthday, String gender, String telephone, String email, String className) {
        this.id = id;
        this.name = name;
        this.birthday = birthday;
        this.gender = gender;
        this.telephone = telephone;
        this.email = email;
        this.className = className;
    }

    // 省略 getter 和 setter 方法
}

4. JDBCUtils 工具类

public class JDBCUtils {
    private static final String URL = "jdbc:mysql://localhost:3306/student_management?useSSL=false&serverTimezone=UTC";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "123456";

    // 获取数据库连接
    public static Connection getConnection() {
        Connection connection = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    // 关闭数据库连接
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void close(Statement statement, Connection connection) {
        close(null, statement, connection);
    }
}

5. 测试类

public class Test {
    public static void main(String[] args) {
        StudentDao studentDao = new StudentDaoImpl();
        Scanner scanner = new Scanner(System.in);
        while (true) {
            System.out.println("请选择操作：");
            System.out.println("1. 查看所有学生信息");
            System.out.println("2. 新增学生信息");
            System.out.println("3. 修改学生信息");
            System.out.println("4. 删除学生信息");
            System.out.println("5. 查询指定班级学生信息");
            System.out.println("0. 退出程序");
            int choice = scanner.nextInt();
            switch (choice) {
                case 1:
                    List<Student> students = studentDao.findAll();
                    for (Student student : students) {
                        System.out.println(student);
                    }
                    break;
                case 2:
                    System.out.println("请输入学生信息：");
                    System.out.print("姓名：");
                    String name = scanner.next();
                    System.out.print("生日（yyyy-MM-dd）：");
                    String birthdayStr = scanner.next();
                    Date birthday = null;
                    try {
                        birthday = new SimpleDateFormat("yyyy-MM-dd").parse(birthdayStr);
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                    System.out.print("性别：");
                    String gender = scanner.next();
                    System.out.print("电话：");
                    String telephone = scanner.next();
                    System.out.print("邮箱：");
                    String email = scanner.next();
                    System.out.print("班级编号：");
                    int classId = scanner.nextInt();
                    Student newStudent = new Student(name, birthday, gender, telephone, email, null, classId);
                    studentDao.add(newStudent);
                    System.out.println("新增学生信息成功！");
                    break;
                case 3:
                    System.out.println("请输入要修改的学生编号：");
                    int id = scanner.nextInt();
                    Student oldStudent = findStudentById(id, studentDao);
                    if (oldStudent == null) {
                        System.out.println("学生编号不存在！");
                        break;
                    }
                    System.out.println("请输入修改后的学生信息：");
                    System.out.print("姓名：");
                    name = scanner.next();
                    System.out.print("生日（yyyy-MM-dd）：");
                    birthdayStr = scanner.next();
                    birthday = null;
                    try {
                        birthday = new SimpleDateFormat("yyyy-MM-dd").parse(birthdayStr);
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                    System.out.print("性别：");
                    gender = scanner.next();
                    System.out.print("电话：");
                    telephone = scanner.next();
                    System.out.print("邮箱：");
                    email = scanner.next();
                    System.out.print("班级编号：");
                    classId = scanner.nextInt();
                    Student newStu = new Student(id, name, birthday, gender, telephone, email, null, classId);
                    studentDao.update(newStu);
                    System.out.println("修改学生信息成功！");
                    break;
                case 4:
                    System.out.println("请输入要删除的学生编号：");
                    id = scanner.nextInt();
                    oldStudent = findStudentById(id, studentDao);
                    if (oldStudent == null) {
                        System.out.println("学生编号不存在！");
                        break;
                    }
                    studentDao.delete(id);
                    System.out.println("删除学生信息成功！");
                    break;
                case 5:
                    System.out.println("请输入班级名称：");
                    String className = scanner.next();
                    List<Student> studentsOfClass = studentDao.findByClassName(className);
                    for (Student student : studentsOfClass) {
                        System.out.println(student);
                    }
                    break;
                case 0:
                    System.exit(0);
                    break;
                default:
                    System.out.println("输入错误，请重新输入！");
                    break;
            }
        }
    }

    // 根据学生编号查找学生信息
    public static Student findStudentById(int id, StudentDao studentDao) {
        List<Student> students = studentDao.findAll();
        for (Student student : students) {
            if (student.getId() == id) {
                return student;
            }
        }
        return null;
    }
}
``