HSQLDB - 100% Java Database
- <Download>
<Support> <License>
- <Features> <FAQ> <Documentation> <How To>
- <Developers> <Software using HSQLDB>
- <SourceForge Project Page>
|
|
|
How to install and run
HSQLDB
How to compile HSQLDB
How to create a new database
How to start programming JDBC
/ HSQLDB
Where to get more
documentation
How to use HSQLDB inside
JBuilder
How to upgrade from an old
version
Can I use HSQLDB in my
program
Reliability, Performance and
Deployment
You don't need to. A compiled JAR of HSQLDB is included in the download. This runs under JRE 1.6. A separate download for the JDK 1.5 jars is also provided on the support page. If you want to re-compile HSQLDB, you will need a JDK and ANT. See the documentation in the /build directory. (The HSQLDB 1.8 jar is compatible with JRE 1.4 or later)
A new database is created automatically if it does not yet exist. Just connect to the not-yet-existing database using the jdbc:hsqldb:file:«database-path» URL (should replace the last part with the path you want) with the user 'sa' (or any name) and a password (can be an empty string). You will use this name and password to connect again.
HSQLDB comes with documentation, example program source code that can help programers who are new to JDBC programming.
Basic sample programs are in the /src/org/hsqldb/sample folder.
Source code of test programs are useful examples of how to use different features of JDBC and SQL. Check the sources in the /src/org/hsqldb/test folder.
SQL test scripts are in the /runtest folder and offer extensive examples of SQL statements.
HSQLDB has a standard JDBC interface. HSQLDB specific JDBC documentation is included in the /doc/src folder.
There are also many books available on JDBC programming.
HSQLDB is covered in hundreds of books on Java programming. Search Google Books for "HSQLDB"
To use HSQLDB at design-time in JBuilder, Eclipse, NetBeans or other tools, you usually require the plug-in for databases that comes with the development environment. You usually need to add a reference to the HSQLDB jar to the environment. Also you normally need to register the JDBC driver (which is part of the hsqldb.jar) with the environment. A dedicated plugin is already available for Eclipse.
It is recommended that you close version 1.8.x databases with the SHUTDOWN command before they are opened with version 2.x. If the database is not read-only, it will be upgraded to the latest version.
If you use CACHED tables, the procedures in the System
Management and Deployment Issues section of the Guide should
be followed. A SHUTDOWN COMPACT after the upgade is recommended.
Note that an upgrade is a one-way process, so please always keep a backup of the old database.
Yes. HSQLDB is Open Source and free to use in any commercial product so long as the terms of the Licenses are met. The Licenses of HSQLDB and Hypersonic SQL (on which a few parts of HSQLDB is based) are both based on the new BSD License.
It stores all data in memory only if you want to. By default, CREATE TABLE results in a memory table, as this is the best type for smaller tables. For larger tables, use CREATE CACHED TABLE and adjust the cache size to suite your memory use requirements (as little as 8MB or so). See the System Management and Deployment Issues chapter of the Guide. There is no simple rule and no imposition on the part of HSQLDB as maximum flexibility is allowed using only a couple of settings. A popular use of HSQLDB is for OLAP, ETL, and data mining applications where huge Java memory allocations are used to hold millions of rows of data in memory.
HSQLDB has very extensive support for SQL-92, SQL-1999 and SQL-2008. This support nearly matches the Advanced level of the old SQL-92 Standard and the Core level of the new Standard, plus 150 optional features. SQL support is more extensive than all open-source database products and includes features that are not yet supported in most closed-source, commercial products. JDBC support is comprehensive and extends to all the features that are supported by the core SQL capabilities of the engine.
This
is something that you can measure. Overall, with disk tables, HSQLDB is
faster, or at least comparable in
speed to the fastest non-java or java open-source RDBMS engines.
In addition, HSQLDB supports fast, persistent, memory tables in a
database which are much faster than traditional disk tables.
You can use the performance tests supplied with
HSQLDB. One test, JDBCBench.java is a standard TPC-B implementation
that measures speed and reliability of multi-threaded access. HSQLDB is
extremely fast in the MVCC mode in this test. Another test,
TestCacheSize.java, is a single-threaded test for speed of INSERT,
UPDATE, DELETE and SELECT operations with hundreds of thousands of
rows. Some comparisons have been posted by users in our mailing lists
and on the web.Any specific SELECT query speed issues can normally be
resolved with slight modifications to the query or by adding
appropriate indexes. See the Guide for details.
HSQLDB 2.2 comes after version 1.7.2 / 1.7.3 / 1.8.0 / 2.0 and uses the experience gained from extensive SQL compatibility tests and stress tests by application vendors that use HSQLDB in their products. Persistence in 2.2 is an improved and hardened version of the persistence engine of 1.8 which has been in use for over 5 years.
HSQLDB 2.2 is fully multithreaded. Both the core engine and the Server.
There is no imposed limitation. Number of columns, tables, indexes, size of columns and so on is limited only by the memory. For example, a user reported using a SELECT statement with 41 LEFT OUTER JOIN clauses on a huge database for a data mining application.
If only memory tables (CREATE TABLE or CREATE MEMORY TABLE) are used then the database is limited by the memory. A minimum of about 100 bytes plus the actual data size are required for each row. If you use CREATE CACHED TABLE, then the size of the table is not limited by the memory beyond a certain minimum size. The data and indexes of cached tables are saved to disk. With text tables, indexes are memory resident but the data is cached to disk.
The current (2.2) size limit of an HSQLDB database is 2 TB (by default) for all CACHED tables and 2GB for each TEXT table. If you use large MEMORY tables, memory is only limited by the allocated JVM memory, which can be several GB on modern machines and 64bit operating systems. We have performed extensive tests with the latest versions using the TestCacheSize and other test programs inserting millions of rows and resulting in data files of up to 16 GB and larger LOB sizes. Users have reported the use of databases with up top 900 million rows.
The database was not shut down properly. When you restart
the database, the *.log file will be processed and an automatic
checkpoint will be performed. No data committed before the
last sync() (see under machine crash above) will be lost. To avoid
this, use the SQL command "SHUTDOWN" when your application has finished
with the database.
The statements that make up the database are saved in the *.script file
(mostly CREATE statements and INSERT statements for memory tables).
Only the data of cached tables (CREATE CACHED TABLE) is stored in the
*.data file. Also all data manipulation operations are stored in the
*.log file (mostly DELETE/INSERT) for crash recovery. When the SHUTDOWN
or CHECKPOINT command is issued to a database, then the *.script file
is re-created and becomes up-to-date. The .log file is deleted. When
the database is restarted, all statements of the *.script file are
executed first and new statements are appended to the .log file as the
database is used. In addition, there are *.bakup and *.lobs files.
HSQLDB tracks only up to a fixed number of the empty spaces left after DELETE or during UPDATE operations. If the delete rate is higher than insert rate, or large number of rows are updated in a single query, then some spaces are left empty. Both CHECKPOINT DEFRAG and SHUTDOWN COMPACT commands will remove the empty spaces. In version 2.2 empty spaces are tracked better and DEFRAG will be performed automatically, based on a user-defined property.
HSQLDB
2.2 supports READ COMMITTED and SERIALIZABLE isolation levels. It
supports both lock-based and multiversion (MVCC) transaction models, and is fully multithreaded in all modes.
See the Guide for a
fuller discussion of all the issues.