Home » Local Data Storage 10 – JDBC and H2 (formerly HypersonicSQL)

Share This Post

Featured Slider / Java / Tools / Tutorial

Local Data Storage 10 – JDBC and H2 (formerly HypersonicSQL)

database binary

H2 BannerH2 Database and Java

When I began using Java years ago I heard of a database that was being coded entirely using Java. This was HypersonicSQL and you can still download it at Sourceforge. The author working on it decided it needed a rewrite and he began H2 database. First download H2 installer at H2 download.  Get the stable version 1.4.195 as the h2 jar file for the last version .196 gave a class not found exception on startup looking for the driver. And the tutorial can be found here H2 Tutorial.  Next look at your install and find the H2/bin folder. Here you find the jar file needed h2-some-version.jar i.e. h2-1.4.195.jar. Most if not all databases now create the database when you connect to it. All we need to do is make the JDBC connection and create a table, add some records with SQL and then query for records with SQL, same when using SQLite or almost any database. H2 will give us some data types that SQLite does not support because well its a Lite-weight database.

Working with Java and Files Tutorial Trail
Left Arrow SQLite Database right arrow

Data Types supported by H2

One thing that is nice about H2 is the huge array of datatypes supported by H2. It supports all Java primitives and Strings as VARCHAR. I’ll list a few here.

  • BigInt (long maybe)
  • Int (int)
  • TinyInt (short)
  • SmallInt (byte maybe)
  • Boolean
  • Identity (ID)
  • Decimal (several subtypes)
  • Double
  • Float
  • Real
  • Time
  • Date
  • TimeStamp
  • TimeStamp with TimeZone
  • Binary
  • Serialized Java Objects
  • VarChar
  • Char
  • Blob
  • Clob
  • UUID
  • Array (Java arrays)
  • Enum
  • Geometry

Our H2 Example

I’m going to use INT, VARCHAR, IDENTITY, BOOLEAN and DATE for our example. This will be the same example we used in the SQLite article except recoded to use H2. I may go ahead and make the output look a little better this time with a helper method getColumnString(aString,columnWidth)  Note for the fields that returned a primitive, not String I had to make new Integer, Float and Boolean objects to get a String representation. Also, I had to get a String representation of the Date object.

The Source

import java.sql.*;

public class ReadWriteH2{
 String url = "jdbc:h2:file:c://dev/java/db/h2test.db";
 String sqlTableCreate = "CREATE TABLE IF NOT EXISTS datarecords (\n"
      + "	id identity PRIMARY KEY NOT NULL,\n"
      + "	name varchar,\n"
      + "	weight float,\n"
      + "  date date,\n"
      + "  active boolean\n" 
      + ")";
 public ReadWriteH2(){
  try {
   Connection conn = DriverManager.getConnection(url);
   Statement stmt =conn.createStatement();
   String sqlInsertUpdate = "INSERT INTO datarecords (id,name,weight,date,active)" +
     "VALUES (1,'John Brown',175.2342, '2008-12-13',true)";
   sqlInsertUpdate = "INSERT INTO datarecords (id,name,weight,date,active)" +
     "VALUES (2,'George Washington',248.9263, '1776-03-14',false)";
   String sqlSelectQuery = "SELECT * FROM datarecords";
   ResultSet rs    = stmt.executeQuery(sqlSelectQuery);
 while (rs.next()) {
  System.out.println(getColumnString(new Integer(rs.getInt("id")).toString(),10) +  "\t" + 
      getColumnString(rs.getString("name"),20) + "\t" +
      getColumnString(new Float(rs.getFloat("weight")).toString(),8) + "\t" +
      getColumnString(rs.getDate("date").toString(),10) + "\t" +
      getColumnString(new Boolean(rs.getBoolean("active")).toString(),6)
  } catch (SQLException sqle) {
  } catch (ClassNotFoundException cnfe){
 public String getColumnString(String data,int colLen){
  data+="                                         ";
  return data.substring(0,colLen);
 public static void main(String args[]){
  new ReadWriteH2();

The Output

c:\dev\java\arksoft\post\files>java -cp "h2.jar;." ReadWriteH2
1               John Brown              175.2342        2008-12-13      true
2               George Washington       248.9263        1776-03-14      false

Share This Post

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>