new created database is not visible between connections

[高兴亮]

I'm trying to use h2 as a unit test. but I found the mem mode has some problem:  the new created database by connection 1 is not visibile from connection2.

below is an example code:

Class.forName("org.h2.Driver");
Connection connection = DriverManager.getConnection("jdbc:h2:mem:;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;");
Connection connection2 = DriverManager.getConnection("jdbc:h2:mem:;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;");
Statement s = connection.createStatement();
s.execute("create schema testdb");
s.execute("create table testdb.testt(id bigint not null primary key auto_increment, a int)");
PreparedStatement ps = connection.prepareStatement("insert into testdb.testt values (111, 33)", Statement.RETURN_GENERATED_KEYS);
int in = ps.executeUpdate();
System.out.println("use connection1 test:" + tableExists(connection, "testdb", "testt"));
System.out.println("use connection2 test:" + tableExists(connection2, "testdb", "testt"));



public static boolean tableExists(Connection conn, String dbName, String tableName) throws SQLException {
    String[] types = {"TABLE"};
    ResultSet rs = null;
    try {
        rs = conn.getMetaData().getTables(null, dbName, tableName, types);
        if(rs.next()) {
            return true;
        }
        else {
            return false;
        }
    }
    finally {
        if (rs != null) {
            rs.close();
        }
    }
}

the output is:

use connection1 test:true
use connection2 test:false

Any idea about this problem?

[Evgenij Ryazanov]

Hello.

Please, read mentioned by you on GitHub section of documentation again, especially after words

Sometimes multiple connections to the same in-memory database are required

https://h2database.com/html/features.html#in_memory_databases

[高兴亮]

Hi Evgenij,  the doc said how to share the database between connections.

but my question is: how to share NEW CREATE database between connections. 

the solution is doc ONLY applies to the only one database (which specified in URL).

In my case it is: url is starts with memory mode

conneciton 1 may create database "db1".

I want another conneciton 2 can see the new created db1. 

As you can see from my test code, it CAN'T be found.

在 2020年5月26日星期二 UTC+8下午9:10:18，Evgenij Ryazanov写道：

[Evgenij Ryazanov]

If your JDBC URL is

jdbc:h2:mem:

the database will be private to the connection, if this URL will be specified in another connection, this another connection will have another private database not related with the first one.

If your JDBC URL is

jdbc:h2:mem:someDatabaseName

the database will be shared between all connections within your Java process.

[高兴亮]

yes. you are right.  this url

jdbc:h2:mem:someDatabaseName

is okay for some FIXED database. but my problem is our code will create some database dynamically during runtime at same vm. 

and the new created database can't be found. that's what I mean.

[Evgenij Ryazanov]

Then you need to generate some name for it and pass this name to all connections. Use some counter, for example.

Don't forget about lifecycle of your in-memory databases. By default, they are removed when all connections to the specific database were closed. But when you're using DB_CLOSE_DELEAY=-1, they aren't removed and this may create a memory leak. If you use this parameter, you need to delete them with SHUTDOWN command.

[高兴亮]

I don't understand how to pass name to all connections.  I guess you mean set different jdbc url for different connections?

Our code use a data source pool to manage all connections (they share a same url).

So I guess there is no way to make h2 provide a JVM level memory database between connections (I think this behavior is more like what mysql is. and the only difference is the h2 is a memory db).

This behavior means:  all connections to this db shares SAME schema,tables collections. they are visible to each other.

Is that right?

[Evgenij Ryazanov]

What exactly do you want? Use the same database it all connections? Then choose some name for your in-memory database and pass JDBC URL with this name to your pool. Don't use jdbc:h2:mem:; use jdbc:h2:mem:something;

And don't use DATABASE_TO_UPPER=FALSE parameter if you need some compatibility with MySQL, use MODE=MySQL;DATABASE_TO_LOWER=TRUE instead.

[高兴亮]

So I will describe my case more clear.

The case is :

1. need a memory db environment for unit test purpose. This memory db env may contains more than one dynamica database/schema.

2. the url is NOT set dedicated to a schema eg: exampleDb. because below requirement.

3. The jvm has a jdbc connection pool which you need to set a jdbc url. and some connection will create a new memory db during jvm runtime and OTHER connection NEED to see this change (new created memory db).

 

[Evgenij Ryazanov]

1. In H2 (and many others) database and schema are different things. Database may have multiple schemas.

2. Each database has own connection URL. You can't use multiple URLs in one connection pool and can't use multiple databases in the one pool. Each database must have own connection pool.

3. You can use multiple schemas in one database, in that case you need to have only one connection pool and only one database. Each connection can change its current schema with Connection.setSchema() method and can access objects from other schemas too by prefixing names of objects with it

SELECT * FROM SCHEMA1.TABLE1

New schemas can be created with

CREATE SCHEMA schemaName

command.