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:[email protected]]
Sent: Mon 9/20/2010 2:48 AM
To: [email protected]
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
<<winmail.dat>>
