首先,要明白为什么要使用多数据库?

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

项目结构图

数据库



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>
<!-- springframework 4 dependencies begin -->
<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>
<!-- springframework 4 dependencies end -->
<!-- hibernate 配置 begin -->
<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>
<!-- hibernate 配置 end -->
<!-- mysql数据库的驱动包 start -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<!-- 引入jstl包 -->
<dependency>
<groupId>javax.servlet.jsp.jstl</groupId>
<artifactId>jstl-api</artifactId>
<version>1.2</version>
</dependency>
<!-- 引入servlet -->
<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">
<!-- 数据源,BasicDataSource,commons-dbcp -->
<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>
<!-- Hibernate SesssionFactory -->
<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">
<!-- 通过不同的key决定用哪个dataSource -->
<entry key="com.liu.dao.jdbc.UserDaoImpl" value="dataSource1"></entry>
<entry key="com.liu.dao.jdbc.BookDaoImpl" value="dataSource2"></entry>
</map>
</property>
</bean>
<!--在执行事务前必须切换好事据源,注意执行顺序 order越小执行越早 -->
<!-- 切面aop -->
<!-- 1、execution(): 表达式主体。 2、第一个*号:表示返回类型,*号表示所有的类型。 3、包名:表示需要拦截的包名,后面的两个句点表示当前包和当前包的所有子包,com.sample.service.impl包、子孙包下所有类的方法。
4、第二个*号:表示类名,*号表示所有的类。 5、*(..):最后这个星号表示方法名,*号表示所有的方法,后面括弧里面表示方法的参数,两个句点表示任何参数。 -->
<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配置,主要是注解开启,扫描包,视图层后缀配置(要不要无所谓)
<?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">
<aop:aspectj-autoproxy />
<!-- 注解支持 -->
<context:annotation-config />
<!-- 定义扫描根路径为com,不使用默认的扫描方式 -->
<context:component-scan base-package="com"></context:component-scan>
<!-- 激活基于注解的配置 @RequestMapping, @ExceptionHandler,数据绑定 ,@NumberFormat ,
@DateTimeFormat ,@Controller ,@Valid ,@RequestBody ,@ResponseBody等 -->
<mvc:annotation-driven />
<!-- 视图层配置 -->
<bean
class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<!--配置JSTL表达式 -->
<property name="viewClass"
value="org.springframework.web.servlet.view.JstlView" />
<!-- 前缀 -->
<property name="prefix" value="/WEB-INF/jsp/" />
<!-- 后缀 -->
<property name="suffix" value=".jsp" />
</bean>
</beans>
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>
<!-- 配置spring容器 -->
<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>
<!-- 请求都交给DispatcherServlet处理 -->
<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空格 -->
<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