JSF java.sql.SQLIntegrityConstraintViolationException nach zweitem persist

[mannohnenamen]

Hallo Leute,

ich schreibe eine Web-Application und nutze den Glassfish (lokal und auf einem Server).
Als Datenbank benutze ich die JavaDB mit JPA.

Was ich jetzt nicht ganz verstehe ist, dass die Datenbank auf dem Server sich anders verhält als lokal.
Lokal habe ich keinerlei Probleme mit dem Eintragen und Löschen von Datenbankeinträgen.

Auf dem Server erhalte ich nun eine Fehlermeldung, beim Löschen der Einträge.

[#|2011-10-24T15:08:55.277+0200|WARNING|glassfish3.1|org.eclipse.persistence.session.file:/srv/javaee6/glassfish3/glassfish/domains/testdomain/applications/CommunityQuiz/WEB-INF/classes/_CommunityQuizPU|_ThreadID=116;_ThreadName=Thread-1;|
Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLIntegrityConstraintViolationException: DELETE on table 'CATEGORY' caused a violation of foreign key constraint 'TRNDCTGRYCTEGORYID' for key (17).  The statement has been rolled back.
Error Code: -1
Call: DELETE FROM CATEGORY WHERE (CATEGORY_ID = ?)
	bind => [1 parameter bound]
Query: DeleteObjectQuery(Category{categoryId=17, name=1})
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:798)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:864)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:583)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:526)
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1729)
	at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:234)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:193)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.deleteObject(DatasourceCallQueryMechanism.java:184)
	at org.eclipse.persistence.internal.queries.StatementQueryMechanism.deleteObject(StatementQueryMechanism.java:101)
	at org.eclipse.persistence.queries.DeleteObjectQuery.executeDatabaseQuery(DeleteObjectQuery.java:175)
	at org.eclipse.persistence.queries.DatabaseQuery.execute(DatabaseQuery.java:808)
	at org.eclipse.persistence.queries.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:711)
	at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:108)
	at org.eclipse.persistence.queries.DeleteObjectQuery.executeInUnitOfWorkObjectLevelModifyQuery(DeleteObjectQuery.java:113)
	at org.eclipse.persistence.queries.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:85)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2842)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1521)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1503)
	at org.eclipse.persistence.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:1463)
	at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(CommitManager.java:334)
	at org.eclipse.persistence.internal.sessions.CommitManager.deleteAllObjects(CommitManager.java:288)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1407)
	at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.commitToDatabase(RepeatableWriteUnitOfWork.java:616)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1511)
	at org.eclipse.persistence.internal.sessions.UnitOfWorkImpl.issueSQLbeforeCompletion(UnitOfWorkImpl.java:3115)
	at org.eclipse.persistence.internal.sessions.RepeatableWriteUnitOfWork.issueSQLbeforeCompletion(RepeatableWriteUnitOfWork.java:331)
	at org.eclipse.persistence.transaction.AbstractSynchronizationListener.beforeCompletion(AbstractSynchronizationListener.java:157)
	at org.eclipse.persistence.transaction.JTASynchronizationListener.beforeCompletion(JTASynchronizationListener.java:68)
	at com.sun.enterprise.transaction.JavaEETransactionImpl.commit(JavaEETransactionImpl.java:437)
	at com.sun.enterprise.transaction.JavaEETransactionManagerSimplified.commit(JavaEETransactionManagerSimplified.java:867)
	at com.sun.ejb.containers.BaseContainer.completeNewTx(BaseContainer.java:5115)
	at com.sun.ejb.containers.BaseContainer.postInvokeTx(BaseContainer.java:4880)
	at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:2039)
	at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:1990)
	at com.sun.ejb.containers.EJBLocalObjectInvocationHandler.invoke(EJBLocalObjectInvocationHandler.java:222)
	at com.sun.ejb.containers.EJBLocalObjectInvocationHandlerDelegate.invoke(EJBLocalObjectInvocationHandlerDelegate.java:88)
	at $Proxy196.removeCategories(Unknown Source)
	at de.mschmidt.cquiz.control.__EJB31_Generated__TreeService__Intf____Bean__.removeCategories(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:601)
	at org.jboss.weld.util.reflection.SecureReflections$13.work(SecureReflections.java:305)
	at org.jboss.weld.util.reflection.SecureReflectionAccess.run(SecureReflectionAccess.java:54)
	at org.jboss.weld.util.reflection.SecureReflectionAccess.runAsInvocation(SecureReflectionAccess.java:163)
	at org.jboss.weld.util.reflection.SecureReflections.invoke(SecureReflections.java:299)
	at org.jboss.weld.bean.proxy.EnterpriseBeanProxyMethodHandler.invoke(EnterpriseBeanProxyMethodHandler.java:126)
	at org.jboss.weld.bean.proxy.EnterpriseTargetBeanInstance.invoke(EnterpriseTargetBeanInstance.java:62)
	at org.jboss.weld.bean.proxy.ProxyMethodHandler.invoke(ProxyMethodHandler.java:125)
	at de.mschmidt.cquiz.control.org$jboss$weld$bean-CommunityQuiz-SessionBean-TreeService_$$_WeldProxy.removeCategories(org$jboss$weld$bean-CommunityQuiz-SessionBean-TreeService_$$_WeldProxy.java)
	at de.mschmidt.cquiz.control.org$jboss$weld$bean-CommunityQuiz-SessionBean-TreeService_$$_WeldClientProxy.removeCategories(org$jboss$weld$bean-CommunityQuiz-SessionBean-TreeService_$$_WeldClientProxy.java)
	at de.mschmidt.cquiz.view.QuestionHandler.remove(QuestionHandler.java:537)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:601)
	at javax.el.BeanELResolver.invokeMethod(BeanELResolver.java:737)
	at javax.el.BeanELResolver.invoke(BeanELResolver.java:467)
	at javax.el.CompositeELResolver.invoke(CompositeELResolver.java:254)
	at com.sun.el.parser.AstValue.invoke(AstValue.java:228)
	at com.sun.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:297)
	at org.jboss.weld.util.el.ForwardingMethodExpression.invoke(ForwardingMethodExpression.java:43)
	at org.jboss.weld.el.WeldMethodExpression.invoke(WeldMethodExpression.java:56)
	at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
	at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88)
	at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
	at javax.faces.component.UICommand.broadcast(UICommand.java:315)
	at javax.faces.component.UIData.broadcast(UIData.java:1093)
	at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:794)
	at javax.faces.component.UIViewRoot.processDecodes(UIViewRoot.java:935)
	at com.sun.faces.lifecycle.ApplyRequestValuesPhase.execute(ApplyRequestValuesPhase.java:78)
	at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
	at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118)
	at javax.faces.webapp.FacesServlet.service(FacesServlet.java:409)
	at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1534)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:281)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
	at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
	at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:98)
	at com.sun.enterprise.web.PESessionLockingStandardPipeline.invoke(PESessionLockingStandardPipeline.java:91)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:162)
	at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:326)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:227)
	at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:170)
	at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:822)
	at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:719)
	at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1013)
	at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:225)
	at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
	at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
	at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
	at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
	at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
	at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
	at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
	at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
	at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
	at java.lang.Thread.run(Thread.java:722)
