Set H2 schema on connection

Question

I am configuring an h2 test database for our oracle production database. All tables are of the schema xxx. My datasource is defined as below:

public DataSource dataSource() {
    JdbcDataSource ds = new JdbcDataSource();
    ds.setUrl("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;MODE=Oracle;TRACE_LEVEL_SYSTEM_OUT=2;INIT=CREATE SCHEMA IF NOT EXISTS xxx;SCHEMA=xxx");
    ds.setUser("xxx");
    ds.setPassword("xxx");
    return ds;
}

With SCHEMA=xxx, I get an error: Caused by: org.h2.jdbc.JdbcSQLException: Schema "xxx" not found; SQL statement: SET SCHEMA xxx [90079-186]

Without SCHEMA=xxx, I get errors whenever Hibernate attempts to run a query with a join because it does not prepend the schema to the table name. It does this in production with our oracle database though.

Edit: To provide some more insight, I am populating my db from creation scripts that are also used in production:

@Bean
public DataSourceInitializer dataSourceInitializer(final DataSource dataSource) {
    final DataSourceInitializer initializer = new DataSourceInitializer();
    initializer.setDataSource(dataSource);
    initializer.setDatabasePopulator(databasePopulator());
    return initializer;
}

private DatabasePopulator databasePopulator() {
    final ResourceDatabasePopulator populator = new ResourceDatabasePopulator();
    populator.setSeparator(";");
    populator.setCommentPrefix("--");
    populator.addScript(new ClassPathResource("db-schema.sql"));
    populator.addScript(new ClassPathResource("db-init-data.sql"));
    return populator;
}

Have you verify that the schema exist? Case sensitive maybe? — Hoàng Long, Mar 24 '15 at 07:18
It exists because it is created with `INIT=CREATE SCHEMA IF NOT EXISTS xxx`. Everything else works as expected besides queries that cause joins. — Roger, Mar 24 '15 at 15:41

score 15 · Accepted Answer · answered Mar 24 '15 at 16:43

15

This connection string worked: ds.setUrl("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;MODE=Oracle;TRACE_LEVEL_SYSTEM_OUT=2;INIT=CREATE SCHEMA IF NOT EXISTS xxx\\;SET SCHEMA xxx");

answered Mar 24 '15 at 16:43

Roger

10,851
3
26
39

score -1 · Answer 2 · edited May 23 '17 at 10:28

-1

I suggest that you should use hibernate.hbl2ddl.auto=create to create scheme on connection.

Here some info Hibernate hbm2ddl.auto possible values and what they do?

edited May 23 '17 at 10:28

Community

1
1

answered Mar 23 '15 at 21:40

Antoniossss

31,590
6
57
99

2

My DDL script has the proper schema and the table is created under that schema. The only issue is that joins are looking for a table called `ROLE_PERMISSION` not `xxx.ROLE_PERMISSION` – Roger Mar 24 '15 at 16:10

Set H2 schema on connection

2 Answers2