H2 database

Normally H2 database is used for junit and integration test. But in some cases, for example for PoC project, we may want to use an embedded mode database, then H2 database will be a good choice to be use.

For H2 embedded mode, an application opens a database from within the same JVM using JDBC. This is the fastest and easiest connection mode.

H2 embedded mode includes two type of setting:

1. Embedded (local) connection, the data will be saved into a local file:

For exampleL

 connection	jdbc:h2:[file:][<path>]<databaseName>
jdbc:h2:~/test
jdbc:h2:file:/data/sample
jdbc:h2:file:C:/data/sample (Windows only)

2. Embedded In-memory db:

For example:

jdbc:h2:mem:<databaseName>
jdbc:h2:mem:test_mem

For H2 DB driver in the pom.xml

In the properties section, add the following line.

              <version.h2>1.3.176</version.h2>  

In the dependencies section, add the following lines (if only use h2 for junit test, add scope as “test” only).

        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>${version.h2}</version>
        </dependency>

Set datacource in datasource.yml config file:

#--------------------------------------------------------------------------------
# datasource.yml
#--------------------------------------------------------------------------------

datasource.H2DataSource:
  DriverClassName: org.h2.jdbcx.JdbcDataSource
  jdbcUrl: jdbc:h2:mem:testdb;INIT=runscript from 'classpath:scripts/schema.sql';
  username: sa
  password: sa
  maximumPoolSize: 2
  connectionTimeout: 500

Inject the datasource in service.yml or values.yml:

  - com.networknt.db.GenericDataSource:
      - com.networknt.db.H2DataSource:
          - java.lang.String: H2DataSource

Enable H2 db console in light4j API

Sometimes we need open DB console to verify the database. To start embedded mode h2 console, we can simply create a StartupHook:

Create a simple StartupHook

public class H2WebServerStartupHook implements StartupHookProvider {
    private static Logger logger = LoggerFactory.getLogger(H2WebServerStartupHook.class);
    @Override
    public void onStartup() {
        try {
            org.h2.tools.Server.createWebServer().start();
        } catch (SQLException e) {
            logger.error("Cannot start H2 web server!");
        }
    }
}

Add the StartupHook to service.yml:

  # StartupHookProvider implementations, there are one to many and they are called in the same sequence defined.
  - com.networknt.server.StartupHookProvider:
    - com.mservicetech.campsite.H2WebServerStartupHook

After start the API server, the H2 web console will be available in 8082 port (Note: we can change port using -webPort flag on h2 server start):

http://localhost:8082