h2 in-memory tables, remote connection

None of the solutions mentioned so far worked for me. Remote part just couldn't connect.

According to H2's official documentation:

To access an in-memory database from another process or from another computer, you need to start a TCP server in the same process as the in-memory database was created. The other processes then need to access the database over TCP/IP or TLS, using a database URL such as: jdbc:h2:tcp://localhost/mem:db1.

I marked the crucial part of the text in bold.

And I found a working solution at this guy's blog:

The first process is going to create the DB, with the following URL:

jdbc:h2:mem:db1

and it’s going to need to start a tcp Server:

org.h2.tools.Server server = org.h2.tools.Server.createTcpServer().start();

The other processes can then access your DB by using the following URL:

"jdbc:h2:tcp://localhost/mem:db1"

And that is it! Worked like a charm!

You might look at In-Memory Databases. For a network connection, you need a host and database name. It looks like you want on elf these:

jdbc:h2:tcp://localhost/mem:db1jdbc:h2:tcp://127.0.0.1/mem:db1

Complete examples may be found here, here and here; related examples are examined here.

Having just faced this problem I found I needed to append DB_CLOSE_DELAY=-1 to the JDBC URL for the tcp connection. So my URLs were:

• In Memory : jdbc:h2:mem:dbname
• TCP Connection : jdbc:h2:tcp://localhost:9092/dbname;DB_CLOSE_DELAY=-1

From the h2 docs:

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.

Not including DB_CLOSE_DELAY=-1 means that I cannot connect to the correct database via TCP. The connection is made, but it uses a different version to the one created in-memory (validated by using the IFEXISTS=true parameter)