Announcement Announcement Module
Collapse
No announcement yet.
Setting Schema for Each DataSource.getConnection() Call Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Setting Schema for Each DataSource.getConnection() Call

    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?

  • #2
    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

    Comment


    • #3
      Originally posted by 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
      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.

      Comment


      • #4
        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

        Comment


        • #5
          Originally posted by 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
          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

          Comment


          • #6
            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

            Comment


            • #7
              Added to JIRA!

              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

              Comment


              • #8
                Re: Added to JIRA!

                Originally posted by 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
                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.

                Code:
                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:

                Code:
                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
                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

                Code:
                	<!-- 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.
                Code:
                		SchemaAwareDataSourceProxy s = &#40;SchemaAwareDataSourceProxy&#41; app.getBean&#40;"dataSource"&#41;;
                		s.setSchemaName&#40;"zbonpgmmdl"&#41;;
                [/code]

                Comment


                • #9
                  Originally posted by davsclaus
                  Originally posted by 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
                  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:
                  Code:
                  ...
                  <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.

                  Comment


                  • #10
                    Transaction approach

                    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.

                    Comment


                    • #11
                      Originally posted by cpalit
                      Originally posted by davsclaus
                      Originally posted by 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
                      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:
                      Code:
                      ...
                      <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.
                      Thanks for the hint for Hibernate. My DAO code will be a mix of Hibernate and plain JDBC. And each web request can be either one of 5 different DB schema's. So I can not rely on default schema from Hibernate due to the 5 different schema name support.

                      Each web request will perform 1..* SQL calls to the database and I want only to set the current schema once per request. The solution in the top of this thread set the schema for every SQL call and it's much slower.

                      Alef's idea is to set the schema name using a ThreadLocal and this is very good.

                      Using Spring transaction support can make sure that the web request will reuse the same connection throughout the transaction. This is great.
                      but the problem is that I can not set the current schema on this connection, if I do this I get the secuirty exception outlined in my previous mail.

                      Storing the databaource in JNDI instead of CommonsPool is possible. Just set it corrently using the spring .xml file. Something with a JndiObjectFactory I think.

                      Comment


                      • #12
                        It works

                        Wauv it now works.

                        Spring really has amazing transaction support.

                        Using spring managed transaction that will reuse the same connection for the given thread and using your own DataSourceProxy that extends TransactionAwareDataSourceProxy is the solution.

                        I will clean up my code and post the solution here for others to follow.

                        Thanks Spring. Hopefully it works as smooth in Websphere 5.1 also, but I wonder since its a beast.

                        Comment


                        • #13
                          code

                          I will post my working code and configuration here:

                          The spring configuration file:
                          Code:
                          <?xml version="1.0" encoding="UTF-8"?>
                          <!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http&#58;//www.springframework.org/dtd/spring-beans.dtd">
                          
                          <beans>
                          
                          	<!-- STANDARD BEANS -->
                          
                          	<bean id="dataSource" class="com.bmd.spring.SchemaAwareDataSourceProxy">
                          		<property name="targetDataSource"><ref local="dataSourcePool"/></property>
                          	</bean>
                          
                          	<bean id="dataSourcePool" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
                          		<property name="driverClassName"><value>com.ibm.as400.access.AS400JDBCDriver</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>
                          
                          	<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
                          		<property name="dataSource"><ref local="dataSource"/></property>
                          	</bean>
                          	
                          	<bean id="baseTransactionProxy" class="org.springframework.transaction.interceptor.TransactionProxyFactoryBean"
                          		  abstract="true">
                          		<property name="transactionManager"><ref local="transactionManager"/></property>
                          		<property name="transactionAttributes">
                          			<props>
                          				<prop key="load*">PROPAGATION_REQUIRED,readOnly</prop>
                          				<prop key="find*">PROPAGATION_REQUIRED,readOnly</prop>
                          				<prop key="*">PROPAGATION_REQUIRED</prop>
                          			</props>
                          		</property>
                          	</bean>
                          	
                          
                          	<!-- DAO BEANS -->
                          
                          	<bean id="concurrentDAO" parent="baseTransactionProxy">
                          		<property name="target">
                          			<bean id="concurrentDAOTarget" class="com.bmd.spring.ConcurrentDAO">
                          				<property name="dataSource"><ref local="dataSource"/></property>
                          			</bean>
                          		</property>
                          	</bean>
                          
                          </beans>

                          And my own DataSourceProxy
                          Code:
                          /*
                           * Created on 19-10-2004
                           */
                          package com.bmd.spring;
                          
                          import java.sql.Connection;
                          import java.sql.SQLException;
                          import java.sql.Statement;
                          
                          import org.apache.commons.logging.Log;
                          import org.apache.commons.logging.LogFactory;
                          import org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy;
                          
                          /**
                           * Proxy for a target DataSource that is aware of thread-bound schema settings.
                           * <p/>
                           * This is used to change schema on the fly. Must use Spring transaction that
                           * reuses the same Connection for the thread within it's transaction boundaries.  
                           * 
                           * @author CIB002
                           * @version $Revision&#58; 1.6 $
                           */
                          public class SchemaAwareDataSourceProxy extends TransactionAwareDataSourceProxy &#123;
                          	
                          	private final static Log logger = LogFactory.getLog&#40;SchemaAwareDataSourceProxy.class&#41;;
                          		
                          	private static SchemaNameHolder holder;
                          
                          	public Connection getConnection&#40;&#41; throws SQLException &#123;
                          		String schemaName = SchemaNameHolder.getSchemaName&#40;&#41;;
                          		if &#40;schemaName != null&#41; &#123;
                          			Connection con = super.getConnection&#40;&#41;;
                          			setSchemaOnConnection&#40;con, schemaName&#41;;
                          			return con;
                          		&#125; else &#123;
                          			throw new IllegalStateException&#40;"Thread bound schema name not set using " + SchemaNameHolder.class.getName&#40;&#41;&#41;;
                          		&#125;
                          		
                          	&#125;
                          	
                          	private void setSchemaOnConnection&#40;Connection con, String schemaName&#41; throws SQLException &#123;
                          		if &#40;logger.isDebugEnabled&#40;&#41;&#41;
                          			logger.debug&#40;"Setting current schema to '" + schemaName + "' on connection " + con&#41;;
                          			
                          		Statement stmt = con.createStatement&#40;&#41;;
                          		try &#123;
                          			// DB2 specific SQL. Oracle uses different syntax.
                          			stmt.execute&#40;"set current schema = " + schemaName&#41;;
                          		&#125; finally &#123;
                          			stmt.close&#40;&#41;;
                          		&#125;
                          	&#125;
                          
                          &#125;
                          And the holder for the current selected schema for the given thread
                          Code:
                          /*
                           * Created on 16-11-2004
                           */
                          package com.bmd.spring;
                          
                          
                          /**
                           * For setting the current schema bound to the thread using a ThreadLocal. 
                           * 
                           * @author CIB002
                           * @version $Revision&#58; 1.1 $
                           */
                          public class SchemaNameHolder &#123;
                          	
                          	private static ThreadLocal schema = new ThreadLocal&#40;&#41;;
                          	
                          	/**
                          	 * Sets the database scema to use for the current thread = current user.
                          	 * 
                          	 * @param name   name of the schema
                          	 */
                          	public static void setSchemaName&#40;String name&#41; &#123;
                          		schema.set&#40;name&#41;;
                          	&#125;
                          
                          	/**
                          	 * Get's the current schema name bound to this thread.
                          	 */
                          	public static String getSchemaName&#40;&#41; &#123;
                          		return &#40;String&#41; schema.get&#40;&#41;;
                          	&#125;
                          
                          &#125;

                          And then I have a sample application doing some concurrent tests to check that each thread will reuse the same connection during it's transaction and return the expected resultset. I will do a SQL count from a table that exists in the three different schemas (DK, S and N)

                          Code:
                          /*
                           * Created on 09-11-2004
                           */
                          package com.bmd.spring;
                          
                          import java.sql.Connection;
                          import java.sql.ResultSet;
                          import java.sql.Statement;
                          import java.util.Random;
                          
                          import javax.sql.DataSource;
                          
                          import org.apache.commons.logging.Log;
                          import org.apache.commons.logging.LogFactory;
                          import org.springframework.jdbc.datasource.DataSourceUtils;
                          
                          /**
                           * 
                           * 
                           * @author CIB002
                           * @version $Revision&#58; 1.1 $
                           */
                          public class ConcurrentDAO &#123;
                          
                          	private final static Log logger = LogFactory.getLog&#40;ConcurrentDAO.class&#41;;
                          	
                          	private DataSource ds;
                          	
                          	public void setDataSource&#40;DataSource ds&#41; &#123;
                          		this.ds = ds;
                          	&#125;
                          
                          	public void findAllKunder&#40;String schema, int loop&#41; throws Exception &#123;
                          		Random ran = new Random&#40;&#41;;
                          		for &#40;int i=0; i < loop; i++&#41; &#123;
                          			Thread.sleep&#40; ran.nextInt&#40;1000&#41; &#41;;
                          			doCountAllKunder&#40;schema&#41;;
                          		&#125;
                          	&#125;
                          	
                          	private void doCountAllKunder&#40;String schema&#41; throws Exception &#123;
                          		Connection con = DataSourceUtils.getConnection&#40;ds&#41;;
                          		Statement stmt = null;
                          		ResultSet rs = null;
                          		try &#123;
                          			String s = SchemaNameHolder.getSchemaName&#40;&#41;;
                          			stmt = con.createStatement&#40;&#41;;
                          			rs = stmt.executeQuery&#40;"select count&#40;*&#41; from kundewas"&#41;;
                          			rs.next&#40;&#41;;
                          			logger.debug&#40;"Kunder&#58; &#40;" + schema + "&#41; = " + rs.getInt&#40;1&#41; + "&#58; " + con + ", s=" + s&#41;;			
                          		&#125; finally &#123;
                          			if &#40;rs != null&#41; rs.close&#40;&#41;;
                          			if &#40;stmt != null&#41; stmt.close&#40;&#41;;
                          			DataSourceUtils.closeConnectionIfNecessary&#40;con, ds&#41;;
                          		&#125;
                          	&#125;
                          
                          &#125;
                          And the demo app
                          Code:
                          /*
                           * Created on 09-11-2004
                           */
                          package com.bmd.spring;
                          
                          import org.springframework.beans.factory.BeanFactory;
                          import org.springframework.context.support.ClassPathXmlApplicationContext;
                          
                          /**
                           * Multi threaded demo for concurrent access to DB2 using a thread bound schema name.
                           * <p/>
                           * Each call to the DB2 should be consistent and return the same ResultSet for the same schema name.
                           * This Demo proofs this. 
                           * 
                           * @author CIB002
                           * @version $Revision&#58; 1.1 $
                           */
                          public class ConcurrentDemo &#123;
                          
                          	private BeanFactory app; 
                          
                          	public ConcurrentDemo&#40;&#41; throws Exception &#123;
                          		ClassPathXmlApplicationContext appContext = new ClassPathXmlApplicationContext&#40;"concurrentContext.xml"&#41;;
                          		app = &#40;BeanFactory&#41; appContext;
                          	&#125;
                          	
                          	private void test&#40;&#41; throws Exception &#123;
                          		Kunder k = new Kunder&#40;"XX"&#41;;
                          		Kunder k2 = new Kunder&#40;"DK"&#41;;
                          		Kunder k3 = new Kunder&#40;"S"&#41;;
                          		Kunder k4 = new Kunder&#40;"N"&#41;;
                          		k2.start&#40;&#41;;
                          		Thread.sleep&#40;1000&#41;;
                          		k.start&#40;&#41;;
                          		Thread.sleep&#40;2000&#41;;
                          		k3.start&#40;&#41;;
                          		Thread.sleep&#40;7000&#41;;
                          		k4.start&#40;&#41;;
                          	&#125;
                          	
                          	public static void main&#40;String&#91;&#93; args&#41; throws Exception &#123;
                          		ConcurrentDemo demo = new ConcurrentDemo&#40;&#41;;
                          		demo.test&#40;&#41;;
                          	&#125;
                          
                          	private class Kunder extends Thread &#123;
                          		
                          		private String schema;
                          		private ConcurrentDAO dao;
                          		
                          		public Kunder&#40;String schema&#41; &#123;
                          			this.schema = schema;
                          			dao = &#40;ConcurrentDAO&#41; app.getBean&#40;"concurrentDAO"&#41;;
                          		&#125;
                          		
                          		public void run&#40;&#41; &#123;
                          			SchemaNameHolder.setSchemaName&#40;schema&#41;;
                          			try &#123;
                          				dao.findAllKunder&#40;schema, 10&#41;;
                          			&#125; catch &#40;Exception e&#41; &#123;
                          				throw new RuntimeException&#40;e&#41;;
                          			&#125;
                          		&#125;
                          	&#125;
                          
                          &#125;
                          Running this demo will output to the console profing the expected result. The counts should be the same for the given schema.

                          Code:
                          main 17 Nov 2004 10&#58;57&#58;16 INFO  org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from class path resource &#91;concurrentContext.xml&#93;
                          main 17 Nov 2004 10&#58;57&#58;16 INFO  org.springframework.context.support.ClassPathXmlApplicationContext - Bean factory for application context &#91;org.springframework.context.support.ClassPathXmlApplicationContext;hashCode=15789782&#93;&#58; org.springframework.beans.factory.support.DefaultListableBeanFactory defining beans &#91;dataSource,dataSourcePool,transactionManager,baseTransactionProxy,concurrentDAO&#93;; root of BeanFactory hierarchy
                          main 17 Nov 2004 10&#58;57&#58;16 INFO  org.springframework.context.support.ClassPathXmlApplicationContext - 5 beans defined in application context &#91;org.springframework.context.support.ClassPathXmlApplicationContext;hashCode=15789782&#93;
                          main 17 Nov 2004 10&#58;57&#58;16 INFO  org.springframework.context.support.ClassPathXmlApplicationContext - Unable to locate MessageSource with name 'messageSource'&#58; using default &#91;org.springframework.context.support.StaticMessageSource&#58; &#123;&#125;&#93;
                          main 17 Nov 2004 10&#58;57&#58;16 INFO  org.springframework.context.support.ClassPathXmlApplicationContext - Unable to locate ApplicationEventMulticaster with name 'applicationEventMulticaster'&#58; using default &#91;org.springframework.context.event.SimpleApplicationEventMulticaster@13a317a&#93;
                          main 17 Nov 2004 10&#58;57&#58;16 INFO  org.springframework.context.support.ClassPathXmlApplicationContext - Refreshing listeners
                          main 17 Nov 2004 10&#58;57&#58;16 INFO  org.springframework.beans.factory.support.DefaultListableBeanFactory - Pre-instantiating singletons in factory &#91;org.springframework.beans.factory.support.DefaultListableBeanFactory defining beans &#91;dataSource,dataSourcePool,transactionManager,baseTransactionProxy,concurrentDAO&#93;; root of BeanFactory hierarchy&#93;
                          main 17 Nov 2004 10&#58;57&#58;16 INFO  org.springframework.beans.factory.support.DefaultListableBeanFactory - Creating shared instance of singleton bean 'dataSource'
                          main 17 Nov 2004 10&#58;57&#58;16 INFO  org.springframework.beans.factory.support.DefaultListableBeanFactory - Creating shared instance of singleton bean 'dataSourcePool'
                          main 17 Nov 2004 10&#58;57&#58;16 INFO  org.springframework.beans.factory.support.DefaultListableBeanFactory - Creating shared instance of singleton bean 'transactionManager'
                          main 17 Nov 2004 10&#58;57&#58;16 INFO  org.springframework.beans.factory.support.DefaultListableBeanFactory - Creating shared instance of singleton bean 'concurrentDAO'
                          main 17 Nov 2004 10&#58;57&#58;16 INFO  org.springframework.core.CollectionFactory - Using JDK 1.4 collections
                          Thread-1 17 Nov 2004 10&#58;57&#58;17 INFO  org.springframework.jdbc.datasource.JdbcTransactionObjectSupport - JDBC 3.0 Savepoint class is available
                          Thread-1 17 Nov 2004 10&#58;57&#58;18 DEBUG com.bmd.spring.SchemaAwareDataSourceProxy - Setting current schema to 'DK' on connection org.apache.commons.dbcp.PoolableConnection@18bf072
                          Thread-1 17 Nov 2004 10&#58;57&#58;19 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;DK&#41; = 5143439&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=DK
                          Thread-1 17 Nov 2004 10&#58;57&#58;19 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;DK&#41; = 5143439&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=DK
                          Thread-1 17 Nov 2004 10&#58;57&#58;20 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;DK&#41; = 5143439&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=DK
                          Thread-1 17 Nov 2004 10&#58;57&#58;20 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;DK&#41; = 5143439&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=DK
                          Thread-0 17 Nov 2004 10&#58;57&#58;20 DEBUG com.bmd.spring.SchemaAwareDataSourceProxy - Setting current schema to 'XX' on connection org.apache.commons.dbcp.PoolableConnection@50988
                          Thread-1 17 Nov 2004 10&#58;57&#58;21 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;DK&#41; = 5143439&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=DK
                          Thread-0 17 Nov 2004 10&#58;57&#58;21 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;XX&#41; = 867&#58; org.apache.commons.dbcp.PoolableConnection@50988, s=XX
                          Thread-0 17 Nov 2004 10&#58;57&#58;21 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;XX&#41; = 867&#58; org.apache.commons.dbcp.PoolableConnection@50988, s=XX
                          Thread-2 17 Nov 2004 10&#58;57&#58;22 DEBUG com.bmd.spring.SchemaAwareDataSourceProxy - Setting current schema to 'S' on connection org.apache.commons.dbcp.PoolableConnection@b4e29b
                          Thread-1 17 Nov 2004 10&#58;57&#58;22 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;DK&#41; = 5143439&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=DK
                          Thread-0 17 Nov 2004 10&#58;57&#58;22 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;XX&#41; = 867&#58; org.apache.commons.dbcp.PoolableConnection@50988, s=XX
                          Thread-0 17 Nov 2004 10&#58;57&#58;22 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;XX&#41; = 867&#58; org.apache.commons.dbcp.PoolableConnection@50988, s=XX
                          Thread-1 17 Nov 2004 10&#58;57&#58;22 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;DK&#41; = 5143439&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=DK
                          Thread-2 17 Nov 2004 10&#58;57&#58;23 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;S&#41; = 4914774&#58; org.apache.commons.dbcp.PoolableConnection@b4e29b, s=S
                          Thread-2 17 Nov 2004 10&#58;57&#58;23 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;S&#41; = 4914774&#58; org.apache.commons.dbcp.PoolableConnection@b4e29b, s=S
                          Thread-1 17 Nov 2004 10&#58;57&#58;23 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;DK&#41; = 5143439&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=DK
                          Thread-0 17 Nov 2004 10&#58;57&#58;23 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;XX&#41; = 867&#58; org.apache.commons.dbcp.PoolableConnection@50988, s=XX
                          Thread-1 17 Nov 2004 10&#58;57&#58;23 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;DK&#41; = 5143439&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=DK
                          Thread-2 17 Nov 2004 10&#58;57&#58;24 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;S&#41; = 4914774&#58; org.apache.commons.dbcp.PoolableConnection@b4e29b, s=S
                          Thread-1 17 Nov 2004 10&#58;57&#58;24 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;DK&#41; = 5143439&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=DK
                          Thread-0 17 Nov 2004 10&#58;57&#58;24 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;XX&#41; = 867&#58; org.apache.commons.dbcp.PoolableConnection@50988, s=XX
                          Thread-2 17 Nov 2004 10&#58;57&#58;25 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;S&#41; = 4914774&#58; org.apache.commons.dbcp.PoolableConnection@b4e29b, s=S
                          Thread-0 17 Nov 2004 10&#58;57&#58;25 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;XX&#41; = 867&#58; org.apache.commons.dbcp.PoolableConnection@50988, s=XX
                          Thread-2 17 Nov 2004 10&#58;57&#58;26 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;S&#41; = 4914774&#58; org.apache.commons.dbcp.PoolableConnection@b4e29b, s=S
                          Thread-0 17 Nov 2004 10&#58;57&#58;26 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;XX&#41; = 867&#58; org.apache.commons.dbcp.PoolableConnection@50988, s=XX
                          Thread-2 17 Nov 2004 10&#58;57&#58;27 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;S&#41; = 4914774&#58; org.apache.commons.dbcp.PoolableConnection@b4e29b, s=S
                          Thread-0 17 Nov 2004 10&#58;57&#58;27 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;XX&#41; = 867&#58; org.apache.commons.dbcp.PoolableConnection@50988, s=XX
                          Thread-3 17 Nov 2004 10&#58;57&#58;27 DEBUG com.bmd.spring.SchemaAwareDataSourceProxy - Setting current schema to 'N' on connection org.apache.commons.dbcp.PoolableConnection@18bf072
                          Thread-0 17 Nov 2004 10&#58;57&#58;27 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;XX&#41; = 867&#58; org.apache.commons.dbcp.PoolableConnection@50988, s=XX
                          Thread-2 17 Nov 2004 10&#58;57&#58;28 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;S&#41; = 4914774&#58; org.apache.commons.dbcp.PoolableConnection@b4e29b, s=S
                          Thread-2 17 Nov 2004 10&#58;57&#58;28 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;S&#41; = 4914774&#58; org.apache.commons.dbcp.PoolableConnection@b4e29b, s=S
                          Thread-3 17 Nov 2004 10&#58;57&#58;28 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;N&#41; = 2001888&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=N
                          Thread-3 17 Nov 2004 10&#58;57&#58;28 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;N&#41; = 2001888&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=N
                          Thread-3 17 Nov 2004 10&#58;57&#58;28 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;N&#41; = 2001888&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=N
                          Thread-2 17 Nov 2004 10&#58;57&#58;29 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;S&#41; = 4914774&#58; org.apache.commons.dbcp.PoolableConnection@b4e29b, s=S
                          Thread-3 17 Nov 2004 10&#58;57&#58;29 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;N&#41; = 2001888&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=N
                          Thread-2 17 Nov 2004 10&#58;57&#58;29 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;S&#41; = 4914774&#58; org.apache.commons.dbcp.PoolableConnection@b4e29b, s=S
                          Thread-3 17 Nov 2004 10&#58;57&#58;30 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;N&#41; = 2001888&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=N
                          Thread-3 17 Nov 2004 10&#58;57&#58;31 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;N&#41; = 2001888&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=N
                          Thread-3 17 Nov 2004 10&#58;57&#58;31 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;N&#41; = 2001888&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=N
                          Thread-3 17 Nov 2004 10&#58;57&#58;31 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;N&#41; = 2001888&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=N
                          Thread-3 17 Nov 2004 10&#58;57&#58;32 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;N&#41; = 2001888&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=N
                          Thread-3 17 Nov 2004 10&#58;57&#58;32 DEBUG com.bmd.spring.ConcurrentDAO - Kunder&#58; &#40;N&#41; = 2001888&#58; org.apache.commons.dbcp.PoolableConnection@18bf072, s=N
                          Hope anyone find it useful.

                          I will do another demo that does a mix with Hibernate for some queries to demo that it works using plain SQL and Hibernate within the same transaction and thus the same schema.[/code]

                          Comment


                          • #14
                            mixing Hibernate, plain JDBC and Spring JdbcTemplate works like a charm. It will reuse the same Connection throughout the Spring managed transaction. And the Connection will have it's schema set before any SQL calls.

                            Works nicely.

                            However I had a few problems switching to use only Websphere 5.1 and it's JTA Transaction Manager and it's connection pool. It caused in-flight transactions on the DB2 database and drained the CPU resources on our AS/400. It was using the same example code as posted.

                            Comment


                            • #15
                              Claus,

                              Great work! You have really improved upon my original implementation!

                              Alef (or other Spring team member),

                              Would you please consider integrating this feature into the next release of Spring? Perhaps you could start by putting it in the Spring sandbox? Before integrating, I think Claus' code should be combined with the part of my original code (or similar code) that automatically figures out the DBMS vendor and adjusts the "set schema" syntax accordingly (the syntax is vendor specific). Please see issue SPR-388 at JIRA for the code.

                              I think this is a really cool and useful feature!

                              Comment

                              Working...
                              X