H2 DB with Java Example

INTRODUCTION

• H2 Database Engine is very fast, open source and lightweight.
• It is another Java Database and it's completely written in java.
• It is an in-memory database.
• Easy installation.
• Tables can be created in an in-memory or local disk.

SOFTWARES & TOOLS

1. H2 DB
2. Eclipse IDE (Mars 2)
3. SQL-Workbench

INSTALLATION

1. Download latest H2 DB Installer from http://www.h2database.com/html/main.html
2. Install the DB to your local drive: C:\ranjiths\jbr\InstalledSofties\H2

CREATE DATABASE & TABLES

Unlike other Open Source In-Memory Database, there is no default DB Editor Tool within H2. So download SQL-WorkBench and install for running the SQL Queries.

Now, Open the SQL-WorkBench and enter below details to create a new connection.

1. Default Group Name : JBR
2. Driver : Select H2 Database(org.h2.Driver)
3. URL: jdbc:h2:C:/ranjiths/jbr/InstalledSofties/h2db/db
4. Username : ranjith
5. Password : sekar

Click OK

Create a table and insert some data.

CREATE TABLE users (  user_id INTEGER NOT NULL CONSTRAINT EMP_NO_PK PRIMARY KEY,  first_name VARCHAR(30) NOT NULL,  last_name VARCHAR(30) NOT NULL,  email VARCHAR(40),  phone INTEGER    ); INSERT INTO users VALUES(1,'Ranjith','Sekar','ranjith@gmail.com',96000); INSERT INTO users VALUES(2,'Manoj','Kumar','manoj@gmail.com',88000); INSERT INTO users VALUES(3,'Sachin','Tendulkar','sachin@gmail.com',77000);

EXAMPLE JAVA PROGRAM

1. Create a Java Project in Eclipse
2. Add C:\ranjiths\jbr\InstalledSofties\H2\bin\h2-1.4.192.jar into the project classpath.

package jbr.h2db; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class H2DBExample {  public static void main(String[] args) {    Connection connection = null;    ResultSet resultSet = null;    Statement statement = null;    String URL = "jdbc:h2:c:/ranjiths/jbr/InstalledSofties/h2db/db";    String username = "ranjith";    String password = "sekar";    try {      Class.forName("org.h2.Driver");      connection = DriverManager.getConnection(URL, username, password);      statement = connection.createStatement();      resultSet = statement.executeQuery("SELECT * FROM users");      while (resultSet.next()) {        System.out.println("User Name: " + resultSet.getString("FIRST_NAME") + " " + resultSet.getString("LAST_NAME"));      }    } catch (Exception e) {      e.printStackTrace();    } finally {      try {        resultSet.close();        statement.close();        connection.close();      } catch (Exception e) {        e.printStackTrace();      }    }  }