Advertise with us

Search Mastertheboss

Our eBooks

H2 Database cheatsheet

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

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

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. Indexes of temporary tables are kept in the main memory unless the temporary table is created using CREATE CACHED TABLE.

ENGINE − The ENGINE option is only required when custom table implementations are used.

NOT PERSISTENT − It is a modifier to keep the complete table data in-memory and 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

Alter table example 1

ALTER TABLE TEST ADD CONSTRAINT NAME_UNIQUE UNIQUE(NAME)

Alter table example 2

ALTER TABLE TEST ALTER COLUMN NAME RENAME TO TEXT

Alter table example 3

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;

JDBC Connection String

//Embedded
jdbc:h2:~/test 

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

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

Server start:

java -jar h2-1.4.197.jar 

Connect with JDBC:

Class.forName ("org.h2.Driver"); 

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

Statement st = conn.createStatement(); 

Stmt.executeUpdate("sql statement"); 

conn.close(); 

Maven dependency

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

Dialect

  <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>
© 2020 mastertheboss.com. All Rights Reserved.

Please publish modules in offcanvas position.