[FIXED] 2 databases on the one H2 instance

Issue

I have JPA Repository with @Query that joins tables from other DBs, that located on the same server.

SELECT id,co.name from Agenc a inner join[other_db_mame].[schema_name].[table_name] co ..... 

I want to write integration tests to cover the flow with this query. In intégration tests I use H2 DB And my question is, how to correctly configure H2 DB to have 2 DBs and make this query work? Maybe there is a way, to create another db via scripts, or smth like this?

Solution

H2 supports direct access only to one database at once, but you can create linked tables to tables from other databases.

To create a linked table, you can use a CREATE LINKED TABLE command: https://h2database.com/html/commands.html#create_linked_table

CREATE LINKED TABLE targetTableName('', 'jdbcURL', 'username', 'password', 'sourceTableName');

You can also link the whole schema with LINK_SCHEMA function: https://h2database.com/html/functions.html#link_schema

CALL LINK_SCHEMA('targetSchemaName', '', 'jdbcURL', 'username', 'password', 'sourceSchemaName');

Note that format of fully-qualified table name in H2 (and in the SQL Standard) is catalogName.schemaName.tableName. H2 supports only one catalog (and its name is the same as a name of database) and you can't define additional catalogs. Non-standard -syntax with [identifier] is not accepted by H2 unless you use a MSSQLServer compatibility mode. In this mode you can use that syntax, but you can't have different names of catalogs anyway, so if they are fixed in your application, you have a problem.

Actually H2 can ignore the specified name of catalog if IGNORE_CATALOGS setting is set to TRUE: https://h2database.com/html/commands.html#set_ignore_catalogs

SET IGNORE_CATALOGS TRUE;

But if combinations of schema and table name aren't unique in your original configuration, there is nothing to do with H2. You can't create different tables with the same schema and table name in H2 in any way.

Answered By - Evgenij Ryazanov