H2 Database Engine Cheat Sheet

Using H2

• H2 is open source, free to use and distribute.
• Download: jar, installer (Windows), zip.
• To start the H2 Console tool, double click the jar file, or run java -jar h2*.jar, h2.bat, or h2.sh.
• A new database is automatically created by default.
• Closing the last connection closes the database.

Documentation

Reference: SQL grammar, functions, data types, tools, API
Features: fulltext search, encryption, read-only (zip/jar), CSV, auto-reconnect, triggers, user functions

Database URLs

Embedded
jdbc:h2v1_4_199:~/test 'test' in the user home directory
jdbc:h2v1_4_199:/data/test 'test' in the directory /data
jdbc:h2v1_4_199:./test in the current(!) working directory

In-Memory
jdbc:h2v1_4_199:mem:test multiple connections in one process
jdbc:h2v1_4_199:mem: unnamed private; one connection

Server Mode
jdbc:h2v1_4_199:tcp://localhost/~/test user home dir
jdbc:h2v1_4_199:tcp://localhost//data/test absolute dir
Server start:java -cp *.jar org.h2v1_4_199.tools.Server

Settings
jdbc:h2v1_4_199:..;MODE=MySQL compatibility (or HSQLDB,...)
jdbc:h2v1_4_199:..;TRACE_LEVEL_FILE=3 log to *.trace.db

Using the JDBC API

Connection conn = DriverManager.
    getConnection("jdbc:h2v1_4_199:~/test");
conn.close();

Connection Pool

import org.h2v1_4_199.jdbcx.JdbcConnectionPool;
JdbcConnectionPool cp = JdbcConnectionPool.
    create("jdbc:h2v1_4_199:~/test", "sa", "sa");
Connection conn = cp.getConnection();
conn.close(); cp.dispose();

Maven 2

<dependency>
    <groupId>de.tracetronic.testguide</groupId>
    <artifactId>birt-legacy-h2</artifactId>
    <version>1.4.199</version>
</dependency>

Hibernate

hibernate.cfg.xml (or use the HSQLDialect):

<property name="dialect">
    org.hibernate.dialect.H2Dialect
</property>

TopLink and Glassfish

Datasource class: org.h2v1_4_199.jdbcx.JdbcDataSource
oracle.toplink.essentials.platform.
database.H2Platform