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?

score 16 · Accepted Answer · edited Mar 20 '15 at 12:42

16

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

edited Mar 20 '15 at 12:42

Jorge Ferreira

96,051
25
122
132

answered Aug 08 '09 at 12:50

Michael Wiles

20,902
18
71
101

The last note is for db2 only. – gdrt Feb 28 '18 at 09:16
This is correct answer that saved me a lot of time. Thank you. – MrD Feb 12 '20 at 09:52

Rich Seller · Answer 2 · 2009-08-08T10:11:13.227

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.

edited Aug 08 '09 at 10:11

answered Aug 08 '09 at 10:01

Rich Seller

83,208
23
172
177

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. – Barun Aug 08 '09 at 10:19
sorry I've never used C3p0, so I'd only be speculating how to set it – Rich Seller Aug 08 '09 at 10:57
I think "connection pool" in this case means the app server you're using: WebLogic, JBOSS, Glassfish, Tomcat, Jetty or, god forbid, WebSphere. – duffymo Aug 08 '09 at 11:39

Arun P Johny · Answer 3 · 2009-08-11T14:09:40.583

If your connection uses the owner of the schema as the user then that connection will point to that particular schema. ie. If user user1 is the owner of the database schema named schema1 then if you connect to the database using user user1 then by default the connection will point to schema1.

We have used UserCredentialsDataSourceAdapter provided by spring to connect to different schemas based on the logged in user. This provides a datasource which is pointing to a particular schema based on the user. This uses a thread based login information. Each thread has to decide to which schema it has to connect and provide the user according to that.

Spring Datasource and Database Schema

3 Answers3

Linked