How to access in memory h2 database of one spring boot application from another spring boot application

Question

In my project, i have created 3 spring boot application. First spring boot application has h2 embedded database. Now i want to access this database from my 2nd and 3rd spring boot application directly without writing any services to get this data. So can anyone tell me how can i achieve this?

Potable

Raj, don't forget to accept an answer that helped you... – Algology

Answer 1

You can setup H2 Server as Spring Bean.

First edit pom.xml - delete <scope>runtime</scope> from h2 dependency:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
</dependency>

Then add H2 server bean to SpringBootApplication or Configuration class:

@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    /**
     * Start internal H2 server so we can query the DB from IDE
     *
     * @return H2 Server instance
     * @throws SQLException
     */
    @Bean(initMethod = "start", destroyMethod = "stop")
    public Server h2Server() throws SQLException {
        return Server.createTcpServer("-tcp", "-tcpAllowOthers", "-tcpPort", "9092");
    }
}

Last - edit application.properties - set the name of the database:

spring.datasource.url=jdbc:h2:mem:dbname
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.jpa.hibernate.ddl-auto=create

Then you can connect to this H2 Server from outside (e.g. to your application with H2 DB) using this connection:

jdbc:h2:tcp://localhost:9092/mem:dbname

As a bonus using this url you can connect to the database of your app right from your IDE.

UPDATE

There is a chance of getting an error when trying to connect to the H2 for Spring Boot app of 1.5.x version. In this case just change a version of H2 to previous one, for example:

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>1.4.193</version>
</dependency>

UPDATE 2

If you need to run several apps with H2 simultaneously on the same host you should set the different H2 ports on them in Server.createTcpServer mothod, for example: 9092, 9093, etc..

// First App
@Bean(initMethod = "start", destroyMethod = "stop")
public Server h2Server() throws SQLException {
    return Server.createTcpServer("-tcp", "-tcpAllowOthers", "-tcpPort", "9092");
}

// Second App
@Bean(initMethod = "start", destroyMethod = "stop")
public Server h2Server() throws SQLException {
    return Server.createTcpServer("-tcp", "-tcpAllowOthers", "-tcpPort", "9093");
}

Then you can connect to the H2 DB of these apps with following urls:

App1 H2: jdbc:h2:tcp://localhost:9092/mem:dbname
App2 H2: jdbc:h2:tcp://localhost:9093/mem:dbname

Algology

@Salman Server is the class of the org.h2.tools package from com.h2database:h2 artifact - see dependency. And don't forget to remove <scope>runtime</scope> from it. – Algology

Thanks. Btw, in the configuration file of other applications which you try to connect from, you should set spring.datasource.url to jdbc:h2:tcp://localhost:9092/mem:dbname – Bestraddle

@sedooe Off cause. I wrote that - see last lines of the answer. Or you mean something different?.. – Algology

ah sorry, didn't see that line :) – Bestraddle

I followed the above instructions but I get errors. Should different spring boot applications be configured & started with different jdbc urls? I started the first application with jdbc:h2:mem:AZ as the url while the second with jdbc:h2:tcp://localhost:9092/mem:AZ keeping the above bean configuration same for both. The second application fails to start with error as "Exception opening port "9092" (port may be in use)". Could you please document the config required by two applications separately? Thanks ! – Nutrition

@Algology - I wasn't asking of running two h2 databases on the same machine. My question is to understand the config of starting two application that refer to a common h2 database. thanks! – Nutrition

@AndyDufresne You need only the first app to have a database, and the second app to connect to the the first app db? In this case you should setup h2Server bean only in the first app. First app should connect to own db with url jdbc:h2:mem:dbname, and second app should connect to the db with url jdbc:h2:tcp://localhost:9092/mem:dbname. – Algology

@Algology - yes, I did exactly that but my second app fails with "Exception opening port "9092" (port may be in use), cause: "java.net.BindException: Address already in use: JVM_Bind" [90061-192]". Note that I have removed the bean configuration for the second app. Spring boot version is 1.3.6. One thing I observed is - after second app fails with the above error, connecting to the database via h2-console also fails which works before starting the second app. – Nutrition

@AndyDufresne It's strange... What if you change the server port in the first app h2Server bean to 9093?.. – Algology

@Algology - same error. For reference i am starting two instances of this application - github.com/stormpath/stormpath-spring-boot-jpa-example but changing the configuration for both the runs. – Nutrition

@AndyDufresne please check my example - all works as expected: first start FirstApplication (serves 9092), then start SecondApplication (works with 9092). – Algology

Let us continue this discussion in chat. – Nutrition

One important difference i noticed is that the two apps in your commit are separate maven projects. I have been starting two applications by executing two java classes inside the same maven project though configured differently by different properties files – Nutrition

Also for me it is important that I run the application through two java classes instead of separate java projects since I want to mimic two app servers serving one web application. I do not think though that these could be the root cause of the problem – Nutrition

Thank you, but now spring boot ignores my data.sql on startup – Convivial

It works but data.sql doesnt add my test data? – Biblicist

You rock!.. Thanks a lot for these wonderful explanations, voted up ;) – Matrilateral

Answer 2

You can run H2 in the server mode.

import org.h2.tools.Server;
...
// start the TCP Server
server = Server.createTcpServer("-tcpAllowOthers").start();
...
// stop the TCP Server
server.stop();

Usage: java org.h2.tools.Server 
When running without options, -tcp, -web, -browser and -pg are started.
Options are case sensitive. Supported options are:
[-help] or [-?]         Print the list of options
[-web]                  Start the web server with the H2 Console
[-webAllowOthers]       Allow other computers to connect - see below
[-webDaemon]            Use a daemon thread
[-webPort ]       The port (default: 8082)
[-webSSL]               Use encrypted (HTTPS) connections
[-browser]              Start a browser connecting to the web server
[-tcp]                  Start the TCP server
[-tcpAllowOthers]       Allow other computers to connect - see below
[-tcpDaemon]            Use a daemon thread
[-tcpPort ]       The port (default: 9092)
[-tcpSSL]               Use encrypted (SSL) connections
[-tcpPassword ]    The password for shutting down a TCP server
[-tcpShutdown ""]  Stop the TCP server; example: tcp://localhost
[-tcpShutdownForce]     Do not wait until all connections are closed
[-pg]                   Start the PG server
[-pgAllowOthers]        Allow other computers to connect - see below
[-pgDaemon]             Use a daemon thread
[-pgPort ]        The port (default: 5435)
[-properties ""]   Server properties (default: ~, disable: null)
[-baseDir ]        The base directory for H2 databases (all servers)
[-ifExists]             Only existing databases may be opened (all servers)
[-trace]                Print additional trace information (all servers)
The options -xAllowOthers are potentially risky.
For details, see Advanced Topics / Protection against Remote Access.
See also http://h2database.com/javadoc/org/h2/tools/Server.html

How to use h2 as a server

Similar question 1

Similar question 2

Ginaginder