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。

解决办法：

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