Spring动态切换数据源出现的问题，请大家帮忙看看项目中配置了2个数据源A和B，登录时从A取用户信息，然后从B
Spring动态切换数据源出现的问题，请大家帮忙看看
项目中配置了2个数据源A和B，登录时从A取用户信息，然后从B取权限信息。
但是从A取出用户信息后，切换到数据源B时，有些时候会出现ORA-00942: table or view does not exist错误
应该是数据源还未切换完成就执行了hibernate操作的原因，请问这个问题如何解决
相关代码如下：
DataSourceContextHolder.setDataSourceType("A");
String sql = "select userId from user where userName="+name;
//hibernate查询操作略。。。。
DataSourceContextHolder.setDataSourceType("B");
String sql1 = "select t from UserRole t where t.userId='"+userId+"'";
Query query = getSession().createQuery(sql1);
//.......
以上代码执行到查询时，偶尔会报错，感觉是数据源还未切换就执行了下面的代码
013-12-23 14:12:17,225 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-10.4.0.137-8080-15) SQL Error: 942, SQLState: 42000
2013-12-23 14:12:17,225 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-10.4.0.137-8080-15) ORA-00942: table or view does not exist
2013-12-23 14:12:17,225 ERROR [STDERR] (http-10.4.0.137-8080-15) org.hibernate.exception.SQLGrammarException: ORA-00942: table or view does not exist
2013-12-23 14:12:17,226 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122)
2013-12-23 14:12:17,226 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
2013-12-23 14:12:17,226 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
2013-12-23 14:12:17,226 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
2013-12-23 14:12:17,226 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129)
2013-12-23 14:12:17,226 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
2013-12-23 14:12:17,226 ERROR [STDERR] (http-10.4.0.137-8080-15) at $Proxy927.executeQuery(Unknown Source)
2013-12-23 14:12:17,226 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.loader.Loader.getResultSet(Loader.java:1962)
2013-12-23 14:12:17,226 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.loader.Loader.doQuery(Loader.java:829)
2013-12-23 14:12:17,226 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289)
2013-12-23 14:12:17,226 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.loader.Loader.doList(Loader.java:2447)
2013-12-23 14:12:17,227 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.loader.Loader.doList(Loader.java:2433)
2013-12-23 14:12:17,227 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2263)
2013-12-23 14:12:17,227 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.loader.Loader.list(Loader.java:2258)
2013-12-23 14:12:17,227 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470)
2013-12-23 14:12:17,227 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355)
2013-12-23 14:12:17,227 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196)
2013-12-23 14:12:17,227 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1161)
2013-12-23 14:12:17,227 ERROR [STDERR] (http-10.4.0.137-8080-15) at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
2013-12-23 14:12:17,227 ERROR [STDERR] (http-10.4.0.137-8080-15) at com.fycq.business.service.user.UserServiceImpl.getRoleByCustId(UserServiceImpl.java:110)
/**
* 获得和设置上下文环境 主要负责改变上下文数据源的名称
*/
public class DataSourceContextHolder {
private static final ThreadLocal contextHolder = new ThreadLocal(); // 线程本地环境
// 设置数据源类型
public static void setDataSourceType(String dataSourceType) {
contextHolder.set(dataSourceType);
}
// 获取数据源类型
public static String getDataSourceType() {
return (String) contextHolder.get();
}
// 清除数据源类型
public static void clearDataSourceType() {
contextHolder.remove();
}
}
/**
* 建立动态数据源
*
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
protected Object determineCurrentLookupKey() {
// 在进行DAO操作前，通过上下文环境变量，获得数据源的类型
return DataSourceContextHolder.getDataSourceType();
}
}
[解决办法]之前做过多个数据源切换，不过ORM是ibatis，未出现此问题。楼主可以试下 sleep一下
[解决办法]这正是用线程的好时候了，
A线程去管A数据源，B线程去管B数据源
[解决办法]
public Connection getConnection() throws SQLException {
return createDataSource().getConnection();
}
public Connection getConnection(String username, String password)
throws SQLException {
return createDataSource().getConnection(username, password);
}
/**
* @return Returns the dataSourceMap.
*/
public Map getDataSourceMap() {
return dataSourceMap;
}
/**
* @param dataSourceMap
* The dataSourceMap to set.
*/
public void setDataSourceMap(Map dataSourceMap) {
this.dataSourceMap = dataSourceMap;
}
[解决办法]
protected BasicDataSource createDataSource() throws SQLException {
String sp = (String) SpObserver.getSp();
if (sp == null) {
throw new SQLException(
"Cannot create datasource because of missing sp");
}
String dataSourceName = "dataSource" + sp;
BasicDataSource dataSource = (BasicDataSource) dataSourceMap
.get(dataSourceName);
return dataSource;
}
public class SpObserver {
private static ThreadLocal local=new ThreadLocal();
public static void putSp(Object sp){
local.set(sp);
}
public static Object getSp(){
return local.get();
}
[解决办法]这个不会啊 还是来给你点支持吧
