Java Tutorial - Java Script :
HSqlDb
HSqlDb is the continuation of Thomas Mueller’s Hypersonic database. Thomas Mueller originally created the popular Java database and brought it up to version 1.43. The original product was used in a number of other open source projects such as the Jakarta Turbine and Jetspeed portal projects. When Mr. Mueller decided to abandon the project, a number of people jumped in and created the HSqlDb project on SourceForge.net. This project now represents a true example of the value of open source community. The project has moved from a single developer to a group of developers and has matured considerably under this new group. HSqlDb now represents one of the best Javabased open source database products around. A summary appears in the following table. Like Axion, HSqlDb offers support for both in memory- (transient) and disk-based (persistent) databases. HSqlDb is small and suitable for embedded environments. However, HSqlDb has a number of advanced features that don’t exist in Axion.
HSqlDb Features
HSqlDb supports two modes of operation, in process and client/server. In process mode allows HSqlDb to be run as a standalone database, embedded inside a Java product. Calls to the database are handled as function calls and do not require a network connection. This mode limits access to an HSqlDb database to one application at a time. HSqlDb also offers a client/server (or just server for short) mode. In server mode HSqlDb runs in its own JVM. A JDBC client connects to the database using a network connection. As a server, HSqlDb supports database access by multiple applications simultaneously. Server mode is available using two protocols. One protocol is unique to HSqlDb, but is very efficient. The JDBC driver understands this protocol, so the details of the protocol implementation are not important for most developers. The other supported protocol is HTTP. HTTP is, of course, the protocol used for the World Wide Web. The HSqlDb HTTP server is not designed to serve HTML files or for use as a Web server. It is actually designed so that an HSqlDb client can access a database server even if the server is located behind a firewall or proxy. This Web-server mode does has some limitations, most important of which is that transactions are not currently supported. HSqlDb supports a wide variety of Java environments. In order to do this efficiently, it provides a build tool called Code Switcher. Code Switcher acts in a similar fashion to the C or C++ preprocessor, except that it only serves to comment and uncomment out blocks of Java code. This allows HSqlDb to be built to support the more recent versions of Java and also support earlier versions from the same code base. The binary distribution presently supports the 1.3 JDK by default but source code can be easily rebuilt to support the 1.1 JDK by using Code Switcher. If you attempt to open a database that does not exist, HSqlDb automatically creates a database for you. The following code fragment provides an example
of how to create and connect to a HSqlDb database.
Connection conn = null;
try
{
Class.forName(“org.hsqldb.jdbcDriver”).newInstance();
conn = DriverManager.getConnection(
“jdbc:hsqldb:c:/openjava/hsqldb/data/hsqltest”, “sa”, “”);
}
catch (Exception e)
{
e.printStackTrace();
}
The URL in the above example will create a database in the c:/openjava/ hsqldb/data directory that will be named hsqltest. By default HSqlDb tables are created in memory. By providing a filename for the database, the memory-based database is persisted, or saved to disk. As the database is modified a transaction log of SQL commands is written to disk as a script file. When the database is reloaded, this script is executed to automatically reload
the memory-based database. The database would not be saved to disk if the URL parameter had been
specified as: jdbc:hsqldb:. This default behavior of memory-based tables being rebuilt through transaction logs can be modified on a table-by-table basis. Each table can be stored as a data file or as a comma-delimited text file. Unfortunately, this behavior can’t be configured but is accessed through specialized commands. The syntax for the CREATE TABLE command in HSqlDb is:
CREATE [ MEMORY | CACHED | TEMP | TEXT ] TABLE name
( columnDefinition [, ...] [, constraintDefinition...]);
An example for a normal command for creating a table in a SQL database is:
CREATE TABLE USERS(
ID INTEGER PRIMARY KEY,
USERID VARCHAR(32),
PASSWD VARCHAR*(32)
);
In HSqlDb, this will create, by default, an in-memory table that is automatically regenerated from a SQL script each time the database is started. In order to create a disk-based table, the command needs to be modified by adding the
keyword CACHE between CREATE and TABLE as follows:
CREATE CACHE TABLE USERS(
ID INTEGER PRIMARY KEY,
USERID VARCHAR(32),
PASSWD VARCHAR(32)
);
Now, the table will be disk-based but caching will be used to improve performance. Replacing the keyword CACHE with the keyword TEXT creates the table on disk as a comma-delimited file. A comma-delimited file is a file of
comma-separated values (CSV), where each column is separated from the others by a comma. The CSV file format is a common means of exchanging data between applications, and the files can be read directly by many products—for example, Microsoft’s Excel spreadsheet program. MEMORY and TEMP keywords are also supported. MEMORY is used for explicitly creating a memory-based table (the default behavior) that is reloaded automatically from a script file. The TEMP keyword is for creating a memorybased table, but this table is not recreated and a script file is not maintained. Although these commands reflect a powerful ability to create a mixture of
storage types within the database, not being able to determine a default behavior in the absence of a keyword limits script portability.
