Spring4+JPA+hibernate4创建多数据源

一、场景描述：

近期公司做了一个项目，使用Spring4+JPA+hibernate4、数据库使用MySql数据库。

目前项目进入对接阶段，需求：

1. 部分数据需要从对接平台获取。
2. 获取方式：对接平台开放数据库访问，由我方直接获取。
3. 数据库类型：sqlserver

解决方案：在原有的数据源基础上增加1或多数据源支撑。

二、具体实现：

1、配置多数据源：需要修改spring配置文件（applicationContext.xml）

原来配置如下图

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
		<property name="driverClass" value="${jdbc.driver}" />
		<property name="jdbcUrl" value="${jdbc.url}" />
		<property name="user" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
		<property name="initialPoolSize" value="${connection_pools.initial_pool_size}" />
		<property name="minPoolSize" value="${connection_pools.min_pool_size}" />
		<property name="maxPoolSize" value="${connection_pools.max_pool_size}" />
		<property name="maxIdleTime" value="${connection_pools.max_idle_time}" />
		<property name="acquireIncrement" value="${connection_pools.acquire_increment}" />
		<property name="checkoutTimeout" value="${connection_pools.checkout_timeout}" />
	</bean>

	<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="packagesToScan">
		    <list>
                        <value>..实体类包..</value>
                    </list>
                </property>  
		<property name="jpaVendorAdapter">
			<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
				<property name="showSql" value="false" />
				<property name="generateDdl" value="true" />
			</bean>
		</property>
		<property name="jpaProperties">
			<props>
				<prop key="hibernate.dialect">${hibernate.dialect}</prop>
				<prop key="hibernate.ejb.naming_strategy">org.hibernate.cfg.ImprovedNamingStrategy</prop>
				<prop key="hibernate.cache.use_second_level_cache">${hibernate.cache.use_second_level_cache}</prop>
				<prop key="hibernate.cache.region.factory_class">${hibernate.cache.region.factory_class}</prop>
				<prop key="hibernate.cache.use_query_cache">${hibernate.cache.use_query_cache}</prop>
				<prop key="hibernate.jdbc.fetch_size">${hibernate.jdbc.fetch_size}</prop>
				<prop key="hibernate.jdbc.batch_size">${hibernate.jdbc.batch_size}</prop>
				<prop key="hibernate.connection.isolation">2</prop>
				<prop key="javax.persistence.validation.mode">none</prop>
				<prop key="hibernate.search.lucene_version">LUCENE_36</prop>
				<prop key="hibernate.search.analyzer">org.wltea.analyzer.lucene.IKAnalyzer</prop>
				<prop key="hibernate.search.default.directory_provider">org.hibernate.search.store.impl.FSDirectoryProvider</prop>
				<prop key="hibernate.search.default.indexBase">${webapp.root}/WEB-INF/index</prop>
			</props>
		</property>
	</bean>

	<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
		<property name="entityManagerFactory" ref="entityManagerFactory" />
	</bean>

	<bean id="transactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
		<property name="transactionManager" ref="transactionManager" />
	</bean>

	<bean id="readOnlyTransactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
		<property name="transactionManager" ref="transactionManager" />
		<property name="readOnly" value="true" />
	</bean>

	<tx:annotation-driven transaction-manager="transactionManager" />

修改配置

