CODEWITHSUNDEEP
spring-booth2jdbctemplate
J.Pip
J.Pip

Reputation: 4613

Spring jdbctemplate h2 dynamically switch schema

I'm trying to have h2's in memory database switch schema's during unit testing. Almost all my data is in the default schema PUBLIC but to mimic how production works I need to switch to another schema SETTINGS for 1 query.

I have tried to switch schema's with jdbcTemplate.getDataSource().getConnection().setSchema("SETTINGS"); but when I log the value of jdbcTemplate.getDataSource().getConnection().getSchema(); I get the default public one.
So my query fails because I get a BadSqlGrammarException because the table I try to query isn't found, which makes sense since it's in another schema.

Can anyone help me out with this one?

Thx!

Upvotes: 0

Views: 1039

Answers (1)

Niki Driessen
Niki Driessen

Reputation: 36

You can either prefix the table with your schema (assuming the user you are using to connect to the database has sufficient privileges) like:

SELECT * from SETTINGS.you_other_table;

Or, if you can create another connection which specifies the schema in the connection string.

I would recommend to use the prefix as that makes it very clear you are using another schema.

Upvotes: 1

Related Questions