HSQLDB (''Hyper SQL Database'') is a

relational database management system A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970. A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured for ...

written in

Java Java is one of the Greater Sunda Islands in Indonesia. It is bordered by the Indian Ocean to the south and the Java Sea (a part of Pacific Ocean) to the north. With a population of 156.9 million people (including Madura) in mid 2024, proje ...

. It has a

JDBC driver A JDBC driver is a software component enabling a Java application to interact with a database. JDBC drivers are analogous to ODBC drivers, ADO.NET data providers, and OLE DB providers. To connect with individual databases, JDBC (the Java Databa ...

and supports a large subset of

SQL-92 SQL-92 (also called SQL 2) was the third revision of the SQL database query language. Unlike SQL-89, it was a major revision of the standard. Aside from a few minor incompatibilities, the SQL-89 standard is forward-compatible with SQL-92. The ...

, SQL:2008, SQL:2011, and SQL:2016 standards. It offers a fast, small (around 1300 kilobytes in version 2.2)

database engine A database engine (or storage engine) is the underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database. Most database management systems include their own application ...

which offers both in-memory and disk-based tables. Both embedded and server modes are available. Additionally, it includes tools such as a minimal

Web server A web server is computer software and underlying Computer hardware, hardware that accepts requests via Hypertext Transfer Protocol, HTTP (the network protocol created to distribute web content) or its secure variant HTTPS. A user agent, co ...

, command line and GUI management tools (can be run as applets), and a number of demonstration examples. It can run on Java runtimes from version 1.1 upwards, including free Java implementations such as Kaffe. HSQLDB is available under a

BSD license BSD licenses are a family of permissive free software licenses, imposing minimal restrictions on the use and distribution of covered software. This is in contrast to copyleft licenses, which have share-alike requirements. The original BSD lic ...

. It is used as a database and persistence engine in many

open source Open source is source code that is made freely available for possible modification and redistribution. Products include permission to use and view the source code, design documents, or content of the product. The open source model is a decentrali ...

software projects, such as descendants of OpenOffice.org Base (i.e.,

Apache OpenOffice Base The Apache ( ) are several Southern Athabaskan language-speaking peoples of the Southwest, the Southern Plains and Northern Mexico. They are linguistically related to the Navajo. They migrated from the Athabascan homelands in the north into th ...

LibreOffice Base LibreOffice Base is a free and open-source database development and administration tool for relational database management systems that is part of the LibreOffice suite. LibreOffice Base was built off of a fork of OpenOffice.org and was first ...

, etc.), and the

Jitsi Jitsi (from — "wires") is a collection of free and open-source software, free and open-source multiplatform Voice over IP, voice (VoIP), Video conference, video conferencing and instant messaging applications for the Web platform, Windows, Li ...

VoIP and video-conference client since version 2.6. It is also used in commercial products, such as

Mathematica Wolfram (previously known as Mathematica and Wolfram Mathematica) is a software system with built-in libraries for several areas of technical computing that allows machine learning, statistics, symbolic computation, data manipulation, network ...

and InstallAnywhere (starting with version 8.0).

Transaction support

HSQLDB version 2.0 has three transaction control modes. It supports read committed and serializable transaction isolation levels with table level locks or with

multiversion concurrency control Multiversion concurrency control (MCC or MVCC), is a non-locking concurrency control method commonly used by database management systems to provide concurrent access to the database and in programming languages to implement transactional memory. ...

(MVCC), or a combination of locks and MVCC. Version 1.8.1 supports transaction isolation level 0 (read uncommitted) only.

Data storage

HSQLDB has two main table types used for durable read-write data storage, i.e., if a transaction has been successfully committed, it is guaranteed that the data will survive system failure and will keep their integrity. The default MEMORY type stores all data changes to the disk in the form of a

SQL Structured Query Language (SQL) (pronounced ''S-Q-L''; or alternatively as "sequel") is a domain-specific language used to manage data, especially in a relational database management system (RDBMS). It is particularly useful in handling s ...

script. During engine start-up, these commands are executed and data are reconstructed into the memory. Another table type is CACHED, which allows one to store more data, at the cost of the slower performance. The HSQLDB engine loads them only partially and synchronizes the data to the disk on transaction commits. However, the engine always loads all rows affected during an update into the memory. This renders very large updates impossible without splitting the work into smaller parts. Other table types allow access to

comma-separated values Comma-separated values (CSV) is a text file format that uses commas to separate values, and newlines to separate records. A CSV file stores Table (information), tabular data (numbers and text) in plain text, where each line of the file typically r ...

(CSV) files. These tables can participate, for example, in queries with JOINs and simplify spreadsheet processing and read-write non-durable in-memory data storage.

SQL features

HSQLDB 2.0 supports all the core features and many optional features of SQL:2008. Advanced features include user-defined SQL procedures and functions, schemas, datetime intervals, updatable views, arrays, lobs, full and lateral joins and set operations. Many non-standard functions such as TO_CHAR and DECODE are also supported. Extensions to standard SQL include user-defined

aggregate function In database management, an aggregate function or aggregation function is a function where multiple values are processed together to form a single summary statistic. Common aggregate functions include: * Average (i.e., arithmetic mean) * Count ...

Releases

Several versions of HSQLDB have been released since 2001. Early versions were based on the discontinued HypersonicSQL database engine. Version 2.0, released in 2010, is mostly new code, written to conform to Standard

and

JDBC Java Database Connectivity (JDBC) is an application programming interface (API) for the Java (programming language), Java programming language which defines how a client may access a database. It is a Java-based data access technology used for Java ...

4 Specification. Version 2.3.2 (released in 2014) is fully multi-threaded and supports high performance

two-phase locking In databases and transaction processing, two-phase locking (2PL) is a pessimistic concurrency control method that guarantees conflict-serializability. Philip A. Bernstein, Vassos Hadzilacos, Nathan Goodman (1987) ''Concurrency Control and Recov ...

and MVCC (multiversion concurrency control) transaction control models.

References

External links