Skip to main content

Using HyperSQL (HSQLDB)

HSQLDB is a portable RDBMS implemented in pure java. It can be embedded with your application as well as can be used separately. It is very a small database that supports almost all features of the standard database system. It comes with small jar file that can be found in lib folder.

The HSQLDB jar package is located in the /lib directory of the ZIP package and contains several components and programs. Core components of jar file are : HyperSQL RDBMS Engine (HSQLDB), HyperSQL JDBC Driver, Database Manager, and Sql Tool.

Installing and Using
Download: download latest release of HyperSQL database from http://hsqldb.org website and extract it. You will see following contents.

Here "bin" directory contains some batch files those can be used to run a swing based GUI tool. You can use runManagerSwing.bat to connect to database, but database must be on before running it.

Directory lib contains File hsqldb.jar . It is the database to be used by you.

Running database
First of all, java must be in the path of your command prompt before doing anything. If it is not in command prompt, execute following command into command prompt

hsqldb>set path=%path%;D://program files/java/jdk1.6.0_13/bin

Now check whether java in the path or not. Just execute javac command and wait. If path has been set to java, then it will display some textual output showing command options.

Now prepare a file named server.properties and place it in hsqldb directory with following contents.



Now execute following command on command prompt

hsqldb>java -classpath lib/hsqldb.jar org.hsqldb.server.Server

As above command executes you will see some output and a new directory will be created into current path with named hsqldb in which you will find following contents

Once you created database then you have to run the command same way as before with some extra arguments to connect with existing database e.g.

hsqldb>java -classpath lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:hsqldb/hemrajdb --dbname.0 testdb

And you will see console output as below

This command will start database server and then you need to run runManagerSwing.bat file from bin directory. Before start you have to enter some connection configurations and then press ok button to connect to the graphical user interface to database.

After connecting to your database, you can create tables using SQL statement and insert data into table. To view data from the table just execute select SQL statement for that table. You will get something like below.






Comments

Anonymous said…
good example
Anonymous said…
English in the post requires a bit of polish, not exactly tutorial style.
Though I was able to understand the steps but still it would be better if it is refined a little bit.

Thanks a Lot for this Post.
Unknown said…
Hi,

I would like to know how to connect a hsqldb emebbed into a web .war app in file mode.

Can you help please ?

Thanks
Hemraj said…
You can use HsqlDb into any java application without any external exposure. Here is an example using the HSQLDB in the web application. http://www.programmingforfuture.com/2011/02/in-process-mode-of-hsqldb-in-web.html.

Thanks
Anonymous said…
it is a good "Getting started guide for Hsqldb"

Thanks
Anonymous said…
thanks, its easy & clear to understand
Anonymous said…
You would think a company like this would ensure their web site included proper english grammar...How hard is it to have someone proof read before publishing?
Hemraj said…
Thank for suggestion. It is not a company website, I am individual and I will try to correct any English grammar.
mee said…
simple and useful, thanks!
Mike said…
I think I am highly confused.

Is there something in the samples directory that loads this schema?

Did you accidentally delete this step in the entry during some edit?

You start the database twice and magically there is a defined testdb schema.

Magic. Please explain.
Anonymous said…
Followed the instructions in tutorial, but not seeing the database in runManagerSwing. Instead all I see is Properties folder. Can you help
Paul Broekhof said…
Great stuff! I only tripped when I tried to execute the command "java -classpath lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:hsqldb/hemrajdb --dbname.0 testdb". Since I still had a cmd line open from the previous command, I got an error message stating that the port was already in use. So I killed the DOS box in which the previous command was still active and could proceed without a problem.

Thanks for making this available. It was a great help.
snaulus said…
The example does not work with version 1.8 of HSQLDB. Do you have any instructions for that version perhaps?

Thanks!
Anonymous said…
Hi,
Thank you for simple and nicely explained article. It certainly helped a lot.
-ShekharL
Anonymous said…
It was redundant to use those extra arguments on the command line and a properties file with the same contents.
Nice blog and very helpful information thanks.
Rafinhaa said…
Hi, have one exemple for linux users?

Popular posts from this blog

How to handle values from dynamically generated elements in web page using struts2

Some time you will see the form containing the button " Add More " . This facility is provided for the user to get the values for unknown number of repeating for some information. for example when you are asking to get the projects details from user, you need to put the option to add the more project for the user since you don't known how many projects user have. In the HTML form, you repeat the particular section to get the multiple values for those elements. In Html page , you can put the option to add new row of elements or text fields by writing the java script or using JQuery API. Now, the question is that how to capture the values of dynamically generated text fields on the server. Using the servlet programming you can get the values by using getParameters() method that resultants the array of the parameter having the same name. But this limit you to naming the text fields in the HTML form. To ally this approach, you have to take the same name for t

In Process Mode of HSQLDB in web application.

If you want to use the database into your web application, you can use the HSQLDB in In_Process mode. In this mode, you can embed the HSQLDB into your web application and it runs as a part of your web application programm in the same JVM. In this mode, the database does not open any port to connect to the application on the hosing machine and you don't need to configure anything to access it. Database is not expposed to other application and can not be accessed from any dabase tools like dbVisualizer etc. In this mode ,database will be unknown from any other person except you. But in the 1.8.0 version, you can use Server intance for external as well as in process access.  To close the databse, you can issue SHUTDOWN command as an SQL query.   In the in-process mode, database starts from JDBC with the associated databse file provided through  connection URL. for example   DriverManager.getConnection("jdbc:hsqldb:mydatabase","SA","");   Here myd