HSQLDB - 100% Java Database
- <Download>
<Support> <License>
- <Features> <FAQ> <Documentation> <Changes>
- <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
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. If you want to re-compile HSQLDB, you will need a JDK and ANT. See the documentation in the /build directory.
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' and an empty password.
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 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.
Version 1.8 databases must be shutdown properly before they are opened with version 2.0. If the database is not read-only, it will be upgraded to the latest version.
With CACHED tables, the procedures in the System Management and Deployment Issues section of the Guide should be followed.
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 section 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 exceeds the Intermediate level of the old standard and the Core level of the new Standard. It 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, HSQLDB is comparable in
speed to fast, non-java open-source RDBMS engines. It is generally faster
than java open-source engines in most operations, in comparable persistence
settings. 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. 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.0 comes after version 1.7.2 / 1.7.3 / 1.8.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.0 is an improved and hardened version of the persistence engine of 1.8 which has been in use for over 4 years.
HSQLDB 2.0 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.0) size limit of an HSQLDB database is 16GB (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. Extensive tests have been made 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.
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 committed data 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.
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.0 empty spaces are tracked better and DEFRAG will be performed automatically, based on a user-defined property.
HSQLDB
2.0 supports READ COMMITTED and SERIALIZABLE isolation levels. It
supports both lock-based an multiversion (MVCC) transaction models.
See the Guide for a fuller discussion
of all the issues.