Batch insert with Hibernate and Oracle does not log which entity causes the ConstraintViolationException

Question

How can I determine from the error message from Hibernate / Oracle which entity caused the problem when batch inserting entities.

Is there a way setup Hibernate or Oracle to log this information?

 Caused by: org.hibernate.exception.ConstraintViolationException: could not execute batch
          at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:129)
          at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
          at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:124)
          at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:122)
          at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.doExecuteBatch(BatchingBatch.java:101)
          at org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl.execute(AbstractBatchImpl.java:161)
          at org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl.executeBatch(JdbcCoordinatorImpl.java:207)
          at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:390)
          at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:303)
          at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:349)
          at org.hibernate.event.internal.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:67)
          at org.hibernate.internal.SessionImpl.autoFlushIfRequired(SessionImpl.java:1166)
          at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1223)
          at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
          at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:268)
          ... 375 more
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint  (FHIR.SYS_C0022940074) violated
          at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:11190)
          at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:244)
          at org.jboss.jca.adapters.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:1077)
          at org.hibernate.engine.jdbc.batch.internal.BatchingBatch.performExecution(BatchingBatch.java:113)
          ... 386 more

Constraint is shown in the exception. To find it refer this question: http://stackoverflow.com/questions/2095415/oracle-find-a-constraint — mohit, Mar 20 '17 at 09:23
@mohit the problem is not finding the constraint that caused the problem, but to figure out which entity trigger the constraint — fharms, Mar 20 '17 at 09:30

Vlad Mihalcea · Accepted Answer · 2021-01-10T13:20:12.603

Assuming we have the following JPA entity:

@Entity(name = "Post")
@Table(name = "post")
public class Post {

    @Id
    private Long id;

    private String title;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }
}

The most important thing to note about this entity is that the entity identifier must be assigned manually.

Now, let's emulate a batch processing that will always end up throwing a ConstraintViolationException:

Session session = entityManager.unwrap(Session.class);
session.doWork(connection -> {

    try (PreparedStatement st = connection.prepareStatement(
            "INSERT INTO post (id, title) " +
            "VALUES (?, ?)")) {
        for (long i = 0; i < 5; i++) {
            st.setLong(1, i % 2);
            st.setString(2, String.format("High-Performance Java Persistence, Part %d", i));
            st.addBatch();
        }
        st.executeBatch();
    } catch (BatchUpdateException e) {
        LOGGER.info("Batch has managed to process {} entries", e.getUpdateCounts().length);
    }
});

Because we assign the identifier using the modulo operator, the third entry will fail to be inserted since it will clash with the firsts row that we saved into the database.

So, when running the aforementioned test case, this is what we get logged:

c.v.b.h.h.b.BatchExceptionTest - testInsertPosts
n.t.d.l.SLF4JQueryLoggingListener - Name:DATA_SOURCE_PROXY, Time:0, 
Success:False, 
Type:Prepared, 
Batch:True, 
QuerySize:1, 
BatchSize:5, 
Query:[
    "INSERT INTO post (id, title) VALUES (?, ?)"], 
    Params:[
        (0, High-Performance Java Persistence, Part 0), 
        (1, High-Performance Java Persistence, Part 1), 
        (0, High-Performance Java Persistence, Part 2), 
        (1, High-Performance Java Persistence, Part 3), 
        (0, High-Performance Java Persistence, Part 4)
    ]
c.v.b.h.h.b.BatchExceptionTest - Batch has managed to process 2 entries

So, to answer the question, you need to use the BatchUpdateException#getUpdateCounts method to know how many items you managed to processed successfully, and so the next one is the one that caused the failure.

Batch insert with Hibernate and Oracle does not log which entity causes the ConstraintViolationException

1 Answers1