多数据库，多数据源，数据库路由

忘是亡心i 2022-04-05 10:22 297阅读 0赞

##                                 首先，要明白为什么要使用多数据库？ ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3MDI2NjAz_size_16_color_FFFFFF_t_70][]

##           正常情况下，如果，百万数据在单个mysql其实没太大影响的，但是如果数据达到了，千万，亿，甚至更多，从数据库着手我们得对数据库进行拆分成多个库，但是多个库之间的数据操作，这就涉及到了我们的数据源之间的路由的，但是具体代码怎么实现的呢？这里我用spring+springmvc+hibernate+maven做成案例，废话不多说，直接上代码。 ##

![faddfad8dbf51d88923848b21f9d5271.png][]

### 项目结构图 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3MDI2NjAz_size_16_color_FFFFFF_t_70 1][]

### 数据库 ###

![20181213102350760.png][]![20181213102400667.png][]![2018121310240912.png][]

## pom.xml引入的jar包 ##

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    	<modelVersion>4.0.0</modelVersion>
    	<groupId>com.lcj.blog</groupId>
    	<artifactId>datasourceDynamic</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    	<properties>
    		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    		<spring.version>4.2.6.RELEASE</spring.version>
    	</properties>
    
    	<dependencies>
    		
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-core</artifactId>
    			<version>${spring.version}</version>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-context</artifactId>
    			<version>${spring.version}</version>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-web</artifactId>
    			<version>${spring.version}</version>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-webmvc</artifactId>
    			<version>${spring.version}</version>
    		</dependency>
    		<dependency>
    			<groupId>org.springframework</groupId>
    			<artifactId>spring-orm</artifactId>
    			<version>${spring.version}</version>
    		</dependency>
    		
    
    		
    		<dependency>
    			<groupId>org.hibernate</groupId>
    			<artifactId>hibernate-core</artifactId>
    			<version>3.6.9.Final</version>
    		</dependency>
    		<dependency>
    			<groupId>org.hibernate</groupId>
    			<artifactId>hibernate-entitymanager</artifactId>
    			<version>3.6.9.Final</version>
    		</dependency>
    		
    
    		
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    			<version>5.1.6</version>
    		</dependency>
    
    		
    		<dependency>
    			<groupId>javax.servlet.jsp.jstl</groupId>
    			<artifactId>jstl-api</artifactId>
    			<version>1.2</version>
    		</dependency>
    
    		
    		<dependency>
    			<groupId>javax.servlet</groupId>
    			<artifactId>javax.servlet-api</artifactId>
    			<version>3.1.0</version>
    		</dependency>
    
    		
    		<dependency>
    			<groupId>commons-dbcp</groupId>
    			<artifactId>commons-dbcp</artifactId>
    			<version>1.4</version>
    		</dependency>
    		<dependency>
    			<groupId>commons-pool</groupId>
    			<artifactId>commons-pool</artifactId>
    			<version>1.6</version>
    		</dependency>
    
    		<dependency>
    			<groupId>org.aspectj</groupId>
    			<artifactId>aspectjweaver</artifactId>
    			<version>1.8.6</version>
    		</dependency>
    	</dependencies>
    </project>
    ！

### spring.xml配置，主要的是多数据源初始化，sessionFactory工厂，事物，切面 ###

