多数据源简单实现,spring aop动态切换

多数据源简单实现

实现理念

本次使用DRUID连接池连接mysql和oracl数据库，默认mysq连接，在需要切换oracle连接 ，通过 DynamicDataSource来动态获取数据源

实现步骤

配置文件：
mysql配置

udp.db.driverClassName=com.mysql.jdbc.Driver
udp.db.url=jdbc:mysql://127.0.0.1:33306/dianqu?useUnicode=true&characterEncoding=UTF-8&useLocalSessionState=true
udp.db.username=root
udp.db.password=123456
#Druid
druid.initialSize=1
druid.minIdle=1
druid.maxActive=300
druid.maxWait=60000
druid.timeBetweenEvictionRunsMillis=60000
druid.minEvictableIdleTimeMillis=30000
druid.validationQuery=SELECT 1
druid.testWhileIdle=true
druid.testOnBorrow=false
druid.testOnReturn=false
druid.poolPreparedStatements=false
druid.maxPoolPreparedStatementPerConnectionSize=20
druid.filters=stat
druid.removeAbandoned=true
druid.removeAbandonedTimeout=1800
druid.logAbandoned=true
druid.defaultAutoCommit=false

oracle设置

database.driverClassName=oracle.jdbc.driver.OracleDriver
database.url=jdbc:oracle:thin:@//127.0.0.1:1521/orcl
database.username=orcl
database.password=123456

database.maxActive=10
database.maxWait=60000

spring-datasource.xml 配置
加载配置文件，创建spring

<bean id="propertyConfigurer"
          class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="ignoreUnresolvablePlaceholders" value="true"/>
        <property name="locations">
            <list>
                <value>classpath:jdbc.properties</value>
            </list>
        </property>
    </bean>

设置mysql_dataSource 的DataSource

<bean id="mysql_dataSource" class="com.alibaba.druid.pool.DruidDataSource"
          init-method="init" destroy-method="close">
        <!-- 基本属性 url、user、password -->
        <property name="driverClassName" value="${udp.db.driverClassName}" />
        <property name="url" value="${udp.db.url}" />
        <property name="username" value="${udp.db.username}" />
        <property name="password" value="${udp.db.password}" />

        <!-- 配置初始化大小、最小空闲、最大使用连接数量 -->
        <property name="initialSize" value="${druid.initialSize}" />
        <property name="minIdle" value="${druid.minIdle}" />
        <property name="maxActive" value="${druid.maxActive}" />

        <!-- 配置获取连接等待超时的最大时间 -->
        <property name="maxWait" value="${druid.maxWait}" />

        <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
        <property name="timeBetweenEvictionRunsMillis" value="${druid.timeBetweenEvictionRunsMillis}" />

        <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
        <property name="minEvictableIdleTimeMillis" value="${druid.minEvictableIdleTimeMillis}" />

        <property name="validationQuery" value="${druid.validationQuery}" />
        <property name="testWhileIdle" value="${druid.testWhileIdle}" />
        <property name="testOnBorrow" value="${druid.testOnBorrow}" />
        <property name="testOnReturn" value="${druid.testOnReturn}" />
        <property name="defaultAutoCommit" value="${druid.defaultAutoCommit}"/>

        <!-- 打开PSCache，并且指定每个连接上PSCache的大小 -->
        <property name="poolPreparedStatements" value="${druid.poolPreparedStatements}" />
        <property name="maxPoolPreparedStatementPerConnectionSize" value="${druid.maxPoolPreparedStatementPerConnectionSize}" />

        <!-- 配置监控统计拦截的filters，去掉后监控界面sql无法统计 -->
        <property name="filters" value="${druid.filters}" />

        <!-- 打开removeAbandoned功能 -->
        <property name="removeAbandoned" value="${druid.removeAbandoned}" />
        <!-- 清除无用连接的等待时间，1800秒 -->
        <property name="removeAbandonedTimeout" value="${druid.removeAbandonedTimeout}" />
        <!-- 关闭abanded连接时输出错误日志 -->
        <property name="logAbandoned" value="${druid.logAbandoned}" />
    </bean>

配置oracle的DataSource

