Setting Schema for Each DataSource.getConnection() Call

[cyboc]

I'm working on a multi-company system where each company's data is stored in a separate schema in a single physical database. During an application session, the user can work with more than one company's data. However, during each business method invocation, all data access will be within one company and hence one schema.

All of my SQL is written WITHOUT explicit schema references. For example, my statements look like this:
SELECT * FROM ACCOUNT
Instead of this:
SELECT * FROM MY_COMPANY.ACCOUNT

By not using explicit schema references, I avoid having to change the SQL at runtime. For this to work, I rely on setting the default schema on the database connection just after it is acquired from DataSource.getConnection(). For example, in DB2, this is acheived by executing this SQL:
SET CURRENT SCHEMA = MY_COMPANY

I'm currently converting the application to use Spring. My question is, what is a good way of setting the schema each time getConnection() is called? I thought that maybe I could derive from org.springframework.jdbc.datasource.DelegatingData Source to create a schema-setting proxy for a target DataSource. Would this work?

[Alef Arendsen]

Yes this is the right way to do things.

I assume that based on some property you figure out in each request, you determine the schema to use (based on the principal or something?). If it's bound to the HttpSession or HttpServletRequest, implement a HandlerInterceptor, bind the proeprty that determines the schema to a ThreadLocal and pick the ThreadLocal up again in your DelegateDataSource. Override getConnection(), retrieve the connection, execute the set current schema call and you're done I guess.

Alef

[davsclaus]

Yes this is the right way to do things.

I assume that based on some property you figure out in each request, you determine the schema to use (based on the principal or something?). If it's bound to the HttpSession or HttpServletRequest, implement a HandlerInterceptor, bind the proeprty that determines the schema to a ThreadLocal and pick the ThreadLocal up again in your DelegateDataSource. Override getConnection(), retrieve the connection, execute the set current schema call and you're done I guess.

Alef

Cool idea I just have the same problem using pure Hibernate.
I did not figure out to do this in Hibernate only so another strong argument for using Spring. Well if you start getting the verbose .xml configuration narrowed down. I can see there is a JIRA on this.

[cyboc]

I assume that based on some property you figure out in each request, you determine the schema to use

Exactly.

