H2 Database Engine Cheat Sheet

Using H2

• H2 is open source, free to use and distribute.
• Download the jar file, Windows installer, or zip file.
• 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 if it does not yet exist.
• Closing the last connection closes a database.

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 dir

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

Server Mode
jdbc:h2:tcp://localhost/~/test user home dir
jdbc:h2:tcp://localhost//data/test absolute dir

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

Documentation

Reference: SQL grammar, functions, data types, tools.
Features: fulltext search, database encryption, read-only databases (in a zip or jar file), CSV files,

Using the JDBC API

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

Connection Pool

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

Start a Server

java -cp h2*.jar org.h2.tools.Server

Maven

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

Hibernate

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

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

TopLink and Glassfish

datasource classname: org.h2.jdbcx.JdbcDataSource
oracle.toplink.essentials.platform.database.H2Platform