多数据源简单实现

实现理念

本次使用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; } }