My new website on Artificial Intelligence and Machine Learning www.aimlfront.com

Connect Java with HSQLDB Tutorial

The HSQLDB is used for interactive user access to databases, including creation of a database, inserting or modifying data, or querying the database.

The product is currently being used as a database and persistence engine in many Open Source Software projects and even in commercial projects and products. In its current version it is extremely stable and reliable. It is best known for its small size, ability to execute completely in memory, its flexibility and speed.

Pre-Requisites:
  • jdk1.6.0_11(Works with previous versions of JDK) can be downloaded from here
  • hsqldb-2.2.9 can be downloaded from here
  • JDBC Driver - hsqldb.jar (available in the hsqldb-2.2.9.zip),This same jar you should place it in java project.
Environment Variables:
Variable name: JAVA_HOME
Variable value: C:\Program Files\Java\jdk1.6.0_11
Variable name: PATH
Variable value: C:\Program Files\Java\jdk1.6.0_11\bin
Variable name: CLASSPATH
Variable value: C:\hsqldb-2.2.9\lib\hsqldb.jar

Procedure to connect HSQLDB


HSQL Database Manager

HSQL Database Manager is a general-purpose database tool (Similar to Toad, SQLyog..) that can be used with any database engine that has a JDBC driver. It comes by default with HSQLDB download. To execute Swing version of the Database Manager Go to command prompt and type or otherwise you can go to hsqldb.jar which is available in HSQLDB folder, double click on that HSQLDB window will be open..
java org.hsqldb.util.DatabaseManagerSwing
(Make sure that hsqldb.jar is in the CLASSPATH)
It should be something like
C:\>java org.hsqldb.util.DatabaseManagerSwing

cmd prompt for DatabaseManagerSwing

See the below screen shot for HSQL window, it will be like this....

HSQL window

Here we can find small connection window, there we have to provide the connection details. Here I have taken file catalog for the URL that means my database engine memory is file.

CREATE TABLE IF NOT EXISTS salarydetails (
  EmpID varchar(6) PRIMARY KEY,
  Salary INT NOT NULL,
  Bonus INT NOT NULL,
  Increment INT NOT NULL,
) 


create table in HSQL DB

INSERT INTO salarydetails VALUES ('54601A', 10000, 5000, 2000); 
INSERT INTO salarydetails VALUES ('54602A', 12000, 500, 2000); 
INSERT INTO salarydetails VALUES ('54603A', 11000, 500, 2000);  


We have created table and inserted data to that table. Now data base is ready, will write java program now...
Simple java program to connect HyperSQL DataBase with java using JDBC
Required jars

HSQL DB required jars for setup


I have highlated one jar, that we should get from our lib folder of HSQLDB source code(C:\hsqldb-2.2.9\lib\hsqldb.jar)...
We need to place it log4j.properties in source folder, find below properties for log4j
log4j.properties
####Use two appenders, one to log to console, another to log to a file
log4j.rootCategory=info, stdout, R

####First appender writes to console
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout

#Pattern to output the caller's file name and line number.
log4j.appender.stdout.layout.ConversionPattern=%5p[%t] (%F%L) - %m%n

####Second appender writes to a file
log4j.appender.R=org.apache.log4j.RollingFileAppender
log4j.appender.R.File=C:\\JAVA\\Log\\import.log

#Control the maximum log file size
log4j.appender.R.MaxFileSize=10000KB

#Archive log files (one backup file here)
log4j.appender.R.MaxBackupIndex=10
log4j.appender.R.layout=org.apache.log4j.PatternLayout
log4j.appender.R.layout.ConversionPattern=%p%t %c - %m%n


ConnectHSQLDB.java
package org.javaVillage.hsqldb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * @author www.javaworkspace.com
 * 
 */
public class ConnectHSQLDB {
	public static void main(String[] args) {
		Connection connection = null;
		ResultSet resultSet = null;
		Statement statement = null;
		try {
			Class.forName("org.hsqldb.jdbcDriver");
			connection = DriverManager.getConnection(
			"jdbc:hsqldb:file:C:/JavaInstallation/HSQLDB/DB", "SA", "");
			statement = connection.createStatement();
			resultSet = statement
					.executeQuery("SELECT Salary FROM SALARYDETAILS WHERE Emptitle='54601A'");
			while (resultSet.next()) {
				System.out.println("EMPLOYEE Salary:"
						+ resultSet.getString("Salary"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				resultSet.close();
				statement.close();
				connection.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}


Now time to execute the program, see the result after executing it.
result page for HSQL DB