HSQLDB

HSQLDB is a 100% Java database which is embeddable. It has a small footprint and can be used for self-contained applications. The source files, the JAR file and support documents can be downloaded from hsqldb.org. It also provides a JDBC driver. HSQLDB is perfect for quick implementation and testing.

Following is a sample program which uses the JDBC driver for creating a table, inserting some data in the table and listing the rows from the table:

view plainprint?

1. import java.sql.DriverManager;  
2. import java.sql.Connection;  
3. import java.sql.Statement;  
4. import java.sql.SQLException;  
5. import java.sql.DatabaseMetaData;  
6. import java.sql.ResultSet;  
7. import java.sql.PreparedStatement;  
8.   
9. public class HSQLTest {  
10.  Connection conn;  
11.  Statement stmt;  
12.  PreparedStatement add_stmt, get_stmt;  
13.    
14.  static {  
15.   try{  
16.    Class.forName("org.hsqldb.jdbcDriver").newInstance();  
17.   }  
18.   catch(ClassNotFoundException e) {  
19.    e.printStackTrace();  
20.   }  
21.   catch(InstantiationException e) {  
22.    e.printStackTrace();  
23.   }  
24.   catch(IllegalAccessException e) {  
25.    e.printStackTrace();  
26.   }  
27.  }  
28.    
29.  HSQLTest() {  
30.   try {  
31.    initializeDB();  
32.    createTable();  
33.    populateTable();  
34.    displayRows();  
35.   }  
36.   catch(SQLException e) {  
37.    e.printStackTrace();  
38.   }  
39.  }  
40.    
41.  void initializeDB() throws SQLException {  
42.   conn = DriverManager.getConnection("jdbc:hsqldb:db/test", "sa", "");  
43.   stmt = conn.createStatement();  
44.  }  
45.    
46.  public void createTable() throws SQLException {  
47.   String table_name = "sample_table";  
48.   DatabaseMetaData dbM = conn.getMetaData();  
49.   ResultSet rs = dbM.getTables(null, null, "%", null);  
50.   boolean found = false;  
51.   while(rs.next()) {  
52.    String s = rs.getString(3);  
53.    //System.out.println(s);  
54.    if(s.equalsIgnoreCase(table_name)) {  
55.     found = true;  
56.     break;  
57.    }  
58.   }  
59.     
60.   if(!found) {  
61.    String s = "CREATE TABLE " + table_name + "(id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1000) PRIMARY KEY, " +  
62.       "category VARCHAR(40), name VARCHAR(40))";  
63.    stmt.execute("SET WRITE_DELAY FALSE;");  
64.    stmt.executeUpdate(s);  
65.   }  
66.  }  
67.    
68.  public void populateTable() throws SQLException {  
69.   stmt.executeUpdate("insert into SAMPLE_TABLE (category, name) values ('E0', 'Ben')");  
70.  }  
71.    
72.  public void displayRows() throws SQLException {  
73.   ResultSet rs = stmt.executeQuery("SELECT * FROM SAMPLE_TABLE");  
74.   int numCols = rs.getMetaData().getColumnCount();  
75.   while(rs.next()) {  
76.    for(int i=1; i<=numCols; i++) {  
77.     System.out.println(rs.getString(i) + ": " + rs.getString(i));  
78.    }  
79.   }  
80.  }  
81.    
82.  public static void main(String[] args) {  
83.   HSQLTest hTest = new HSQLTest();  
84.  }  
85. }  

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.PreparedStatement;

public class HSQLTest {
 Connection conn;
 Statement stmt;
 PreparedStatement add_stmt, get_stmt;
 
 static {
  try{
   Class.forName("org.hsqldb.jdbcDriver").newInstance();
  }
  catch(ClassNotFoundException e) {
   e.printStackTrace();
  }
  catch(InstantiationException e) {
   e.printStackTrace();
  }
  catch(IllegalAccessException e) {
   e.printStackTrace();
  }
 }
 
 HSQLTest() {
  try {
   initializeDB();
   createTable();
   populateTable();
   displayRows();
  }
  catch(SQLException e) {
   e.printStackTrace();
  }
 }
 
 void initializeDB() throws SQLException {
  conn = DriverManager.getConnection("jdbc:hsqldb:db/test", "sa", "");
  stmt = conn.createStatement();
 }
 
 public void createTable() throws SQLException {
  String table_name = "sample_table";
  DatabaseMetaData dbM = conn.getMetaData();
  ResultSet rs = dbM.getTables(null, null, "%", null);
  boolean found = false;
  while(rs.next()) {
   String s = rs.getString(3);
   //System.out.println(s);
   if(s.equalsIgnoreCase(table_name)) {
    found = true;
    break;
   }
  }
  
  if(!found) {
   String s = "CREATE TABLE " + table_name + "(id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1000) PRIMARY KEY, " +
      "category VARCHAR(40), name VARCHAR(40))";
   stmt.execute("SET WRITE_DELAY FALSE;");
   stmt.executeUpdate(s);
  }
 }
 
 public void populateTable() throws SQLException {
  stmt.executeUpdate("insert into SAMPLE_TABLE (category, name) values ('E0', 'Ben')");
 }
 
 public void displayRows() throws SQLException {
  ResultSet rs = stmt.executeQuery("SELECT * FROM SAMPLE_TABLE");
  int numCols = rs.getMetaData().getColumnCount();
  while(rs.next()) {
   for(int i=1; i<=numCols; i++) {
    System.out.println(rs.getString(i) + ": " + rs.getString(i));
   }
  }
 }
 
 public static void main(String[] args) {
  HSQLTest hTest = new HSQLTest();
 }
}