Geek Answers Handbook

Constraint violation works with short updates in one transaction

I get a very strange violation of referential integrity restrictions, the following code illustrates what I am doing (everything works in one transaction using the same entityManager):

...
em.persist(newEntity); //id -> @GeneratedValue(strategy = GenerationType.AUTO)
updateReference(oldEntity, newEntity);
...

public void updateReference(Entity1 oldReference, Entity1 newReference) {
    String jpql = "UPDATE entity2 e"
            + " SET e.entity1 = :newReference"
            + " WHERE e.entity1 = :oldReference";

    Query query = entityManager.createQuery(jpql);
    query.setParameter("newReference", newReference);
    query.setParameter("oldReference", oldReference);
    query.executeUpdate();
}

The error message "JdbcSQLException: violation of referential integrity constraint" appears in the update statement. The strangest thing is that if I run the list below before starting the update statement, it works without erros ...

entityManager.createQuery("SELECT e FROM entity1 e WHERE e.id = " + newReference.getId()).getResultList();

, - ( , ...), , . , , .

* JPA 2.1, Hibernate 4.3.11.Final H2

StackTrace:

2016-04-15 16:07:35.976; [http-bio-8080-exec-19]; ERROR; o.h.e.jdbc.spi.SqlExceptionHelper; Referential integrity constraint violation: "FK_ANAL_RPPA_ATUALIZADA: PUBLIC.RESULTADO_PRE_PROCESSADO_ANALISE FOREIGN KEY(ANAL_CD_IDENTIFICADOR_ATUALIZADA) REFERENCES PUBLIC.ANALISE_ALARME(ANAL_CD_IDENTIFICADOR) (10032)"; SQL statement:
update RESULTADO_PRE_PROCESSADO_ANALISE set ANAL_CD_IDENTIFICADOR_ATUALIZADA=? where ANAL_CD_IDENTIFICADOR_ATUALIZADA=? [23506-175]

...

    Caused by: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute statement
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:1771) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:87) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.ResultadoPreProcessadoAnaliseJpaDao.atualizarReferenciaResultados(ResultadoPreProcessadoAnaliseJpaDao.java:373) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.negocio.GerenciadorReferenciaAnalise.atualizarReferenciaAnalise(GerenciadorReferenciaAnalise.java:34) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.AnaliseJpaDao.atualizarAnalise(AnaliseJpaDao.java:114) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.AnaliseAlarmeJpaDao.atualizarAnalise(AnaliseAlarmeJpaDao.java:74) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.AnaliseAlarmeHistoricoJpaDao.atualizarAnalise(AnaliseAlarmeHistoricoJpaDao.java:37) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.AnaliseAlarmeHistoricoJpaDao.atualizarAnalise(AnaliseAlarmeHistoricoJpaDao.java:14) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.AnaliseJpaDao.salvar(AnaliseJpaDao.java:97) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.AnaliseAlarmeJpaDao.salvar(AnaliseAlarmeJpaDao.java:60) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.AnaliseAlarmeHistoricoJpaDao.salvar(AnaliseAlarmeHistoricoJpaDao.java:43) ~[classes/:na]
        at br.com.logique.bralarmexpert.modelo.dao.jpa.AnaliseAlarmeHistoricoJpaDao.salvar(AnaliseAlarmeHistoricoJpaDao.java:14) ~[classes/:na]
        at br.com.logique.lsvraptorarq.controlador.CRUDControlador.salvar(CRUDControlador.java:69) ~[VRaptor-arq-1.5.1-SNAPSHOT.jar:na]
        at br.com.logique.bralarmexpert.controlador.AnaliseCRUDController.salvar(AnaliseCRUDController.java:169) ~[classes/:na]
        at br.com.logique.bralarmexpert.controlador.AnaliseAlarmesAnunciadosPorTempoController$Proxy$_$$_WeldClientProxy.salvar(Unknown Source) ~[classes/:na]
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_65]
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_65]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_65]
        at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_65]
        at net.vidageek.mirror.provider.java.PureJavaMethodReflectionProvider.invoke(PureJavaMethodReflectionProvider.java:38) [mirror-1.6.1.jar:na]
        ... 211 common frames omitted
    Caused by: org.hibernate.exception.ConstraintViolationException: could not execute statement
        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:129) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:211) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.hql.internal.ast.exec.BasicExecutor.doExecute(BasicExecutor.java:109) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.hql.internal.ast.exec.BasicExecutor.execute(BasicExecutor.java:78) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:445) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.engine.query.spi.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:379) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.internal.SessionImpl.executeUpdate(SessionImpl.java:1322) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.internal.QueryImpl.executeUpdate(QueryImpl.java:118) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.jpa.internal.QueryImpl.internalExecuteUpdate(QueryImpl.java:371) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
        at org.hibernate.jpa.spi.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:78) ~[hibernate-entitymanager-4.3.11.Final.jar:4.3.11.Final]
        ... 229 common frames omitted
    Caused by: org.h2.jdbc.JdbcSQLException: Referential integrity constraint violation: "FK_ANAL_RPPA_ATUALIZADA: PUBLIC.RESULTADO_PRE_PROCESSADO_ANALISE FOREIGN KEY(ANAL_CD_IDENTIFICADOR_ATUALIZADA) REFERENCES PUBLIC.ANALISE_ALARME(ANAL_CD_IDENTIFICADOR) (10032)"; SQL statement:
    update RESULTADO_PRE_PROCESSADO_ANALISE set ANAL_CD_IDENTIFICADOR_ATUALIZADA=? where ANAL_CD_IDENTIFICADOR_ATUALIZADA=? [23506-175]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:332) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.message.DbException.get(DbException.java:172) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.message.DbException.get(DbException.java:149) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.constraint.ConstraintReferential.checkRowOwnTable(ConstraintReferential.java:368) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:310) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.table.Table.fireConstraints(Table.java:894) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.table.Table.fireAfterRow(Table.java:911) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.command.dml.Update.update(Update.java:150) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.command.CommandContainer.update(CommandContainer.java:79) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.command.Command.executeUpdate(Command.java:253) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:154) ~[h2-1.3.175.jar:1.3.175]
        at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:140) ~[h2-1.3.175.jar:1.3.175]
        at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:410) ~[c3p0-0.9.5.1.jar:0.9.5.1]
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208) ~[hibernate-core-4.3.11.Final.jar:4.3.11.Final]
        ... 237 common frames omitted
+4
2

. entityManager, , . , em.persist(e) , . flush, .

. , : , , , -. COMMET flush-mode.

, :

https://vladmihalcea.com/a-beginners-guide-to-jpahibernate-flush-strategies/

+2

, , :

org.h2.jdbc.JdbcSQLException: Referential integrity constraint violation: "FK_ANAL_RPPA_ATUALIZADA: [...] 
SQL statement: update RESULTADO_PRE_PROCESSADO_ANALISE set [...] where [...]

EntityManager - "" [JPA Doc]. , EntityManager SQL-, , . // ( , , ).

, -, em.persist(newEntity), . update, updateReference (oldEntity, newEntity), - .

em.flush() [JPA Doc] em.persist(newEntity),

insert .

em.persist(newEntity); //id -> @GeneratedValue(strategy = GenerationType.AUTO)
em.flush(); // trigger insert in db
updateReference(oldEntity, newEntity);

- "" ( " " ). . , H2, [ H2].

+2

More articles:


All Articles