I solved the exception I was getting below, but still have an issue. Please see the update at the end I have 2 classes, student and course. A student can have one or more courses and a course can have one or more students. It is a many-to-many relationship. Student has a HashSet<Course> and Course has a HashSet<Student> as an attribute. The two tables are joined by a student_course_join table.
The tables are defined by:
Code:
CREATE TABLE student (
id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
UNIQUE UQ_1 (first_name, last_name),
PRIMARY KEY(id)
);
CREATE TABLE course (
course_id VARCHAR(20),
title VARCHAR(40),
sched VARCHAR(50),
units INT,
PRIMARY KEY(course_id)
);
CREATE TABLE student_course_join (
student_id INT NOT NULL,
course_id VARCHAR(20) NOT NULL,
PRIMARY KEY(student_id, course_id),
CONSTRAINT FK_SCJ_1 FOREIGN KEY (student_id)
REFERENCES student (id) ON DELETE CASCADE,
CONSTRAINT FK_SCJ_2 FOREIGN KEY (course_id)
REFERENCES course (course_id)
);
The Student class, showing mostly the ManyToMany relationship is:
Quote:
package edu.univ.mht.domain;
// import statements
@Entity
@Table(name = "student")
public class Student implements Serializable {
private Long id;
private String firstName;
private String lastName;
// For change 1, classes student is enrolled in
private Set<Course> courses = new HashSet<Course>();
// Constructors and setter/getter methods.....
@ManyToMany(cascade = CascadeType.ALL)
@JoinTable(name = "student_course_join",
joinColumns = @JoinColumn(name = "STUDENT_ID"),
inverseJoinColumns = @JoinColumn(name = "COURSE_ID"))
public Set<Course> getCourses() {
return this.courses;
}
public void setCourses(Set<Course> courses) {
this.courses = courses;
}
} // Class
The Course class is (showing mostly the ManyToMany mapping):
Code:
package edu.univ.mht.domain;
// imports....
@Entity
@Table(name = "course")
public class Course implements Serializable {
private String course_id;
private String title;
private String sched;
private int units;
// The students enrolled in this class
private Set<Student> students = new HashSet<Student>();
// Constructors and setters/getters.....
@ManyToMany(cascade = CascadeType.ALL)
@JoinTable(name = "student_course_join",
joinColumns = @JoinColumn(name = "COURSE_ID"),
inverseJoinColumns = @JoinColumn(name = "STUDENT_ID"))
public Set<Student> getStudents() {
return this.students;
}
public void setStudents(Set<Student> students) {
this.students = students;
}
}
When I create a student with no Course set and save it, everything works fine. If I delete a student with no course set it works fine. If I try to create and save a student with a course set (a new or existing student) I get the following error:
SEVERE: Servlet.service() for servlet [appServlet] in context with path [/mht] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: Could not execute JDBC batch update; SQL [insert into student_course_join (STUDENT_ID, COURSE_ID) values (?, ?)]; constraint [null]; nested exception is org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update] with root cause
java.sql.BatchUpdateException: Cannot add or update a child row: a foreign key constraint fails (`college`.`student_course_join`, CONSTRAINT `FK_SCJ_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`course_id`))
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1666)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1082)
at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
Update 4/24/13I was able to get the basic save and retrieval to work, however I am still having one problem. If I create a student object, then create a course object and add it to the student Set<course> everything works fine in terms of saving and retrieving the data as long as the course I'm adding does not have an entry in the student_course_join table. However, if I obtain an existing student that already has a course (with entry in the student_course_join table) then add another course to the student Set<Course> and save the student (with a session.saveOrUpdate(student)) the row in the student_course_join table that has an entry for the course_id is overwritten with the new value of (student_id, course_id) this loosing old data.
My logic for adding a new course to a student is:
Code:
// The steps to adding a course to a student are to look up the course
// information, create a new course from it, then call student.addCourse(), then
// save the student to the database.
// get the course data from the course_catalog table
CourseData courseData = studentService.getCourseInfo(courseId);
// Look up the student
Long lStudentId = new Long(studentId);
Student student = studentService.findById(lStudentId);
// Create a new course object from the course information
Course course = new Course(courseData.getId(), courseData.getTitle(),
courseData.getSched(), courseData.getUnits());
// Add the course to the students Set<Course>, the following will actually call
// student.getCourse().add(student):
student.addCourse(course);
// Now save student away (update), the following ultimately does a session.saveOrUpdate(student)
studentService.saveStudent(student);
Login
FAQ
Search