Constraint Violation Question

[Mike London]

Hello,

I’m trying to create a simple example of a M:M relationship in Hibernate using a STUDENTS-COURSES database.

The single table queries/updates work fine, but when trying to add a record to the M:M resolver table, I get this exception:

Exception: Cannot add or update a child row: a foreign key constraint fails (`student_courses`.`studentcourse`, CONSTRAINT `studentcourse_ibfk_2` FOREIGN KEY (`COURSE_ID`) REFERENCES `COURSE` (`COURSE_ID`))
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:74)I’

and also,

Caused by: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`student_courses`.`studentcourse`, CONSTRAINT `studentcourse_ibfk_2` FOREIGN KEY (`COURSE_ID`) REFERENCES `COURSE` (`COURSE_ID`))

-----------

I can add a M:M record manually, however so the database allows records being added to the STUDENT_COURSE (the M:M resolver) table.

Here’s the code from my "main" method:

public class AppTest
{
   public static final String SELECT_QUERY = "from Course";
    public static final String SELECT2_QUERY = "from Student where STUDENT_NAME='Fred'";
public static void main(String[] args)
    {
PersistenceProvider persistanceProvider = new HibernatePersistence();
        EntityManagerFactory entityManagerFactory = persistanceProvider.createEntityManagerFactory(
                "NewPersistenceUnit", new HashMap());
        EntityManager entityManager = entityManagerFactory.createEntityManager();

        EntityTransaction tx = entityManager.getTransaction();
        tx.begin();

  entityManager.flush();

        // Find student and get his PK from the Student table, Should only be one element
        Student studentFred = null;
        studentFred = entityManager.createQuery(SELECT2_QUERY, Student.class).getSingleResult();

        // System.out.println("Student Meeko: " + studentMeeko.getStudentName());
        int studentPK = studentFred.getStudentId(); // existing student

        // Now do the same to get Calculus
        Course calCourse = entityManager.createQuery("from Course where COURSE_NAME = 'Calculus'",
                           Course.class).getSingleResult();
        int coursePk = calCourse.getCourseId();

        // now write this information into the STUDENTCOURSE table
        Studentcourse sc = new Studentcourse();
        sc.setCourseByCourseId(calCourse);

        Course newCourse = new Course();
        sc.setCourseByCourseId(newCourse);

        entityManager.persist(sc);

        tx.commit();

Here’s the SQL that defines the simple database:

CREATE TABLE COURSE
(
COURSE_ID INT(11) NOT NULL AUTO_INCREMENT,
COURSE_NAME VARCHAR(255),
PRIMARY KEY (COURSE_ID)
) ENGINE=InnoDB;

CREATE TABLE STUDENT
(
STUDENT_ID INT(11) NOT NULL AUTO_INCREMENT,
STUDENT_NAME VARCHAR(20),
PRIMARY KEY (STUDENT_ID)
) ENGINE=InnoDB;

CREATE TABLE STUDENTCOURSE
(
STUDENT_ID INT(11),
COURSE_ID INT(11),
SC_ID INT(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (SC_ID)
) ENGINE=InnoDB;

CREATE INDEX STUDENT_ID_idx ON STUDENTCOURSE(STUDENT_ID);
ALTER TABLE STUDENTCOURSE ADD FOREIGN KEY studentcourse_ibfk_1 (STUDENT_ID) REFERENCES STUDENT (STUDENT_ID) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE INDEX COURSE_ID_idx ON STUDENTCOURSE(COURSE_ID);
ALTER TABLE STUDENTCOURSE ADD FOREIGN KEY studentcourse_ibfk_2 (COURSE_ID) REFERENCES COURSE (COURSE_ID) ON DELETE NO ACTION ON UPDATE NO ACTION;

The entity class for StudentCourse is:

@Entity
public class Studentcourse
{
    private int scId;

    @javax.persistence.Column(name = "SC_ID", nullable = false, insertable = true, updatable = true, length = 10, precision = 0)
    @Id
    public int getScId()
    {
        return scId;
    }

    public void setScId(int scId)
    {
        this.scId = scId;
    }

    @Override
    public boolean equals(Object o)
    {
        if (this == o)
        {
            return true;
        }
        if (o == null || getClass() != o.getClass())
        {
            return false;
        }

        Studentcourse that = (Studentcourse) o;

        if (scId != that.scId)
        {
            return false;
        }

        return true;
    }

    @Override
    public int hashCode()
    {
        return scId;
    }

    private Student studentByStudentId;

    @ManyToOne (cascade = CascadeType.ALL)
    @javax.persistence.JoinColumn(name = "STUDENT_ID", referencedColumnName = "STUDENT_ID")
    public Student getStudentByStudentId()
    {
        return studentByStudentId;
    }

    public void setStudentByStudentId(Student studentByStudentId)
    {
        this.studentByStudentId = studentByStudentId;
    }

    private Course courseByCourseId;

    @ManyToOne (cascade = CascadeType.ALL)
    @javax.persistence.JoinColumn(name = "COURSE_ID", referencedColumnName = "COURSE_ID")
    public Course getCourseByCourseId()
    {
        return courseByCourseId;
    }

    public void setCourseByCourseId(Course courseByCourseId)
    {
        this.courseByCourseId = courseByCourseId;
    }
}

I don’t have a standard DAO object and am using the EntityManager and related classes as shown above. Don’t think that’s the issue, however, since the single table queries work fine.

Look forward to any suggestions and replies.

- Mike

[Jayesh A Lalwani]

First of all, JPA supports ManyToMany relationship. You shouldn't need to create an Entity class for StudentCourse table. You just need Student and COurse, and in the annotation you can use JPA to use the StudentCourse relationship table

Secondly, in your code you set calCourse to StudentCOurse, and then set a new Course to StudentCourse. Is that what you intended to do. The problem could be that the new COurse is not being saved properly. Can you post your COurse class? Is the ID tag placed on Course correctly?

Third, your naming conventions are a bit off. Usually, you would name your methods as StudentCourse.getStudent and StudentCourse.getCourse, not StudentCourse.getStudentbyStudentId and StudentCourse.getCourseByCourseId,

[Mike London]

Jayesh A Lalwani wrote:First of all, JPA supports ManyToMany relationship. You shouldn't need to create an Entity class for StudentCourse table. You just need Student and COurse, and in the annotation you can use JPA to use the StudentCourse relationship table

Secondly, in your code you set calCourse to StudentCOurse, and then set a new Course to StudentCourse. Is that what you intended to do. The problem could be that the new COurse is not being saved properly. Can you post your COurse class? Is the ID tag placed on Course correctly?

Third, your naming conventions are a bit off. Usually, you would name your methods as StudentCourse.getStudent and StudentCourse.getCourse, not StudentCourse.getStudentbyStudentId and StudentCourse.getCourseByCourseId,

What I was trying to do was to get course and student object references to "feed" to a Hibernate method to then persist the new record in the M:M table.

The naming was done by Intellij 12, not me, and it doesn't create DAO objects as far as I can see.

Here's the Course Class generated by Intellij Entity reverse engineering.

@Entity
public class Course
{
    private int courseId;

    @javax.persistence.Column(name = "COURSE_ID", nullable = false, insertable = true, updatable = true, length = 10, precision = 0)
    @Id
    public int getCourseId()
    {
        return courseId;
    }

    public void setCourseId(int courseId)
    {
        this.courseId = courseId;
    }

    private String courseName;

    @javax.persistence.Column(name = "COURSE_NAME", nullable = true, insertable = true, updatable = true, length = 255, precision = 0)
    @Basic
    public String getCourseName()
    {
        return courseName;
    }

    public void setCourseName(String courseName)
    {
        this.courseName = courseName;
    }

    @Override
    public boolean equals(Object o)
    {
        if (this == o)
        {
            return true;
        }
        if (o == null || getClass() != o.getClass())
        {
            return false;
        }

        Course course = (Course) o;

        if (courseId != course.courseId)
        {
            return false;
        }
        if (courseName != null ? !courseName.equals(course.courseName) : course.courseName != null)
        {
            return false;
        }

        return true;
    }

    @Override
    public int hashCode()
    {
        int result = courseId;
        result = 31 * result + (courseName != null ? courseName.hashCode() : 0);
        return result;
    }

    private Collection<Studentcourse> studentcoursesByCourseId;

    @OneToMany(mappedBy = "courseByCourseId")
    public Collection<Studentcourse> getStudentcoursesByCourseId()
    {
        return studentcoursesByCourseId;
    }

    public void setStudentcoursesByCourseId(Collection<Studentcourse> studentcoursesByCourseId)
    {
        this.studentcoursesByCourseId = studentcoursesByCourseId;
    }
}

---------------

I made these changes to the "main" class and it now works:

    public static void main(String[] args)
    {
        // AppTest obj = new AppTest();
        PersistenceProvider persistanceProvider = new HibernatePersistence();
        EntityManagerFactory entityManagerFactory = persistanceProvider.createEntityManagerFactory(
                "NewPersistenceUnit", new HashMap());
        EntityManager entityManager = entityManagerFactory.createEntityManager();

        EntityTransaction tx = entityManager.getTransaction();
        tx.begin();

        // find a student
        Student student = entityManager.find(Student.class, 2);
        System.out.println("\n*****Found student using 'find'*********" + student.getStudentName() + "\n");

        // Add M:M Record:

        entityManager.flush();

        // Find Meeko and get his PK from the Student table, Should only be one element
        Student studentFred = null;
        studentFred = entityManager.createQuery(SELECT2_QUERY, Student.class).getSingleResult();

        // System.out.println("Student Meeko: " + studentMeeko.getStudentName());
        int studentPK = studentFred.getStudentId(); // existing student

        // Now do the same to get Calculus
        Course calCourse = entityManager.createQuery("from Course where COURSE_NAME = 'Calculus'",
                           Course.class).getSingleResult();
        int coursePk = calCourse.getCourseId();

        // now write this information into the STUDENTCOURSE table
        Studentcourse sc = new Studentcourse();
        sc.setCourseByCourseId(calCourse);
        sc.setStudentByStudentId(studentFred);


        entityManager.persist(sc);

        tx.commit();

Do you know if there's any way to generate DAOs using Intellij? I can't find a plugin that works.

Thanks,

Mike