H2 Database Example

By admin | July 05, 2014 | Updated : May 08, 2016 database, h2, in-memory, 6 Comments

In this H2 Database Example, we are going to create a simple 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. As due to Embedded database it is not used for production deployment, but mostly used for development & testing

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 testing H2 database, You need to download

h2-1.4.182.jar

Project Structure

H2 Database Example

H2 SQL Statement AND Prepared Statement Examples

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

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;

import org.h2.tools.DeleteDbFiles;

// H2 Database Example

public class H2FileDatabaseExample {

    private static final String DB_DRIVER = "org.h2.Driver";
    private static final String DB_CONNECTION = "jdbc:h2:~/test";
    private static final String DB_USER = "";
    private static final String DB_PASSWORD = "";

    public static void main(String[] args) throws Exception {
        try {
            // delete the H2 database named 'test' in the user home directory
            DeleteDbFiles.execute("~", "test", true);
            insertWithStatement();
            DeleteDbFiles.execute("~", "test", true);
            insertWithPreparedStatement();

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

    // H2 SQL Prepared Statement Example
    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 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();
        }
    }

    // H2 SQL Statement Example
    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 Database inserted through Statement");
            while (rs.next()) {
                System.out.println("Id "+rs.getInt("id")+" Name "+rs.getString("name"));
            }
            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 Database inserted through Statement
Id 1 Name Anju
Id 2 Name Sonia
Id 3 Name Asha
H2 Database inserted through PreparedStatement
Id 1 Name Jose

You might also like following tutorials :

6 Responses to "H2 Database Example"

jaz 2014-06-10 21:07:05.0

Hi admin,

I am getting following exception, Can I get any help?

org.h2.jdbc.JdbcSQLException: Column "name" not found; SQL statement:

admin 2014-06-11 21:07:05.0

@Jaz,

May be your H2 table does not have any column name as "name"? Please check?

jaz 2014-06-12 21:07:05.0

@admin,

Sorry, it is a mistake from my end.

Charles Ouyang 2014-06-13 21:07:05.0
```
Thank you for sharing.
```

Anil 2014-06-14 21:07:05.0

Am getting the below error:
org.h2.jdbc.JdbcSQLException: Table "PERSON" already exists; SQL statement

Deletedbfiles is not deleting the files.

admin 2014-06-15 21:07:05.0

@Anil,

Can you check the permission for the deletion?

Required Libraries

Project Structure

H2 SQL Statement AND Prepared Statement Examples

Output

Related Posts