H2 In-Memory Database Example

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

  1. 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

 






2 Responses to "H2 In-Memory Database Example"
  1. Socy 2014-07-15 19:09:50.0
  1. admin 2014-07-16 19:09:50.0

Your email address will not be published. Required fields are marked *