Spring MVC +Spring + Mybatis 构建分库分表总结 SSM搭建以及分库分表

最新推荐文章于 2020-03-09 21:18:25 发布

weixin_34307464

最新推荐文章于 2020-03-09 21:18:25 发布

阅读量169

点赞数

原文链接：https://my.oschina.net/u/2335525/blog/1921510

版权

2019独角兽企业重金招聘Python工程师标准>>>

分库分表在小型公司很少能遇到也很少使用,毕竟数据量没有那么大,当数据量大,所有数据都压在一张表时,如果单从数据库的角度考虑是可以分库分表处理来存储数据。分库分表顾名思义就是根据查询条件动态的去获取数据所在的库和表的位置.例如一个系统有唯一的标识userNum，所有路由规则都可以根据userNum做库表的定位工作.本文测试用3个库5个表做测试.

三个库book_00，book_01,book_02  每个库里的表 t_user_0000,t_user_0001,t_user_0002,t_user_0003,t_user_0004,
根据userNum寻找匹配库的_00后缀，匹配表的_0000 后缀 即可定位到是几库几表.

本人会建立一个新的项目,一步一步讲解如果简单的搭建一个分库分表的系统并且用页面展示从库里查询出来的数据.

1,首先需要创建一个新的maven的webApp项目

使用框架版本：

Spring 4.0.2 RELEASE

Spring MVC 4.0.2 RELEASE

MyBatis 3.2.6

2，pom里引入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/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.subdt</groupId>
<artifactId>subDbTable</artifactId>
<packaging>war</packaging>
<version>1.0-SNAPSHOT</version>
<name>subDbTable Maven Webapp</name>
<url>http://maven.apache.org</url>
<properties>

<spring.version>4.0.2.RELEASE</spring.version>

<mybatis.version>3.2.6</mybatis.version>

<slf4j.version>1.7.7</slf4j.version>
<log4j.version>1.2.17</log4j.version>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>

<scope>test</scope>
</dependency>

<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</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-oxm</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</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-aop</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context-support</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.8.9</version>
</dependency>
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<version>1.8.3</version>
</dependency>
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>2.6</version>
</dependency>

<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>${mybatis.version}</version>
</dependency>

<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>

<dependency>
<groupId>javax</groupId>
<artifactId>javaee-api</artifactId>
<version>7.0</version>
</dependency>

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.30</version>
</dependency>

<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.2.2</version>
</dependency>

<dependency>
<groupId>jstl</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>


<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>${log4j.version}</version>
</dependency>

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.1.41</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>${slf4j.version}</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>${slf4j.version}</version>
</dependency>


<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-mapper-asl</artifactId>
<version>1.9.13</version>
</dependency>

<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<dependency>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
<version>1.9</version>
</dependency>
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>1.7.1</version>
</dependency>
</dependencies>
<build>
<finalName>subDbTable</finalName>
<resources>

<resource>
<directory>src/main/resources</directory>
<filtering>true</filtering>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-resources-plugin</artifactId>
<configuration>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
</plugins>
</build>
</project>

首先看下目录结构

3，Spring与MyBatis的整合

首先创建 jdbc.properties 配置数据库信息

driver=com.mysql.jdbc.Driver
#定义初始连接数
initialSize=0
#定义最大连接数
maxActive=20
#定义最大空闲
maxIdle=20
#定义最小空闲
minIdle=1
#定义最长等待时间
maxWait=60000
jdbc.mysql.url0=jdbc:mysql://localhost:3306/book_02?createDatabaseIfNotExist=true&characterEncoding=utf-8&useUnicode=true
jdbc.mysql.username0=root
jdbc.mysql.password0=123456
jdbc.mysql.url1=jdbc:mysql://localhost:3306/book_00?createDatabaseIfNotExist=true&characterEncoding=utf-8&useUnicode=true
jdbc.mysql.username1=root
jdbc.mysql.password1=123456
jdbc.mysql.url2=jdbc:mysql://localhost:3306/book_01?createDatabaseIfNotExist=true&characterEncoding=utf-8&useUnicode=true
jdbc.mysql.username2=root
jdbc.mysql.password2=123456

