Dissolving a many to many relationship

[chrismalan]

There is a many to many relationship between students and subjects. At the end of the year this relationship has to be dissolved so new subjects can be assigned to each student the next year.

I thought it was going to be easy. First, get all the students in a list with a query. Then loop through this list while at the same time looping through each student

 for(Student s : students){
 s.setYear(s.getYear() + 1);
 q = manager.createQuery("select ss from Student s join s.subjects ss where s = :student");
 q.setParameter("student", s);
 List<Subject> subjects = q.getResultList();
 if(subjects != null && subjects.size() > 0){
 System.out.println("The number of subjects for student " +
 s.getNames() + " " + s.getSurname() + " is " + subjects.size());
 Iterator<Subject> iter = subjects.iterator();
 while(iter.hasNext()){
 Subject sj = iter.next(); //This is where it hits the fan
 subjects.remove(sj);
 }
 }

I've also tried using a set (Set subjects = s.getSubjects()) with the same results. I also used a cast in the line with the problems.

This is a short version of the exceptions:

javax.ejb.EJBException: null; CausedByException is:
 null
 at org.jboss.ejb3.tx.Ejb3TxPolicy.handleExceptionInOurTx(Ejb3TxPolicy.java:46)
 at org.jboss.aspects.tx.TxPolicy.invokeInOurTx(TxPolicy.java:70)
 at org.jboss.aspects.tx.TxInterceptor$Required.invoke(TxInterceptor.java:134)
 at org.jboss.aop.joinpoint.MethodInvocation.invokeNext(MethodInvocation.java:98)

There is also a

java.util.ConcurrentModificationException
 at java.util.AbstractList$Itr.checkForComodification(AbstractList.java:449)
 at java.util.AbstractList$Itr.next(AbstractList.java:420)
 at au.com.databaseapplications.jtest.session.PrincipalSessionBean.newYear(PrincipalSessionBean.java:355)

The last line referred to in the exception above is the one indicated in the code.

Any idea how one can do what seems to be simple?

[chrismalan]

To further clarify, the following two SQL queries do what I want done:
"delete from STUDENT_SUBJECT;" and
"delete from STUDENT_CLASS;"
The second one is not mentioned in the post, but if I can get one to work, so will the other.
There is a method that removes any number of stidents (comma separated) from a subject and also from the class for a subject. Putting the code for this in a loop for all subjects does not work with the same exception as above.
This should be possible, and preferably without the looping - more something like the native delete query, which is not implemented in the EJB3/JBoss version I use (4.0.3)
Maybe I should create explicit classes for the relationship tables and one to many relationships. I think I can get that to work. What a hassle. It will mean a lot of re-writing.

[squishy]

I think you get the ConcurrentModificationException because you remove objects from the list while you're iterating over it.
If you go through the list with a simple

for (int i = 0; i < subjects.size(); i++)

maybe it will work?

[chrismalan]

Hi Squishy,

Thanks very much. That was it. However, before the code posted above I had a for(Subject s : subjects). That threw the same exceptions. This time I got a Set of subjects and turned it into an Object array and for looped through that - no problem. It didn't take long to execute, either.

Thanks again,

[squishy]

Don't you have a relationship on EJB level so you can do student.getSubjects() instead of going through the trouble of doing a query? Looks awful complicated to me :)

[chrismalan]

Yes, I have. Then I did a subjects.clear() No exceptions or anything, but the student-subject relationship was still there in the relationship table in MySQL.

I think I may even have called flush(). I did call subjects.size() before clearing.