非常好！我们已经定义了 `DAO` 接口，并实现了部分 `DaoImpl`。现在，我将为你 **完整生成所有实体类的 DAO 实现类（DaoImpl）**，包括：
- `StudentDaoImpl.java`
- `TeacherDaoImpl.java`
- `CourseDaoImpl.java`
- `SchoolClassDaoImpl.java`
- `EnrollmentDaoImpl.java`
全部使用 JDBC + `PreparedStatement` + `DBConnection` 工具类，代码结构统一、可读性强，适合集成到学生管理系统中。
---
## ✅ 公共依赖说明
确保你有以下内容已准备就绪：
1. 数据库连接工具类：`util.DBConnection`
2. 所有实体类在 `entity` 包下
3. 使用 MySQL，数据库名为 `school_db`
4. 已创建对应的数据表（见上一节 SQL）
---
### 🔹 1. `dao/impl/StudentDaoImpl.java`
```java
package dao.impl;
import dao.StudentDao;
import entity.Student;
import util.DBConnection;
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
public class StudentDaoImpl implements StudentDao {
@Override
public void insert(Student student) throws Exception {
String sql = "INSERT INTO students (id, name, gender, birth_date, phone, email, address, enrollment_date, class_id, emergency_contact, is_active) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, student.getId());
ps.setString(2, student.getName());
ps.setString(3, student.getGender());
ps.setDate(4, Date.valueOf(student.getBirthDate()));
ps.setString(5, student.getPhone());
ps.setString(6, student.getEmail());
ps.setString(7, student.getAddress());
ps.setDate(8, Date.valueOf(student.getEnrollmentDate()));
ps.setInt(9, student.getClassId());
ps.setString(10, student.getEmergencyContact());
ps.setBoolean(11, student.isActive());
ps.executeUpdate();
}
}
@Override
public void update(Student student) throws Exception {
String sql = "UPDATE students SET name=?, gender=?, birth_date=?, phone=?, email=?, address=?, enrollment_date=?, class_id=?, emergency_contact=?, is_active=? WHERE id=?";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, student.getName());
ps.setString(2, student.getGender());
ps.setDate(3, Date.valueOf(student.getBirthDate()));
ps.setString(4, student.getPhone());
ps.setString(5, student.getEmail());
ps.setString(6, student.getAddress());
ps.setDate(7, Date.valueOf(student.getEnrollmentDate()));
ps.setInt(8, student.getClassId());
ps.setString(9, student.getEmergencyContact());
ps.setBoolean(10, student.isActive());
ps.setInt(11, student.getId());
ps.executeUpdate();
}
}
@Override
public void delete(int id) throws Exception {
String sql = "DELETE FROM students WHERE id=?";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
ps.executeUpdate();
}
}
@Override
public Student findById(int id) throws Exception {
String sql = "SELECT * FROM students WHERE id=?";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
return mapRowToStudent(rs);
}
return null;
}
}
@Override
public List<Student> findAll() throws Exception {
String sql = "SELECT * FROM students";
List<Student> students = new ArrayList<>();
try (Connection conn = DBConnection.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
students.add(mapRowToStudent(rs));
}
}
return students;
}
@Override
public List<Student> findByClassId(int classId) throws Exception {
String sql = "SELECT * FROM students WHERE class_id=?";
List<Student> students = new ArrayList<>();
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, classId);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
students.add(mapRowToStudent(rs));
}
}
return students;
}
private Student mapRowToStudent(ResultSet rs) throws SQLException {
Student s = new Student();
s.setId(rs.getInt("id"));
s.setName(rs.getString("name"));
s.setGender(rs.getString("gender"));
s.setBirthDate(rs.getDate("birth_date").toLocalDate());
s.setPhone(rs.getString("phone"));
s.setEmail(rs.getString("email"));
s.setAddress(rs.getString("address"));
s.setEnrollmentDate(rs.getDate("enrollment_date").toLocalDate());
s.setClassId(rs.getInt("class_id"));
s.setEmergencyContact(rs.getString("emergency_contact"));
s.setActive(rs.getBoolean("is_active"));
return s;
}
}
```
---
### 🔹 2. `dao/impl/TeacherDaoImpl.java`
```java
package dao.impl;
import dao.TeacherDao;
import entity.Teacher;
import util.DBConnection;
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
public class TeacherDaoImpl implements TeacherDao {
@Override
public void insert(Teacher teacher) throws Exception {
String sql = "INSERT INTO teachers (id, name, gender, birth_date, phone, email, hire_date, title, degree, department, subject_area, office_location) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, teacher.getId());
ps.setString(2, teacher.getName());
ps.setString(3, teacher.getGender());
ps.setDate(4, Date.valueOf(teacher.getBirthDate()));
ps.setString(5, teacher.getPhone());
ps.setString(6, teacher.getEmail());
ps.setDate(7, Date.valueOf(teacher.getHireDate()));
ps.setString(8, teacher.getTitle());
ps.setString(9, teacher.getDegree());
ps.setString(10, teacher.getDepartment());
ps.setString(11, teacher.getSubjectArea());
ps.setString(12, teacher.getOfficeLocation());
ps.executeUpdate();
}
}
@Override
public void update(Teacher teacher) throws Exception {
String sql = "UPDATE teachers SET name=?, gender=?, birth_date=?, phone=?, email=?, hire_date=?, title=?, degree=?, department=?, subject_area=?, office_location=? WHERE id=?";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, teacher.getName());
ps.setString(2, teacher.getGender());
ps.setDate(3, Date.valueOf(teacher.getBirthDate()));
ps.setString(4, teacher.getPhone());
ps.setString(5, teacher.getEmail());
ps.setDate(6, Date.valueOf(teacher.getHireDate()));
ps.setString(7, teacher.getTitle());
ps.setString(8, teacher.getDegree());
ps.setString(9, teacher.getDepartment());
ps.setString(10, teacher.getSubjectArea());
ps.setString(11, teacher.getOfficeLocation());
ps.setInt(12, teacher.getId());
ps.executeUpdate();
}
}
@Override
public void delete(int id) throws Exception {
String sql = "DELETE FROM teachers WHERE id=?";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
ps.executeUpdate();
}
}
@Override
public Teacher findById(int id) throws Exception {
String sql = "SELECT * FROM teachers WHERE id=?";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
return mapRowToTeacher(rs);
}
return null;
}
}
@Override
public List<Teacher> findAll() throws Exception {
String sql = "SELECT * FROM teachers";
List<Teacher> list = new ArrayList<>();
try (Connection conn = DBConnection.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
list.add(mapRowToTeacher(rs));
}
}
return list;
}
private Teacher mapRowToTeacher(ResultSet rs) throws SQLException {
Teacher t = new Teacher();
t.setId(rs.getInt("id"));
t.setName(rs.getString("name"));
t.setGender(rs.getString("gender"));
t.setBirthDate(rs.getDate("birth_date").toLocalDate());
t.setPhone(rs.getString("phone"));
t.setEmail(rs.getString("email"));
t.setHireDate(rs.getDate("hire_date").toLocalDate());
t.setTitle(rs.getString("title"));
t.setDegree(rs.getString("degree"));
t.setDepartment(rs.getString("department"));
t.setSubjectArea(rs.getString("subject_area"));
t.setOfficeLocation(rs.getString("office_location"));
return t;
}
}
```
---
### 🔹 3. `dao/impl/CourseDaoImpl.java`
```java
package dao.impl;
import dao.CourseDao;
import entity.Course;
import util.DBConnection;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class CourseDaoImpl implements CourseDao {
@Override
public void insert(Course course) throws Exception {
String sql = "INSERT INTO courses (course_code, name, credits, type, teacher_id, semester, weekly_hours, max_students, department, description) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, course.getCourseCode());
ps.setString(2, course.getName());
ps.setInt(3, course.getCredits());
ps.setString(4, course.getType());
ps.setInt(5, course.getTeacherId());
ps.setString(6, course.getSemester());
ps.setInt(7, course.getWeeklyHours());
ps.setInt(8, course.getMaxStudents());
ps.setString(9, course.getDepartment());
ps.setString(10, course.getDescription());
ps.executeUpdate();
}
}
@Override
public void update(Course course) throws Exception {
String sql = "UPDATE courses SET name=?, credits=?, type=?, teacher_id=?, semester=?, weekly_hours=?, max_students=?, department=?, description=? WHERE course_code=?";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, course.getName());
ps.setInt(2, course.getCredits());
ps.setString(3, course.getType());
ps.setInt(4, course.getTeacherId());
ps.setString(5, course.getSemester());
ps.setInt(6, course.getWeeklyHours());
ps.setInt(7, course.getMaxStudents());
ps.setString(8, course.getDepartment());
ps.setString(9, course.getDescription());
ps.setString(10, course.getCourseCode());
ps.executeUpdate();
}
}
@Override
public void delete(String courseCode) throws Exception {
String sql = "DELETE FROM courses WHERE course_code=?";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, courseCode);
ps.executeUpdate();
}
}
@Override
public Course findByCode(String courseCode) throws Exception {
String sql = "SELECT * FROM courses WHERE course_code=?";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, courseCode);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
return mapRowToCourse(rs);
}
return null;
}
}
@Override
public List<Course> findAll() throws Exception {
String sql = "SELECT * FROM courses";
List<Course> list = new ArrayList<>();
try (Connection conn = DBConnection.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
list.add(mapRowToCourse(rs));
}
}
return list;
}
@Override
public List<Course> findByTeacherId(int teacherId) throws Exception {
String sql = "SELECT * FROM courses WHERE teacher_id=?";
List<Course> list = new ArrayList<>();
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, teacherId);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
list.add(mapRowToCourse(rs));
}
}
return list;
}
private Course mapRowToCourse(ResultSet rs) throws SQLException {
Course c = new Course();
c.setCourseCode(rs.getString("course_code"));
c.setName(rs.getString("name"));
c.setCredits(rs.getInt("credits"));
c.setType(rs.getString("type"));
c.setTeacherId(rs.getInt("teacher_id"));
c.setSemester(rs.getString("semester"));
c.setWeeklyHours(rs.getInt("weekly_hours"));
c.setMaxStudents(rs.getInt("max_students"));
c.setDepartment(rs.getString("department"));
c.setDescription(rs.getString("description"));
return c;
}
}
```
---
### 🔹 4. `dao/impl/SchoolClassDaoImpl.java`
```java
package dao.impl;
import dao.SchoolClassDao;
import entity.SchoolClass;
import util.DBConnection;
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;
public class SchoolClassDaoImpl implements SchoolClassDao {
@Override
public void insert(SchoolClass clazz) throws Exception {
String sql = "INSERT INTO school_classes (id, class_name, grade, homeroom_teacher_id, enrollment_year, major, department, current_student_count, max_capacity, created_at, is_active) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, clazz.getId());
ps.setString(2, clazz.getClassName());
ps.setString(3, clazz.getGrade());
ps.setInt(4, clazz.getHomeroomTeacherId());
ps.setInt(5, clazz.getEnrollmentYear());
ps.setString(6, clazz.getMajor());
ps.setString(7, clazz.getDepartment());
ps.setInt(8, clazz.getCurrentStudentCount());
ps.setInt(9, clazz.getMaxCapacity());
ps.setDate(10, Date.valueOf(clazz.getCreatedAt()));
ps.setBoolean(11, clazz.isActive());
ps.executeUpdate();
}
}
@Override
public void update(SchoolClass clazz) throws Exception {
String sql = "UPDATE school_classes SET class_name=?, grade=?, homeroom_teacher_id=?, enrollment_year=?, major=?, department=?, current_student_count=?, max_capacity=?, created_at=?, is_active=? WHERE id=?";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, clazz.getClassName());
ps.setString(2, clazz.getGrade());
ps.setInt(3, clazz.getHomeroomTeacherId());
ps.setInt(4, clazz.getEnrollmentYear());
ps.setString(5, clazz.getMajor());
ps.setString(6, clazz.getDepartment());
ps.setInt(7, clazz.getCurrentStudentCount());
ps.setInt(8, clazz.getMaxCapacity());
ps.setDate(9, Date.valueOf(clazz.getCreatedAt()));
ps.setBoolean(10, clazz.isActive());
ps.setInt(11, clazz.getId());
ps.executeUpdate();
}
}
@Override
public void delete(int id) throws Exception {
String sql = "DELETE FROM school_classes WHERE id=?";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
ps.executeUpdate();
}
}
@Override
public SchoolClass findById(int id) throws Exception {
String sql = "SELECT * FROM school_classes WHERE id=?";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
return mapRowToClass(rs);
}
return null;
}
}
@Override
public List<SchoolClass> findAll() throws Exception {
String sql = "SELECT * FROM school_classes";
List<SchoolClass> list = new ArrayList<>();
try (Connection conn = DBConnection.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
list.add(mapRowToClass(rs));
}
}
return list;
}
private SchoolClass mapRowToClass(ResultSet rs) throws SQLException {
SchoolClass c = new SchoolClass();
c.setId(rs.getInt("id"));
c.setClassName(rs.getString("class_name"));
c.setGrade(rs.getString("grade"));
c.setHomeroomTeacherId(rs.getInt("homeroom_teacher_id"));
c.setEnrollmentYear(rs.getInt("enrollment_year"));
c.setMajor(rs.getString("major"));
c.setDepartment(rs.getString("department"));
c.setCurrentStudentCount(rs.getInt("current_student_count"));
c.setMaxCapacity(rs.getInt("max_capacity"));
c.setCreatedAt(rs.getDate("created_at").toLocalDate());
c.setActive(rs.getBoolean("is_active"));
return c;
}
}
```
---
### 🔹 5. `dao/impl/EnrollmentDaoImpl.java`
```java
package dao.impl;
import dao.EnrollmentDao;
import entity.Enrollment;
import util.DBConnection;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
public class EnrollmentDaoImpl implements EnrollmentDao {
@Override
public void insert(Enrollment enrollment) throws Exception {
String sql = "INSERT INTO enrollments (student_id, course_code, enrollment_time, grade, grade_level, status, passed, make_up_exam, comment) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, enrollment.getStudentId());
ps.setString(2, enrollment.getCourseCode());
ps.setTimestamp(3, Timestamp.valueOf(enrollment.getEnrollmentTime()));
ps.setDouble(4, enrollment.getGrade() != null ? enrollment.getGrade() : -1);
ps.setString(5, enrollment.getGradeLevel());
ps.setString(6, enrollment.getStatus());
ps.setBoolean(7, enrollment.isPassed());
ps.setBoolean(8, enrollment.isMakeUpExam());
ps.setString(9, enrollment.getComment());
ps.executeUpdate();
}
}
@Override
public void updateGrade(int studentId, String courseCode, Double grade) throws Exception {
String sql = "UPDATE enrollments SET grade=?, grade_level=?, passed=?, status='已评分' WHERE student_id=? AND course_code=?";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setDouble(1, grade);
ps.setString(2, getGradeLevel(grade));
ps.setBoolean(3, grade >= 60);
ps.setInt(4, studentId);
ps.setString(5, courseCode);
ps.executeUpdate();
}
}
@Override
public void delete(int studentId, String courseCode) throws Exception {
String sql = "DELETE FROM enrollments WHERE student_id=? AND course_code=?";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, studentId);
ps.setString(2, courseCode);
ps.executeUpdate();
}
}
@Override
public Enrollment findByStudentAndCourse(int studentId, String courseCode) throws Exception {
String sql = "SELECT * FROM enrollments WHERE student_id=? AND course_code=?";
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, studentId);
ps.setString(2, courseCode);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
return mapRowToEnrollment(rs);
}
return null;
}
}
@Override
public List<Enrollment> findByStudentId(int studentId) throws Exception {
String sql = "SELECT * FROM enrollments WHERE student_id=?";
List<Enrollment> list = new ArrayList<>();
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, studentId);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
list.add(mapRowToEnrollment(rs));
}
}
return list;
}
@Override
public List<Enrollment> findByCourseCode(String courseCode) throws Exception {
String sql = "SELECT * FROM enrollments WHERE course_code=?";
List<Enrollment> list = new ArrayList<>();
try (Connection conn = DBConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, courseCode);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
list.add(mapRowToEnrollment(rs));
}
}
return list;
}
private Enrollment mapRowToEnrollment(ResultSet rs) throws SQLException {
Enrollment e = new Enrollment();
e.setStudentId(rs.getInt("student_id"));
e.setCourseCode(rs.getString("course_code"));
e.setEnrollmentTime(rs.getTimestamp("enrollment_time").toLocalDateTime());
double grade = rs.getDouble("grade");
e.setGrade(rs.wasNull() ? null : grade);
e.setGradeLevel(rs.getString("grade_level"));
e.setStatus(rs.getString("status"));
e.setMakeUpExam(rs.getBoolean("make_up_exam"));
e.setComment(rs.getString("comment"));
return e;
}
private String getGradeLevel(Double grade) {
if (grade == null) return "N/A";
return switch ((int)(grade / 10)) {
case 10, 9 -> "A";
case 8 -> "B";
case 7 -> "C";
case 6 -> "D";
default -> "F";
};
}
}
```
---
## ✅ 总结
你现在已经拥有了完整的 **DAO 层实现类**，具备以下能力：
| 特性 | 支持情况 |
|------|----------|
| 增删改查（CRUD） | ✅ 所有类均支持 |
| 防止 SQL 注入 | ✅ 使用 `PreparedStatement` |
| 结果集自动映射 | ✅ `mapRowToXxx()` 方法封装 |
| 外键关联查询 | ✅ 如按班级查学生、按教师查课程等 |
| 时间类型处理 | ✅ `LocalDate`, `LocalDateTime` 转换 |
---
## 📌 下一步建议
你可以继续构建：
- **Service 层**：封装业务逻辑（如“选课前检查是否满员”）
- **Swing UI 层**：展示学生列表、成绩录入界面
- **工具类增强**：加入分页、异常日志、连接池（如 HikariCP）
---
扫一扫
808
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
