H2 Database Example

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

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

 






6 Responses to "H2 Database Example"
  1. jaz 2014-06-10 19:07:05.0
  1. admin 2014-06-11 19:07:05.0
  1. jaz 2014-06-12 19:07:05.0
  1. Charles Ouyang 2014-06-13 19:07:05.0
  1. Anil 2014-06-14 19:07:05.0
  1. admin 2014-06-15 19:07:05.0

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