spring配置oracle多个用户的datasource

一个oracle下的两个用户，App和Fc。程序对Fc只有select操作，不涉及事务。App为一般应用，要求有事务。

<bean id="dataSourceApp" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"> </property> <property name="jdbcUrl" value="jdbc:oracle:thin:@localhost:1521:oranb"> </property> <property name="user" value="stmg"></property> <property name="password" value="stmg"></property> <property name="minPoolSize"> <value>1</value> </property> <property name="maxPoolSize"> <value>10</value> </property> <property name="initialPoolSize"> <value>10</value> </property> <property name="maxIdleTime"> <value>30</value> </property> <property name="acquireIncrement"> <value>5</value> </property> <property name="maxStatements"> <value>0</value> </property> <property name="idleConnectionTestPeriod"> <value>60</value> </property> <property name="acquireRetryAttempts"> <value>30</value> </property> <property name="breakAfterAcquireFailure"> <value>true</value> </property> <property name="testConnectionOnCheckout"> <value>false</value> </property> </bean> <!-- C3P0 --> <bean id="dataSourceFc" class="com.mchange.v2.c3p0.ComboPooledDataSource"> <property name="driverClass" value="oracle.jdbc.driver.OracleDriver"> </property> <property name="jdbcUrl" value="jdbc:oracle:thin:@localhost:1521:oranb"> </property> <property name="user" value="etpdb"></property> <property name="password" value="etpu1234#"></property> <property name="minPoolSize"> <value>1</value> </property> <property name="maxPoolSize"> <value>10</value> </property> <property name="initialPoolSize"> <value>10</value> </property> <property name="maxIdleTime"> <value>30</value> </property> <property name="acquireIncrement"> <value>5</value> </property> <property name="maxStatements"> <value>0</value> </property> <property name="idleConnectionTestPeriod"> <value>60</value> </property> <property name="acquireRetryAttempts"> <value>30</value> </property> <property name="breakAfterAcquireFailure"> <value>true</value> </property> <property name="testConnectionOnCheckout"> <value>false</value> </property> </bean> <!-- sessionFactory App --> <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean"> <property name="dataSource"> <ref bean="dataSourceApp" /> </property> <property name="hibernateProperties"> <props> <prop key="hibernate.dialect"> org.hibernate.dialect.Oracle9Dialect </prop> <!-- 允许自动提交 --> <prop key="hibernate.connection.autocommit">false</prop> <!-- 显示sql语句 --> <prop key="hibernate.show_sql">true</prop> </props> </property> <property name="mappingResources"> <list> <value>com/mutildatasource/po/Iasolution.hbm.xml</value> <value>com/mutildatasource/po/Iaquestion.hbm.xml</value> </list> </property> </bean> <!-- sessionFactory Fc --> <bean id="sessionFactoryFc" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean"> <property name="dataSource"> <ref bean="dataSourceFc" /> </property> <property name="hibernateProperties"> <props> <prop key="hibernate.dialect"> org.hibernate.dialect.Oracle9Dialect </prop> <!-- 允许自动提交 --> <prop key="hibernate.connection.autocommit">false</prop> <!-- 显示sql语句 --> <prop key="hibernate.show_sql">true</prop> </props> </property> </bean> <!-- App hibernateTemplate --> <bean id="hibernateTemplate" class="org.springframework.orm.hibernate3.HibernateTemplate"> <property name="sessionFactory"> <ref bean="sessionFactory" /> </property> </bean> <!-- Fc hibernateTemplate --> <bean id="hibernateTemplateFc" class="org.springframework.orm.hibernate3.HibernateTemplate"> <property name="sessionFactory"> <ref bean="sessionFactoryFc" /> </property> </bean> <!-- 定义事务管理器，使用适用于Hibernte的事务管理器--> <bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager"> <!-- HibernateTransactionManager bean需要依赖注入一个SessionFactory bean的引用--> <property name="sessionFactory"> <ref local="sessionFactory" /> </property> </bean> <!-- 配置事务拦截器--> <bean id="transactionInterceptor" class="org.springframework.transaction.interceptor.TransactionInterceptor"> <!-- 事务拦截器bean需要依赖注入一个事务管理器 --> <property name="transactionManager" ref="transactionManager" /> <property name="transactionAttributes"> <!-- 下面定义事务传播属性--> <props> <prop key="save*">PROPAGATION_REQUIRED</prop> <prop key="set*">PROPAGATION_REQUIRED</prop> <prop key="update*">PROPAGATION_REQUIRED</prop> <prop key="delete*">PROPAGATION_REQUIRED</prop> <prop key="sort*">PROPAGATION_REQUIRED</prop> <prop key="*">PROPAGATION_REQUIRED</prop> </props> </property> </bean> <!-- 定义BeanNameAutoProxyCreator,该bean是个bean后处理器，无需被引用，因此没有id属性 这个bean后处理器，根据事务拦截器为目标bean自动创建事务代理 --> <bean class="org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator"> <!-- 指定对满足哪些bean name的bean自动生成业务代理 --> <property name="beanNames"> <!-- 下面是所有需要自动创建事务代理的bean--> <list> <value>mutilDataSourceService</value> </list> <!-- 此处可增加其他需要自动创建事务代理的bean--> </property> <!-- 下面定义BeanNameAutoProxyCreator所需的事务拦截器--> <property name="interceptorNames"> <list> <value>transactionInterceptor</value> <!-- 此处可增加其他新的Interceptor --> </list> </property> </bean>