创建Spring配置文件 spring-config.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:context="http://www.springframework.org/schema/context"
xmlns:aop="http://www.springframework.org/schema/aop"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-3.0.xsd"
default-autowire="byName">
<context:component-scan base-package="com.sub.dt"/>
<aop:aspectj-autoproxy/>

<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<list>
<value>classpath:props/*.properties</value>
</list>
</property>
</bean>
<import resource="classpath:spring/spring-*.xml"/>
</beans>

创建spring与mybatis结合的整合配置文件. spring-config-datasource-dbcp.xml

这个文件主要是多数据源配置,自动扫描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"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

<bean id="propertyConfigurer"
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="classpath:props/jdbc.properties"/>
</bean>
<bean id="dataSource0" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${driver}"/>
<property name="url" value="${jdbc.mysql.url0}"/>
<property name="username" value="${jdbc.mysql.username0}"/>
<property name="password" value="${jdbc.mysql.password0}"/>

<property name="initialSize" value="${initialSize}"></property>

<property name="maxActive" value="${maxActive}"></property>

<property name="maxIdle" value="${maxIdle}"></property>

<property name="minIdle" value="${minIdle}"></property>

<property name="maxWait" value="${maxWait}"></property>
</bean>
<bean id="dataSource1" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${driver}"/>
<property name="url" value="${jdbc.mysql.url1}"/>
<property name="username" value="${jdbc.mysql.username1}"/>
<property name="password" value="${jdbc.mysql.password1}"/>

<property name="initialSize" value="${initialSize}"></property>

<property name="maxActive" value="${maxActive}"></property>

<property name="maxIdle" value="${maxIdle}"></property>

<property name="minIdle" value="${minIdle}"></property>

<property name="maxWait" value="${maxWait}"></property>
</bean>
<bean id="dataSource2" class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${driver}"/>
<property name="url" value="${jdbc.mysql.url2}"/>
<property name="username" value="${jdbc.mysql.username2}"/>
<property name="password" value="${jdbc.mysql.password2}"/>

<property name="initialSize" value="${initialSize}"></property>

<property name="maxActive" value="${maxActive}"></property>

<property name="maxIdle" value="${maxIdle}"></property>

<property name="minIdle" value="${minIdle}"></property>

<property name="maxWait" value="${maxWait}"></property>
</bean>
<bean id="mysqlDynamicDataSource" class="com.sub.dt.dbRouting.db.DynamicDataSource">
<property name="targetDataSources">

<map>
<entry key="db0" value-ref="dataSource0"/>
<entry key="db1" value-ref="dataSource1"/>
<entry key="db2" value-ref="dataSource2"/>
</map>
</property>
</bean>
<bean id="dbRuleSet" class="com.sub.dt.dbRouting.bean.RouterSet">
<property name="routeFieldStart" value="0"></property>
<property name="routeFieldEnd" value="9200000000000000000"></property>
<property name="dbNumber" value="3"></property>
<property name="routeType" value="2"></property>
<property name="ruleType" value="3"></property>
<property name="tableNumber" value="5"></property>
<property name="dbKeyArray">
<list>
<value>db0</value>
<value>db1</value>
<value>db2</value>
</list>
</property>
</bean>
<bean id="dBRouter" class="com.sub.dt.dbRouting.router.DBRouterImpl">
<property name="routerSetList">

<list>
<ref bean="dbRuleSet"/>
</list>
</property>
</bean>

<bean id="baiTiaoTransactionManager"
class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="mysqlDynamicDataSource"></property>
</bean>
<bean id="btTransactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
<property name="transactionManager" ref="baiTiaoTransactionManager"></property>
<property name="propagationBehaviorName" value="PROPAGATION_REQUIRED"></property>
</bean>

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="mysqlDynamicDataSource"/>

<property name="mapperLocations" value="classpath:com/sub/dt/mapping/*.xml"></property>
</bean>

<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.sub.dt.dao"/>
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
</bean>
</beans>

程序使用spring aop切面+自定义注解的方式去在每个方法调用时根据userNum定位库表, 关键类已标红(代码会上传附件自行下载查看)

spring-config-datasource-dbcp.xml中配置的com.sub.dt.dbRouting.db.DynamicDataSource 继承 AbstractRoutingDataSource方法,单表配置mybatis是使用的是BasicDataSource

BasicDataSourBasicDataSource

BasicDataSource 自定义的AbstractRoutingDataSource与spring的BasicDataSource都是一个道理管理数据源实现了DataSource接口.

package com.sub.dt.dbRouting.db;
import com.sub.dt.dbRouting.DbContextHolder;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import java.util.logging.Logger;
/**
* @Description SPring 的动态数据源的实现
* @Autohr supers【weChat:13031016567】
*/
public class DynamicDataSource extends AbstractRoutingDataSource {
public static final Logger logger = Logger.getLogger(DynamicDataSource.class.toString());
@Override
protected Object determineCurrentLookupKey() {
return DbContextHolder.getDbKey();//获取当前数据源
}
}

