H2 In-Memory Database Example

By admin | July 13, 2014 | Updated : April 29, 2016 database, h2, 3 Comments

In this H2 In-Memory Database Example, we are going to store the database contents on In-Memory of the system. Here persistence happens on Memory of the system. In this example we are creating a java class that shows how to load the driver, create a database, create table and insert some values into table

H2 is an open source software implementation of Java SQL database. The main features of H2 are.

Very fast, open source, JDBC API
Embedded and server modes; in-memory databases
Browser based Console application
Small footprint: around 1.5 MB jar file size

Reference -> http://www.h2database.com/html/main.html

Note

Other H2 examples

1)    H2 Database Example
2)    H2 File Database Example
3)    H2 In-Memory Database Example
4)    H2 Database Connection Pool Example
5)    How To view Content Of H2 In-memory & File Database
6)    H2 Batch Update Example With SQL Statement & PreparedStatment

Required Libraries

For using H2 database, You need to download

h2-1.4.182.jar

Project Structure

H2 In-Memory Database Example

H2 Database Using In-Memory

Here we are showing implementation for SQL Statement and PreparedStatement using H2 In-Memory Database.

In below code you can see that we are using following H2 JDBC URL jdbc:h2:mem:test;DB_CLOSE_DELAY=-1 for connecting the database

By following way, H2 database contents are stored in the memory of the system

package com.h2.examples;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

// H2 In-Memory Database Example shows about storing the database contents into memory.

public class H2MemoryDatabaseExample {

    private static final String DB_DRIVER = "org.h2.Driver";
    private static final String DB_CONNECTION = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1";
    private static final String DB_USER = "";
    private static final String DB_PASSWORD = "";

    public static void main(String[] args) throws Exception {
        try {
            insertWithStatement();
            insertWithPreparedStatement();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void insertWithPreparedStatement() throws SQLException {
        Connection connection = getDBConnection();
        PreparedStatement createPreparedStatement = null;
        PreparedStatement insertPreparedStatement = null;
        PreparedStatement selectPreparedStatement = null;

        String CreateQuery = "CREATE TABLE PERSON(id int primary key, name varchar(255))";
        String InsertQuery = "INSERT INTO PERSON" + "(id, name) values" + "(?,?)";
        String SelectQuery = "select * from PERSON";

        try {
            connection.setAutoCommit(false);

            createPreparedStatement = connection.prepareStatement(CreateQuery);
            createPreparedStatement.executeUpdate();
            createPreparedStatement.close();

            insertPreparedStatement = connection.prepareStatement(InsertQuery);
            insertPreparedStatement.setInt(1, 1);
            insertPreparedStatement.setString(2, "Jose");
            insertPreparedStatement.executeUpdate();
            insertPreparedStatement.close();

            selectPreparedStatement = connection.prepareStatement(SelectQuery);
            ResultSet rs = selectPreparedStatement.executeQuery();
            System.out.println("H2 In-Memory Database inserted through PreparedStatement");
            while (rs.next()) {
                System.out.println("Id " + rs.getInt("id") + " Name " + rs.getString("name"));
            }
            selectPreparedStatement.close();

            connection.commit();
        } catch (SQLException e) {
            System.out.println("Exception Message " + e.getLocalizedMessage());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            connection.close();
        }
    }

    private static void insertWithStatement() throws SQLException {
        Connection connection = getDBConnection();
        Statement stmt = null;
        try {
            connection.setAutoCommit(false);
            stmt = connection.createStatement();
            stmt.execute("CREATE TABLE PERSON(id int primary key, name varchar(255))");
            stmt.execute("INSERT INTO PERSON(id, name) VALUES(1, 'Anju')");
            stmt.execute("INSERT INTO PERSON(id, name) VALUES(2, 'Sonia')");
            stmt.execute("INSERT INTO PERSON(id, name) VALUES(3, 'Asha')");

            ResultSet rs = stmt.executeQuery("select * from PERSON");
            System.out.println("H2 In-Memory Database inserted through Statement");
            while (rs.next()) {
                System.out.println("Id " + rs.getInt("id") + " Name " + rs.getString("name"));
            }

            stmt.execute("DROP TABLE PERSON");
            stmt.close();
            connection.commit();
        } catch (SQLException e) {
            System.out.println("Exception Message " + e.getLocalizedMessage());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            connection.close();
        }
    }

    private static Connection getDBConnection() {
        Connection dbConnection = null;
        try {
            Class.forName(DB_DRIVER);
        } catch (ClassNotFoundException e) {
            System.out.println(e.getMessage());
        }
        try {
            dbConnection = DriverManager.getConnection(DB_CONNECTION, DB_USER, DB_PASSWORD);
            return dbConnection;
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return dbConnection;
    }
}

Output

H2 In-Memory Database inserted through Statement
Id 1 Name Anju
Id 2 Name Sonia
Id 3 Name Asha
H2 In-Memory Database inserted through PreparedStatement
Id 1 Name Jose

You might also like following tutorials :

3 Responses to "H2 In-Memory Database Example"

Socy 2014-07-15 21:09:50.0

I am getting following exception when executing the queries,

org.h2.jdbc.JdbcSQLException: Table "PERSON" not found; SQL statement:select * from PERSON [42102-182]

admin 2014-07-16 21:09:50.0

@Socy,

There are so many possibilities, some of them are

1) There is no table named as "PERSON" exists in your H2 database.

2) If you are using H2 Memory database with connection URL as "jdbc:h2:mem:test", then database contents in the Memory will be lost when connection is closed.
If you are querying the database after the connection is closed, you will get above exception as there is no database existing at that moment.
So you must add "DB_CLOSE_DELAY=-1" into connection URL, so JDBC Connection String will become "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1". In this case database conents will exists upto VM lives.

alex 2018-08-01 17:29:33.0

I have cut and pasted your example to Netbeans 8.2 and getting this error

run:
org.h2.Driver
No suitable driver found for jdbc:h2:mem:test;DB_CLOSE_DELAY=-1
java.lang.NullPointerException
	at mctlookupservice.MctLookupService.insertWithStatement(MctLookupService.java:99)
	at mctlookupservice.MctLookupService.main(MctLookupService.java:88)
Exception in thread "main" java.lang.NullPointerException
	at mctlookupservice.MctLookupService.insertWithStatement(MctLookupService.java:120)
	at mctlookupservice.MctLookupService.main(MctLookupService.java:88)
/home/admin1/.cache/netbeans/8.2/executor-snippets/run.xml:53: Java returned: 1
BUILD FAILED (total time: 0 seconds)

Required Libraries

Project Structure

H2 Database Using In-Memory

Output

Related Posts