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 if an embedded URL is used.
• 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:h2:~/test 'test' in the user home directory
jdbc:h2:/data/test 'test' in the directory /data
jdbc:h2:./test in the current(!) working directory

In-Memory
jdbc:h2:mem:test multiple connections in one process, database is removed when all connections are closed
jdbc:h2:mem:test;DB_CLOSE_DELAY=-1 multiple connections in one process, database in not removed when all connections are closed (may create a memory leak)
jdbc:h2:mem: unnamed private; one connection

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

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

Using the JDBC API

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

Connection Pool

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

Maven 2

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

Hibernate

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

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

TopLink and Glassfish

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