How do I use EntityManager in a Bean with foreign key constraints

[pete007]

Hello, I have a table persons with a unique key on (department, title, first_name, last_name).

My bean looks something like this:

@Stateful @Name("personUpload")
public class PersonUploadBean implements PersonUpload
{
  @In EntityManager em;

  public void fileUpload()
  {
    ArrayList<Person> oldPs = (ArrayList<Person>) em.createQuery("from Person").getResultList();
    ArrayList<Person> oldPs = getNewPersonsFromCSV();
    for(Person oP:oldPs) { em.remove(oP); }
    for(Person nP:newPs) { em.persist(nP); }
  }
}

It does the right thing (deleting all old entries and creating new ones) when I call it with a file, that contains only persons that do not match a constraint.

But when I use the same file a second time I get this exception:

WARN  [JDBCExceptionReporter] SQL Error: 1062, SQLState: 23000
ERROR [JDBCExceptionReporter] Duplicate entry 'EWZ-Dr.-Patrick-Master' for key 2
FATAL [application] javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.ConstraintViolationException: could not insert: [de.gdc.Person]
javax.faces.el.EvaluationException: javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.ConstraintViolationException: could not insert: [de.gdc.Person]
          ...

Do I have to commit (what?) between the removes and the persists or did I miss something relevant?

Thanks in advance, Peter

[mvidal0001]

Hi

why your EntityManager is @IN   ?

normally it should be something like this :
  
   @PersistenceContext                                             
private EntityManager em;

it seems you're initiating twice the same array  : oldPS

hope this help.
regards

[pete007]

I think, I've read it somewhere and it worked, now changed it to @PersistenceContext, thanks. .. erm, and the double initiation was only a typo from simplifying the question.

My Problem is still there, the persons don't seem to get deleted, and the constraint fails.

I think, I have to do something between the deletes and the removes, but what?

[mvidal0001]

May be you can try em.flush() and em.refresh() between the two

I don't think you need a stateful bean for this kind of component.

May be the problem is that you have the same person twice in your csv file  :o)

[pete007]

matthieu vidal wrote on Mar 09, 2008 04:08 PM:

May be the problem is that you have the same person twice in your csv file  :o)

This one really shocked me, but fortunately I wasn't that stupid. :D .. After calming down a bit I tried the em.flush() and it worked, although I don't understand, why I must flush the context after removing objects to be able to insert objects with the same business key again??

Thanks a lot, Peter

[keithnaas]

Peter,

I'm not sure about the internal workings of the entity manager, but if I were coding up a JPA implementation, I would likely have done the same thing since its likely easier to code.

Should the em assume that a delete/insert in one transaction is an update?  What happens with cascades? 

Seems a little squirrely to deal with this scenario in an automated manner.

[pete007]

Okay, I see this point, but then it should either try to update the current element to save some time/resources or to do both delete and insert operations with the consequence of losing all references. My newly created person e.g doesn't have the same references as the old one. In my app, it tried to insert a second element with the same business key, which is not so easy to understand, particularily for newbies like me. ;)

[keithnaas]

Its definitely not obvious how the behavior will work. 

I did find HHH-2801 which might be related to this.