The previous weeks I was working on springframework course. We wanted to get to know and learn other about spring. During this process we started using tools like databases, message queuing, integrated development environments, etc. Most of the used tools are opensource projects. The coming blog items will deal with a number of these opensource tools. This item deals with the database that I used Hypersonic. Usually I use mysql or postgresql. Problem with these databases is that you have to install them. We wanted to get the others up and running as soon as possible, so as least possible of installlations as possible. Hypersonic on the other hand only needs to be started. Sounds cool doesn’t it. Well it gets better. Hypersonic is a pretty complete database with the usual contraint possibilities, a good amount of datatypes and ofcourse the standard sql language. A short text from there website:
HSQLDB is the leading SQL relational database engine written in Java. It has a JDBC driver and supports a rich subset of ANSI-92 SQL (BNF tree format) plus SQL 99 and 2003 enhancements. It offers a small (less than 100k in one version for applets), fast database engine which offers both in-memory and disk-based tables and supports embedded and server modes. Additionally, it includes tools such as a minimal web server, in-memory query and management tools (can be run as applets) and a number of demonstration examples.
In what way did we use it? We used ant to start it up, o and we used ant to run the creation and insertion sql statements, by the way we used ant to start the very basic swing client to browse the database. There are ofcourse the usual database interface tools that can be used to interact with the database. The ant scrips are very easy, but in case you just do not have them, here they are:
Starting the database
<target name=”hsql”>
<echo message=”Starting HSQLDB”/>
<java fork=”true” dir=”.” classname=”org.hsqldb.Server”>
<classpath>
<pathelement path=”${path.to.hsqldb.jar}/hsqldb.jar”/>
</classpath>
<arg value=”-database.0″/>
<arg value=”${path.to.db}/${db.name}”/>
<arg value=”-dbname.0″/>
<arg value=”${db.name}”/>
</java>
</target>
Run sql statements versus the database
<target name=”setup_db”>
<sql
driver=”org.hsqldb.jdbcDriver”
url=”jdbc:hsqldb:hsql://localhost/${db.name}”
userid=”${user.id}”
password=”${user.pwd}”
src=”setup.sql”
print=”yes”>
<classpath>
<pathelement location=”${path.to.hsqldb.jar}/hsqldb.jar”/>
</classpath>
</sql>
</target>
Running the client
<target name=”hsqlclient”>
<echo message=”Starting HSQLDB swing client”/>
<java fork=”true” dir=”.” classname=”org.hsqldb.util.DatabaseManagerSwing”>
<classpath>
<pathelement path=”${path.to.hsqldb.jar}/hsqldb.jar”/>
</classpath>
</java>
</target>
Now we have a running database, that is created if it did not exist yet. We want to have some tables with columns and constraints. Lets have a look how hsqldb solves this.
We have the following case. We are creating a program that registers students for courses. Therefore we have a student table, a course table and a registrations table. The name of a student must be unique as wel as the name of the course. A student can only register once for a course.
Create table
create table COURSE(
ID identity NOT NULL PRIMARY KEY,
NAME VARCHAR(25) not null,
CONSTRAINT UNIQUE_NAME UNIQUE (NAME));create table STUDENT(
ID identity NOT NULL PRIMARY KEY,
LOGIN VARCHAR(20) not null,
NAME VARCHAR(15) not null,
EMAIL VARCHAR(25) not null,
CONSTRAINT UNIQUE_LOGIN UNIQUE (LOGIN));create table REGISTRATION (
ID identity NOT NULL PRIMARY KEY,
STUDENTID INTEGER NOT NULL,
COURSEID INTEGER NOT NULL,
FOREIGN KEY (COURSEID) REFERENCES COURSE(ID),
FOREIGN KEY (STUDENTID) REFERENCES STUDENT(ID),
CONSTRAINT UNIQUE_REGISTRATION UNIQUE (STUDENTID,COURSEID)
);
Create primary key
Using auto numbers
hsqldb uses identity columns for autonumbers but can also use sequences. Within our example we use the identity columns. This works great with hibernate.
I hope this helps in getting you up to speed with hsql.