h2 in-memory tables, remote connection

Question

I am having problems with creating an in memory table, using H2 database, and accessing it outside of the JVM it is created and running in.

The documentation structures the url as jdbc:h2:tcp://<host>/mem:<databasename>

I've tried many combinations, but simply cannot get the remote connection to work. Is this feature working, can anyone give me the details of how they used this.

Solution 1

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!

Solution 2

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:db1
jdbc:h2:tcp://127.0.0.1/mem:db1

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

Solution 3

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)

Comments

[tomfumb]

I think DB_CLOSE_DELAY=-1 should be on the in-memory connection string, rather than the TCP connection string, so that the command that creates the db is responsible for controlling its lifetime. Ideally the external connection should be ignorant of H2's implementation details and should simply ask for data

[tddmonkey]

It's been quite a while since I've used H2, but the rest of my answer would indicate I meant the TCP connection