4

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.

kohane15
  • 809
  • 12
  • 16
Dhwanit
  • 610
  • 1
  • 9
  • 17
  • Couldn't you use the [persistent mode](http://h2database.com/html/features.html#embedded_databases) with a [large cache](http://h2database.com/html/grammar.html#set_cache_size)? As for persisting an in-memory database, did you consider the [`SCRIPT TO`](http://h2database.com/html/grammar.html#script) command? – Thomas Mueller Aug 10 '13 at 19:51
  • Large cache is a good option but not for aggregate queries or those which require full table scan in absence of indexes. Also to add explicitly caching indexes will prove to be a good strategy. SCRIPT TO looks promising but has to be triggered like checkpoint sync to take periodic snapshot of db to maximize recovery to latest db state in case of any system-failure/power-off/out-of-memory. What do you say Thomas Mueller ? – Dhwanit Aug 10 '13 at 22:40

2 Answers2

4

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.

Thomas Mueller
  • 48,905
  • 14
  • 116
  • 132
  • By the way even by giving large cache size would require the sql engine to atleast once move only that part of data from disk to cache which is required to serve that query and later that data can stay in cache. But by the way how internally caching works is still a question. Can you explicitly command H2 in persistent mode to keep some 'X' table in cache ? And for "SCRIPT TO" it looks promising for memory-mode for some extent but it has to be triggered like checkpoint event to take periodic snapshots of db to maximize recovery to latest near time db state in case of any failure. – Dhwanit Aug 11 '13 at 08:55
  • "atleast once move only that part of data from disk to cache": sure, how else do you want to load the data from disk to memory? If you don't want to load it to memory, then you don't need to persist the change at all. "how internally caching works is still a question": what is your question, and why do you need to know? – Thomas Mueller Aug 12 '13 at 06:28
0

It is not that much difficult just do follow my below code change

Please do change spring.datasource.url=jdbc:h2:~/test;MVCC=true;DB_CLOSE_DELAY=-1;MODE=Oracle this will create a test data file in local system C:\Users\yourname .. here test.mv file will create

I hope it may help you