I implemented my DelegatingDataSource yesterday afternoon and it works great. In doing so, I discovered that org.springframework.jdbc.support.SQLErrorCodes.get DatabaseProductName()
made it very easy to discover the database product name at runtime so that I could choose the correct SQL syntax to use for setting the schema (the syntax is product specific and I don't believe there is a standard way to do it with the JDBC API).

Cheers,
Joe

[davsclaus]

I assume that based on some property you figure out in each request, you determine the schema to use

Exactly.

I implemented my DelegatingDataSource yesterday afternoon and it works great. In doing so, I discovered that org.springframework.jdbc.support.SQLErrorCodes.get DatabaseProductName()
made it very easy to discover the database product name at runtime so that I could choose the correct SQL syntax to use for setting the schema (the syntax is product specific and I don't believe there is a standard way to do it with the JDBC API).

Cheers,
Joe

Maybe in the future it could be a part of Spring if we figure out how to do it in several other database as well?

If you could donate the code and add a JIRA for it your solution will not be forgotten and lost in the ever growing masses of topics in this forum.

I need a similar solution for my current project as we use different schemas pr. request also. Do you mind to share it? At JIRA or by e-mail?

Regards

Claus

[cyboc]

Claus,

Maybe in the future it could be a part of Spring if we figure out how to do it in several other database as well?

If you could donate the code and add a JIRA for it your solution will not be forgotten and lost in the ever growing masses of topics in this forum.

I need a similar solution for my current project as we use different schemas pr. request also. Do you mind to share it? At JIRA or by e-mail?

I'm more than happy to share the code. What is JIRA and how do I submit code to it?

Joe

[cyboc]

Okay, I've created a "New Feature" request in JIRA. I also posted my stupid, hackish, trivial implementation there. You can view it at:

http://opensource.atlassian.com/proj...browse/SPR-388

I hope someone besides me finds this useful. :wink:

Cheers,
Joe

[davsclaus]

Okay, I've created a "New Feature" request in JIRA. I also posted my stupid, hackish, trivial implementation there. You can view it at:

http://opensource.atlassian.com/proj...browse/SPR-388

I hope someone besides me finds this useful. :wink:

Cheers,
Joe

I followed Joe's solution and got it to work with DB2. Thanks.

However I want to optimize it a bit to only set the current schema if needed. As it is now it will set it for every call to the database.

27 Oct 2004 09:42:21 DEBUG com.bmd.spring.SchemaAwareDataSourceProxy - Setting schema to xx on connection org.apache.commons.dbcp.PoolableConnection@1a28362
27 Oct 2004 09:42:22 DEBUG com.bmd.spring.SchemaAwareDataSourceProxy - Setting schema to xx on connection org.apache.commons.dbcp.PoolableConnection@1a28362
27 Oct 2004 09:42:23 DEBUG com.bmd.spring.SchemaAwareDataSourceProxy - Setting schema to zbonpgmmdl on connection org.apache.commons.dbcp.PoolableConnection@1a28362
Hibernate: select synonfile0_.ØØFIL as ØØFIL, synonfile0_.objatr as objatr, synonfile0_.fil as fil, synonfile0_.srcmbr as srcmbr, synonfile0_.rpgpfx as rpgpfx, synonfile0_.olddbs as olddbs from zbonpgmmdl.YFILDTARFP synonfile0_ where (synonfile0_.srcmbr=? )
27 Oct 2004 09:42:24 DEBUG com.bmd.spring.SchemaAwareDataSourceProxy - Setting schema to zbonpgmmdl on connection org.apache.commons.dbcp.PoolableConnection@1a28362
Hibernate: select synonnarra1_.ØØOBJ as ØØOBJ, synonnarra1_.txttyp as txttyp, synonnarra1_.pagnbr as pagnbr, synonnarra1_.l01 as l01, synonnarra1_.l02 as l02, synonnarra1_.l03 as l03, synonnarra1_.l04 as l04, synonnarra1_.l05 as l05, synonnarra1_.l06 as l06, synonnarra1_.l07 as l07, synonnarra1_.l08 as l08, synonnarra1_.l09 as l09, synonnarra1_.l10 as l10, synonnarra1_.l11 as l11, synonnarra1_.l12 as l12, synonnarra1_.l13 as l13, synonnarra1_.l14 as l14, synonnarra1_.l15 as l15, synonnarra1_.l16 as l16, synonnarra1_.l17 as l17, synonnarra1_.l18 as l18 from zbonpgmmdl.YRELDTARFP synonrelat0_, zbonpgmmdl.YTXTDTARFP synonnarra1_ where (synonrelat0_.ØØOBJ=? )AND(synonrelat0_.relcde='005' )AND(synonrelat0_.ØØOBJ=synonnarra1_.ØØOBJ )
27 Oct 2004 09:42:25 DEBUG com.bmd.spring.SchemaAwareDataSourceProxy - Setting schema to zbonpgmmdl on connection org.apache.commons.dbcp.PoolableConnection@1a28362
Hibernate: select synonfield1_.ØØFLD as ØØFLD, synonfield1_.fld as fld, synonfield1_.ØØDOM as ØØDOM, synonfield1_.rhshdg as rhshdg, synonfield1_.ddsvnm as ddsvnm, synonfield1_.lhshdg as lhshdg from zbonpgmmdl.YRELDTARFP synonrelat0_, zbonpgmmdl.YFLDDTARFP synonfield1_ where (synonrelat0_.ØØOBJ=? )AND(synonrelat0_.relcde='060' )AND(synonrelat0_.ØØREF=synonfield1_.ØØFLD )
27 Oct 2004 09:42:25 DEBUG com.bmd.spring.SchemaAwareDataSourceProxy - Setting schema to zbonpgmmdl on connection org.apache.commons.dbcp.PoolableConnection@1a28362

My use-case is pr. HttpSession tu use the same DB schema. So I want to store the schemanme in the HttpSession and set it as a ThreadLocal attribute that the SchemaAwareDataSourceProxy holds and thus has this information and can determine if it needs to set the current schema on the Connection object.

Since I will use a connection pool I can get an arbitrary Connection object from the pool that has its current schema set to something I do not know.

How do I store the schemaname that the Connection object has set?

My idea is to create a ConnectionSchemaWrapper object that delegates to the real Connection object but also stores its current schema settings - getSchemaName().

Then I can check in my SchemaAwareDataSourceProxy object:

Connection con = super.getConnection();
ConnectionSchemaWrapper wrapper = (ConnectionSchemaWrapper) con;
if (! schemaName.equals(wrapper.getSchemaName())
    setSchemaOnConnection(con, schemaName);

Is there a better aprroach to link the Connection with the schema it has currently been set to?



And here is my simple proxy code

public class SchemaAwareDataSourceProxy extends TransactionAwareDataSourceProxy {
	
	// TODO: Connection wrapper to store current set schema name
	
	private final Log logger = LogFactory.getLog(getClass());
		
	private final ThreadLocal schema = new ThreadLocal();

	public void setSchemaName(String schemaName) {
		schema.set(schemaName);
	}
	
	public Connection getConnection() throws SQLException {
		Connection con = super.getConnection();
		
		String schemaName = (String) schema.get();
		if (schemaName == null)
			throw new IllegalStateException("SchemaName not set. Call setSchemaName before doing any database access");
			
		setSchemaOnConnection(con, schemaName);
		
		return con;
	}
	
	private void setSchemaOnConnection(Connection con, String schemaName) throws SQLException {
		if (logger.isDebugEnabled())
			logger.debug("Setting schema to " + schemaName + " on connection " + con);
			
		Statement stmt = con.createStatement();
		try {
			// DB2 specific SQL. Oracle uses different syntax.
			stmt.execute("set current schema = " + schemaName);
		} finally {
			stmt.close();
		}
	}

And the spring .xml

	<!-- Our datasource that is capable of setting thread-bound schma name -->
	<bean id="dataSource" class="com.bmd.spring.SchemaAwareDataSourceProxy">
		<property name="targetDataSource"><ref local="dataSourceTarget"/></property>
		<property name="schemaName"><value>xx</value></property>
	</bean>

	<bean id="dataSourceTarget" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName"><value>com.p6spy.engine.spy.P6SpyDriver</value></property>
		<property name="url"><value>jdbc&#58;as400&#58;//XXX;translate binary=true</value></property>
		<property name="username"><value>XXX</value></property>
		<property name="password"><value>XXX</value></property>
	</bean>

And how to change the schema name for the current thread.

		SchemaAwareDataSourceProxy s = (SchemaAwareDataSourceProxy) app.getBean("dataSource");
		s.setSchemaName("zbonpgmmdl");

[/code]

[cpalit]

Yes this is the right way to do things.

I assume that based on some property you figure out in each request, you determine the schema to use (based on the principal or something?). If it's bound to the HttpSession or HttpServletRequest, implement a HandlerInterceptor, bind the proeprty that determines the schema to a ThreadLocal and pick the ThreadLocal up again in your DelegateDataSource. Override getConnection(), retrieve the connection, execute the set current schema call and you're done I guess.

Alef

Cool idea I just have the same problem using pure Hibernate.
I did not figure out to do this in Hibernate only so another strong argument for using Spring. Well if you start getting the verbose .xml configuration narrowed down. I can see there is a JIRA on this.

This is a neat idea. I'm wondering, will this approach of using a DelegatingDatasource also work with a JNDI resource factory? Essentially, I would like to keep it simple, by specificing only the JNDI location of the datasource in my Spring config file, since my app always runs in a J2EE container. Would a DelegatingDatasource extension that knows how to set a schema still be a good fit?

As a side note, if you are using Hibernate, you can specify the default schema in the Hibernate config file by adding a property for it in the session-factory section:

...
<session-factory>
    ...
    <property name="default_schema">myschema</property>
    ...
</session-factory>
...

(Obviously, I left out all the mapping tags, and the rest of the property tags that specify the connection jndi, type, etc.)

Unfortunately, I can't use Hibernate on my current project since the name of my tables vary depending on the primary key of my base entity (not my choice, have to use the existing corporate tables). But, any insight into using JNDI db lookups with JDBC without needing to add a schema-retrieving function call to the SQL in my classes would be great.

[davsclaus]

Hi

I though that the thread-bound connection transaction support by Spring was the way to go to support different schemas and optimize the speed by only setting the curernt schema once.

I worked through the samples with transactions and made something work .... well I thought it worked until I tried to set the current schema on the connectiion within a transaction I get a security SQLException.

Caused by: java.sql.SQLException: Connection authorization failure occurred. This is from a DB2-AS/400 database.

If I disable the transaction settings from the spring .xml file the security exception is not thrown. I guess IBM dont want people to be able to set current schema within a local transaction.

I will work on this case some more and keep posting here until I find a good solution.