基于HikariCP连接池实现的多数据源动态切换

基于HikariCP连接池实现的多数据源动态切换

1.添加基于maven的依赖包

<dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP-java7</artifactId>
            <version>2.4.11</version>
        </dependency>

2.spring-mybatis.xml的配置

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
    xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
       http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
       http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.2.xsd
       http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
       http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd ">

    <!-- 通过扫描的模式，扫描目录在com/hoo/任意目录下的mapper目录下，所有的mapper都需要继承SqlMapper接口的接口 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.wanda.crs.**.mapper" />
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
    </bean>

    <!-- 配置DataSource数据源 -->
    <!-- 数据源A -->
    <bean id="portal" class="com.zaxxer.hikari.HikariDataSource"
        destroy-method="close">
        <!-- 无需指定，除非系统无法自动识别 -->
        <property name="driverClassName" value="${db.driver}" />
        <property name="jdbcUrl" value="${db.url}" />
        <property name="username" value="${db.user}" />
        <property name="password" value="${db.password}" />
        <!-- 连接只读数据库时配置为true， 保证安全 -->
        <property name="readOnly" value="${readOnly}" />
        <!-- 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒 -->
        <property name="connectionTimeout" value="${connectionTimeout}" />
        <!-- 一个连接idle状态的最大时长（毫秒），超时则被释放（retired），缺省:10分钟 -->
        <property name="idleTimeout" value="${idleTimeout}" />
        <!-- 一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），缺省:30分钟， 建议设置比数据库超时时长少30秒 -->
        <property name="maxLifetime" value="${maxLifetime}" />
        <!-- 连接池中允许的最大连接数。缺省值：10；推荐的公式：((core_count * 2) + effective_spindle_count) -->
        <property name="maximumPoolSize" value="${maximumPoolSize}" />
        <property name="connectionTestQuery" value="${connectionTestQuery}" />
    </bean>

    <!-- 配置动态数据源 -->
    <bean id="dataSource" class="com.wanda.crs.common.core.DynamicDataSource">
        <property name="targetDataSources">
            <map key-type="java.lang.String">
                <!-- <entry key="1" value-ref="portal" /> -->
                <!-- <entry key="2" value-ref="bo" /> -->
            </map>
        </property>
        <property name="defaultTargetDataSource" ref="portal" />
    </bean>

    <!--创建sqlSessionFactory -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />
        <property name="mapperLocations" value="classpath*:com/wanda/crs/**/*.xml" />
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource">
            <ref bean="dataSource" />
        </property>
    </bean>

    <bean id="applicationEventListener" class="com.wanda.crs.common.core.DynamicCreateDataSourceBean">
        <property name="dataSource" ref="dataSource"></property>
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>

    <!-- 事务切面配置 -->
    <aop:config>
        <aop:pointcut id="serviceOperation"
            expression="execution(* com.wanda..service.*.*(..))" />
        <aop:advisor pointcut-ref="serviceOperation" advice-ref="txAdvice" />
    </aop:config>
    <!-- 通知配置 -->
    <tx:advice id="txAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="del*" propagation="REQUIRED" read-only="false"
                rollback-for="java.lang.Exception" no-rollback-for="java.lang.RuntimeException" />
            <tx:method name="insert*" propagation="REQUIRED" read-only="false"
                rollback-for="java.lang.Exception" />
            <tx:method name="update*" propagation="REQUIRED" read-only="false"
                rollback-for="java.lang.Exception" />
            <tx:method name="add*" propagation="REQUIRED" read-only="false"
                rollback-for="java.lang.Exception" />
            <tx:method name="modify*" propagation="REQUIRED" read-only="false"
                rollback-for="java.lang.Exception" />
            <tx:method name="correction*" propagation="REQUIRED"
                read-only="false" rollback-for="java.lang.Exception" />

            <tx:method name="find*" propagation="SUPPORTS" />
            <tx:method name="query*" propagation="SUPPORTS" />
            <tx:method name="get*" propagation="SUPPORTS" />
            <tx:method name="select*" propagation="SUPPORTS" />
            <tx:method name="list*" propagation="SUPPORTS" />
            <tx:method name="*" propagation="SUPPORTS" />
        </tx:attributes>
    </tx:advice>
    <!-- 配置事务管理器，注意这里的dataSource和SqlSessionFactoryBean的dataSource要一致，不然事务就没有作用了 -->
    <bean id="transactionManager"
        class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>
