How to specify schema for SQL Server in JDBC Spring configuration

Question

We are using the org.apache.commons.dbcp.BasicDataSource as parent class for our datasources in Spring.

Is it possible to specify which schema to use in either the "url" or in another property in the DBCP datasource?

Solution

I don't think it's possible to specify the schema since this is a function of the user, not the JDBC session. You should probably create a user with their default schema set to each schema you want to use, and then specify the user in the dbcp configuration.

Comments

[Michael Böckling]

That is for DB2, I don't think it works for SQL Server, as currentSchema is not listed in the available properties: msdn.microsoft.com/en-us/library/ms378988.aspx

[Andreas Selenwall]

Yes, I guess that is the way to do it. Thanks alot for your help, appreciate it.

[Oly Dungey]

This example can not work for sql server as it doesn't support schemas in that way, please remove this solution.