H2 in-memory database. Table not found

Question

I've got a H2 database with URL "jdbc:h2:test". I create a table using CREATE TABLE PERSON (ID INT PRIMARY KEY, FIRSTNAME VARCHAR(64), LASTNAME VARCHAR(64));. I then select everything from this (empty) table using SELECT * FROM PERSON. So far, so good.

However, if I change the URL to "jdbc:h2:mem:test", the only difference being the database is now in memory only, this gives me an org.h2.jdbc.JdbcSQLException: Table "PERSON" not found; SQL statement: SELECT * FROM PERSON [42102-154]. I'm probably missing something simple here, but any help would be appreciated.

Answer 1

DB_CLOSE_DELAY=-1

hbm2ddl closes the connection after creating the table, so h2 discards it.

If you have your connection-url configured like this

jdbc:h2:mem:test 

the content of the database is lost at the moment the last connection is closed.

If you want to keep your content you have to configure the url like this

jdbc:h2:mem:test;DB_CLOSE_DELAY=-1 

If doing so, h2 will keep its content as long as the vm lives.

Notice the semicolon (;) rather than colon (:).

See the In-Memory Databases section of the Features page. To quote:

By default, closing the last connection to a database closes the database. For an in-memory database, this means the content is lost. To keep the database open, add ;DB_CLOSE_DELAY=-1 to the database URL. To keep the content of an in-memory database as long as the virtual machine is alive, use jdbc:h2:mem:test;DB_CLOSE_DELAY=-1.

Answer 2

I know this was not your case but I had the same problem because H2 was creating the tables with UPPERCASE names then behaving case-sensitive, even though in all scripts (including in the creation ones) i used lowercase.

Solved by adding ;DATABASE_TO_UPPER=false to the connection URL.