</beans>

3.DynamicCreateDataSourceBean.java

package com.wanda.crs.common.core;

import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.BeansException;
import org.springframework.beans.factory.support.BeanDefinitionBuilder;
import org.springframework.beans.factory.support.DefaultListableBeanFactory;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.context.ApplicationEvent;
import org.springframework.context.ApplicationListener;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.event.ContextRefreshedEvent;
import org.springframework.jdbc.core.JdbcTemplate;

import com.wanda.crs.common.entity.DbConfig;
import com.wanda.crs.utils.PropertyUtil;

public class DynamicCreateDataSourceBean implements ApplicationContextAware,
        ApplicationListener<ApplicationEvent> {
    
    private static final String DBFILE_PATH = "/jdbc.properties";

    private ConfigurableApplicationContext app;
    
    private JdbcTemplate jdbcTemplate;

    private DynamicDataSource dataSource;
    
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public void setDataSource(DynamicDataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
    public void setApplicationContext(ApplicationContext app)
            throws BeansException {
        this.app = (ConfigurableApplicationContext)app;
    }

    @Override
    public void onApplicationEvent(ApplicationEvent event) {
        // 如果是容器刷新事件OR Start Event
        if (event instanceof ContextRefreshedEvent) {
            try {
                regDynamicBean();
            } catch (IOException e) {
                e.printStackTrace();
            }
            // System.out.println(event.getClass().getSimpleName()+" 事件已发生！");
        }
        
    }

    private void regDynamicBean() throws IOException {          
        // 解析属性文件，得到数据源Map          
        Map<String, DbConfig> mapCustom = parsePropertiesFile();          
        // 把数据源bean注册到容器中          
        addSourceBeanToApp(mapCustom);      
    }
    
    /**
     * 功能说明：根据DataSource创建bean并注册到容器中
     * 
     * @param acf
     * @param mapCustom
     */
    private void addSourceBeanToApp(Map<String, DbConfig> mapCustom) {
        DefaultListableBeanFactory acf = (DefaultListableBeanFactory) app.getAutowireCapableBeanFactory();
        
        PropertyUtil propertyUtil = new PropertyUtil();
        String readOnly = propertyUtil.getProperty(DBFILE_PATH, "readOnly");
        String connectionTimeout = propertyUtil.getProperty(DBFILE_PATH, "connectionTimeout");
        String idleTimeout = propertyUtil.getProperty(DBFILE_PATH, "idleTimeout");
        String maxLifetime = propertyUtil.getProperty(DBFILE_PATH, "maxLifetime");
        String maximumPoolSize = propertyUtil.getProperty(DBFILE_PATH, "maximumPoolSize");
        String connectionTestQuery= propertyUtil.getProperty(DBFILE_PATH, "connectionTestQuery");
    
        String DATASOURCE_BEAN_CLASS = "com.zaxxer.hikari.HikariDataSource";
        BeanDefinitionBuilder bdb;

        Iterator<String> iter = mapCustom.keySet().iterator();

        Map<Object, Object> targetDataSources = new LinkedHashMap<Object, Object>();
        
//        BeanDefinition beanDefinition = new ChildBeanDefinition("portal");
        // 将默认数据源放入 targetDataSources map中
        targetDataSources.put("portal1", app.getBean("portal"));
        
        // 根据数据源得到数据，动态创建数据源bean 并将bean注册到applicationContext中去
        while (iter.hasNext()) {

            // bean ID
            String beanKey = iter.next();
            // 创建bean
            bdb = BeanDefinitionBuilder.rootBeanDefinition(DATASOURCE_BEAN_CLASS);
            bdb.getBeanDefinition().setAttribute("id", beanKey);
            bdb.getBeanDefinition().setAttribute("destroy-method", "close");
            bdb.addPropertyValue("driverClassName", mapCustom.get(beanKey).getDriverclass());
            bdb.addPropertyValue("jdbcUrl", mapCustom.get(beanKey).getJdbcurl());
            bdb.addPropertyValue("username", mapCustom.get(beanKey).getUsername());
            bdb.addPropertyValue("password", mapCustom.get(beanKey).getPassword());
            
            bdb.addPropertyValue("readOnly", readOnly);
            bdb.addPropertyValue("connectionTimeout", Integer.parseInt(connectionTimeout));
            bdb.addPropertyValue("idleTimeout", Integer.parseInt(idleTimeout));
            bdb.addPropertyValue("maxLifetime", Integer.parseInt(maxLifetime));
            bdb.addPropertyValue("maximumPoolSize", Integer.parseInt(maximumPoolSize));
            bdb.addPropertyValue("connectionTestQuery", connectionTestQuery);
            
            // 注册bean
            acf.registerBeanDefinition("ds" + beanKey, bdb.getBeanDefinition());

            // 放入map中，注意一定是刚才创建bean对象
            targetDataSources.put(beanKey, app.getBean("ds" + beanKey));

        }
        // 将创建的map对象set到 targetDataSources；
        dataSource.setTargetDataSources(targetDataSources);

        // 必须执行此操作，才会重新初始化AbstractRoutingDataSource 中的
        // resolvedDataSources，也只有这样，动态切换才会起效
        dataSource.afterPropertiesSet();

    }

    /**
     * 功能说明：GET ALL SM_STATIONS FROM DB1
     * 
     * @return
     * @throws IOException
     */
    @SuppressWarnings("rawtypes")
    private Map<String, DbConfig> parsePropertiesFile()
            throws IOException {

        String sql = "SELECT ID_COMPANY,DRIVERCLASS,JDBCURL,USERNAME,PASSWORD FROM CRS_DB_CONFIG  WHERE IS_DEL=0 AND IS_ENABLED=10091020";

        List list = jdbcTemplate.queryForList(sql);
        Iterator iterator = list.iterator();
        Map<String, DbConfig> mds = new HashMap<String, DbConfig>();
        while (iterator.hasNext()) {
            Map map4station = (Map) iterator.next();
            DbConfig dsi = new DbConfig();
            dsi.setIdCompany((String)map4station.get("ID_COMPANY"));
            dsi.setDriverclass((String)map4station.get("DRIVERCLASS"));
            dsi.setJdbcurl((String)map4station.get("JDBCURL"));
            dsi.setUsername((String)map4station.get("USERNAME"));
            dsi.setPassword((String)map4station.get("PASSWORD"));
            mds.put(dsi.getIdCompany(), dsi);
        }
        return mds;
    }

}

4.DynamicDataSource.java

package com.wanda.crs.common.core;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

import com.wanda.crs.utils.CustomerContextHolder;

public class DynamicDataSource extends AbstractRoutingDataSource {
    
    protected Object determineCurrentLookupKey() {
        return CustomerContextHolder.getContextType();
    }
}

5.DbConfig.java

package com.wanda.crs.common.entity;

public class DbConfig {

    private String idCompany;
    
    private String driverclass;
    
    private String jdbcurl;
    
    private String username;
    
    private String password;

    public String getIdCompany() {
        return idCompany;
    }

    public void setIdCompany(String idCompany) {
        this.idCompany = idCompany;
    }

    public String getDriverclass() {
        return driverclass;
    }

    public void setDriverclass(String driverclass) {
        this.driverclass = driverclass;
    }

    public String getJdbcurl() {
        return jdbcurl;
    }

    public void setJdbcurl(String jdbcurl) {
        this.jdbcurl = jdbcurl;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
    
}

6.CustomerContextHolder

package com.wanda.crs.utils;
  
 /**
  * <b>function:</b> 多数据源
  */
 public class CustomerContextHolder {
  
     public final static String SESSION_FACTORY_PORTAL1 = "portal1";
     public final static String SESSION_FACTORY_BO1 = "bo1";
     
     private static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();  
     
     public static void setContextType(String contextType) {  
         contextHolder.set(contextType);  
     }  
       
     public static String getContextType() {  
         return contextHolder.get();  
     }  
       
     public static void clearContextType() {  
         contextHolder.remove();  
     }  
 }

7.切换使用

    CustomerContextHolder.setContextType(order.getIdCompany());
                Order orderBean=orderMapper.selectByPrimaryKey(order.getIdOrder());