<?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:cache="http://www.springframework.org/schema/cache"
    	xmlns:context="http://www.springframework.org/schema/context"
    	xmlns:jdbc="http://www.springframework.org/schema/jdbc" xmlns:jee="http://www.springframework.org/schema/jee"
    	xmlns:jms="http://www.springframework.org/schema/jms" xmlns:lang="http://www.springframework.org/schema/lang"
    	xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:oxm="http://www.springframework.org/schema/oxm"
    	xmlns:p="http://www.springframework.org/schema/p" xmlns:task="http://www.springframework.org/schema/task"
    	xmlns:tx="http://www.springframework.org/schema/tx" xmlns:util="http://www.springframework.org/schema/util"
    	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.3.xsd 
    	http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd  
       	http://www.springframework.org/schema/cache http://www.springframework.org/schema/cache/spring-cache-4.3.xsd  
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd  
        http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.3.xsd  
        http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee-4.3.xsd  
        http://www.springframework.org/schema/jms http://www.springframework.org/schema/jms/spring-jms-4.3.xsd  
      	http://www.springframework.org/schema/lang http://www.springframework.org/schema/lang/spring-lang-4.3.xsd  
       	http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.3.xsd  
      	http://www.springframework.org/schema/oxm http://www.springframework.org/schema/oxm/spring-oxm-4.3.xsd  
     	http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-4.3.xsd  
        http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd  
        http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.3.xsd">
    
    	
    	<bean id="dataSource1" class="org.apache.commons.dbcp.BasicDataSource">
    		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
    		<property name="url"
    			value="jdbc:mysql://localhost:3306/dataSource1?useUnicode=true&characterEncoding=UTF-8" />
    		<property name="username" value="root" />
    		<property name="password" value="123456" />
    	</bean>
    
    	<bean id="dataSource2" class="org.apache.commons.dbcp.BasicDataSource">
    		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
    		<property name="url"
    			value="jdbc:mysql://localhost:3306/dataSource2?useUnicode=true&characterEncoding=UTF-8" />
    		<property name="username" value="root" />
    		<property name="password" value="123456" />
    	</bean>
    
    	
    	<bean id="dataSource" class="com.liu.core.datasource.DynamicDatasource">
    		<property name="targetDataSources">
    			<map key-type="java.lang.String">
    				<entry key="dataSource1" value-ref="dataSource1" />
    				<entry key="dataSource2" value-ref="dataSource2" />
    			</map>
    		</property>
    		<property name="defaultTargetDataSource" ref="dataSource1" />
    	</bean>
    
    	
    	<bean id="sessionFactory"
    		class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    		<property name="dataSource" ref="dataSource" />
    		<property name="hibernateProperties">
    			<props>
    				<prop key="hibernate.dialect">org.hibernate.dialect.MySQLDialect</prop>
    				<prop key="hibernate.format_sql">true</prop>
    				<prop key="hibernate.hbm2ddl.auto">update</prop>
    			</props>
    		</property>
    		<property name="mappingLocations" value="classpath:/com/liu/model/*.hbm.xml" />
    	</bean>
    
    	
    	<bean id="transactionManager"
    		class="org.springframework.orm.hibernate3.HibernateTransactionManager">
    		<property name="sessionFactory" ref="sessionFactory" />
    	</bean>
     
    	<bean id="transactionTemplate"
    		class="org.springframework.transaction.support.TransactionTemplate">
    		<property name="transactionManager">
    			<ref bean="transactionManager" />
    		</property>
    	</bean>
    
     
    	
    	<bean id="dataSourceChangeAspect" class="com.liu.core.datasource.DatasourceAspect">
    		<property name="defaultDatasource" value="dataSource1"></property>
    		<property name="datasourceMap">
    			<map key-type="java.lang.String">
    				
    				<entry key="com.liu.dao.jdbc.UserDaoImpl" value="dataSource1"></entry>
    				<entry key="com.liu.dao.jdbc.BookDaoImpl" value="dataSource2"></entry>
    			</map>
    		</property>
     
    	</bean>
    	
    	
    	
    	<aop:config>
    		<aop:aspect id="datasourceAspect" ref="dataSourceChangeAspect"
    			order="0">
    			<aop:pointcut id="daoAop" expression="execution(* com.liu.dao..*.*(..))" />
    			<aop:before pointcut-ref="daoAop" method="tabDataSource" />
    			<aop:after-returning pointcut-ref="daoAop"
    				method="doAfterReturning" />
    		</aop:aspect>
    	</aop:config>
    </beans>

### springmvc.xml配置,主要是注解开启，扫描包，视图层后缀配置（要不要无所谓） ###

