Any ideas for persisting H2 Database In-Memory mode transaction?

Question

The following code for H2 database with "in-memory mode" runs perfectly fine until connection is open or VM is running. But H2 db loses data when connection is closed or when VM shutdown takes place. Is there any other way to persist data across multiple startup-shutdown/online-offline cycles ?

One way would be to create diskbased replica of in-memory database by tracking DDLs and DMLs issued from application and a sync process in background that checks for integrity of data on disk and memory. Diskbased DMLs might be slower + additional overhead of copying/loading disk data to memory on each startup, will be there but still persistence will be achievable to some extent.

Are there any other ways provided by H2 for persistence issue with in-memeory mode or any other workarounds ?

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class H2InMemoryModeTest {        

public static void main(String[] args)
    {
        try
        {
            Class.forName("org.h2.Driver");

           DriverManager.getConnection("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1","sa","sa");

            Statement stmt = con.createStatement();

            //stmt.executeUpdate( "DROP TABLE table1" );
            stmt.executeUpdate( "CREATE TABLE table1 ( user varchar(50) )" );
            stmt.executeUpdate( "INSERT INTO table1 ( user ) VALUES ( 'John' )" );
            stmt.executeUpdate( "INSERT INTO table1 ( user ) VALUES ( 'Smith' )" );
            ResultSet rs = stmt.executeQuery("SELECT * FROM table1");

            while( rs.next() )
            {
                String name = rs.getString("user");
                System.out.println( name );
            }
            stmt.close();
            con.close();
        }
        catch( Exception e )
        {
            System.out.println( e.getMessage() );
        }
    }
}

Kindly Help. Thanks.

Answer 1

You could use the persistent mode with a large cache. With 'large' I mean so that the whole database fits in memory. That way even table scans will not read from disk.

To persist an in-memory database, you could use the SCRIPT command, but you need to execute it manually.