<bean id="oracle_dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="${database.driverClassName}" />
        <property name="url" value="${database.url}" />
        <property name="username" value="${database.username}" />
        <property name="password" value="${database.password}" />
        <property name="maxActive" value="${database.maxActive}" />
        <property name="maxWait" value="${database.maxWait}" />
    </bean>

配置DataSource 设置mysql_dataSource为DataSource

<!--动态数据源-->
    <bean id="dataSource" class="com.jsict.common.dataSource.DynamicDataSource">
        <property name="targetDataSources">
            <map key-type="java.lang.String">
                <entry value-ref="mysql_dataSource" key="mysql"></entry>
                <entry value-ref="oracle_dataSource" key="oracle"></entry>
            </map>
        </property>
        <!-- 默认目标数据源为主库数据源 -->
        <property name="defaultTargetDataSource" ref="mysql_dataSource"/>
    </bean>

配置文件配置结束，如果需要切换则可以直接

设置DynamicDataSource，mysql和oracle需要和上面配置文件的key一致

public class DynamicDataSource extends AbstractRoutingDataSource {
    public static final String MYSQL = "mysql";
    public static final String ORACLE = "oracle";
    //----获取到当前正在执行的CurrentThread
    public static final ThreadLocal<String> contextHolder = new ThreadLocal<String>();

    public static void setCustomerType(String customerType) {
        contextHolder.set(customerType);
    }

    public static String getCustomerType() {
        return contextHolder.get();
    }

    public static void clearCustomerType() {
        contextHolder.remove();
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return getCustomerType();
    }
}

切换时需要

 设置oracle为数据源
 DynamicDataSource.setCustomerType(DynamicDataSource.ORACLE);
 设置mysql为数据源
 DynamicDataSource.setCustomerType(DynamicDataSource.MYSQL);

ps: 如果session已经创建了，则无法在一个session里面切换，需要新建一个session做到切换，如下面：

 DynamicDataSource.setCustomerType(DynamicDataSource.ORACLE);
 User entity= UserDao.getUser;
 DynamicDataSource.clearCustomerType();
 DynamicDataSource.setCustomerType(DynamicDataSource.MYSQL);

需要在UserDao.getUserByMobile内新建session,这时候的连接才是oracle连接

        Session session =this.entityManager.unwrap(Session.class);
        SessionFactoryImpl sessionFactory = (SessionFactoryImpl)session.getSessionFactory();
        Session ession_oracle = sessionFactory.openSession();
        String sql =" select * from user ";
        Query query = ession_oracle.createNativeQuery(sql,User.class); 
        return (User) query.getResultList();

以上就是完整完整，但是每次切换需要手动切换很不方便，下一步可以使用spring aop特性，通过加注解的实现动态切换和事务管理

以下就是通过spring AOP实现动态切换
先定义DynamicDataSourceOper 注解，通过value值进行切换

@Target({ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface DynamicDataSourceOper {
//默认是主数据源
   String dynamicDataSource() default DynamicDataSource.MYSQL;
}

配置切面类：
通过@Around("@annotation(dynamicDataSourceOper )")拦截执行方法上面有@dynamicDataSourceOper注解获取注解里面的值，调用ThreadLocal.setCustomerType()做到切换

@Aspect
@Component
public class DataSourceAspect {
    private static final Logger logger = LoggerFactory.getLogger(DataSourceAspect.class);
    /**
     * @param pjp
     * @param dynamicDataSourceOper
     * @return
     * @throws Throwable
     * @throws RuntimeException
     */
    @Around("@annotation(dynamicDataSourceOper)")
    public Object doAround(ProceedingJoinPoint pjp, DynamicDataSourceOper dynamicDataSourceOper) {
        Object retVal = null;
        boolean selectedDataSource = false;
        try {
            if (null != dynamicDataSourceOper) {
                selectedDataSource = true;
                DynamicDataSource.setCustomerType(dynamicDataSourceOper.dynamicDataSource());
            }
            retVal = pjp.proceed();
        } catch (Throwable e) {
            logger.error("切换错误",e);
            throw new RuntimeException(e);
        } finally {
            if (selectedDataSource) {
                DynamicDataSource.clearCustomerType();
            }
        }
        return retVal;
    }
 }