Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

h2 in-memory tables, remote connection

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.

like image 878
Carolyn Avatar asked May 04 '10 19:05

Carolyn


People also ask

How do I access h2 in-memory database?

Accessing the H2 Console H2 database has an embedded GUI console for browsing the contents of a database and running SQL queries. By default, the H2 console is not enabled in Spring. Then, after starting the application, we can navigate to http://localhost:8080/h2-console, which will present us with a login page.

How do I connect to in-memory database?

Shareable in-memory databases In-memory databases can be shared between multiple connections by using Mode=Memory and Cache=Shared in the connection string. The Data Source keyword is used to give the in-memory database a name. Connection strings using the same name will access the same in-memory database.

What is h2 in-memory database?

H2 is an embedded, open-source, and in-memory database. It is a relational database management system written in Java. It is a client/server application. It stores data in memory, not persist the data on disk.


3 Answers

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!

like image 113
RealMan Avatar answered Sep 27 '22 14:09

RealMan


You might look at In-Memory Databases. For a network connection, you need a host and database name. It looks like you want one of 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.

like image 20
trashgod Avatar answered Sep 27 '22 14:09

trashgod


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)

like image 2
tddmonkey Avatar answered Sep 25 '22 14:09

tddmonkey