Caused by: java.sql.SQLIntegrityConstraintViolationException: DELETE on table 'CATEGORY' caused a violation of foreign key constraint 'TRNDCTGRYCTEGORYID' for key (17).  The statement has been rolled back.
	at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
	at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
	at org.apache.derby.client.am.PreparedStatement.executeUpdate(Unknown Source)
	at com.sun.gjc.spi.base.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:120)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:789)
	... 99 more
Caused by: org.apache.derby.client.am.SqlException: DELETE on table 'CATEGORY' caused a violation of foreign key constraint 'TRNDCTGRYCTEGORYID' for key (17).  The statement has been rolled back.
	at org.apache.derby.client.am.Statement.completeExecute(Unknown Source)
	at org.apache.derby.client.net.NetStatementReply.parseEXCSQLSTTreply(Unknown Source)
	at org.apache.derby.client.net.NetStatementReply.readExecute(Unknown Source)
	at org.apache.derby.client.net.StatementReply.readExecute(Unknown Source)
	at org.apache.derby.client.net.NetPreparedStatement.readExecute_(Unknown Source)
	at org.apache.derby.client.am.PreparedStatement.readExecute(Unknown Source)
	at org.apache.derby.client.am.PreparedStatement.flowExecute(Unknown Source)
	at org.apache.derby.client.am.PreparedStatement.executeUpdateX(Unknown Source)
	... 102 more
|#]

Woran könnte das liegen bzw. welchen Fehler habe ich wohlmöglich gemacht?
Es handelt sich zwar um eine komplexere Entitybeziehung, aber wie gesagt lokal funktioniert es.

 

[maki]

Lokal hast du wohl andere Daten

Die Categoray die du löschen willst, wird referenziert, musst also erst alle Referenzen darauf löschen, alternativ kannst du das auch über die Cascade machen, ist aber nur zu empfehlen wenn du wirklich weisst was du da machst

 

[mannohnenamen]

Ich nutze Cascade bereits.
Es gibt in dem Sinne ein Ober-Entity, bei dem die Daten hinzugefügt und gelöscht werden.

Andere Daten können es kaum sein. Ich nutze die Strategie Drop & Create.
Es muss an etwas anderem liegen.

 