<!-- mysql数据源 -->
	<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
		<property name="driverClass" value="${jdbc.driver}" />
		<property name="jdbcUrl" value="${jdbc.url}" />
		<property name="user" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
		<property name="initialPoolSize" value="${connection_pools.initial_pool_size}" />
		<property name="minPoolSize" value="${connection_pools.min_pool_size}" />
		<property name="maxPoolSize" value="${connection_pools.max_pool_size}" />
		<property name="maxIdleTime" value="${connection_pools.max_idle_time}" />
		<property name="acquireIncrement" value="${connection_pools.acquire_increment}" />
		<property name="checkoutTimeout" value="${connection_pools.checkout_timeout}" />
	</bean>

	<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
		<property name="dataSource" ref="dataSource" />
		<property name="persistenceUnitName" value="mysql" />
		<property name="packagesToScan">
		    <list>
                         <value>..实体类包..</value>
                    </list>
                </property>  
		<property name="jpaVendorAdapter">
			<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
				<property name="showSql" value="false" />
				<property name="generateDdl" value="true" />
			</bean>
		</property>
		<property name="jpaProperties">
			<props>
				<prop key="hibernate.dialect">${hibernate.dialect}</prop>
				<prop key="hibernate.ejb.naming_strategy">org.hibernate.cfg.ImprovedNamingStrategy</prop>
				<prop key="hibernate.cache.use_second_level_cache">${hibernate.cache.use_second_level_cache}</prop>
				<prop key="hibernate.cache.region.factory_class">${hibernate.cache.region.factory_class}</prop>
				<prop key="hibernate.cache.use_query_cache">${hibernate.cache.use_query_cache}</prop>
				<prop key="hibernate.jdbc.fetch_size">${hibernate.jdbc.fetch_size}</prop>
				<prop key="hibernate.jdbc.batch_size">${hibernate.jdbc.batch_size}</prop>
				<prop key="hibernate.connection.isolation">2</prop>
				<prop key="javax.persistence.validation.mode">none</prop>
				<prop key="hibernate.search.lucene_version">LUCENE_36</prop>
				<prop key="hibernate.search.analyzer">org.wltea.analyzer.lucene.IKAnalyzer</prop>
				<prop key="hibernate.search.default.directory_provider">org.hibernate.search.store.impl.FSDirectoryProvider</prop>
				<prop key="hibernate.search.default.indexBase">${webapp.root}/WEB-INF/index</prop>
			</props>
		</property>
	</bean>

	<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
		<property name="entityManagerFactory" ref="entityManagerFactory" />
		<qualifier value="mysqlEM" />
	</bean>

	<bean id="transactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
		<property name="transactionManager" ref="transactionManager" />
		<qualifier value="mysqlEM" />
	</bean>

	<bean id="readOnlyTransactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
		<property name="transactionManager" ref="transactionManager" />
		<property name="readOnly" value="true" />
		<qualifier value="mysqlEM" />
	</bean>

	<tx:annotation-driven transaction-manager="transactionManager" />
	<!-- mysql(end) -->
	
	<!-- sqlserver数据源 -->
	<bean id="sqlserverDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
		<property name="driverClass" value="${jdbc.driver.sqlserver}" />
		<property name="jdbcUrl" value="${jdbc.url.sqlserver}" />
		<property name="user" value="${jdbc.username.sqlserver}" />
		<property name="password" value="${jdbc.password.sqlserver}" />
		<property name="initialPoolSize" value="${connection_pools.initial_pool_size}" />
		<property name="minPoolSize" value="${connection_pools.min_pool_size}" />
		<property name="maxPoolSize" value="${connection_pools.max_pool_size}" />
		<property name="maxIdleTime" value="${connection_pools.max_idle_time}" />
		<property name="acquireIncrement" value="${connection_pools.acquire_increment}" />
		<property name="checkoutTimeout" value="${connection_pools.checkout_timeout}" />
	</bean>

	<bean id="sqlserverEntityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
		<property name="dataSource" ref="sqlserverDataSource" />
		<property name="persistenceUnitName" value="sqlserver" />
		<property name="packagesToScan">
		 	<list>
                             <value>..实体类包..</value>
                        </list>
                </property>  
		<property name="jpaVendorAdapter">
			<bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
				<property name="showSql" value="false" />
				<property name="generateDdl" value="false" />
			</bean>
		</property>
		<property name="jpaProperties">
			<props>
				<prop key="hibernate.dialect">${hibernate.dialect.sqlserver}</prop>
				<prop key="hibernate.cache.use_second_level_cache">${hibernate.cache.use_second_level_cache}</prop>
				<prop key="hibernate.cache.region.factory_class">${hibernate.cache.region.factory_class}</prop>
				<prop key="hibernate.cache.use_query_cache">${hibernate.cache.use_query_cache}</prop>
				<prop key="hibernate.jdbc.fetch_size">${hibernate.jdbc.fetch_size}</prop>
				<prop key="hibernate.jdbc.batch_size">${hibernate.jdbc.batch_size}</prop>
				<prop key="hibernate.connection.isolation">2</prop>
				<prop key="javax.persistence.validation.mode">none</prop>
				<prop key="hibernate.search.lucene_version">LUCENE_36</prop>
				<prop key="hibernate.search.analyzer">org.wltea.analyzer.lucene.IKAnalyzer</prop>
				<prop key="hibernate.search.default.directory_provider">org.hibernate.search.store.impl.FSDirectoryProvider</prop>
				<prop key="hibernate.search.default.indexBase">${webapp.root}/WEB-INF/index</prop>
			</props>
		</property>
	</bean>

	<bean id="sqlserverTransactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
		<property name="entityManagerFactory" ref="sqlserverEntityManagerFactory" />
		<qualifier value="sqlserverEM" />
	</bean>

	<bean id="sqlserverTransactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
		<property name="transactionManager" ref="sqlserverEntityManagerFactory" />
		<qualifier value="sqlserverEM" />
	</bean>

	<bean id="sqlserverReadOnlyTransactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
		<property name="transactionManager" ref="sqlserverEntityManagerFactory" />
		<property name="readOnly" value="true" />
		<qualifier value="sqlserverEM" />
	</bean>

	<tx:annotation-driven transaction-manager="sqlserverEntityManagerFactory" />
	<!-- sqlserver(end) -->

红色字体部分为添加修改时注意的地方，用来区分数据源。

2、服务层（service）

修改前

修改后

新添加的service服务

3、DAO层

修改前

修改后

新添加的DAO

以上配置，当调用不同的service、dao即实现多数据源的分别使用了。

三、使用时遇到的问题

1、sqlserver数据库表字段样式

当使用sqlserver数据源访问时发出的查询请求，字段名称变为（这里用CreateDate字段举例）create_date。

解决办法：

在之前的配置中，如下图红圈部分，这里做了限定。所以出现了上述问题。将其去掉即可。