<bean id="appDao" class="com.mutildatasource.dao.impl.AppDaoImpl"> <property name="hibernateTemplate"> <ref bean="hibernateTemplate"/> </property> </bean> <bean id="fcDao" class="com.mutildatasource.dao.impl.FcDaoImpl"> <property name="hibernateTemplateFc"> <ref bean="hibernateTemplateFc"/> </property> <property name="hibernateTemplate"> <ref bean="hibernateTemplate"/> </property> </bean> <bean id="mutilDataSourceService" class="com.mutildatasource.service.impl.MutilDataSourceServiceImpl"> <property name="appDao"> <ref bean="appDao"/> </property> <property name="fcDao"> <ref bean="fcDao"/> </property> </bean> <bean name="/datasource" class="com.mutildatasource.struts.action.MutilDataSourceAction"> <property name="mutilDataSourceService"> <ref bean="mutilDataSourceService" /> </property> </bean>

service ： updateAppData() 和 updateAppData2()之一发生异常，两个都会回滚，updateFcData()则不会，完全和以上两个无关（实际需求中只有select），也就是只有updateAppData，updateAppData2是在一个事务中的。

public class MutilDataSourceServiceImpl implements MutilDataSourceService { private AppDaoImpl appDao; private FcDaoImpl fcDao; public String getMutilDataSourceDataService(int answerid, int status) { String result = fcDao.updateFcData(); appDao.updateAppData(answerid, status); appDao.updateAppData2(answerid, status); return null; } public void setAppDao(AppDaoImpl appDao) { this.appDao = appDao; } public void setFcDao(FcDaoImpl fcDao) { this.fcDao = fcDao; } }

dao

public class FcDaoImpl extends HibernateDaoSupport implements FcDao { private HibernateTemplate hibernateTemplateFc; public String updateCaiHuiData() { final String sql = "update tbusinflag set c_type='1' where c_businflag='01'"; return (String)hibernateTemplateFc.execute(new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException { Query query = session.createSQLQuery(sql); query.executeUpdate(); return null; } }); } public void setHibernateTemplateFc(HibernateTemplate hibernateTemplateFc) { this.hibernateTemplateFc = hibernateTemplateFc; } }

public class AppDaoImpl extends HibernateDaoSupport implements AppDao{ public void updateAppData(final int answerid, final int status) { getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException { String hql = "UPDATE Iaquestion SET stsatus=? WHERE answerid=?"; //异常 Query q = session.createQuery(hql); q.setInteger(0, 888); q.setInteger(1, 29); q.executeUpdate(); return null; } }); } public void updateAppData2(final int solutionid, final int status) { getHibernateTemplate().execute(new HibernateCallback() { public Object doInHibernate(Session session) throws HibernateException { String hql = "UPDATE Iasolution SET status=? WHERE solutionid=?"; //正常 // String hql = "UPDATE Iasolution SET statuss=? WHERE solutionid=?"; 异常 Query q = session.createQuery(hql); q.setInteger(0, status); q.setInteger(1, solutionid); q.executeUpdate(); return null; } }); } }

配置了两个sessionFactory不知道会不会有问题，Fc换成sqlserver不知道行不行，还有datasource换成JNDI，试试。