H2 - can't write to database in 'after insert' trigger

Question

I am 300% sure the id field is autogenerated just fine, as this was perfectly working before adding the trigger.

I have an entity which extends a base entity with an autogenerated id field:

@MappedSuperclass
public abstract class BaseEntity {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

I have registered a trigger for that entity in H2:

create trigger after_subtest_result_insert after insert on subtest_result
for each row call "package.path.SubtestResultTrigger";

The trigger itself:

public class SubtestResultTrigger implements Trigger {

  private static final int EQUIPMENT_ID = 5;

  @Override
  public void init(Connection conn, String schemaName, String triggerName,
      String tableName, boolean before, int type) {
  }

  @Override
  public void fire(Connection conn, Object[] oldRow, Object[] newRow)
      throws SQLException {
    try (PreparedStatement ps = conn.prepareStatement(
        "update equipment e set " +
        (...)
       )
    ) {
      ps.setObject(1, newRow[EQUIPMENT_ID]);
      ps.setObject(2, newRow[EQUIPMENT_ID]);

      ps.executeUpdate();
    }
  }

  @Override
  public void close() throws SQLException {
  }

  @Override
  public void remove() throws SQLException {
  }
}

When I uncomment ps.executeUpdate();, it breaks with HibernateException: The database returned no natively generated identity value.

It seems like somewhere under the hood H2 gets the generated keys from the last prepared statement that was executed, rather than the first one, since everything is fine when the update in the trigger is not run. Is there any fix or workaround for this?

EDIT: I realize what's happening, I still have zero idea how to work around it. Digging into the code, the H2 driver has a session-wide GeneratedKeys object, that is cleared every time the keys are requested, and presumably overwritten every time a PreparedStatement is executed in the same session. This makes it literally impossible to have a post insert trigger that writes anything at all to the database. I'll write a bug report, in the meantime I'll have to either not use triggers (hard in this context, I'm already only resorting to triggers because every alternative is even worse) or just ditch H2 entirely, which I'm not sure I can since the stack is out of my hands.

Answer 1

I had the same problem but found a different workaround, one you might like better. Like you, I'm using Hibernate, and Spring.

I was able to make the H2 Trigger work fully, but the key was to use Hibernate within the Trigger for all of its database manipulation. Here's how I did it:

1. Used this technique to create a static method from which to access the Spring ApplicationContext. I did it this way:

   @Service
   public class SpringContextHook {
   
       private static ApplicationContext context;
   
       @Autowired
       public SpringContextHook(ApplicationContext inContext) {
           context = inContext;
       }
   
       public static ApplicationContext getContext() {
           return context;
       }
   }
   

2. Inside the H2 Trigger, I get the currently active Session by digging around a little in Spring JPA:

   private Session getHibernateSession() {
       ApplicationContext context = SpringContextHook.getContext();
       EntityManagerFactory emf = (EntityManagerFactory) context.getBean("entityManagerFactory");
       EntityManager entityManager = SharedEntityManagerCreator.createSharedEntityManager(emf, null, true);
       return entityManager.unwrap(Session.class);
   }
   

3. Use Hibernate @Entity objects to perform the update, using getHibernateSession().save(entity) to save them.
4. I had to make sure to not take action on the Hibernate object that is the reason the trigger was fired.

I can give more details if you're interested in pursuing this strategy and need more help.

Answer 2

EDIT: I went with arogos's solution, which is better, afterwards. I'm leaving this here in case someone needs to do a more esoteric update where Hibernate won't cut it.

While I never managed to find a true solution, I found a workaround in the context of spring boot, which works as long as you need to or don't mind applying the trigger also on update. It has many of the downsides that forced me to the last resort of using triggers in the first place, but at least this minimizes the scope of the issue to only H2 and only inserts, in a project that must support two other DBMSes where the trigger approach works just fine.

I basically created an update trigger, a post insert handler that forces a bogus update if the DBMS is H2, and a comment reminding that every insert operation that does not come from a REST endpoint request must invoke the handler manually. This behaviour was in fact the reason I endep up resorting to triggers, after trying JPA's @PostInsert and @PostUpdate and getting errors which stem from attempting to read from the table you just wrote to - my understanding of it was that there's a reading lock so your @PostInsert/@PostUpdate cannot read from the table in any way.

The trigger in my src/main/resources/data-h2.sql:

-- creating schema changes in data file because schema.sql overwrites JPA schema intialization
-- https://github.com/spring-projects/spring-boot/issues/9048
create trigger after_subtest_result_update after update on subtest_result
for each row call "com.siemens.mftool.dialects.H2SubtestResultTrigger";

The handler:

package com.siemens.mftool.entity.handlers;

import com.siemens.mftool.entity.SubtestResult;
import com.siemens.mftool.repositories.SubtestResultRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.data.rest.core.annotation.HandleAfterCreate;
import org.springframework.data.rest.core.annotation.RepositoryEventHandler;
import org.springframework.stereotype.Component;

/** workaround for H2 post insert triggers not working because of improperly
 *  handled generated keys
 *  must be called manually if the repo invocation does not come from a REST
 *  request
 */
@Component
@RepositoryEventHandler(SubtestResult.class)
public class H2WorkaroundSubtestResultHandler {

  private final SubtestResultRepository subtestResultRepository;

  @Value("${spring.datasource.platform}")
  private String platform;

  @Autowired
  public H2WorkaroundSubtestResultHandler(SubtestResultRepository subtestResultRepository) {
    this.subtestResultRepository = subtestResultRepository;
  }


  @HandleAfterCreate
  public void handleAfterCreate(final SubtestResult subtestResult) {
    if("h2".equals(platform)) {
      subtestResultRepository.h2Workaround(subtestResult);
    }
  }
}

The repository method:

  // force an update to the newly inserted subtestResult so the
  // after-update trigger is triggered
  @Modifying
  @Query(nativeQuery = true, value =
      "update subtest_result " +
      "set id = :#{ #subtestResult.id } " +
      "where id = :#{ #subtestResult.id } ")
  void h2Workaround(SubtestResult subtestResult);

How the call looks when done programatically:

h2WorkaroundSubtestResultHandler.handleAfterCreate(subtestResult);

Still a pain point, but it's a least a pain point rather a whole pain line.