Spring+Jpa多数据源配置
Posted on: 2015-07-22, Last modified: 2015-07-30, View: 3281

项目中遇到多数据源问题,网上搜了一大圈,找个其中的两个解决方法,方案一是配置多个EntityManagerFactory,使用不同数据源时声明要用的entityManaferFactory即可;还看到有人通过设置一个dataSource代理来动态选择当前操作的数据源。第一种方法我使用到了项目中,确认在Spring3.2中没有问题,第二种方法没有试过,暂且记录下来:

方案一:

基本上是对Spring配置文件的修改,添加另外一套管理bean,设计到两个文件persistence.xml和applicatonContext.xml,下面是针对两个数据源的配置文件实例:

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"
             version="1.0">
 
    <!--配置mysql数据源-->
    <persistence-unit name="mysql" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <non-jta-data-source>mysqlDataSource</non-jta-data-source>
       <!--这里的class是指要储存于本数据库的Entity-->
        <class>com.sunhope.model.core.ExternalEnvironment</class>   
        
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect"/>
            <property name="hibernate.archive.autodetection" value="false"/>
            <property name="hibernate.transaction.auto_close_session" value="false"/>
            <property name="hibernate.hbm2ddl.auto" value="update" />
        </properties>
    </persistence-unit>
    <!--配置postgre数据源-->
    <persistence-unit name="postgre" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        
        <non-jta-data-source>postgreDataSource</non-jta-data-source>
 
        <class>com.sunhope.model.baseinfo.BdStorageCompany</class>
        <class>com.sunhope.model.baseinfo.BdHouseType</class>
        <class>com.sunhope.model.baseinfo.BdStorage</class>
        <class>com.sunhope.model.core.StoreEnvironment</class>
        
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
            <property name="hibernate.archive.autodetection" value="false"/>
            <property name="hibernate.transaction.auto_close_session" value="false"/>
            <property name="hibernate.hbm2ddl.auto" value="update" />
        </properties>
    </persistence-unit>
    
</persistence>

applicationContext.xml

     .......
             
    <!-- Data Source -->
    <bean id="dataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="" />
        <property name="username" value="" />
        <property name="password" value="" />
    </bean>

    <bean id="dataSource2"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="" />
        <property name="username" value="" />
        <property name="password" value="" />
    </bean>

    <!-- This will ensure that Hibernate or JPA exceptions are automatically
        translated into Spring's generic DataAccessException hierarchy for those
        classes annotated with Repository. For example, see ***DAOImpl. -->
    <bean class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor" />


    <!-- JPA Entity Manager Factory -->
    <bean id="entityManagerFactory" p:persistenceUnitName="bullEM"
          class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
          p:packagesToScan="com.sonft.bull.domain" p:dataSource-ref="dataSource"
          p:jpaVendorAdapter-ref="hibernateVendor" p:jpaPropertyMap-ref="jpaPropertyMap"  />


    <bean id="entityManagerFactory2" p:persistenceUnitName="sysEM"
          class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
          p:packagesToScan="com.sonft.bull.sysInfo" p:dataSource-ref="dataSource2"
          p:jpaVendorAdapter-ref="hibernateVendor2" p:jpaPropertyMap-ref="jpaPropertyMap2"  />

    <util:map id="jpaPropertyMap">
        <entry key="hibernate.hbm2ddl.auto" value="update" />
        <entry key="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
    </util:map>

    <util:map id="jpaPropertyMap2">
        <entry key="hibernate.hbm2ddl.auto" value="update" />
        <entry key="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
    </util:map>

    <bean id="hibernateVendor"
          class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"
          p:database="MYSQL" p:showSql="false" p:generateDdl="true"
          p:databasePlatform="org.hibernate.dialect.MySQLDialect" />

    <bean id="hibernateVendor2"
          class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"
          p:database="MYSQL" p:showSql="false" p:generateDdl="true"
          p:databasePlatform="org.hibernate.dialect.MySQLDialect" />

    <!-- Transaction Config -->
    <bean id="bullTransactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"
          p:entityManagerFactory-ref="entityManagerFactory">
        <qualifier value="em"/>
        <property name="jpaDialect">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect" />
        </property>
    </bean>

    <bean id="sysTransactionManager" class="org.springframework.orm.jpa.JpaTransactionManager"
          p:entityManagerFactory-ref="entityManagerFactory2">
        <qualifier value="em2"/>
        <property name="jpaDialect">
            <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect" />
        </property>
    </bean>

    <!-- User declarative transaction management -->
    <tx:annotation-driven transaction-manager="bullTransactionManager"/>
    <tx:annotation-driven transaction-manager="sysTransactionManager"/>
 
     ..........

调用的时候加上transactionManager的参数就可以:

@Transactional("sysTransactionManager")
public class JdpItemServiceImpl implements JdpItemService{
    @PersistenceContext(unitName = "sysEM")
    private EntityManager sem;

    public List<JdbTBItem> getAllJdbItems(){
        return sem.createNamedQuery("jdpItem.getAllJdpItems", JdbTBItem.class)
                    .getResultList();
    }
}

注意的问题:

1、如果在声明的时候不设置unitName的话,特别是项目原来是单数据源时,unitName一般都不写使用默认的,就会报一个NoSuchBeanDefinetionException的异常