## web.xml,spring容器,转发器，监听器等 ##

<?xml version="1.0" encoding="UTF-8"?>
    <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    	xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
    	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
    	id="WebApp_ID" version="3.0">
    	<display-name>datasourceDynamic</display-name>
    	<welcome-file-list>
    		<welcome-file>index.do</welcome-file>
    	</welcome-file-list>
    	
    	<context-param>
    		<param-name>contextConfigLocation</param-name>
    		<param-value>classpath:spring.xml</param-value>
    	</context-param>
    
    	
    	<listener>
    		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    	</listener>
    
    	
    	<servlet>
    		<servlet-name>springmvc</servlet-name>
    		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
    		<init-param>
    			<param-name>contextConfigLocation</param-name>
    			<param-value>classpath:spring-mvc.xml</param-value>
    		</init-param>
    	</servlet>
    	<servlet-mapping>
    		<servlet-name>springmvc</servlet-name>
    		<url-pattern>/</url-pattern>
    	</servlet-mapping>
    
    	
    	<filter>
    		<filter-name>CharacterEncodingFilter</filter-name>
    		<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
    	</filter>
    	<filter-mapping>
    		<filter-name>CharacterEncodingFilter</filter-name>
    		<url-pattern>/*</url-pattern>
    	</filter-mapping>
    
    	
    	<jsp-config>
    		<jsp-property-group>
    			<url-pattern>*.jsp</url-pattern>
    			<trim-directive-whitespaces>true</trim-directive-whitespaces>
    		</jsp-property-group>
    	</jsp-config>
    </web-app>

### 最后我放几个关键的类，其余dao，service，controller就不放，意义没有太大，理解原理就行了 ###

### DatasourceAspect动态数据源切面 ###

/**
     * 
     */
    package com.liu.core.datasource;
    
    import java.util.Map;
    
    import org.aspectj.lang.JoinPoint;
    import org.aspectj.lang.annotation.Aspect;
    import org.springframework.stereotype.Component;
    import org.springframework.transaction.support.TransactionTemplate;
    
     
    /**
     * 动态数据源切面
     * 
     * @author liuchaojun
     * @date 2018-12-12 上午10:42:11
     */
    @Component
    @Aspect
    public class DatasourceAspect {
    	private Map<String, String> datasourceMap;
    	private String defaultDatasource;
     
    
    	/*
    	 * 切入后执行的方法
    	 */
    	public void tabDataSource(JoinPoint joinPoint) {
    		boolean flag = false;
    		// 首先通过切点获得类名
    		String className = joinPoint.getTarget().getClass().getName();
    		System.out.println(className);
    		for (String datasourceKey : datasourceMap.keySet()) {
    			if (datasourceKey.equals(className)) {
    				// 初始化数据源
    				DatasourceTabUtils.setDatasourceName(datasourceMap
    						.get(datasourceKey));
    				System.out.println("切换数据源...."+datasourceMap
    						.get(datasourceKey));
    				flag = true;
    				break;
    			}
    		}
    		if (!flag) {
    			DatasourceTabUtils.setDatasourceName(defaultDatasource);
    		}
    	}
    
    	/*
    	 * 做完后的执行的方法 清除线程变量
    	 */
    	public void doAfterReturning(JoinPoint joinPoint) {
    		DatasourceTabUtils.clear();
    	}
    
    	/**
    	 * @return the datasourceMap
    	 */
    	public Map<String, String> getDatasourceMap() {
    		return datasourceMap;
    	}
    
    	/**
    	 * @param datasourceMap
    	 *            the datasourceMap to set
    	 */
    	public void setDatasourceMap(Map<String, String> datasourceMap) {
    		this.datasourceMap = datasourceMap;
    	}
    
    	/**
    	 * @return the defaultDatasource
    	 */
    	public String getDefaultDatasource() {
    		return defaultDatasource;
    	}
    
    	/**
    	 * @param defaultDatasource
    	 *            the defaultDatasource to set
    	 */
    	public void setDefaultDatasource(String defaultDatasource) {
    		this.defaultDatasource = defaultDatasource;
    	}
     
    }

