H2-in memory Database

Lesson - 305.6 - H2 In-Memory Database

Table of Contents

  • In-Memory Database

  • H2 In-Memory Database

  • H2 Database - Execution Mode

  • Install H2-In Memory Database

  • H2 Database Commands

  • H2 Database Maven Dependency

Learning Objectives

This presentation is designed for all those software professionals who would like to learn how to use H2 database in simple and easy steps. This presentation will give you a good overall understanding of the basic concepts of H2 database. By the end of this lesson, learners will be able to:

  • Describe In-Memory Database.

  • Install and setup H2 in memory Database.

  • Run and execute basic SQL queries.

Prerequisites

H2 database primarily deals with relational data. Hence, you should have a good understanding of the concepts of databases in general; especially, relational database management system (RDBMS) concepts before going ahead with this presentation.

In-Memory Database

Typical databases require a great deal of setup. For example, with Oracle or MySQL databases, you would need to perform the following:

  • Install the Database, set up a Schema, and set up the tables.

  • Populate the data.

  • Connect the application to the database by setting up a data source and a lot of other code.

Scenario 1 - Let’s consider a situation where you would want to perform a quick POC. Using a traditional database involves much overhead. Scenario 2 - Consider your unit tests:

  • You do not want them to fail when some data/schema in the database changes.

  • You want to be able to run them parallel. Multiple developers might be running the tests in parallel.

In these kind of scenarios, an in-memory database provides an ideal solution. An in-memory database is created when an application starts up and is destroyed when the application is stopped.

Advantages-

  • Zero project setup or infrastructure.

  • Zero configuration.

  • Zero maintenance.

  • Easy to use for learning, POCs, and unit tests.

  • Spring Boot provides simple configuration to switch between a real database and an in-memory database such as H2.

H2 In-Memory Database

  • H2 is one of the popular in-memory databases. Remember: An in-memory database is live only during the time of execution of the application because data will not persist on the disk.

  • H2 is a relational database management system written in Java. It can be embedded in Java applications or run in the client-server mode; but it is not a persistent database.

  • H2 is an efficient way to learn a framework.

  • This database can be used in embedded or server mode. Following are the main features of the H2 database:

    • Extremely fast, open-source, JDBC API.

    • Available in embedded and server modes; in-memory databases.

    • Browser-based console application.

    • Web console to maintain the database.

H2-In Memory Database - Execution Mode

H2 in-memory database can be executed in three modes:

  1. Embedded mode.

  2. Server mode.

  3. Mixed mode.

Embedded Mode: In embedded mode, the database performance is better. Because the application and database are executed in the same JVM, the Java application uses JDBC to access H2 DB. Data can be persisted to data file also, but at the same time, only one client can connect to the H2 DB.

Server Mode: In server mode, the Java application and H2 database run in different JVM or machines. H2 database is executed in an H2 server. Server mode supports multiple DB connections.

Mixed Mode: The mixed mode is a combination of the embedded and the server mode. The first application that connects to a database does so in the embedded mode, but also starts a server so that other applications (running in different processes or virtual machines) can concurrently access the same data. The local connections are as fast as if the database is used in just the embedded mode, while the remote connections are a bit slower. The server can be started and stopped from within the application (using the server API), or automatically (automatic mixed mode). When using the automatic mixed mode, all clients that want to connect to the database (no matter if it is a local or remote connection) can do so using the exact same database URL.

Lab - Install H2-In Memory Database

Find the GLAB 305.6.1 - Install H2-in Memory Database on Canvas under the Assignment section.

H2 Database Commands

Popular H2 database commands, which are similar to SQL:

Commands (Data Manipulation)

  • SELECT

  • INSERT

  • UPDATE

  • DELETE

Commands (Data Definition)

  • ALTER INDEX RENAME

  • ALTER SCHEMA RENAME

  • ALTER SEQUENCE

  • ALTER TABLE ADD

  • ALTER TABLE ADD CONSTRAINT

  • ALTER TABLE RENAME CONSTRAINT

  • ALTER TABLE ALTER COLUMN

  • ALTER TABLE DROP COLUMN

  • ALTER TABLE DROP CONSTRAINT

  • ALTER TABLE SET

  • ALTER TABLE RENAME COMMENT

  • CREATE AGGREGATE

  • CREATE CONSTANT

  • CREATE ROLE

  • CREATE SCHEMA

  • CREATE SEQUENCE

  • CREATE TABLE

  • CREATE TRIGGER

  • CREATE USER

  • DROP ALIAS

  • DROP ALL OBJECTS

  • DROP CONSTANT

  • DROP SCHEMA

  • DROP TABLE

  • DROP TRIGGER

  • DROP USER

  • TRUNCATE TABLE

H2 Database Maven Dependency

<dependency>
  <groupId>com.h2database</groupId>
  <artifactId>h2</artifactId>
  <scope>runtime</scope>
</dependency>

or

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

Click here for official Maven website.

For more information:https://h2database.com

LAB - Hibernate with H2 Database

Find the GLAB- 305.6.2 - Hibernate with H2 Database on Canvas under the Assignment section.

Knowledge Check

  • What is In-Memory Database?

  • What is H2 In-Memory Database?

Summary

H2 is an open-source lightweight Java database. It can be embedded in Java applications or run in the client-server mode. Mainly, H2 database can be configured to run as an in-memory database, which means that data will not persist on the disk. Because of an embedded database, it is not used for production development, but mostly used for development and testing.

This database can be used in embedded mode or in server mode. Following are the main features of H2 database:

  • Extremely fast, open source, JDBC API

  • Available in embedded and server modes; in-memory databases

  • Browser-based Console application

  • Small footprint − Around 1.5MB jar file size

References

PreviousHibernate Mapping and Relationship / AssociationNextFUNDAMENTALS OF JUNIT TESTING

Last updated