Spring Datasource and Database Schema

Question

I am trying to declare a Spring datasource pointing to a DB2 database. Presently I am using a org.springframework.jdbc.datasource.DriverManagerDataSource to setup the connection but am not finding any way to specify the database schema in the database in the datasource bean. Could anyone help me on this?

Solution 1

Problem is there is no standard way to set the schema, each database has a different mechanism.

A work around is to set the schema as part of the db url...

For db2 the url will look something like:

jdbc:db2://SERVER_NAME:PORT/DATABASE:currentSchema=SCHEMA_NAME;

hope that helps...

Special note: make sure you add the semicolon ; at the end of the URL, otherwise you will get errors saying URL is invalid. Also make sure nothing after last ; exists (not even spaces).

Solution 2

There isn't a means to do this with the standard Spring namespace. Rob Harrop's response to a request to add the schema to the configuration:

In general, this kind of functionality should be pushed into the connection pool since there is no really elegant and performant way to do this via a decorator. The pool can set the schema once per connection it creates, whereas here you have to set it each time a connection is retrieved.

If you're desperate to set the proxy in your configuration, the submitter included some code for a proxy to allow the schema to be specified.

Comments

[Barun]

Then does this mean the schema can be specified only for the pool like when using c3p0. Because I could not find any resources to specify the schema when using a c3p0 connection pool.

[Rich Seller]

sorry I've never used C3p0, so I'd only be speculating how to set it

[gdrt]

The last note is for db2 only.

[MrD]

This is correct answer that saved me a lot of time. Thank you.