H2 Database cheatsheet

by

Here is a cheat sheet for H2 Database which shows some common Data definition, Data modification statements, JDBC Settings and H2 Database server administration.

Firstly, if you are looking for an introduction to using H2 Database in your applications, then check this tutorial: H2 database tutorial

DDL cheat sheet

Table Creation (Basic)

CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))

Table Creation (Advanced)

CREATE TABLE [ CACHED | MEMORY ] [ TEMP | [ GLOBAL | LOCAL ] TEMPORARY ] TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))

Where:

  • CACHED − Default. This means the number of rows is not limited by the main memory.
  • MEMORY − The memory tables are the default type for temporary tables. This means the memory tables should not get too large and the index data is kept in the main memory.
  • TEMPORARY − Temporary tables are deleted while closing or opening a database. Basically, temporary tables are of two types
    • GLOBAL type − Accessible by all connections.
    • LOCAL type − Accessible by the current connection. The default type for temporary tables is a global type.
  • ENGINE − The ENGINE option is available when using custom table implementations.
  • NOT PERSISTENT − If set, all rows are lost when the database is closed.
  • TRANSACTIONAL − It is a keyword that commits an open transaction and this command supports only temporary tables.

Create table if not exists

CREATE TABLE IF NOT EXISTS TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))

Create Schema

CREATE SCHEMA TEST_SCHEMA AUTHORIZATION SA

Add Constraint

ALTER TABLE TEST ADD CONSTRAINT NAME_UNIQUE UNIQUE(NAME)

Rename column

ALTER TABLE TEST ALTER COLUMN NAME RENAME TO TEXT

Drop column

ALTER TABLE TEST DROP COLUMN NAME

Drop Table

DROP TABLE TEST

Drop Sequence

DROP SEQUENCE sequenceName

Truncate Table

TRUNCATE TABLE tableName

Show tables

SHOW TABLES;

Create schemas

SHOW SCHEMAS;

Sequences

CREATE SEQUENCE SEQ1

# Current value for sequence
CALL CURRENT VALUE FOR SEQ1

# Next value for sequence
CALL NEXT VALUE FOR SEQ1

JDBC settings

JDBC Connection String

//Embedded
jdbc:h2:~/test 

//In-Memory
jdbc:h2:mem:test  

//Server Mode
jdbc:h2:tcp://localhost/~/test

How to start the H2 Database server:

java -jar h2-2.1.212.jar

Start Server and allow remote connections (-tcpAllowOthers) and expose the Web Console (-web):

java -cp h2-2.1.212.jar org.h2.tools.Server -tcp -web -tcpAllowOthers -ifNotExists

H2 Web Console is available at http://localhost:8082

h2 database cheatsheet

Example JDBC Connection with H2 Database

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
 
public class ConnectH2Example {
 
    public static void main(String[] args) throws SQLException {
        String jdbcURL = "jdbc:h2:mem:test";
 
        Connection connection = DriverManager.getConnection(jdbcURL); 
        System.out.println("Connected to H2 in-memory database.");
 
        String sql = "Create table person (ID int primary key, name varchar(50))";
         
        Statement statement = connection.createStatement();      
        statement.execute(sql);
              
        sql = "Insert into person (ID, name) values (1, 'John Smith')";
         
        int rows = statement.executeUpdate(sql);
         
        if (rows > 0) {
            System.out.println("Inserted a new row.");
        }
 
        connection.close();
 
    }
}

Maven dependency

Here is the latest version of H2 Database

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

Dialect

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

The current version of H2 Database is 2.3.232