How To view Content Of H2 In-memory & File Database

By admin | July 26, 2014 | Updated : May 01, 2016 database, h2, in-memory, No Comments

In this tutorial, we are discussing about the usage of H2 Console Application. This console application is available with h2 package.

We can see / browse the contents in H2 Database in a browser, by using this handy tool

View the content of H2 Database

H2 Console application helps you to view the database contents using a browser, whether it is a In-Memory database or a File based database. It is also possible to view the contents of other database, if it is according to JDBC standards.

We need to start both client / server inorder to view the contents of database through browser.

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 File Database Example

H2 Database Viewer Using Browser

In below code you can see that we are using following H2 JDBC URL jdbc:h2:mem:test for connecting the database, here 'test' is the in-memory database

You can see openServerModeInBrowser() method which is showing the JDBC Connection URL

By following way, H2 database can be embedded in Java applications

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 org.h2.tools.Server;

// H2 Database Viewer Example
public class H2DatabaseViewerExample {

    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 {
            openServerModeInBrowser();
            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 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;
    }

    private static void openServerModeInBrowser() throws SQLException {
        Server server = Server.createTcpServer().start();
        System.out.println("Server started and connection is open.");
        System.out.println("URL: jdbc:h2:" + server.getURL() + "/mem:test");
    }
}

Output

Server started and connection is open.
URL: jdbc:h2:tcp://127.0.1.1:9092/mem:test
H2 In-Memory Database inserted through PreparedStatement
Id 1 Name Jose

When running the above class, you are establishing the connection with H2 database, You can see the connection URL as jdbc:h2:tcp://127.0.1.1:9092/mem:test in above output

For viewing the contents of H2 Database, you need to run below command (H2 Console application)


java -jar h2-1.4.182.jar

After starting the console application, just paste connection url "jdbc:h2:tcp://127.0.1.1:9092/mem:test" into JDBC URL text box (see screenshot) and connect it.

H2 Console Application

Now you can query the table PERSON (see screenshot)