Caused by: org.springframework.beans.factory.NoSuchBeanDefinitionException: No unique bean 
of type [javax.persistence.EntityManagerFactory] is defined: expected single bean but found 2

只要加上UnitName的制定就好了。@PersistenceContext(unitName = "sysEM")

2、通过设置transactionManagement的属性p:packagesToScan="com.sonft.bull.sysInfo"制定数据源的扫描范围,不然在两个数据库中会生成所有的实体的表;

3、@Transactional("sysTransactionManager") 同样需要写明参数,不然无法正确的加入事务控制

 

方案二:

需要开发一个DataSource的代理,代理实现javax.sql.DataSource接口。该代理根据一定的策略从已有的多个DataSource中选择一个,提供给SessionFactory,供数据访问层使用。

DataSource Proxy选择DataSource的方式可以根据实际进行设计,这里为简单,就采用随机方式选择一个。源代码如下:

package code;  
  
import java.io.PrintWriter;  
import java.sql.Connection;  
import java.sql.SQLException;  
  
import java.util.List;  
import java.util.Random;  
  
import javax.sql.DataSource;  
  
import org.apache.commons.logging.Log;  
import org.apache.commons.logging.LogFactory;  
  
public class RandomDataSource  implements DataSource {  
  
    private List<DataSource> dataSourcePool ;  
    protected Log log =  LogFactory.getLog(getClass());  
      
    private  static ThreadLocal<DataSource> dsHolder = new ThreadLocal<DataSource>();  
      
      
    /** 
     * 从已有DataSource中随机选择一个 
     * 
     * 
     */  
    private DataSource randomDs(){  
          
        int size = dataSourcePool.size();  
          
        Random r= new Random();  
          
        int t = r.nextInt(size);  
          
        dsHolder.set(dataSourcePool.get(t));  
        return dsHolder.get();  
          
          
    }  
      
      
    @Override  
    public Connection getConnection() throws SQLException {  
          
        Connection conn = randomDs().getConnection();  
        log.info("conn URL---->"+conn.getMetaData().getURL());  
        return conn;  
    }  
  
    @Override  
    public Connection getConnection(String username, String password)  
            throws SQLException {  
        // TODO Auto-generated method stub  
          
        Connection conn = randomDs().getConnection(username, password);  
          
          
        return conn;  
    }  
  
    @Override  
    public PrintWriter getLogWriter() throws SQLException {  
          
        return dsHolder.get().getLogWriter();  
    }  
  
    @Override  
    public int getLoginTimeout() throws SQLException {  
        // TODO Auto-generated method stub  
        return dsHolder.get().getLoginTimeout();  
    }  
  
    @Override  
    public void setLogWriter(PrintWriter out) throws SQLException {  
        // TODO Auto-generated method stub  
        dsHolder.get().setLogWriter(out);  
    }  
  
    @Override  
    public void setLoginTimeout(int seconds) throws SQLException {  
        // TODO Auto-generated method stub  
        dsHolder.get().setLoginTimeout(seconds);  
    }  
  
    @Override  
    public boolean isWrapperFor(Class<?> iface) throws SQLException {  
        // TODO Auto-generated method stub  
        return dsHolder.get().isWrapperFor(iface);  
    }  
  
    @Override  
    public <T> T unwrap(Class<T> iface) throws SQLException {  
        // TODO Auto-generated method stub  
        return dsHolder.get().unwrap(iface);  
    }  
  
    public List<DataSource> getDataSourcePool() {  
        return dataSourcePool;  
    }  
  
    public void setDataSourcePool(List<DataSource> dataSourcePool) {  
          
        this.dataSourcePool = dataSourcePool;  
    }  
  
}  

为了可用性,实际生产环境中需要判断取到的DataSource对应的数据库是否可访问,若不能访问,则要取其他可用的DataSource。


在springframework中的配置如下:

<bean id="dataSource" class="code.RandomDataSource">  
        <property name="dataSourcePool">  
            <list>  
                <ref bean="dataSource1" />  
                <ref bean="dataSource2" />  
            </list>  
        </property>  
    </bean>  
      
      
    <bean id="dataSource1" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">  
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>  
        <property name="jdbcUrl" value="jdbc:mysql://192.168.2.192/ebook"/>  
        <property name="user" value="test"/>  
        <property name="password" value="test"/>  
    </bean>  
      
     <bean id="dataSource2" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">  
        <property name="driverClass" value="com.mysql.jdbc.Driver"/>  
        <property name="jdbcUrl" value="jdbc:mysql://192.168.2.105/ebook"/>  
        <property name="user" value="test"/>  
        <property name="password" value="test"/>  
    </bean>  
      
      
    <!-- Hibernate SessionFactory -->  
    <bean id="sessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">  
        <property name="hibernateProperties">  
            <props>  
                <prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>  
                <prop key="hibernate.show_sql">true</prop>  
                <prop key="hibernate.format_sql">true</prop>  
                  
            </props>  
        </property>  
        <property name="dataSource" ref="dataSource"/>  
          
          
    </bean>  

这里连接的是MySQL数据库,如果是其他数据库,需要将DataSource1和DataSource2中的参数以及hibernate.dialect做相应修改。

方案二是摘抄别人的方法,没有试过到底可行不,只是记录在这里。

 

Go
Friend Links:
Bill Site
https://item.taobao.com/item.htm?spm=a21an.7676007.1998473182.296.lAvrE2&id=45399580446