in memory database h2 how long keep connection open?

Question

I am testing currently a H2 DB in memory mode. I set up a connection by

h2Con = DriverManager.getConnection( 
                "jdbc:h2:mem:db1", "SA", "");

I want to some imports with dbunit and set up dbUnits db connection

IDataBaseConnection dBUnitConnection = new DatabaseConnection(h2con);

and the imports which i want to query later

So my question is, in memory mode, when can i close the connection? Normaly i do something like this

try{
   //some sql query
}catch{
   //error handling
}finally{
    if(connection!=null)
        connection.close()
}

But in memory if the connection is closed i loose the data? So should it stay open until i end my program?

Answer 1

Add DB_CLOSE_DELAY=-1 in URL

From H2 documentation:

By default, closing the last connection to a database closes the database. For an in-memory database, this means the content is lost. To keep the database open, add ;DB_CLOSE_DELAY=-1 to the database URL. To keep the content of an in-memory database as long as the virtual machine is alive, use jdbc:h2:mem:test;DB_CLOSE_DELAY=-1.

So you can configure H2 to keep in-memory database intact due to the lifetime of your JVM and then you can connect and disconnect to it as you wish.

So, this:

JdbcDataSource ds = new org.h2.jdbcx.JdbcDataSource();
ds.setURL("jdbc:h2:mem:example_db_");
ds.setUser("scott");
ds.setPassword("tiger");

…becomes this:

JdbcDataSource ds = new org.h2.jdbcx.JdbcDataSource();
ds.setURL("jdbc:h2:mem:example_db_;DB_CLOSE_DELAY=-1"); // ⬅ Add ‘delay’ element to URL.
ds.setUser("scott");
ds.setPassword("tiger");

Answer 2

In some situations, the database should not be closed in this case, for example because the database is still used at virtual machine shutdown (to store the shutdown process in the database for example). For those cases, the automatic closing of the database can be disabled in the database URL. The first connection (the one that is opening the database) needs to set the option in the database URL (it is not possible to change the setting afterwards). The database URL to disable database closing on exit is:

String url = "jdbc:h2:~/test;DB_CLOSE_ON_EXIT=FALSE";