Going through the documentation of HSQLDB, it appears that even if you specify a file in the connection URL, it will still hold some of the table data in memory. Would setting the property hsqldb.cache_rows to 0 do what I want?
Essentially, I want it to behave like sqlite where the data is stored only in the file. After an operate (select/insert/update/delete), it will simply modify this file instead of having to make any in-memory changes. And as a result, when the application is restarted, it can simply just read from this file for the previously persisted data. Is it possible to replicate this behavior?
All database systems make changes to memory before writing to disk.
If your question is about all changes being committed to disk, these are the settings and options.
The property setting hsqldb.write_delay=false forces all changes to disk immediately at commit time. See http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html#dpc_db_file_mem
If you use CREATE TEXT TABLE to store table data in text files, all data is written to disk at commit. See http://hsqldb.org/doc/2.0/guide/texttables-chapt.html