### DatasourceTabUtils  数据源切换工具类  本地线程变量操作 ###

/**
     * 
     */
    package com.liu.core.datasource;
    
    /**
     * @author liuchaojun
     * @date 2018-12-12 上午10:43:47
     */
    public class DatasourceTabUtils {
    	private static ThreadLocal<String> threadLocal = new ThreadLocal<String>();
    
    	public static void setDatasourceName(String datasourceName) {
    		threadLocal.set(datasourceName);
    	}
    
    	public static Object getDatasourceName() {
    		return threadLocal.get();
    	}
    
    	public static void clear() {
    		threadLocal.remove();
    	}
    }

### DynamicDatasource  动态数据源路由类   这个类必须实现AbstractRoutingDataSource覆盖determineCurrentLookupKey的方法 ###

### AbstractRoutingDataSource是Spring2.0后增加的。 ###

### 这个其实也是数据源切换的关键类，我们看下源码，简单了解下 ###

/*jadclipse*/// Decompiled by Jad v1.5.8g. Copyright 2001 Pavel Kouznetsov.
    // Jad home page: http://www.kpdus.com/jad.html
    // Decompiler options: packimports(3) radix(10) lradix(10) 
    // Source File Name:   AbstractRoutingDataSource.java
    
    package org.springframework.jdbc.datasource.lookup;
    
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.*;
    import javax.sql.DataSource;
    import org.springframework.beans.factory.InitializingBean;
    import org.springframework.jdbc.datasource.AbstractDataSource;
    import org.springframework.util.Assert;
    
    // Referenced classes of package org.springframework.jdbc.datasource.lookup:
    //            JndiDataSourceLookup, DataSourceLookup
    
    public abstract class AbstractRoutingDataSource extends AbstractDataSource
        implements InitializingBean
    {
    
        public AbstractRoutingDataSource()
        {
            lenientFallback = true;
            dataSourceLookup = new JndiDataSourceLookup();
        }
    
        public void setTargetDataSources(Map targetDataSources)
        {
            this.targetDataSources = targetDataSources;
        }
    
        public void setDefaultTargetDataSource(Object defaultTargetDataSource)
        {
            this.defaultTargetDataSource = defaultTargetDataSource;
        }
    
        public void setLenientFallback(boolean lenientFallback)
        {
            this.lenientFallback = lenientFallback;
        }
    
        public void setDataSourceLookup(DataSourceLookup dataSourceLookup)
        {
            this.dataSourceLookup = ((DataSourceLookup) (dataSourceLookup == null ? ((DataSourceLookup) (new JndiDataSourceLookup())) : dataSourceLookup));
        }
    
        public void afterPropertiesSet()
        {
            if(targetDataSources == null)
                throw new IllegalArgumentException("Property 'targetDataSources' is required");
            resolvedDataSources = new HashMap(targetDataSources.size());
            Object lookupKey;
            DataSource dataSource;
            for(Iterator iterator = targetDataSources.entrySet().iterator(); iterator.hasNext(); resolvedDataSources.put(lookupKey, dataSource))
            {
                java.util.Map.Entry entry = (java.util.Map.Entry)iterator.next();
                lookupKey = resolveSpecifiedLookupKey(entry.getKey());
                dataSource = resolveSpecifiedDataSource(entry.getValue());
            }
    
            if(defaultTargetDataSource != null)
                resolvedDefaultDataSource = resolveSpecifiedDataSource(defaultTargetDataSource);
        }
    
        protected Object resolveSpecifiedLookupKey(Object lookupKey)
        {
            return lookupKey;
        }
    
        protected DataSource resolveSpecifiedDataSource(Object dataSource)
            throws IllegalArgumentException
        {
            if(dataSource instanceof DataSource)
                return (DataSource)dataSource;
            if(dataSource instanceof String)
                return dataSourceLookup.getDataSource((String)dataSource);
            else
                throw new IllegalArgumentException((new StringBuilder()).append("Illegal data source value - only [javax.sql.DataSource] and String supported: ").append(dataSource).toString());
        }
    
        public Connection getConnection()
            throws SQLException
        {
            return determineTargetDataSource().getConnection();
        }
    
        public Connection getConnection(String username, String password)
            throws SQLException
        {
            return determineTargetDataSource().getConnection(username, password);
        }
    
        public Object unwrap(Class iface)
            throws SQLException
        {
            if(iface.isInstance(this))
                return this;
            else
                return determineTargetDataSource().unwrap(iface);
        }
    
        public boolean isWrapperFor(Class iface)
            throws SQLException
        {
            return iface.isInstance(this) || determineTargetDataSource().isWrapperFor(iface);
        }
    
        protected DataSource determineTargetDataSource()
        {
            Assert.notNull(resolvedDataSources, "DataSource router not initialized");
            Object lookupKey = determineCurrentLookupKey();
            DataSource dataSource = (DataSource)resolvedDataSources.get(lookupKey);
            if(dataSource == null && (lenientFallback || lookupKey == null))
                dataSource = resolvedDefaultDataSource;
            if(dataSource == null)
                throw new IllegalStateException((new StringBuilder()).append("Cannot determine target DataSource for lookup key [").append(lookupKey).append("]").toString());
            else
                return dataSource;
        }
    
        protected abstract Object determineCurrentLookupKey();
    
        private Map targetDataSources;
        private Object defaultTargetDataSource;
        private boolean lenientFallback;
        private DataSourceLookup dataSourceLookup;
        private Map resolvedDataSources;
        private DataSource resolvedDefaultDataSource;
    }
    
    
    /*
    	DECOMPILATION REPORT
    
    	Decompiled from: F:\mavenJarBaoLcj\.m2\org\springframework\spring-jdbc\4.2.6.RELEASE\spring-jdbc-4.2.6.RELEASE.jar
    	Total time: 62 ms
    	Jad reported messages/errors:
    	Exit status: 0
    	Caught exceptions:
    */

