Search code examples
databaseinserth2persist

In-memory H2 database, insert scripts not persisting

I'm tying to use H2 as an in-memory database for my unit testing. I'm wiring everything together through Spring. Everything seems to work fine. The DB loads up, the ddl sql file runs, as does the dml sql (or so it seems).

I can run a test that creates a record in the database, then retrieves and validates it was appropriately persisted. So this tells me the ddl scripts ran fine.

The issue is when I try to receive a record that was inserted via the dml script, i get nothing.

I tried putting the same insert statement in the file twice and do indeed receive a unique constraint error, which tells me they are running...but for some reason not persisting.

DDL:

DROP TABLE IF EXISTS `schema`.`region` ;CREATE  TABLE IF NOT EXISTS `schema`.`region` (
  `region_id` INT(11) NOT NULL ,
  `name` VARCHAR(56) NOT NULL ,
  `description` VARCHAR(512) NULL DEFAULT NULL ,
  PRIMARY KEY (`region_id`) );

DML:

INSERT INTO `schema`.`region` (`region_id`, `name`, `description`) VALUES (1001, 'TEST', 'TESTING');

URL:

jdbc:h2:file:db_test;MODE=MYSQL;INIT=create schema if not exists test_db\\;runscript from 'classpath:test_ddl.sql'\\;runscript from 'classpath:test_dml.sql'

Any help would be helpful.

thanks

Solution

  • See Where are the Database Files Stored? in the FAQ. With the database URL you used, jdbc:h2:file:db_test, the files are stored in the current working directory. Depending on where you start your application, this is a different place, so a different database is used.

    I suggest to use jdbc:h2:~/db/test_db... instead.

    I'm tying to use H2 as an in-memory database

    You are actually using a persistent database, see the database URL overview.