0

I am using H2 Database to test my SpringBoot application. I do not use the file to store the data. instead I just use the In Memory datatabase. in the properties file, my JdbcUrl look like below:

spring.datasource.url=jdbc:h2:mem:;MODE=MSSQLServer;INIT=runscript from 'classpath:/schema.sql'\\;runscript from 'classpath:/data.sql'

Now When I run the tests, I have the following test scenario

  1. Add Some Entities in a table (This adds some rows in a table)
  2. search those entities by some criteria
  3. Do the assertion

Now, sometime this runs successfully, but sometimes what happens is, the search query returns empty list, which causes the test to be failed.

I tried to add print statements just to check whether my entities are getting inserted properly, so in the insert function. after each insertion, I run the below query

SELECT * FROM tableName;

Which returns correct list. means each insertion is inserting in the table correctly. Now in the search function, before running the actual search query, I run the same query again

SELECT * from tableName;

And Surprisingly this is returning empty also, which means there is no data in the table. Please suggest what I check for?

Abdul Mohsin
  • 1,253
  • 1
  • 13
  • 24
  • 2
    `jdbc:h2:mem:` is a connection-private database, usually you should use something like `jdbc:h2:mem:test`. You may also need to add `;DB_CLOSE_DELAY=-1` or something like it. – Evgenij Ryazanov Feb 04 '22 at 14:31
  • 1
    `jdbc:h2:mem` without a colon (`:`) is not a valid JDBC URL for H2, H2 throws an exception on attempt to use it. – Evgenij Ryazanov Feb 04 '22 at 14:36
  • 1
    maybe there is an issue with transactional behaviour. when is the data inserted? during a test execution? depending on your testconfig the data isn't committed, but rolled back... good luck – rloeffel Feb 16 '22 at 20:07
  • 1
    Are you using JPATransactionManager? And also not sure if you're doing an uncommitted read to the database. You may be overriding the existing value in the database. What is your transaction isolation level? If you can give this information its more light into resolving the issue. – Roe hit Feb 16 '22 at 22:56

1 Answers1

0

Pretty sure @Evgenij Ryazanov's comment is correct here.

Closing the last connection to a database closes the database. When using in-memory databases this means the content is lost.

After step 1) Add Some Entities in a table - is the connection closing? If so to keep the database open, add ;DB_CLOSE_DELAY=-1 to the database URL.

e.g.

spring.datasource.url=jdbc:h2:mem:;DB_CLOSE_DELAY=-1;MODE=MSSQLServer;INIT=runscript from 'classpath:/schema.sql'\\;runscript from 'classpath:/data.sql'

Note, this can create a memory leak!

see: http://www.h2database.com/html/features.html#in_memory_databases

Fraser
  • 15,275
  • 8
  • 53
  • 104
  • I already tried the datasource.url that you have mentioned. but in this case. the error I am facing is, when a test class executes, it alway run the script schema.sql and creates the tables. but when it tries to executes another test class. it again tries to run the init script. and that obviously fails because the tables already there. – Abdul Mohsin Feb 22 '22 at 16:35
  • I also asked another question for that. https://stackoverflow.com/questions/71116757/h2-database-with-multiple-test-classes-in-springboot – Abdul Mohsin Feb 22 '22 at 16:36