### 我们直接关注determineTargetDataSource方法的determineCurrentLookupKey的方法，我们通过集成这个抽象类实现这方法最后返回的是数据源编号，也就相当于数据源的key，spring会根据key来路由底层数据源，而我们只需要提供具体切换的是哪一个，我们刚好从本地线程里面去设置和拿取返回给这个determineCurrentLookupKey的方法。 ###

### 其实就是相当于，把数据源数据源动态全部加载到程序中，通过aop灵活的进行数据源切换，实现了读写分离，缺点无法动态的增加数据源。 ###

## 最后也说明一下，这个做法只支持单事物，也就是默认配置的事物，并且事物和数据源切换初始化，数据源一定要在前面，事物在后面一点，设置order越小越在前面加载。  ##

## 如果需要跨库事物的切换或者分布式二阶段事物，由于内容过多，我们在下一遍文章分开进行讲解，事物也要根据具体项目业务哪种合适进行取舍。 ##

## 本项目源码：链接：https://pan.baidu.com/s/1d4WBCnvheKK64MBHhUUdaw  提取码：ddlv      ##

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3MDI2NjAz_size_16_color_FFFFFF_t_70]: /images/20220405/b50c1048aa5f41eebf843f40424a772a.png
[faddfad8dbf51d88923848b21f9d5271.png]: /images/20220405/3e9be7843e004e89a2d7d96cd0705ce1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3MDI2NjAz_size_16_color_FFFFFF_t_70 1]: /images/20220405/0d73106f98e6437fb13d072a7b78f126.png
[20181213102350760.png]: https://img-blog.csdnimg.cn/20181213102350760.png
[20181213102400667.png]: https://img-blog.csdnimg.cn/20181213102400667.png
[2018121310240912.png]: /images/20220405/5e91704fc1b1466aa8bd76f586cbe8d7.png