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?

Answer 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).

Answer 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.