in-process mode hsqldb doesn't create tables and doesn't save values

Question

My main class:

public static void main(String[] args) {
    Connection con = null;
    Statement stmt = null;
    int result = 0;
    try {
        Class.forName("org.hsqldb.jdbc.JDBCDriver");
        con = DriverManager.getConnection("jdbc:hsqldb:file:data", "SA", "SA");
        stmt = con.createStatement();
        result = stmt.executeUpdate("CREATE TABLE surr (ID INTEGER IDENTITY NOT NULL, NAME VARCHAR(40) NOT NULL ) ");
        con.commit();
    } catch (Exception e) {
        e.printStackTrace(System.out);
    }
    System.out.println(result + " rows effected");
    System.out.println("Rows inserted successfully");
}

Pom xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>


    <groupId>org.example</groupId>
    <artifactId>untitled</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>2.5.0</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

The automatically generated data.script file absolutely refuses to create tables. Once it was possible to create a table, and even managed to insert a single row, but no more. I found out that if you insert rows into a miraculously created table, the rows are created, they can be obtained in the current connection, but when the main method completes, the changes are not saved in data. script. What can be done about it?

Answer 1

There are multiple ways to solve the issue.

The default setting has a write delay of 500 milliseconds. If you want the commit to write the changes to disk immediately, change the write delay:

con = DriverManager.getConnection("jdbc:hsqldb:file:data;hsqldb.write_delay=false", "SA", "SA");

If you close the connection as suggested in comments, all the committed changes are written.

A good option is to also shutdown the database when your program ends:

stmt.executeUpdate("SHUTDOWN");

The shutdown writes all the database files in a form that takes less time to load when you restart your program and connect to the database.