[mannohnenamen]

Ich konnte das Problem ein bisschen näher lokalisieren.
Ich nutze einen Baum, um Kategorien zu speichern.
Der Baum soll die Hierarchie der Kategorien widerspiegeln (Oberkategorie -> Kategorie -> Unterkategorie). Per JPA speicher ich ein Objekt der Baumklasse (root) ab.

Hier die Baumklasse:

@Entity
public class TreeNode implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long treeNodeId;  
   
    @OneToOne(cascade=CascadeType.ALL)
    private TreeNode parent = null;
    
    @OneToMany(cascade=CascadeType.ALL, orphanRemoval=true)
    private List<TreeNode> children = null;
    
    @OneToOne(cascade=CascadeType.ALL, orphanRemoval=true)
    private Category category;

Hier die Kategorieklasse (sieh besitzt noch eine n:m Beziehung zu Question):

@Entity
public class Category implements Serializable {
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long categoryId;
    
    @OneToOne
    private TreeNode treeNode;
    
    @ManyToMany(mappedBy="categories")
    private List<Question> questions;

Wenn nun eine Oberkategorie gelöscht wird, sprich ein Knoten des Baum wird gelöscht, werden natürlich mehr als eine Kategorie auf einmal gelöscht. Bei diesem Vorgang entsteht der Fehler.

Hier noch die remove-Methode:

public void removeCategories(List<Category> categories) {
        
        if (categories == null) {
            return;
        }

        TreeNode father = root;
        father.decreaseCount();
        for(Category category : categories) {

            TreeNode child = father.getChild(category.getName());
            child.decreaseCount();
            if(child.getCount() == 0) {
                father.removeChild(child);
                break;
            }
            
            father = child;
        }
        
        root = em.merge(father);
        Logger.getLogger(TreeService.class.getName()).log(Level.INFO, "### root wurde aktualisiert.");  
}

und die JPA-Methode:

@Stateless
public class TreeNodeService implements Serializable{
    
    @PersistenceContext
    private EntityManager em;
    
    public void create(TreeNode node) {
        em.persist(node);
    }
    
    public TreeNode update(TreeNode node) {
        return em.merge(node);
    }
    
    public void remove(TreeNode node) {
         em.remove(em.merge(node));
    }
}

 

[mannohnenamen]

Kleine Ergänzung

Man kann hier ganz schön sehen, dass der Fehler nach dieser Ausführung kommt.

### father: [101]Allgemein:0;|#]

[#|2011-10-24T17:49:42.279+0200|INFO|### root wurde aktualisiert.|#]

[#|2011-10-24T17:49:42.279+0200|INFO|#Aktualisiert: [101]Allgemein:0;|#]

[#|2011-10-24T17:49:42.318+0200|WARNING|glassfish3.1|org.eclipse.persistence.session.file:/srv/javaee6/glassfish3/glassfish/domains/testdomain/applications/CommunityQuiz/WEB-INF/classes/_CommunityQuizPU|_ThreadID=94;_ThreadName=Thread-1;|
Local Exception Stack: 
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLIntegrityConstraintViolationException: DELETE on table 'CATEGORY' caused a violation of foreign key constraint 'TRNDCTGRYCTEGORYID' for key (116).  The statement has been rolled back.
Error Code: -1
Call: DELETE FROM CATEGORY WHERE (CATEGORY_ID = ?)
	bind => [1 parameter bound]
Query: DeleteObjectQuery(Category{categoryId=116, name=Sport})
	at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:324)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeDirectNoSelect(DatabaseAccessor.java:798)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeNoSelect(DatabaseAccessor.java:864)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:583)
	at org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:526)
	at org.eclipse.persistence.internal.sessions.AbstractSession.basicExecuteCall(AbstractSession.java:1729)
	at org.eclipse.persistence.sessions.server.ClientSession.executeCall(ClientSession.java:234)
	at org.eclipse.persistence.internal.queries.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:207)
	at

  . . .

 

[maki]

Was ist denn die Frage?

Ist dir denn nicht klar warum dieser Fehler kommt?

Deine bi-directionale Verbindung zwischen Category und TreeNode ist eine komplexe Sache, so komplex dass sie dich zu verwirren scheint
Was passiert, wenn du hier eine Cascade.ALL einstellst?

    @OneToOne
    private TreeNode treeNode;

Würde das nicht auf bi-direktional lassen an deiner Stelle.

 

[mannohnenamen]

Das war es!!!

Das es für mich teilweise verwirrend ist, da wirst du wohl recht haben.
Ist mein erstes Projekt mit JPA und JSF.

Vielen vielen Dank

Frage mich trotzdem abschliessend, warum das lokal geklappt hat.