Spring Test Recipes

dumping-h2-contents-on-every-test-class.md

How to dump H2 database contents on every Spring test class

Sometimes, you get into weird situations when test class passes when run alone, but fails, when run as part of a suite (i.e. alongside some other tests)

In order to get better visibility into what is happening, you might resort to dumping the whole H2 database content (or just selected tables)

For that, we can create a TestExecutionListener like this:

@Slf4j
public class H2DumpingTestExecutionListener implements TestExecutionListener {

  @Override
  public void afterTestClass(TestContext testContext) throws Exception {
    logger.info("We've finished with test class {}", testContext.getTestClass().getSimpleName());
    if (testContext.hasApplicationContext()) {
      dumpDb(testContext, "AFTER");
    }
  }

  @Override
  public void beforeTestClass(TestContext testContext) throws Exception {
    logger.info("We're starting test class {}", testContext.getTestClass().getSimpleName());
    if (testContext.hasApplicationContext()) {
      dumpDb(testContext, "BEFORE");
    }
  }

  private void dumpDb(TestContext testContext, String mode) throws SQLException, IOException {
    ApplicationContext applicationContext = testContext.getApplicationContext();
    DataSource dataSource = applicationContext.getBean("orderdataDataSource", DataSource.class);
    Statement statement = dataSource.getConnection().createStatement();
    Path path = Files.createTempFile(testContext.getTestClass().getSimpleName() + "_" + mode, "sql");
    String fileName = path.toString();
    logger.info("Will dump H2 tables to {}", fileName);
    statement.executeQuery("SCRIPT TO '" + fileName + "' TABLE RA_DOCUMENTS, RA_DOCUMENT_LINKS");
  }
}

and then register it via META-INF\spring.factories files (put it into the root of test\resources folder), like this:

org.springframework.test.context.TestExecutionListener=\
  org.company.jpa.H2DumpingTestExecutionListener

and then, once run you should be able to see in the logs something like

2022-01-05 13:44:47.220 INFO  [main] c.k.i.e.p.j.H2DumpingTestExecutionListener Will dump H2 tables to C:\Users\62mkv\AppData\Local\Temp\IlsordRepositoryIntegrationTest_AFTER3966808119959397638sql at org.company.jpa.H2DumpingTestExecutionListener.dumpDb(H2DumpingTestExecutionListener.java:42) 

and then the file itself could look like this:

CREATE MEMORY TABLE "PUBLIC"."RA_DOCUMENT_LINKS"(
    "ID" BIGINT NOT NULL,
    "CUSTOMER_CODE" VARCHAR(255),
    "LINKED_AT" TIMESTAMP,
    "LINKED_ENTITY_TYPE" VARCHAR(255),
    "LINKED_ENTITY_NUMBER" VARCHAR(255),
    "DOCUMENT_ID" BIGINT
);   
ALTER TABLE "PUBLIC"."RA_DOCUMENT_LINKS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_9A" PRIMARY KEY("ID");           
-- 1 +/- SELECT COUNT(*) FROM PUBLIC.RA_DOCUMENT_LINKS;       
INSERT INTO "PUBLIC"."RA_DOCUMENT_LINKS" VALUES
(1, 'YYXXX79', NULL, 'ORDER', 'ENCC_OCS-3747900', 1);        
CREATE MEMORY TABLE "PUBLIC"."RA_DOCUMENTS"(
    "ID" BIGINT NOT NULL,
    "CUSTOMER_CODE" VARCHAR(255) NOT NULL,
    "DOCUMENT_EXTENSION" VARCHAR(255),
    "DOCUMENT_NUMBER" VARCHAR(255),
    "DOCUMENT_TYPE" VARCHAR(255),
    "INSERT_DATE" TIMESTAMP,
    "INSERT_TYPE" VARCHAR(255),
    "MODIFIED_DATE" TIMESTAMP,
    "MODIFYING_USER" VARCHAR(255),
    "SOURCE_ID" BINARY NOT NULL,
    "UPLOAD_USER" VARCHAR(255),
    "VERIFICATION_STATUS" VARCHAR(255)
);         
ALTER TABLE "PUBLIC"."RA_DOCUMENTS" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_CA" PRIMARY KEY("ID");
-- 1 +/- SELECT COUNT(*) FROM PUBLIC.RA_DOCUMENTS;            
INSERT INTO "PUBLIC"."RA_DOCUMENTS" VALUES
(1, 'YYXXX79', NULL, '100', '521', NULL, NULL, NULL, NULL, X'33fe9ace49594cfa946531bf9e111760', NULL, NULL);      
CREATE UNIQUE INDEX "PUBLIC"."UNQ_RA_DOCUMENT_SOURCE_ID" ON "PUBLIC"."RA_DOCUMENTS"("SOURCE_ID");             
ALTER TABLE "PUBLIC"."RA_DOCUMENTS" ADD CONSTRAINT "PUBLIC"."UK_HTSGLWPE5U4UEDO6G4RANUG7N" UNIQUE("SOURCE_ID");               
ALTER TABLE "PUBLIC"."RA_DOCUMENT_LINKS" ADD CONSTRAINT "PUBLIC"."FKNLJN02BOKCV9H17L63925VH2D" FOREIGN KEY("DOCUMENT_ID") REFERENCES "PUBLIC"."RA_DOCUMENTS"("ID") NOCHECK;   

References:

1. The Spring TestExecutionListener
2. H2 SCRIPT TO reference