23 Years of HyperSQL: Java’s Reliable Database Marks Milestone with Major Update

HSQLDB stands as a testament to Java's versatility in database management - a pure Java RDBMS that emerged from the innovative Hypersonic SQL initiative back in 2001.

What makes HSQLDB particularly interesting is its chameleon-like ability to operate in multiple modes: it can run as a full server, work standalone, or seamlessly embed itself within applications.

Think of HSQLDB as the Swiss Army knife of Java databases - it effortlessly juggles in-memory operations for lightning-fast performance while also offering rock-solid disk-based storage when persistence matters.

Unlike many databases, HSQLDB's footprint is remarkably small, yet it packs a comprehensive SQL feature set that rivals its larger counterparts.

The database has found its sweet spot in several niches. Development teams at industry giants like Oracle and the Apache Software Foundation have embraced HSQLDB for its perfect fit in testing environments.

Its ability to spin up quickly, operate without fuss, and integrate naturally with Java applications makes it invaluable for developers who need a reliable testing backend.

What's particularly compelling about HSQLDB is how it shines in scenarios where traditional heavyweight databases would be overkill. In my experience working with clients, it's proven especially valuable for applications that need embedded data management without the complexity of external database servers.

The pure Java implementation means developers can focus on building features rather than wrestling with database compatibility issues.

HSQLDB represents a pragmatic approach to database design - it doesn't try to be everything to everyone, but instead excels at being the right tool for specific scenarios, particularly in Java-centric environments where efficiency and simplicity are paramount.

Features

Here’s a reorganized list of HSQLDB’s features:

General and Performance

• Original code based on in-depth database theory and SQL standards, offering extensive SQL feature support.
• Ahead of other open-source solutions in supported SQL functionality, optimized for small and medium-sized databases.
• Extremely fast SQL performance, especially for lightweight use cases.
• Multi-threaded architecture with compact code footprint.
• Three transaction control models, including lock-based and MVCC.

Java Compatibility

• 100% Java, compatible with JRE 8 and above, with module support for JRE 11.
• Extensive JDBC interface support, including batch statement processing and scrollable ResultSets.
• Full JDBC DatabaseMetaData and ResultSetMetaData compatibility.
• Supports updatable and insertable ResultSets for enhanced data manipulation.
• Enables user-defined Java stored procedures, functions, and aggregate functions.
• Supports Java procedures with multiple INOUT parameters, returning multiple ResultSets and Array objects.
• Includes SQL and Java triggers, with options for synchronous and asynchronous execution.
• Full support for CallableStatement and PreparedStatement with batch execution.

SQL Standard Compliance

• Extensive support for SQL:2023 Standard, covering many optional features and data types.
• Relational Database Management System (RDBMS) that can store Serializable Java objects.
• Advanced transaction support with COMMIT, ROLLBACK, and SAVEPOINT.
• Offers recursive queries, natural and outer joins, and extensive built-in functions.
• Provides INNER, LEFT OUTER, RIGHT OUTER, and FULL joins with various join types.
• Support for primary keys, unique constraints, and foreign keys with cascading options.
• SQL triggers, including blocks of SQL statements, INSTEAD OF triggers, and loop/condition support.
• Extended SQL auto-increment IDENTITY columns and sequence functionality.
• Built-in functions for statistical analysis, aggregation, and various data operations.
• Includes a range of SQL expressions (e.g., CASE, NULLIF, MATCHES) and INTERVAL expressions (e.g., CURRENT_DATE - 3 MONTH).
• Supports SQL-generated columns, arrays, and user-defined DOMAIN types.

Persistence and Data Storage

• Supports in-memory tables for high-speed operations and disk-based tables for large datasets.
• Disk tables (CACHED TABLE) support up to 8TB, with text tables up to 256GB and LOBs up to 64TB.
• No memory limits for individual CLOB/BLOB sizes.
• Fast database startup and shutdown, with incremental backups and both online and offline backup options.
• External file data sources (e.g., CSV) can be used as SQL tables.

Deployment Options

• Versatile deployment as embedded, client-server, or applet-based, including compatibility with CD and webstart applications.
• Operates in embedded and client-server modes with HSQL, HTTP, and HSQL-BER protocols (with SSL support).
• Supports multiple databases per JVM.
• ODBC support for additional connectivity options.

Tools and Utilities

• SQLTool, a robust Java command-line tool for database management, with compatibility for major database systems.
• Two GUI database management tools for ease of use.
• Transfer tool for database conversions between other database systems.
• Test and script utility for running SQL scripts with user-defined validations.

This reordered list showcases HSQLDB's versatility across Java integration, SQL standards, persistence, deployment, and utilities, enhancing its appeal for both embedded applications and lightweight client-server implementations.

HyperSQL 23rd Anniversary Release Highlights

The latest release of HyperSQL, version 2.7.4, marks its 23rd anniversary with key enhancements and compliance with the latest SQL:2023 standard. This release introduces significant new features that broaden its SQL capabilities and enhance performance, making it even more versatile and powerful for Java-based applications.

New Features in Version 2.7.4

1. Expanded SQL:2023 Support: HyperSQL now includes new SQL:2023 aggregate functions, such as ANY_VALUE and LISTAGG, along with an enhanced CAST function that supports templates. These additions further extend its SQL Standard compliance, keeping HyperSQL ahead in terms of feature breadth among open-source databases.
2. Java 11 Module and Java 8 Compatibility: The release offers two separate Java versions—Java 11 modules and Java 8 jars, making it adaptable to modern as well as legacy Java environments.
3. Enhanced JSON and CSV Handling: Building on recent updates, HyperSQL includes JSON constructor functions and streamlined support for CSV data load/unload. These features make handling structured and unstructured data more efficient.
4. Temporal System-Versioned Tables: This feature allows historical tracking of data changes over time, enabling users to query past data snapshots with PERIOD predicates, making it ideal for audit trails and change tracking.
5. Fine-Grained Row-Level Access Control: HyperSQL now provides row-level access permissions, allowing detailed control over data visibility and user access, which enhances data security.
6. Replication and Synchronization: Log-based synchronization ensures that replicas stay up-to-date, improving consistency in distributed environments.
7. Additional JDBC and Data Management Features: HSQLDB’s support for Java 8 java.time classes in JDBC, UUID data type, auto-updated TIMESTAMP columns, and SQL syntax compatibility with MySQL’s REPLACE, INSERT IGNORE, and ON DUPLICATE KEY UPDATE options further extend its usability in complex Java applications.

Platforms

• Windows
• Linux
• macOS

License

BSD License

Resources & Downloads

• Home

HSQLDB

hsqldb: Full-featured 100% Java RDBMS

hsqldb.org Home Page