Thirdstage's Blog: Comparison of HSQLDB, H2, SQLite

Item	HSQLDB (1.8)	H2(1.1.109)	SQLite (3.6.22)
Lanaguage	Java	Java	C
Supports Client/Server Mode	Yes	Yes	No
Supports Embedded Mode	Yes	Yes	Yes
Supports In-Memory Databases	Yes	Yes	Yes
Provided Transaction Isolation Mode	Read Uncommitted	Read Committed, Serializable	Unclear
Supports Foreign Key Constraint	Yes	Yes	Yes (≥ 3.6.19)
Supports View Object	Yes	Yes	Yes
Supports Sequence Object	Yes	Yes	No
Supports User-defined Procedure/Function	Yes	No	No
Supports Trigger	Yes	Yes	Yes
Supports Inner Join	Yes	Yes	Yes
Supports Left Outer Join	Yes	Yes	Yes
Supports Right Outer Join	Yes	Yes	No
Supports Full Outer Join	Yes	Yes	No
Supports ORDER BY Clause	Yes	Yes	Yes
Supports GROUP BY/HAVING Clause	Yes	Yes	Yes
Supports Paginated Query	Yes (offet m fetch n lows)	Yes (limit n offset m)	Yes (limit n offset m)
Provides C API	No	No	Yes
Provides JDBC Driver	Yes	Yes	by 3rd party
Provides ODBC Driver	under development	Yes	No
Explicitly Supported by Hibernate 3.3	Yes	Yes	No
Explicitly Supported by DbUnit 2.4	Yes	Yes	No
Explicitly Supported by Power*Architect 0.9.13-RC1	Yes	No	No

100% Java
Support JDK 1.1.x, 1.2.x, 1.3.x, 1.4.x, 1.5.x, 1.6.x
Extensive JDBC interface support with batch statement and scrollable ResultSet functionality
Full JDBC DatabaseMetaData and ResultSetMetaData support
Java stored procedures and functions
Full support for PreparedStatement objects to speed up query processing

SQL

Relational Database Management System, with the object capabilities of Java
Very extensive support for SQL:2003 Standard syntax, including most optional features
Supports all base data types of the SQL Standard, including BINARY, BIT, BOOLEAN, date-time, INTERVAL, BLOB, CLOB
Supports user-defined DOMAIN types, including type constraints
Fast SELECT, INSERT, DELETE, UPDATE and MERGE operations
INNER, LEFT OUTER, RIGHT OUTER and FULL joins
UNION, EXCEPT, INTERSECT, including use of parentheses, limits and offsets
Scalar (single value) SELECTS, row and correlated subqueries including IN, EXISTS, ANY, ALL
Views, Temp tables and sequences
Primary key, unique and check constraints on single or multiple columns
Referential Integrity (foreign keys) on multiple columns with full cascading options (delete, update, set null, set default)
ORDER BY, GROUP BY and HAVING
COUNT, SUM, MIN, MAX, AVG and statistical aggregate functions
Full support for SQL expressions such as CASE .. WHEN .. ELSE .. , NULLIF etc.
SQL standard autoincrement column support plus sequences
Transaction COMMIT, ROLLBACK and SAVEPOINT support
Multiple schemata per database
Triggers, implemented as Java classes, or as SQL statements
Database security with passwords, user rights and roles with GRANT and REVOKE
Extensive set of ALTER TABLE commands, including change of table column type

Persistence

Deployment

Embedded (into Java applications) and Client-Server operating modes
Three client server protocols: HSQL, HTTP and HSQL-BER - can run as an HTTP web server - all with SSL option
Can be used in applets, read-only media (CD), inside jars, webstart and embedded applications
Multiple databases per JVM

Utilities

Powerful and compact java command line and GUI tools for database management
Tranfer tool for conversion of databases to / from other popular database managment systems

Additional Features

Disk based or in-memory databases and tables, read-only database support, temporary tables
Transaction support (read committed and serializable transaction isolation), 2-phase-commit
Multiple connections, table level locking
Cost based optimizer, using a genetic algorithm for complex queries, zero-administration
Scrollable and updatable result set support, large result set, external result sorting, functions can return a result set
Encrypted database (AES or XTEA), SHA-256 password encryption, encryption functions, SSL

SQL Support

Support for multiple schemas, information schema
Referential integrity / foreign key constraints with cascade, check constraints
Inner and outer joins, subqueries, read only views and inline views
Triggers and Java functions / stored procedures
Many built-in functions, including XML and lossless data compression
Wide range of data types including large objects (BLOB/CLOB) and arrays
Sequence and autoincrement columns, computed columns (can be used for function based indexes)
ORDER BY, GROUP BY, HAVING, UNION, LIMIT, TOP
Collation support, users, roles
Compatibility modes for IBM DB2, Apache Derby, HSQLDB, MS SQL Server, MySQL, Oracle, and PostgreSQL.

Security Features

Includes a solution for the SQL injection problem
User password authenticated uses SHA-256 and salt
User passwords are never transmitted in plain text over the network (even when using insecure connections)
All database files (including script files that can be used to backup data) can be encrypted using AES-256 and XTEA encryption algorithms
The remote JDBC driver supports TCP/IP connections over SSL/TLS
The built-in web server supports connections over SSL/TLS
Passwords can be sent to the database using char arrays instead of Strings

Other Features and Tools

Small footprint (smaller than 1 MB), low memory requirements
Multiple index types (b-tree, tree, hash)
Support for multi-dimensional indexes
CSV (comma separated values) file support
Support for linked tables, and a built-in virtual 'range' table
EXPLAIN PLAN support, sophisticated trace options
Database closing can be delayed or disabled to improve the performance
Web-based Console application (English, German, partially French and Spanish) with autocomplete
The database can generate SQL script files
Contains a recovery tool that can dump the contents of the data file
Support for variables (for example to calculate running totals)
Automatic re-compilation of prepared statements
Uses a small number of database files
Uses a checksum for each record and log entry for data integrity
Well tested (high code coverage, randomized stress tests)

Thirdstage's Blog