db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Clark, Harry" <Harry.Cl...@knovalent.com>
Subject RE: Multiple Schemas with Derby, Hibernate & JPA
Date Mon, 20 Sep 2010 21:01:14 GMT
I used the following to deal with multiple schemas. First is the Spring config. Then are 2
classes, one a SchemaAwareDataSourceProxy, the other a thread-local SchemaHolder. You have
to set the schema using SchemaAwareDataSource.setSchemaName("someschema"). Subsequent getConnection()
calls on the data source by the framework set the schema with an SQL stmt. I got this idea
from the Spring forums. I added Atomikos to the mix, which isn't fundamental. I'm not sure
how you would set it up with EntityManager. This is for use by the framework, not by the application.

  <bean id="vanguardTempDataSource" class="com.kve.vanguard.model.orm.dao.SchemaAwareDataSourceProxy">
    <property name="targetDataSource"><ref local="vanguardTempDataSourceTarget"/></property>
    <property name="schemaName"><value>APP</value></property>
  </bean> 

  <bean id="vanguardTempDataSourceTarget" class="com.atomikos.jdbc.AtomikosDataSourceBean">
    <property name="uniqueResourceName" value="VANGUARDTEMP" />
    <property name="xaDataSourceClassName">
      <value>org.apache.derby.jdbc.EmbeddedXADataSource40</value>
    </property>
    <property name="xaProperties"> 
      <props>       
        <prop key="databaseName">vanguard</prop> 
        <prop key="connectionAttributes">create=true;</prop>
      </props> 
    </property>    
    <property name="poolSize" value="3" />
  </bean>


import org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy;
import java.sql.*;
import org.apache.commons.logging.*;

/**
 * class to intercept getConnection() and set schema
 * 
 * @author clarkh
 *
 */
public class SchemaAwareDataSourceProxy extends TransactionAwareDataSourceProxy {  
  
  /**
   * logger
   */
  private final Log logger = LogFactory.getLog(getClass());
    
  /**
   * name of schema, thread-local var
   */
  private static SchemaHolder schemaHolder;
 
  /**
   * set schemas
   * 
   * @param schemaName
   */
  public void setSchemaName(String schemaName) {
    if (schemaHolder == null) 
      schemaHolder = new SchemaHolder();
    schemaHolder.setSchemaName(schemaName);
  }
  
  /**
   * override of getConnection
   */
  public Connection getConnection() throws SQLException {
    Connection con = super.getConnection();   
    
    String schemaName = (String) schemaHolder.getSchemaName();
    
    if (schemaName == null)
      throw new IllegalStateException("SchemaName not set. Call setSchemaName before doing
any database access");
      
    setSchemaOnConnection(con, schemaName);
    
    return con;
  }
  
  /**
   * set schema upon obtaining connection
   * 
   * @param con
   * @param schemaName
   * @throws SQLException
   */
  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 {
      stmt.execute("set schema = '" + schemaName + "'");
      
    }
    catch (Exception e) {
      System.out.println("Exception in setSchema: " + e.getMessage() + e.getCause());
    }
    finally {
      stmt.close();
    }
  }  
}

=====

/**
 * hold schema name for temp database
 * @author clarkh
 *
 */
public class SchemaHolder {
  
  /**
   * thread-bound schema name
   */
  private static ThreadLocal schema = new ThreadLocal();
  
  /**
   * set schema name for current thread.
   * 
   * @param name   schema name
   */
  public static void setSchemaName(String name) {
    schema.set(name);
  }

  /**
   * Gets the schema name for thread
   */
  public static String getSchemaName() {
    return (String) schema.get();
  }

}


-----Original Message-----
From:	Simon James [mailto:sjames@btisystems.com]
Sent:	Mon 9/20/2010 2:48 AM
To:	derby-user@db.apache.org
Cc:	
Subject:	Multiple Schemas with Derby, Hibernate & JPA 


I have a problem using multiple schemas with Derby, Hibernate & JPA. 
Although I don’t have the problem when using MySql, the database/schema models of Derby
and MySql are different and so 
I realise this might be a problem with Hibernate rather than Derby.

I have 2 schemas (S1 & S2) each of which contains a table of the same name (MyTable) which
is created from the same Java entity. 

When I create the EntityManagerFactory with the url for S1 (for example: jdbc:derby:dbname;create=true;user=S1),
MyTable is created in schema S1.
When I create the EntityManagerFactory with the url for S2, Hibernate logs a message indicating
that MyTable already exists and so does not create it.
However, if I subsequently use an EntityManager created by the factory for S2, inserts fail
because MyTable does not exist.

Has anybody else experienced this problem?

Regards

Simon 



Mime
View raw message