spring配置多个用户的datasource

配置两个不同的数据源

 

1、jdbc.properties文件

hibernate.hbm2ddl.auto=update
hibernate.show_sql=true

#SQL SERVER 2000 SP4
#database=SQL Server 2000
hibernate.dialectSql=org.hibernate.dialect.SQLServerDialect
jdbc.driverClassNameSql=net.sourceforge.jtds.jdbc.Driver
jdbc.urlSql=jdbc:jtds:sqlserver://10.108.19.117:1433/air
jdbc.usernameSql=sa
jdbc.passwordSql=123456789


#Oralce
database=Oracle
hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
jdbc.driverClassName=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@10.108.19.117:1521:csair
jdbc.username=kongqin2
jdbc.password=p2ssword

 在Spring中配置

<!-- ========================= RESOURCE DEFINITIONS ========================= -->
<bean id="propertyConfigurer"
	class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
	<property name="fileEncoding" value="UTF-8" />
	<property name="locations">
		<list>
			<value>classpath:jdbc.properties</value>
		</list>
	</property>
</bean>

 

2、DataSource配置

<bean id="dataSource" depends-on="initDataBaseManager"
	class="com.mchange.v2.c3p0.ComboPooledDataSource"
	destroy-method="close">
	<property name="driverClass"
		value="${jdbc.driverClassName}" />
	<property name="jdbcUrl" value="${jdbc.url}" />
	<property name="user" value="${jdbc.username}" />
	<property name="password" value="${jdbc.password}" />
	<property name="minPoolSize">
		<value>5</value>
	</property>
	<property name="maxPoolSize" value="30" />
	<property name="initialPoolSize" value="10" />
	<property name="maxIdleTime" value="60" />
	<property name="acquireIncrement" value="5" />
	<property name="maxStatements" value="0" />
	<property name="idleConnectionTestPeriod" value="60" />
	<property name="acquireRetryAttempts" value="30" />
	<property name="breakAfterAcquireFailure" value="true" />
	<property name="testConnectionOnCheckout" value="false" />
</bean>


<bean id="dataSourceSql" 
        class="com.mchange.v2.c3p0.ComboPooledDataSource"
        destroy-method="close">
        <property name="driverClass"
            value="${jdbc.driverClassNameSql}" />
        <property name="jdbcUrl" value="${jdbc.urlSql}" />
        <property name="user" value="${jdbc.usernameSql}" />
        <property name="password" value="${jdbc.passwordSql}" />
        <property name="minPoolSize">
            <value>5</value>
        </property>
        <property name="maxPoolSize" value="30" />
        <property name="initialPoolSize" value="10" />
        <property name="maxIdleTime" value="60" />
        <property name="acquireIncrement" value="5" />
        <property name="maxStatements" value="0" />
        <property name="idleConnectionTestPeriod" value="60" />
        <property name="acquireRetryAttempts" value="30" />
        <property name="breakAfterAcquireFailure" value="true" />
        <property name="testConnectionOnCheckout" value="false" />
</bean>

 

3、SessionFactory配置

<bean id="sessionFactory" 
	class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
	<property name="dataSource" ref="dataSource" />
	<property name="mappingLocations">
		<list>
			<value>classpath*:/org/jbpm/**/*.hbm.xml</value>
		</list>
	</property>
	<property name="annotatedClasses">
		<list>
			<value>com.forgon.security.model.Resource</value>
		</list>
	</property>
	<property name="hibernateProperties">
		<props>
			<prop key="hibernate.dialect">
				${hibernate.dialect}
			</prop>
			<prop key="hibernate.show_sql">true</prop>
			<prop key="hibernate.generate_statistics">true</prop>
               			<prop key="hibernate.connection.release_mode">auto</prop>
		               	<prop key="hibernate.autoReconnect">true</prop>
               			<prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>
			<prop key="hibernate.cache.use_query_cache">true</prop>
			<prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
			<prop key="current_session_context_class">thread</prop>
		</props>
	</property>
	<property name="eventListeners">
		<map>
			<entry key="merge">
				<bean
					class="org.springframework.orm.hibernate3.support.IdTransferringMergeEventListener" />
			</entry>
		</map>
	</property>
</bean>


<!-- Hibernate SessionFactory -->
    <bean id="sessionFactorySql" 
        class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
        <property name="dataSource" ref="dataSourceSql" />
        <property name="hibernateProperties">
            <props>
                <prop key="hibernate.dialect">
                    ${hibernate.dialectSql}
                </prop>
                <prop key="hibernate.show_sql">true</prop>
                <prop key="hibernate.generate_statistics">true</prop>
                <prop key="hibernate.connection.release_mode">auto</prop>
                <prop key="hibernate.autoReconnect">true</prop>
                <prop key="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</prop>
                <prop key="hibernate.cache.use_query_cache">true</prop>
                <prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
                <prop key="current_session_context_class">thread</prop>
            </props>
        </property>
    </bean>

 

4、Dao配置

<bean id="objectSqlDao"
        class="com.forgon.tools.hibernate.ObjectSqlDaoImpl">
        <property name="sessionFactory" ref="sessionFactorySql" />
</bean>

<bean id="objectDao"
	class="com.forgon.tools.hibernate.ObjectDaoImpl">
	<property name="sessionFactory" ref="sessionFactory" />
</bean>

 这里的Dao继承了HibernateDaoSupport。具体代码如下：

public class ObjectSqlDaoImpl extends HibernateDaoSupport implements ObjectSqlDao,
		Serializable {

	private static final long serialVersionUID = 1L;

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	public void save(Object po) {
		getHibernateTemplate().save(po);
	}

	public void clearCache() {
		getHibernateSession().flush();
		getHibernateSession().clear();
	}

	public void evict(Object object) {
		getHibernateTemplate().evict(object);
	}

	public void flush() {
		getHibernateTemplate().flush();
	}

}

 

5、测试用例

 

public class OtherDBConnetionTest extends AbstractTransactionalDataSourceSpringContextTests{

	private ObjectDao objectDao;
	
	private ObjectSqlDao objectSqlDao;
	
	public void setObjectDao(ObjectDao objectDao) {
		this.objectDao = objectDao;
	}

	public void setObjectSqlDao(ObjectSqlDao objectSqlDao) {
		this.objectSqlDao = objectSqlDao;
	}

	protected String[] getConfigLocations() {
		setAutowireMode(AutowireCapableBeanFactory.AUTOWIRE_BY_NAME);
		return new String[] { "spring/*.xml"};
	}
	
	@SuppressWarnings("unchecked")
	public void testOtherDBConnetion(){
		String sql = "where po.name = 'admin'";
		User user = (User) objectDao.getBySql(User.class.getSimpleName(), sql);
		assertNotNull(user);
		assertEquals("admin", user.getName());
		
		sql = "select * from SS_USERS ";
		ResultSet rs = objectSqlDao.executeSql(sql);
		assertNotNull(rs);
		try {
			rs.next();
			String name = (String)rs.getObject("name");
			assertEquals("admin", name);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

    objectSqlDao中的executeSql方法，源码如下：

public ResultSet executeSql(String sql) {
	Connection conn = getSession().connection();
	PreparedStatement ps;
	ResultSet rs = null;
	try {
		ps = conn.prepareStatement(sql);
		rs = ps.executeQuery();
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	return rs;
}