H2 (DBMS)

H2 is a relational database management system written in Java. It can be embedded in Java applications or run in the client-server mode.[1] The disk footprint (size of the jar file) is about 1.5 MB.[2]

The software is available as open source software under modified versions of the Mozilla Public License or the original Eclipse Public License. The modification of the MPL is a shorter file header and the license name.

A subset of the SQL (Structured Query Language) standard is supported. The main programming APIs are SQL and JDBC, however the database also supports using the PostgreSQL ODBC driver by acting like a PostgreSQLserver.[4]

It is possible to create both in-memory tables, as well as disk-based tables. Tables can be persistent or temporary. Index types are hash table and tree for in-memory tables, and b-tree for disk-based tables. All data manipulation operations are transactional. Table level locking andmultiversion concurrency control are implemented. The 2-phase commit protocol is supported as well, but no standard API for distributed transactions is implemented. The security features of the database are: role based access rights, encryption of the password using SHA-256and data using the AES or the Tiny Encryption Algorithm, XTEA. The cryptographic features are available as functions inside the database as well. SSL / TLS connections are supported in the client-server mode, as well as when using the console application.

Two full text search implementations are included, a native implementation and one using Lucene.

A simple form of high availability is implemented: when used in the client-server mode, the database engine supports hot failover (this is commonly known as clustering). However, the clustering mode must be enabled manually after a failure.[5]

The database supports protection against SQL injection by enforcing the use of parameterized statements. In H2, this feature is called ‘disabling literals’.[6]

Since version 1.1.111, H2 in-memory database can run inside the Google App Engine.[

 

Also see: http://www.h2database.com/html/main.html

Advertisements

in-memory H2 instance

 

examining H2 by a web browser

The correct configuration of H2 for interactive web query is something of a black art. The approach described here may not be the only way to do it, it may not even be the best way, but it does work.

The “IR Standalone Pipeline” launch configuration in /DslInboundRouter.App.Test/src/eclipse/IR – StandalonePipeline.launch provides a working example via the spring configuration /DslInboundRouter.App.Test/src/java-test-src/dsl/inboundrouter/config/standalone-pipeline-bootstrap.xml

Create the in-memory H2 instance

From /DslInboundRouter.App.Test/src/java-test-src/dsl/inboundrouter/config/datasource-h2.xml

<bean id=”dslDataSource” class=”msjava.dbpool.datasource.H2DataSource”

p:databaseName=”mem:1;AUTOCOMMIT=ON;LOCK_MODE=1;MVCC=true”

p:performExpiration=”false”

p:minIdle=”10″ />

This bean creates an in-memory H2 instance, that does not support any TCP connectivity, so is accessible only from within the JVM

Create an H2 TCP server

From /DslInboundRouter.App.Test/src/java-test-src/dsl/inboundrouter/config/webserver-h2.xml

<bean id=”h2TcpServer”

factory-method=”createTcpServer”

init-method=”start” destroy-method=”stop” lazy-init=”false”>

<constructor-arg value=”-tcp,-tcpAllowOthers,true,-tcpPort,8043″/>

</bean>

This creates an instance of an H2 TCP server on port 8043 that can accept H2 JDBC client connections from other processes. The instance is not linked to the database instance we have already created – that is done at connect time according to the JDBC connection string used.

The -tcpAllowOthers option in the constructor arguments means that connections will be accepted from other hosts. Default behaviour is to accept connections from the current host only.

Create an H2 Web server

Also from /DslInboundRouter.App.Test/src/java-test-src/dsl/inboundrouter/config/webserver-h2.xml

<bean id=”h2WebServer”

factory-method=”createWebServer”

init-method=”start” destroy-method=”stop” lazy-init=”false”>

<constructor-arg value=”-web,-webAllowOthers,-webPort,11111″/>

</bean>

This creates an instance of an H2 web server on port 11111. Again, there is no implicit link between this web server and the tcp server defined above. The -webAllowOthers allows connections from browsers on other hosts; by default only local connections are accepted.

Using the browser

Browse to http://localhost:11111/ to connect to the H2 Web server, this will present a login screen

Enter jdbc:h2:tcp://localhost:8043/mem:1 as the JDBC Url to connect to.

If browsing from another host, you would still use localhost in the JDBC url (but not the http url) as the JDBC url is resolved relative to the location of the H2 web server.