UserMapper.xml创建库表对应的xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.sub.dt.dao.IUserDao">
<resultMap id="BaseResultMap" type="com.sub.dt.pojo.User">
<id column="id" property="id" jdbcType="INTEGER"/>
<result column="user_name" property="userName" jdbcType="VARCHAR"/>
<result column="password" property="password" jdbcType="VARCHAR"/>
<result column="age" property="age" jdbcType="INTEGER"/>
<result column="user_num" property="userNum" jdbcType="VARCHAR"/>
</resultMap>
<sql id="Base_Column_List">
id,user_num, user_name, password, age
</sql>
<insert id="insertUser" parameterType="com.sub.dt.pojo.User">
insert into t_user${tableIndex} (id,user_num,user_name, password,age)
values (#{id,jdbcType=INTEGER},#{userNum,jdbcType=VARCHAR}, #{userName,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR},#{age,jdbcType=INTEGER})
</insert>
<insert id="deleteByuserNum" parameterType="com.sub.dt.pojo.User">
delete from t_user${tableIndex}
where user_num = #{userNum,jdbcType=VARCHAR}
</insert>
<update id="updateByUserNum" parameterType="com.sub.dt.pojo.User">
update t_user${tableIndex}
<set>
<if test="userName != null">
user_name = #{userName,jdbcType=VARCHAR},
</if>
<if test="password != null">
password = #{password,jdbcType=VARCHAR},
</if>
<if test="age != null">
age = #{age,jdbcType=INTEGER},
</if>
</set>
where user_num = #{userNum,jdbcType=VARCHAR}
</update>
<select id="selectByUserNum" resultMap="BaseResultMap" parameterType="com.sub.dt.pojo.User">
select
<include refid="Base_Column_List"/>
from t_user${tableIndex}
where user_num = #{userNum,jdbcType=VARCHAR}
</select>
</mapper>

IUserDao.java

package com.sub.dt.dao;
import com.sub.dt.pojo.User;
public interface IUserDao {
/**
* @Description
* @Autohr supers【weChat:13031016567】
*/
int insertUser(User user);
/**
* @Description
* @Autohr supers【weChat:13031016567】
*/
int deleteByuserNum(User user);
/**
* @Description
* @Autohr supers【weChat:13031016567】
*/
int updateByUserNum(User user);
/**
* @Description
* @Autohr supers【weChat:13031016567】
*/
User selectByUserNum(User user);
}

IUserService.java

package com.sub.dt.service;
import com.sub.dt.pojo.User;
/**
* @Description
* @Autohr supers【weChat:13031016567】
*/
public interface IUserService {
/**
* @Description
* @Autohr supers【weChat:13031016567】
*/
public int insertUser(User user);
/**
* @Description
* @Autohr supers【weChat:13031016567】
*/
public int deleteByuserNum(User user);
/**
* @Description
* @Autohr supers【weChat:13031016567】
*/
public int updateByUserNum(User user);
/**
* @Description
* @Autohr supers【weChat:13031016567】
*/
public User selectByUserNum(User user);
}

UserServiceImpl.java

package com.sub.dt.service.impl;
import com.sub.dt.dao.IUserDao;
import com.sub.dt.dbRouting.annotation.Router;
import com.sub.dt.pojo.User;
import com.sub.dt.service.IUserService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
/**
* @Description
* @Autohr supers【weChat:13031016567】
*/
@Service("userService")
public class UserServiceImpl implements IUserService {
@Resource
private IUserDao userDao;
@Router
public int insertUser(User user) {
return this.userDao.insertUser(user);
}
@Router
public int deleteByuserNum(User user) {
return this.userDao.deleteByuserNum(user);
}
@Router
public int updateByUserNum(User user) {
return this.userDao.updateByUserNum(user);
}
@Router
public User selectByUserNum(User user) {
return this.userDao.selectByUserNum(user);
}
}

UserController.java

package com.sub.dt.controller;
import com.sub.dt.pojo.User;
import com.sub.dt.service.IUserService;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
@Controller
@RequestMapping("/user")
public class UserController {
@Resource
private IUserService userService;
@RequestMapping("/queryUser")
public String toIndex(HttpServletRequest request,Model model,User user){
User userDb = this.userService.selectByUserNum(user);
model.addAttribute("user", userDb);
return "queryUser";
}
}

4，核心分库分表包都在dbRouting文件夹下

Router.java  自定义注解,此注解作用是当做一个切点,在方法上添加此注解,执行方法前会执行DBRouterInterceptor的doRoute方法.详情可参考:http://blog.csdn.net/buchengbugui/article/details/60875401

package com.sub.dt.dbRouting.annotation;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* @Description
* @Autohr supers【weChat:13031016567】
*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface Router {
String routerField() default RouterConstants.ROUTER_FIELD_DEFAULT;
String tableStyle() default RouterConstants.ROUTER_TABLE_SUFFIX_DEFAULT;
}

DBRouterInterceptor.java 定义切点为方法上使用,@Router注解的方法,执行注解方法前前执行doRoute方法,根据参数中的userNum设置是几库几表

package com.sub.dt.dbRouting;
import com.sub.dt.dbRouting.annotation.Router;
import com.sub.dt.dbRouting.annotation.RouterConstants;
import com.sub.dt.dbRouting.router.RouterUtils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.StringUtils;
import org.aspectj.lang.JoinPoint;
import org.aspectj.lang.Signature;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
/**
* @Description 切面切点在Router注解的方法执行前执行切点织入
* @Autohr supers【weChat:13031016567】
*/
@Aspect
@Component
public class DBRouterInterceptor {
private static final Logger log = LoggerFactory.getLogger(DBRouterInterceptor.class);
private DBRouter dBRouter;
@Pointcut("@annotation( com.sub.dt.dbRouting.annotation.Router)")
public void aopPoint() {
}
@Before("aopPoint()")
public Object doRoute(JoinPoint jp) throws Throwable {
long t1 = System.currentTimeMillis();
boolean result = true;
Method method = getMethod(jp);
Router router = method.getAnnotation(Router.class);
String routeField = router.routerField();
Object[] args = jp.getArgs();
if (args != null && args.length > 0) {
for (int i = 0; i < args.length; i++) {
long t2 = System.currentTimeMillis();
String routeFieldValue = BeanUtils.getProperty(args[i],
routeField);
log.debug("routeFieldValue{}" + (System.currentTimeMillis() - t2));
if (StringUtils.isNotEmpty(routeFieldValue)) {
if (RouterConstants.ROUTER_FIELD_DEFAULT.equals(routeField)) {
dBRouter.doRouteByResource("" + RouterUtils.getResourceCode(routeFieldValue));
break;
} else {
this.searchParamCheck(routeFieldValue);
String resource = routeFieldValue.substring(routeFieldValue.length() - 4);
dBRouter.doRouteByResource(resource);
break;
}
}
}
}
log.debug("doRouteTime{}" + (System.currentTimeMillis() - t1));
return result;
}
private Method getMethod(JoinPoint jp) throws NoSuchMethodException {
Signature sig = jp.getSignature();
MethodSignature msig = (MethodSignature) sig;
return getClass(jp).getMethod(msig.getName(), msig.getParameterTypes());
}
private Class<? extends Object> getClass(JoinPoint jp)
throws NoSuchMethodException {
return jp.getTarget().getClass();
}
/**
* 查询支付结构参数检查
*
* @param payId
*/
private void searchParamCheck(String payId) {
if (payId.trim().equals("")) {
throw new IllegalArgumentException("payId is empty");
}
}
public DBRouter getdBRouter() {
return dBRouter;
}
public void setdBRouter(DBRouter dBRouter) {
this.dBRouter = dBRouter;
}
}

RouterSet.java  主要是 配置在spring-config-datasource-dbcp.xml中 存储库的数量,表的数量,库表相关信息

package com.sub.dt.dbRouting.bean;
import java.util.List;
/**
* @Description
* @Autohr supers【weChat:13031016567】
*/
public class RouterSet {
/**根据字符串*/
public final static int RULE_TYPE_STR=3;
public final static int ROUTER_TYPE_DB=0;
public final static int ROUTER_TYPE_TABLE =1;
public final static int ROUTER_TYPE_DBANDTABLE=2;
/**数据库表的逻辑KEY,与数据源MAP配置中的key一致*/
private List<String> dbKeyArray;
/**数据库数量*/
private int dbNumber;
/**数据表数量*/
private int tableNumber;
/**数据表index样式*/
private String tableIndexStyle;
/**Id开始*/
private String routeFieldStart;
/**Id结束*/
private String routeFieldEnd;
/**规则类型*/
private int ruleType;
/**路由类型类型*/
private int routeType;
public static int getRULE_TYPE_STR() {
return RULE_TYPE_STR;
}
public static int getROUTER_TYPE_DB() {
return ROUTER_TYPE_DB;
}
public static int getROUTER_TYPE_TABLE() {
return ROUTER_TYPE_TABLE;
}
public static int getROUTER_TYPE_DBANDTABLE() {
return ROUTER_TYPE_DBANDTABLE;
}
public List<String> getDbKeyArray() {
return dbKeyArray;
}
public void setDbKeyArray(List<String> dbKeyArray) {
this.dbKeyArray = dbKeyArray;
}
public int getDbNumber() {
return dbNumber;
}
public void setDbNumber(int dbNumber) {
this.dbNumber = dbNumber;
}
public int getTableNumber() {
return tableNumber;
}
public void setTableNumber(int tableNumber) {
this.tableNumber = tableNumber;
}
public String getTableIndexStyle() {
return tableIndexStyle;
}
public void setTableIndexStyle(String tableIndexStyle) {
this.tableIndexStyle = tableIndexStyle;
}
public String getRouteFieldStart() {
return routeFieldStart;
}
public void setRouteFieldStart(String routeFieldStart) {
this.routeFieldStart = routeFieldStart;
}
public String getRouteFieldEnd() {
return routeFieldEnd;
}
public void setRouteFieldEnd(String routeFieldEnd) {
this.routeFieldEnd = routeFieldEnd;
}
public int getRuleType() {
return ruleType;
}
public void setRuleType(int ruleType) {
this.ruleType = ruleType;
}
public int getRouteType() {
return routeType;
}
public void setRouteType(int routeType) {
this.routeType = routeType;
}
}

DBRouterImpl.java  getDbKey方法根据userNum定位库表算法

package com.sub.dt.dbRouting.router;
import com.sub.dt.dbRouting.DBRouter;
import com.sub.dt.dbRouting.DbContextHolder;
import com.sub.dt.dbRouting.annotation.RouterConstants;
import com.sub.dt.dbRouting.bean.RouterSet;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.text.DecimalFormat;
import java.util.List;
/**
* @Description 根据指定变量动态切库和表
* @Autohr supers【weChat:13031016567】
*/
public class DBRouterImpl implements DBRouter {
private static final Logger log = LoggerFactory.getLogger(DBRouterImpl.class);
/**
* 配置列表
*/
private List<RouterSet> routerSetList;
@Override
public String doRoute(String fieldId) {
if (StringUtils.isEmpty(fieldId)) {
throw new IllegalArgumentException("dbsCount and tablesCount must be both positive!");
}
int routeFieldInt = RouterUtils.getResourceCode(fieldId);
String dbKey = getDbKey(routerSetList, routeFieldInt);
return dbKey;
}
@Override
public String doRouteByResource(String resourceCode) {
if (StringUtils.isEmpty(resourceCode)) {
throw new IllegalArgumentException("dbsCount and tablesCount must be both positive!");
}
int routeFieldInt = Integer.valueOf(resourceCode);
String dbKey = getDbKey(routerSetList, routeFieldInt);
return dbKey;
}
/**
* @Description 根据数据字段来判断属于哪个段的规则,获得数据库key
* @Autohr supers【weChat:13031016567】
*/
private String getDbKey(List<RouterSet> routerSets, int routeFieldInt) {
RouterSet routerSet = null;
if (routerSets == null || routerSets.size() <= 0) {
throw new IllegalArgumentException("dbsCount and tablesCount must be both positive!");
}
String dbKey = null;
for (RouterSet item : routerSets) {
if (item.getRuleType() == routerSet.RULE_TYPE_STR) {
routerSet = item;
if (routerSet.getDbKeyArray() != null && routerSet.getDbNumber() != 0) {
long dbIndex = 0;
long tbIndex = 0;
//默认按照分库进行计算
long mode = routerSet.getDbNumber();
//如果是按照分库分表的话，计算
if (item.getRouteType() == RouterSet.ROUTER_TYPE_DBANDTABLE && item.getTableNumber() != 0) {
mode = routerSet.getDbNumber() * item.getTableNumber();
dbIndex = routeFieldInt % mode / item.getTableNumber();
tbIndex = routeFieldInt % item.getTableNumber();
String tableIndex = getFormateTableIndex(item.getTableIndexStyle(), tbIndex);
DbContextHolder.setTableIndex(tableIndex);
} else if (item.getRouteType() == RouterSet.ROUTER_TYPE_DB) {
mode = routerSet.getDbNumber();
dbIndex = routeFieldInt % mode;
} else if (item.getRouteType() == RouterSet.ROUTER_TYPE_TABLE) {
tbIndex = routeFieldInt % item.getTableNumber();
String tableIndex = getFormateTableIndex(item.getTableIndexStyle(), tbIndex);
DbContextHolder.setTableIndex(tableIndex);
}
dbKey = routerSet.getDbKeyArray().get(Long.valueOf(dbIndex).intValue());
log.debug("getDbKey resource:{}------->dbkey:{},tableIndex:{},", new Object[]{routeFieldInt, dbKey, tbIndex});
DbContextHolder.setDbKey(dbKey);
}
break;
}
}
return dbKey;
}
/**
* @Description 此方法是将例如+++0000根式的字符串替换成传参数字例如44 变成+++0044
* @Autohr supers【weChat:13031016567】
*/
private static String getFormateTableIndex(String style, long tbIndex) {
String tableIndex = null;
DecimalFormat df = new DecimalFormat();
if (StringUtils.isEmpty(style)) {
style = RouterConstants.ROUTER_TABLE_SUFFIX_DEFAULT;//在格式后添加诸如单位等字符
}
df.applyPattern(style);
tableIndex = df.format(tbIndex);
return tableIndex;
}
public List<RouterSet> getRouterSetList() {
return routerSetList;
}
public void setRouterSetList(List<RouterSet> routerSetList) {
this.routerSetList = routerSetList;
}
}

spring-mvc.xml这个很简单了就是前段与后端controller层的结合,不多说.

<?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:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.1.xsd">

<context:component-scan base-package="com.sub.dt.controller"/>

<bean id="mappingJacksonHttpMessageConverter"
class="org.springframework.http.converter.json.MappingJacksonHttpMessageConverter">
<property name="supportedMediaTypes">
<list>
<value>text/html;charset=UTF-8</value>
</list>
</property>
</bean>

<bean
class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter">
<property name="messageConverters">
<list>
<ref bean="mappingJacksonHttpMessageConverter"/>

</list>
</property>
</bean>

<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">

<property name="prefix" value="/WEB-INF/jsp/"/>
<property name="suffix" value=".jsp"/>
</bean>

<bean id="multipartResolver"
class="org.springframework.web.multipart.commons.CommonsMultipartResolver">

<property name="defaultEncoding" value="utf-8"/>

<property name="maxUploadSize" value="10485760000"/>

<property name="maxInMemorySize" value="40960"/>
</bean>
</beans>

web.xml配置

<?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"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
version="3.0">
<display-name>Archetype Created Web Application</display-name>

<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:spring-config.xml</param-value>
</context-param>

<filter>
<filter-name>encodingFilter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
<async-supported>true</async-supported>
<init-param>
<param-name>encoding</param-name>
<param-value>UTF-8</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>encodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>

<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>

<listener>
<listener-class>org.springframework.web.util.IntrospectorCleanupListener</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>
<load-on-startup>1</load-on-startup>
<async-supported>true</async-supported>
</servlet>
<servlet-mapping>
<servlet-name>SpringMVC</servlet-name>

<url-pattern>/</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>/index.jsp</welcome-file>
</welcome-file-list>
</web-app>

奉上 mysql创建表的sql语句,首先自行创建三个库book_00，book_01,book_02 每个库都执行以下sql即可

/*
Navicat MySQL Data Transfer
Source Server : local
Source Server Version : 50173
Source Host : localhost:3306
Source Database : book_00
Target Server Type : MYSQL
Target Server Version : 50173
File Encoding : 65001
Date: 2017-03-10 09:48:12
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `t_user_0000`
-- ----------------------------
DROP TABLE IF EXISTS `t_user_0000`;
CREATE TABLE `t_user_0000` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_num` varchar(64) NOT NULL,
`user_name` varchar(16) NOT NULL,
`password` varchar(64) NOT NULL,
`age` int(4) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_user_num` (`user_num`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_user_0000
-- ----------------------------
-- ----------------------------
-- Table structure for `t_user_0001`
-- ----------------------------
DROP TABLE IF EXISTS `t_user_0001`;
CREATE TABLE `t_user_0001` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_num` varchar(64) NOT NULL,
`user_name` varchar(16) NOT NULL,
`password` varchar(64) NOT NULL,
`age` int(4) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_user_num` (`user_num`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_user_0001
-- ----------------------------
-- ----------------------------
-- Table structure for `t_user_0002`
-- ----------------------------
DROP TABLE IF EXISTS `t_user_0002`;
CREATE TABLE `t_user_0002` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_num` varchar(64) NOT NULL,
`user_name` varchar(16) NOT NULL,
`password` varchar(64) NOT NULL,
`age` int(4) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_user_num` (`user_num`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_user_0002
-- ----------------------------
-- ----------------------------
-- Table structure for `t_user_0003`
-- ----------------------------
DROP TABLE IF EXISTS `t_user_0003`;
CREATE TABLE `t_user_0003` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_num` varchar(64) NOT NULL,
`user_name` varchar(16) NOT NULL,
`password` varchar(64) NOT NULL,
`age` int(4) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_user_num` (`user_num`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_user_0003
-- ----------------------------
-- ----------------------------
-- Table structure for `t_user_0004`
-- ----------------------------
DROP TABLE IF EXISTS `t_user_0004`;
CREATE TABLE `t_user_0004` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_num` varchar(64) NOT NULL,
`user_name` varchar(16) NOT NULL,
`password` varchar(64) NOT NULL,
`age` int(4) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_user_num` (`user_num`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_user_0004

所有代码创建完毕

测试类 TestUserService.java

package com.sub.dt.service;
import com.sub.common.GsonUtils;
import com.sub.dt.dbRouting.DbContextHolder;
import com.sub.dt.pojo.User;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
/**
* Created by supers on 2017/3/9.
*/
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("/spring-config.xml")
public class TestUserService {
@Autowired
private IUserService userService;
/**
* @Description 测试分库分表插入
* @Autohr supers【weChat:13031016567】
*/
@Test
public void testInsertUser(){
User user = new User();
user.setUserNum("wergsgdf3243");
user.setUserName("admin");
user.setAge(23);
user.setPassword("adf23");
int re = userService.insertUser(user);
System.out.println(DbContextHolder.getDbKey()+"库 "+DbContextHolder.getTableIndex()+"表的插入结果:"+GsonUtils.toJson(re));
}
/**
* @Description 测试分库分表删除
* @Autohr supers【weChat:13031016567】
*/
@Test
public void testDeleteByuserNum(){
User user = new User();
user.setUserNum("wergsgdf3243");
int re = userService.deleteByuserNum(user);
System.out.println(DbContextHolder.getDbKey()+"库 "+DbContextHolder.getTableIndex()+"表的删除结果:"+GsonUtils.toJson(re));
}
/**
* @Description 测试分库分表修改
* @Autohr supers【weChat:13031016567】
*/
@Test
public void testupdateByUserNum(){
User user = new User();
user.setUserNum("wergsgdf3243");
user.setAge(34);
int re = userService.updateByUserNum(user);
System.out.println(DbContextHolder.getDbKey()+"库 "+DbContextHolder.getTableIndex()+"表的更新结果:"+GsonUtils.toJson(re));
}
/**
* @Description 测试分库分表查询
* @Autohr supers【weChat:13031016567】
*/
@Test
public void testQueryUserByNum(){
User user = new User();
user.setId(1);
user.setUserNum("wergsgdf3243");
User userDb = userService.selectByUserNum(user);
System.out.println(DbContextHolder.getDbKey()+"库 "+DbContextHolder.getTableIndex()+"表的查询结果:"+GsonUtils.toJson(userDb));
}
}

最后奉上 log4j.properties配置

#定义LOG输出级别
log4j.rootLogger=DEBUG,Console,File
#定义日志输出目的地为控制台
log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.Target=System.out
#可以灵活地指定日志输出格式，下面一行是指定具体的格式
log4j.appender.Console.layout = org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=[%c] - %m%n
#文件大小到达指定尺寸的时候产生一个新的文件
log4j.appender.File = org.apache.log4j.RollingFileAppender
#指定输出目录
log4j.appender.File.File = logs/ssm.log
#定义文件最大大小
log4j.appender.File.MaxFileSize = 10MB
# 输出所以日志，如果换成DEBUG表示输出DEBUG以上级别日志
log4j.appender.File.Threshold = ALL
log4j.appender.File.layout = org.apache.log4j.PatternLayout
log4j.appender.File.layout.ConversionPattern =[%p] [%d{yyyy-MM-dd HH\:mm\:ss}][%c]%m%n