打开APP
userphoto
未登录

开通VIP，畅享免费电子书等14项超值服

开通VIP

首页

好书

留言交流

下载APP

联系客服
Spring MVC +Spring + Mybatis 构建分库分表总结 SSM搭建以及分库分表的实现
userphoto 关注

分库分表在小型公司很少能遇到也很少使用,毕竟数据量没有那么大,当数据量大,所有数据都压在一张表时,如果单从数据库的角度考虑是可以分库分表处理来存储数据。分库分表 顾名思义就是根据查询条件动态的去获取数据所在的库和表的位置.例如一个系统有唯一的标识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包

  1. <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  2.          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  3.     <modelVersion>4.0.0</modelVersion>
  4.     <groupId>com.subdt</groupId>
  5.     <artifactId>subDbTable</artifactId>
  6.     <packaging>war</packaging>
  7.     <version>1.0-SNAPSHOT</version>
  8.     <name>subDbTable Maven Webapp</name>
  9.     <url>http://maven.apache.org</url>
  10.     <properties>
  11.         <!-- spring版本号 -->
  12.         <spring.version>4.0.2.RELEASE</spring.version>
  13.         <!-- mybatis版本号 -->
  14.         <mybatis.version>3.2.6</mybatis.version>
  15.         <!-- log4j日志文件管理包版本 -->
  16.         <slf4j.version>1.7.7</slf4j.version>
  17.         <log4j.version>1.2.17</log4j.version>
  18.     </properties>
  20.     <dependencies>
  21.         <dependency>
  22.             <groupId>junit</groupId>
  23.             <artifactId>junit</artifactId>
  24.             <version>4.11</version>
  25.             <!-- 表示开发的时候引入，发布的时候不会加载此包 -->
  26.             <scope>test</scope>
  27.         </dependency>
  28.         <!-- spring核心包 -->
  29.         <dependency>
  30.             <groupId>org.springframework</groupId>
  31.             <artifactId>spring-core</artifactId>
  32.             <version>${spring.version}</version>
  33.         </dependency>
  35.         <dependency>
  36.             <groupId>org.springframework</groupId>
  37.             <artifactId>spring-web</artifactId>
  38.             <version>${spring.version}</version>
  39.         </dependency>
  40.         <dependency>
  41.             <groupId>org.springframework</groupId>
  42.             <artifactId>spring-oxm</artifactId>
  43.             <version>${spring.version}</version>
  44.         </dependency>
  45.         <dependency>
  46.             <groupId>org.springframework</groupId>
  47.             <artifactId>spring-tx</artifactId>
  48.             <version>${spring.version}</version>
  49.         </dependency>
  51.         <dependency>
  52.             <groupId>org.springframework</groupId>
  53.             <artifactId>spring-jdbc</artifactId>
  54.             <version>${spring.version}</version>
  55.         </dependency>
  57.         <dependency>
  58.             <groupId>org.springframework</groupId>
  59.             <artifactId>spring-webmvc</artifactId>
  60.             <version>${spring.version}</version>
  61.         </dependency>
  62.         <dependency>
  63.             <groupId>org.springframework</groupId>
  64.             <artifactId>spring-aop</artifactId>
  65.             <version>${spring.version}</version>
  66.         </dependency>
  68.         <dependency>
  69.             <groupId>org.springframework</groupId>
  70.             <artifactId>spring-context-support</artifactId>
  71.             <version>${spring.version}</version>
  72.         </dependency>
  74.         <dependency>
  75.             <groupId>org.springframework</groupId>
  76.             <artifactId>spring-test</artifactId>
  77.             <version>${spring.version}</version>
  78.         </dependency>
  80.                 <dependency>
  81.                     <groupId>org.aspectj</groupId>
  82.                     <artifactId>aspectjweaver</artifactId>
  83.                     <version>1.8.9</version>
  84.                 </dependency>
  86.                 <dependency>
  87.                     <groupId>commons-beanutils</groupId>
  88.                     <artifactId>commons-beanutils</artifactId>
  89.                     <version>1.8.3</version>
  90.                 </dependency>
  92.         <dependency>
  93.             <groupId>commons-lang</groupId>
  94.             <artifactId>commons-lang</artifactId>
  95.             <version>2.6</version>
  96.         </dependency>
  98.         <!-- mybatis核心包 -->
  99.         <dependency>
  100.             <groupId>org.mybatis</groupId>
  101.             <artifactId>mybatis</artifactId>
  102.             <version>${mybatis.version}</version>
  103.         </dependency>
  104.         <!-- mybatis/spring包 -->
  105.         <dependency>
  106.             <groupId>org.mybatis</groupId>
  107.             <artifactId>mybatis-spring</artifactId>
  108.             <version>1.2.2</version>
  109.         </dependency>
  110.         <!-- 导入java ee jar 包 -->
  111.         <dependency>
  112.             <groupId>javax</groupId>
  113.             <artifactId>javaee-api</artifactId>
  114.             <version>7.0</version>
  115.         </dependency>
  116.         <!-- 导入Mysql数据库链接jar包 -->
  117.         <dependency>
  118.             <groupId>mysql</groupId>
  119.             <artifactId>mysql-connector-java</artifactId>
  120.             <version>5.1.30</version>
  121.         </dependency>
  122.         <!-- 导入dbcp的jar包，用来在applicationContext.xml中配置数据库 -->
  123.         <dependency>
  124.             <groupId>commons-dbcp</groupId>
  125.             <artifactId>commons-dbcp</artifactId>
  126.             <version>1.2.2</version>
  127.         </dependency>
  128.         <!-- JSTL标签类 -->
  129.         <dependency>
  130.             <groupId>jstl</groupId>
  131.             <artifactId>jstl</artifactId>
  132.             <version>1.2</version>
  133.         </dependency>
  134.         <!-- 日志文件管理包 -->
  135.         <!-- log start -->
  136.         <dependency>
  137.             <groupId>log4j</groupId>
  138.             <artifactId>log4j</artifactId>
  139.             <version>${log4j.version}</version>
  140.         </dependency>
  143.         <!-- 格式化对象，方便输出日志 -->
  144.         <dependency>
  145.             <groupId>com.alibaba</groupId>
  146.             <artifactId>fastjson</artifactId>
  147.             <version>1.1.41</version>
  148.         </dependency>
  151.         <dependency>
  152.             <groupId>org.slf4j</groupId>
  153.             <artifactId>slf4j-api</artifactId>
  154.             <version>${slf4j.version}</version>
  155.         </dependency>
  157.         <dependency>
  158.             <groupId>org.slf4j</groupId>
  159.             <artifactId>slf4j-log4j12</artifactId>
  160.             <version>${slf4j.version}</version>
  161.         </dependency>
  162.         <!-- log end -->
  163.         <!-- 映入JSON -->
  164.         <dependency>
  165.             <groupId>org.codehaus.jackson</groupId>
  166.             <artifactId>jackson-mapper-asl</artifactId>
  167.             <version>1.9.13</version>
  168.         </dependency>
  169.         <!-- 上传组件包 -->
  170.         <dependency>
  171.             <groupId>commons-fileupload</groupId>
  172.             <artifactId>commons-fileupload</artifactId>
  173.             <version>1.3.1</version>
  174.         </dependency>
  175.         <dependency>
  176.             <groupId>commons-io</groupId>
  177.             <artifactId>commons-io</artifactId>
  178.             <version>2.4</version>
  179.         </dependency>
  180.         <dependency>
  181.             <groupId>commons-codec</groupId>
  182.             <artifactId>commons-codec</artifactId>
  183.             <version>1.9</version>
  184.         </dependency>
  186.         <dependency>
  187.             <groupId>com.google.code.gson</groupId>
  188.             <artifactId>gson</artifactId>
  189.             <version>1.7.1</version>
  190.         </dependency>
  193.     </dependencies>
  194.     <build>
  195.         <finalName>subDbTable</finalName>
  196.         <resources>
  197.             <!--编译之后包含xml-->
  198.             <resource>
  199.                 <directory>src/main/resources</directory>
  200.                 <filtering>true</filtering>
  201.             </resource>
  202.             <resource>
  203.                 <directory>src/main/java</directory>
  204.                 <includes>
  205.                     <include>**/*.xml</include>
  206.                 </includes>
  207.                 <filtering>true</filtering>
  208.             </resource>
  209.         </resources>
  210.         <plugins>
  211.             <plugin>
  212.                 <groupId>org.apache.maven.plugins</groupId>
  213.                 <artifactId>maven-resources-plugin</artifactId>
  214.                 <configuration>
  215.                     <encoding>UTF-8</encoding>
  216.                 </configuration>
  217.             </plugin>
  218.         </plugins>
  219.     </build>
  220. </project>

首先看下目录结构 




3，Spring与MyBatis的整合

首先创建 jdbc.properties 配置数据库信息
  1. driver=com.mysql.jdbc.Driver
  2. #定义初始连接数
  3. initialSize=0
  4. #定义最大连接数
  5. maxActive=20
  6. #定义最大空闲
  7. maxIdle=20
  8. #定义最小空闲
  9. minIdle=1
  10. #定义最长等待时间
  11. maxWait=60000
  13. jdbc.mysql.url0=jdbc:mysql://localhost:3306/book_02?createDatabaseIfNotExist=true&characterEncoding=utf-8&useUnicode=true
  14. jdbc.mysql.username0=root
  15. jdbc.mysql.password0=123456
  17. jdbc.mysql.url1=jdbc:mysql://localhost:3306/book_00?createDatabaseIfNotExist=true&characterEncoding=utf-8&useUnicode=true
  18. jdbc.mysql.username1=root
  19. jdbc.mysql.password1=123456
  21. jdbc.mysql.url2=jdbc:mysql://localhost:3306/book_01?createDatabaseIfNotExist=true&characterEncoding=utf-8&useUnicode=true
  22. jdbc.mysql.username2=root
  23. jdbc.mysql.password2=123456

创建Spring配置文件 spring-config.xml

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <beans xmlns="http://www.springframework.org/schema/beans"
  3.        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4.        xmlns:context="http://www.springframework.org/schema/context"
  5.        xmlns:aop="http://www.springframework.org/schema/aop"
  6.        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"
  7.        default-autowire="byName">
  8.     <context:component-scan base-package="com.sub.dt"/>
  9.     <aop:aspectj-autoproxy/>
  10.     <!-- 属性文件读入 -->
  11.     <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
  12.         <property name="locations">
  13.             <list>
  14.                 <value>classpath:props/*.properties</value>
  15.             </list>
  16.         </property>
  17.     </bean>
  19.     <import resource="classpath:spring/spring-*.xml"/>
  21. </beans>

创建spring与mybatis结合的整合配置文件. spring-config-datasource-dbcp.xml
这个文件主要是多数据源配置,自动扫描mybatis的xml配置文件.

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <beans xmlns="http://www.springframework.org/schema/beans"
  3.        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4.        xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
  6.     <!-- 引入配置文件 -->
  7.     <bean id="propertyConfigurer"
  8.           class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
  9.         <property name="location" value="classpath:props/jdbc.properties"/>
  10.     </bean>
  13.     <bean id="dataSource0" class="org.apache.commons.dbcp.BasicDataSource">
  14.         <property name="driverClassName" value="${driver}"/>
  15.         <property name="url" value="${jdbc.mysql.url0}"/>
  16.         <property name="username" value="${jdbc.mysql.username0}"/>
  17.         <property name="password" value="${jdbc.mysql.password0}"/>
  18.         <!-- 初始化连接大小 -->
  19.         <property name="initialSize" value="${initialSize}"></property>
  20.         <!-- 连接池最大数量 -->
  21.         <property name="maxActive" value="${maxActive}"></property>
  22.         <!-- 连接池最大空闲 -->
  23.         <property name="maxIdle" value="${maxIdle}"></property>
  24.         <!-- 连接池最小空闲 -->
  25.         <property name="minIdle" value="${minIdle}"></property>
  26.         <!-- 获取连接最大等待时间 -->
  27.         <property name="maxWait" value="${maxWait}"></property>
  28.     </bean>
  31.     <bean id="dataSource1" class="org.apache.commons.dbcp.BasicDataSource">
  32.         <property name="driverClassName" value="${driver}"/>
  33.         <property name="url" value="${jdbc.mysql.url1}"/>
  34.         <property name="username" value="${jdbc.mysql.username1}"/>
  35.         <property name="password" value="${jdbc.mysql.password1}"/>
  36.         <!-- 初始化连接大小 -->
  37.         <property name="initialSize" value="${initialSize}"></property>
  38.         <!-- 连接池最大数量 -->
  39.         <property name="maxActive" value="${maxActive}"></property>
  40.         <!-- 连接池最大空闲 -->
  41.         <property name="maxIdle" value="${maxIdle}"></property>
  42.         <!-- 连接池最小空闲 -->
  43.         <property name="minIdle" value="${minIdle}"></property>
  44.         <!-- 获取连接最大等待时间 -->
  45.         <property name="maxWait" value="${maxWait}"></property>
  46.     </bean>
  49.     <bean id="dataSource2" class="org.apache.commons.dbcp.BasicDataSource">
  50.         <property name="driverClassName" value="${driver}"/>
  51.         <property name="url" value="${jdbc.mysql.url2}"/>
  52.         <property name="username" value="${jdbc.mysql.username2}"/>
  53.         <property name="password" value="${jdbc.mysql.password2}"/>
  54.         <!-- 初始化连接大小 -->
  55.         <property name="initialSize" value="${initialSize}"></property>
  56.         <!-- 连接池最大数量 -->
  57.         <property name="maxActive" value="${maxActive}"></property>
  58.         <!-- 连接池最大空闲 -->
  59.         <property name="maxIdle" value="${maxIdle}"></property>
  60.         <!-- 连接池最小空闲 -->
  61.         <property name="minIdle" value="${minIdle}"></property>
  62.         <!-- 获取连接最大等待时间 -->
  63.         <property name="maxWait" value="${maxWait}"></property>
  64.     </bean>
  66.     <bean id="mysqlDynamicDataSource" class="com.sub.dt.dbRouting.db.DynamicDataSource">
  67.         <property name="targetDataSources">
  68.             <!-- 标识符类型 -->
  69.             <map>
  70.                 <entry key="db0" value-ref="dataSource0"/>
  71.                 <entry key="db1" value-ref="dataSource1"/>
  72.                 <entry key="db2" value-ref="dataSource2"/>
  73.             </map>
  74.         </property>
  75.     </bean>
  77.     <bean id="dbRuleSet" class="com.sub.dt.dbRouting.bean.RouterSet">
  78.         <property name="routeFieldStart" value="0"></property>
  79.         <property name="routeFieldEnd" value="9200000000000000000"></property>
  80.         <property name="dbNumber" value="3"></property>
  81.         <property name="routeType" value="2"></property>
  82.         <property name="ruleType" value="3"></property>
  83.         <property name="tableNumber" value="5"></property>
  84.         <property name="dbKeyArray">
  85.             <list>
  86.                 <value>db0</value>
  87.                 <value>db1</value>
  88.                 <value>db2</value>
  89.             </list>
  90.         </property>
  91.     </bean>
  93.     <bean id="dBRouter" class="com.sub.dt.dbRouting.router.DBRouterImpl">
  94.         <property name="routerSetList">
  95.             <!-- 标识符类型 -->
  96.             <list>
  97.                 <ref bean="dbRuleSet"/>
  98.             </list>
  99.         </property>
  100.     </bean>
  102.     <!--事务-->
  103.     <bean id="baiTiaoTransactionManager"
  104.           class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
  105.         <property name="dataSource" ref="mysqlDynamicDataSource"></property>
  106.     </bean>
  108.     <bean id="btTransactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
  109.         <property name="transactionManager" ref="baiTiaoTransactionManager"></property>
  110.         <property name="propagationBehaviorName" value="PROPAGATION_REQUIRED"></property>
  111.     </bean>
  113.     <!-- spring和MyBatis完美整合，不需要mybatis的配置映射文件 -->
  114.     <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
  115.         <property name="dataSource" ref="mysqlDynamicDataSource"/>
  116.         <!-- 自动扫描mapping.xml文件 -->
  117.         <property name="mapperLocations" value="classpath:com/sub/dt/mapping/*.xml"></property>
  118.     </bean>
  120.     <!-- DAO接口所在包名，Spring会自动查找其下的类 -->
  121.     <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
  122.         <property name="basePackage" value="com.sub.dt.dao"/>
  123.         <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
  124.     </bean>
  126. </beans>

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



spring-config-datasource-dbcp.xml中配置的com.sub.dt.dbRouting.db.DynamicDataSource 继承 AbstractRoutingDataSource方法,单表配置mybatis是使用的是BasicDataSource
BasicDataSourBasicDataSource
BasicDataSource 自定义的AbstractRoutingDataSource与spring的BasicDataSource都是一个道理 管理数据源 实现了DataSource接口.

  1. package com.sub.dt.dbRouting.db;
  4. import com.sub.dt.dbRouting.DbContextHolder;
  5. import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
  7. import java.util.logging.Logger;
  9. /**
  10.  * @Description SPring 的动态数据源的实现
  11.  * @Autohr supers【weChat:13031016567】
  12.  */
  13. public class DynamicDataSource extends AbstractRoutingDataSource {
  14.     public static final Logger logger = Logger.getLogger(DynamicDataSource.class.toString());
  15.     @Override
  16.     protected Object determineCurrentLookupKey() {
  17.     	return DbContextHolder.getDbKey();//获取当前数据源
  18.     }
  20. }
UserMapper.xml创建库表对应的xml文件

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
  3. <mapper namespace="com.sub.dt.dao.IUserDao">
  4.     <resultMap id="BaseResultMap" type="com.sub.dt.pojo.User">
  5.         <id column="id" property="id" jdbcType="INTEGER"/>
  6.         <result column="user_name" property="userName" jdbcType="VARCHAR"/>
  7.         <result column="password" property="password" jdbcType="VARCHAR"/>
  8.         <result column="age" property="age" jdbcType="INTEGER"/>
  9.         <result column="user_num" property="userNum" jdbcType="VARCHAR"/>
  10.     </resultMap>
  11.     <sql id="Base_Column_List">
  12.     id,user_num, user_name, password, age
  13.   </sql>
  15.     <insert id="insertUser" parameterType="com.sub.dt.pojo.User">
  16.     insert into t_user${tableIndex} (id,user_num,user_name, password,age)
  17.     values (#{id,jdbcType=INTEGER},#{userNum,jdbcType=VARCHAR}, #{userName,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR},#{age,jdbcType=INTEGER})
  18.   </insert>
  21.     <insert id="deleteByuserNum" parameterType="com.sub.dt.pojo.User">
  22.     delete from t_user${tableIndex}
  23.     where user_num = #{userNum,jdbcType=VARCHAR}
  24.   </insert>
  26.     <update id="updateByUserNum" parameterType="com.sub.dt.pojo.User">
  27.         update t_user${tableIndex}
  28.         <set>
  29.             <if test="userName != null">
  30.                 user_name = #{userName,jdbcType=VARCHAR},
  31.             </if>
  32.             <if test="password != null">
  33.                 password = #{password,jdbcType=VARCHAR},
  34.             </if>
  35.             <if test="age != null">
  36.                 age = #{age,jdbcType=INTEGER},
  37.             </if>
  38.         </set>
  39.         where user_num = #{userNum,jdbcType=VARCHAR}
  40.     </update>
  42.     <select id="selectByUserNum" resultMap="BaseResultMap" parameterType="com.sub.dt.pojo.User">
  43.         select
  44.         <include refid="Base_Column_List"/>
  45.         from t_user${tableIndex}
  46.         where user_num = #{userNum,jdbcType=VARCHAR}
  47.     </select>
  48. </mapper>

IUserDao.java

  1. package com.sub.dt.dao;
  3. import com.sub.dt.pojo.User;
  5. public interface IUserDao {
  7.     /**
  8.      * @Description
  9.      * @Autohr supers【weChat:13031016567】
  10.      */
  11.     int insertUser(User user);
  13.     /**
  14.      * @Description
  15.      * @Autohr supers【weChat:13031016567】
  16.      */
  17.     int deleteByuserNum(User user);
  19.     /**
  20.      * @Description
  21.      * @Autohr supers【weChat:13031016567】
  22.      */
  23.     int updateByUserNum(User user);
  25.     /**
  26.      * @Description
  27.      * @Autohr supers【weChat:13031016567】
  28.      */
  29.     User selectByUserNum(User user);
  30. }

IUserService.java
  1. package com.sub.dt.service;
  3. import com.sub.dt.pojo.User;
  5. /**
  6.  * @Description
  7.  * @Autohr supers【weChat:13031016567】
  8.  */
  9. public interface IUserService {
  11. 	/**
  12. 	 * @Description
  13. 	 * @Autohr supers【weChat:13031016567】
  14. 	 */
  15. 	public int insertUser(User user);
  17. 	/**
  18. 	 * @Description
  19. 	 * @Autohr supers【weChat:13031016567】
  20. 	 */
  21. 	public int deleteByuserNum(User user);
  23. 	/**
  24. 	 * @Description
  25. 	 * @Autohr supers【weChat:13031016567】
  26. 	 */
  27. 	public int updateByUserNum(User user);
  29. 	/**
  30. 	 * @Description
  31. 	 * @Autohr supers【weChat:13031016567】
  32. 	 */
  33. 	public User selectByUserNum(User user);
  36. }

UserServiceImpl.java
  1. package com.sub.dt.service.impl;
  3. import com.sub.dt.dao.IUserDao;
  4. import com.sub.dt.dbRouting.annotation.Router;
  5. import com.sub.dt.pojo.User;
  6. import com.sub.dt.service.IUserService;
  7. import org.springframework.stereotype.Service;
  9. import javax.annotation.Resource;
  11. /**
  12.  * @Description
  13.  * @Autohr supers【weChat:13031016567】
  14.  */
  15. @Service("userService")
  16. public class UserServiceImpl implements IUserService {
  18.     @Resource
  19.     private IUserDao userDao;
  21.     @Router
  22.     public int insertUser(User user) {
  23.         return this.userDao.insertUser(user);
  24.     }
  26.     @Router
  27.     public int deleteByuserNum(User user) {
  28.         return this.userDao.deleteByuserNum(user);
  29.     }
  31.     @Router
  32.     public int updateByUserNum(User user) {
  33.         return this.userDao.updateByUserNum(user);
  34.     }
  36.     @Router
  37.     public User selectByUserNum(User user) {
  38.         return this.userDao.selectByUserNum(user);
  39.     }
  40. }

UserController.java
  1. package com.sub.dt.controller;
  3. import com.sub.dt.pojo.User;
  4. import com.sub.dt.service.IUserService;
  5. import org.springframework.stereotype.Controller;
  6. import org.springframework.ui.Model;
  7. import org.springframework.web.bind.annotation.RequestMapping;
  9. import javax.annotation.Resource;
  10. import javax.servlet.http.HttpServletRequest;
  12. @Controller
  13. @RequestMapping("/user")
  14. public class UserController {
  15. 	@Resource
  16. 	private IUserService userService;
  18. 	@RequestMapping("/queryUser")
  19. 	public String toIndex(HttpServletRequest request,Model model,User user){
  20. 		User userDb = this.userService.selectByUserNum(user);
  21. 		model.addAttribute("user", userDb);
  22. 		return "queryUser";
  23. 	}
  24. }

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

Router.java  自定义注解,此注解作用是当做一个切点,在方法上添加此注解,执行方法前会执行DBRouterInterceptor的doRoute方法.详情可参考:http://blog.csdn.net/buchengbugui/article/details/60875401
  1. package com.sub.dt.dbRouting.annotation;
  3. import java.lang.annotation.ElementType;
  4. import java.lang.annotation.Retention;
  5. import java.lang.annotation.RetentionPolicy;
  6. import java.lang.annotation.Target;
  8. /**
  9.  * @Description
  10.  * @Autohr supers【weChat:13031016567】
  11.  */
  12. @Retention(RetentionPolicy.RUNTIME)
  13. @Target(ElementType.METHOD)
  14. public @interface Router {
  16.     String routerField() default RouterConstants.ROUTER_FIELD_DEFAULT;
  18.     String tableStyle() default RouterConstants.ROUTER_TABLE_SUFFIX_DEFAULT;
  19. }

DBRouterInterceptor.java 定义切点为方法上使用,@Router注解的方法,执行注解方法前前执行doRoute方法,根据参数中的userNum设置是几库几表
  1. package com.sub.dt.dbRouting;
  3. import com.sub.dt.dbRouting.annotation.Router;
  4. import com.sub.dt.dbRouting.annotation.RouterConstants;
  5. import com.sub.dt.dbRouting.router.RouterUtils;
  6. import org.apache.commons.beanutils.BeanUtils;
  7. import org.apache.commons.lang.StringUtils;
  8. import org.aspectj.lang.JoinPoint;
  9. import org.aspectj.lang.Signature;
  10. import org.aspectj.lang.annotation.Aspect;
  11. import org.aspectj.lang.annotation.Before;
  12. import org.aspectj.lang.annotation.Pointcut;
  13. import org.aspectj.lang.reflect.MethodSignature;
  14. import org.slf4j.Logger;
  15. import org.slf4j.LoggerFactory;
  16. import org.springframework.beans.factory.annotation.Autowired;
  17. import org.springframework.stereotype.Component;
  19. import java.lang.reflect.Method;
  21. /**
  22.  * @Description 切面切点 在Router注解的方法执行前执行 切点织入
  23.  * @Autohr supers【weChat:13031016567】
  24.  */
  25. @Aspect
  26. @Component
  27. public class DBRouterInterceptor {
  29.     private static final Logger log = LoggerFactory.getLogger(DBRouterInterceptor.class);
  31.     private DBRouter dBRouter;
  33.     @Pointcut("@annotation( com.sub.dt.dbRouting.annotation.Router)")
  34.     public void aopPoint() {
  35.     }
  37.     @Before("aopPoint()")
  38.     public Object doRoute(JoinPoint jp) throws Throwable {
  39.         long t1 = System.currentTimeMillis();
  40.         boolean result = true;
  41.         Method method = getMethod(jp);
  42.         Router router = method.getAnnotation(Router.class);
  43.         String routeField = router.routerField();
  44.         Object[] args = jp.getArgs();
  45.         if (args != null && args.length > 0) {
  46.             for (int i = 0; i < args.length; i++) {
  47.                 long t2 = System.currentTimeMillis();
  48.                 String routeFieldValue = BeanUtils.getProperty(args[i],
  49.                         routeField);
  50.                 log.debug("routeFieldValue{}" + (System.currentTimeMillis() - t2));
  51.                 if (StringUtils.isNotEmpty(routeFieldValue)) {
  52.                     if (RouterConstants.ROUTER_FIELD_DEFAULT.equals(routeField)) {
  53.                         dBRouter.doRouteByResource("" + RouterUtils.getResourceCode(routeFieldValue));
  54.                         break;
  55.                     } else {
  56.                         this.searchParamCheck(routeFieldValue);
  57.                         String resource = routeFieldValue.substring(routeFieldValue.length() - 4);
  58.                         dBRouter.doRouteByResource(resource);
  59.                         break;
  60.                     }
  61.                 }
  62.             }
  63.         }
  64.         log.debug("doRouteTime{}" + (System.currentTimeMillis() - t1));
  65.         return result;
  66.     }
  68.     private Method getMethod(JoinPoint jp) throws NoSuchMethodException {
  69.         Signature sig = jp.getSignature();
  70.         MethodSignature msig = (MethodSignature) sig;
  71.         return getClass(jp).getMethod(msig.getName(), msig.getParameterTypes());
  72.     }
  74.     private Class<? extends Object> getClass(JoinPoint jp)
  75.             throws NoSuchMethodException {
  76.         return jp.getTarget().getClass();
  77.     }
  80.     /**
  81.      * 查询支付结构参数检查
  82.      *
  83.      * @param payId
  84.      */
  85.     private void searchParamCheck(String payId) {
  86.         if (payId.trim().equals("")) {
  87.             throw new IllegalArgumentException("payId is empty");
  88.         }
  89.     }
  91.     public DBRouter getdBRouter() {
  92.         return dBRouter;
  93.     }
  95.     public void setdBRouter(DBRouter dBRouter) {
  96.         this.dBRouter = dBRouter;
  97.     }
  99. }
RouterSet.java  主要是 配置在spring-config-datasource-dbcp.xml中 存储库的数量,表的数量,库表相关信息
  1. package com.sub.dt.dbRouting.bean;
  3. import java.util.List;
  5. /**
  6.  * @Description
  7.  * @Autohr supers【weChat:13031016567】
  8.  */
  9. public class RouterSet {
  11.     /**根据字符串*/
  12.     public final static int RULE_TYPE_STR=3;
  14.     public final static int ROUTER_TYPE_DB=0;
  16.     public final static int ROUTER_TYPE_TABLE =1;
  18.     public final static int ROUTER_TYPE_DBANDTABLE=2;
  20.     /**数据库表的逻辑KEY,与数据源MAP配置中的key一致*/
  21.     private List<String> dbKeyArray;
  23.     /**数据库数量*/
  24.     private int dbNumber;
  25.     /**数据表数量*/
  26.     private int tableNumber;
  27.     /**数据表index样式*/
  28.     private String tableIndexStyle;
  29.     /**Id开始*/
  30.     private String routeFieldStart;
  31.     /**Id结束*/
  32.     private String routeFieldEnd;
  33.     /**规则类型*/
  34.     private int ruleType;
  35.     /**路由类型类型*/
  36.     private int routeType;
  38.     public static int getRULE_TYPE_STR() {
  39.         return RULE_TYPE_STR;
  40.     }
  42.     public static int getROUTER_TYPE_DB() {
  43.         return ROUTER_TYPE_DB;
  44.     }
  46.     public static int getROUTER_TYPE_TABLE() {
  47.         return ROUTER_TYPE_TABLE;
  48.     }
  50.     public static int getROUTER_TYPE_DBANDTABLE() {
  51.         return ROUTER_TYPE_DBANDTABLE;
  52.     }
  54.     public List<String> getDbKeyArray() {
  55.         return dbKeyArray;
  56.     }
  58.     public void setDbKeyArray(List<String> dbKeyArray) {
  59.         this.dbKeyArray = dbKeyArray;
  60.     }
  62.     public int getDbNumber() {
  63.         return dbNumber;
  64.     }
  66.     public void setDbNumber(int dbNumber) {
  67.         this.dbNumber = dbNumber;
  68.     }
  70.     public int getTableNumber() {
  71.         return tableNumber;
  72.     }
  74.     public void setTableNumber(int tableNumber) {
  75.         this.tableNumber = tableNumber;
  76.     }
  78.     public String getTableIndexStyle() {
  79.         return tableIndexStyle;
  80.     }
  82.     public void setTableIndexStyle(String tableIndexStyle) {
  83.         this.tableIndexStyle = tableIndexStyle;
  84.     }
  86.     public String getRouteFieldStart() {
  87.         return routeFieldStart;
  88.     }
  90.     public void setRouteFieldStart(String routeFieldStart) {
  91.         this.routeFieldStart = routeFieldStart;
  92.     }
  94.     public String getRouteFieldEnd() {
  95.         return routeFieldEnd;
  96.     }
  98.     public void setRouteFieldEnd(String routeFieldEnd) {
  99.         this.routeFieldEnd = routeFieldEnd;
  100.     }
  102.     public int getRuleType() {
  103.         return ruleType;
  104.     }
  106.     public void setRuleType(int ruleType) {
  107.         this.ruleType = ruleType;
  108.     }
  110.     public int getRouteType() {
  111.         return routeType;
  112.     }
  114.     public void setRouteType(int routeType) {
  115.         this.routeType = routeType;
  116.     }
  117. }


DBRouterImpl.java  getDbKey方法根据userNum定位库表算法
  1. package com.sub.dt.dbRouting.router;
  3. import com.sub.dt.dbRouting.DBRouter;
  4. import com.sub.dt.dbRouting.DbContextHolder;
  5. import com.sub.dt.dbRouting.annotation.RouterConstants;
  6. import com.sub.dt.dbRouting.bean.RouterSet;
  7. import org.apache.commons.lang.StringUtils;
  8. import org.slf4j.Logger;
  9. import org.slf4j.LoggerFactory;
  11. import java.text.DecimalFormat;
  12. import java.util.List;
  14. /**
  15.  * @Description 根据指定变量动态切 库和表
  16.  * @Autohr supers【weChat:13031016567】
  17.  */
  18. public class DBRouterImpl implements DBRouter {
  20.     private static final Logger log = LoggerFactory.getLogger(DBRouterImpl.class);
  22.     /**
  23.      * 配置列表
  24.      */
  25.     private List<RouterSet> routerSetList;
  27.     @Override
  28.     public String doRoute(String fieldId) {
  29.         if (StringUtils.isEmpty(fieldId)) {
  30.             throw new IllegalArgumentException("dbsCount and tablesCount must be both positive!");
  31.         }
  32.         int routeFieldInt = RouterUtils.getResourceCode(fieldId);
  33.         String dbKey = getDbKey(routerSetList, routeFieldInt);
  34.         return dbKey;
  35.     }
  37.     @Override
  38.     public String doRouteByResource(String resourceCode) {
  39.         if (StringUtils.isEmpty(resourceCode)) {
  40.             throw new IllegalArgumentException("dbsCount and tablesCount must be both positive!");
  41.         }
  42.         int routeFieldInt = Integer.valueOf(resourceCode);
  43.         String dbKey = getDbKey(routerSetList, routeFieldInt);
  44.         return dbKey;
  45.     }
  48.     /**
  49.      * @Description 根据数据字段来判断属于哪个段的规则,获得数据库key
  50.      * @Autohr supers【weChat:13031016567】
  51.      */
  52.     private String getDbKey(List<RouterSet> routerSets, int routeFieldInt) {
  53.         RouterSet routerSet = null;
  54.         if (routerSets == null || routerSets.size() <= 0) {
  55.             throw new IllegalArgumentException("dbsCount and tablesCount must be both positive!");
  56.         }
  57.         String dbKey = null;
  58.         for (RouterSet item : routerSets) {
  59.             if (item.getRuleType() == routerSet.RULE_TYPE_STR) {
  60.                 routerSet = item;
  61.                 if (routerSet.getDbKeyArray() != null && routerSet.getDbNumber() != 0) {
  62.                     long dbIndex = 0;
  63.                     long tbIndex = 0;
  64.                     //默认按照分库进行计算
  65.                     long mode = routerSet.getDbNumber();
  66.                     //如果是按照分库分表的话，计算
  67.                     if (item.getRouteType() == RouterSet.ROUTER_TYPE_DBANDTABLE && item.getTableNumber() != 0) {
  68.                         mode = routerSet.getDbNumber() * item.getTableNumber();
  69.                         dbIndex = routeFieldInt % mode / item.getTableNumber();
  70.                         tbIndex = routeFieldInt % item.getTableNumber();
  71.                         String tableIndex = getFormateTableIndex(item.getTableIndexStyle(), tbIndex);
  72.                         DbContextHolder.setTableIndex(tableIndex);
  73.                     } else if (item.getRouteType() == RouterSet.ROUTER_TYPE_DB) {
  74.                         mode = routerSet.getDbNumber();
  75.                         dbIndex = routeFieldInt % mode;
  76.                     } else if (item.getRouteType() == RouterSet.ROUTER_TYPE_TABLE) {
  77.                         tbIndex = routeFieldInt % item.getTableNumber();
  78.                         String tableIndex = getFormateTableIndex(item.getTableIndexStyle(), tbIndex);
  79.                         DbContextHolder.setTableIndex(tableIndex);
  80.                     }
  81.                     dbKey = routerSet.getDbKeyArray().get(Long.valueOf(dbIndex).intValue());
  82.                     log.debug("getDbKey resource:{}------->dbkey:{},tableIndex:{},", new Object[]{routeFieldInt, dbKey, tbIndex});
  83.                     DbContextHolder.setDbKey(dbKey);
  84.                 }
  85.                 break;
  86.             }
  87.         }
  88.         return dbKey;
  89.     }
  92.     /**
  93.      * @Description 此方法是将例如+++0000根式的字符串替换成传参数字例如44 变成+++0044
  94.      * @Autohr supers【weChat:13031016567】
  95.      */
  96.     private static String getFormateTableIndex(String style, long tbIndex) {
  97.         String tableIndex = null;
  98.         DecimalFormat df = new DecimalFormat();
  99.         if (StringUtils.isEmpty(style)) {
  100.             style = RouterConstants.ROUTER_TABLE_SUFFIX_DEFAULT;//在格式后添加诸如单位等字符
  101.         }
  102.         df.applyPattern(style);
  103.         tableIndex = df.format(tbIndex);
  104.         return tableIndex;
  105.     }
  107.     public List<RouterSet> getRouterSetList() {
  108.         return routerSetList;
  109.     }
  111.     public void setRouterSetList(List<RouterSet> routerSetList) {
  112.         this.routerSetList = routerSetList;
  113.     }
  114. }

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

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <beans xmlns="http://www.springframework.org/schema/beans"
  3.        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4.        xmlns:context="http://www.springframework.org/schema/context"
  5.        xsi:schemaLocation="http://www.springframework.org/schema/beans
  6.                         http://www.springframework.org/schema/beans/spring-beans-3.1.xsd
  7.                         http://www.springframework.org/schema/context
  8.                         http://www.springframework.org/schema/context/spring-context-3.1.xsd">
  9.     <!-- 自动扫描该包，使SpringMVC认为包下用了@controller注解的类是控制器 -->
  10.     <context:component-scan base-package="com.sub.dt.controller"/>
  11.     <!--避免IE执行AJAX时，返回JSON出现下载文件 -->
  12.     <bean id="mappingJacksonHttpMessageConverter"
  13.           class="org.springframework.http.converter.json.MappingJacksonHttpMessageConverter">
  14.         <property name="supportedMediaTypes">
  15.             <list>
  16.                 <value>text/html;charset=UTF-8</value>
  17.             </list>
  18.         </property>
  19.     </bean>
  20.     <!-- 启动SpringMVC的注解功能，完成请求和注解POJO的映射 -->
  21.     <bean
  22.             class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter">
  23.         <property name="messageConverters">
  24.             <list>
  25.                 <ref bean="mappingJacksonHttpMessageConverter"/>
  26.                 <!-- JSON转换器 -->
  27.             </list>
  28.         </property>
  29.     </bean>
  30.     <!-- 定义跳转的文件的前后缀 ，视图模式配置-->
  31.     <bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
  32.         <!-- 这里的配置我的理解是自动给后面action的方法return的字符串加上前缀和后缀，变成一个 可用的url地址 -->
  33.         <property name="prefix" value="/WEB-INF/jsp/"/>
  34.         <property name="suffix" value=".jsp"/>
  35.     </bean>
  37.     <!-- 配置文件上传，如果没有使用文件上传可以不用配置，当然如果不配，那么配置文件中也不必引入上传组件包 -->
  38.     <bean id="multipartResolver"
  39.           class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
  40.         <!-- 默认编码 -->
  41.         <property name="defaultEncoding" value="utf-8"/>
  42.         <!-- 文件大小最大值 -->
  43.         <property name="maxUploadSize" value="10485760000"/>
  44.         <!-- 内存中的最大值 -->
  45.         <property name="maxInMemorySize" value="40960"/>
  46.     </bean>
  48. </beans>

web.xml配置

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  3.          xmlns="http://java.sun.com/xml/ns/javaee"
  4.          xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
  5.          version="3.0">
  6.     <display-name>Archetype Created Web Application</display-name>
  7.     <!-- Spring和mybatis的配置文件 -->
  8.     <context-param>
  9.         <param-name>contextConfigLocation</param-name>
  10.         <param-value>classpath:spring-config.xml</param-value>
  11.     </context-param>
  12.     <!-- 编码过滤器 -->
  13.     <filter>
  14.         <filter-name>encodingFilter</filter-name>
  15.         <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
  16.         <async-supported>true</async-supported>
  17.         <init-param>
  18.             <param-name>encoding</param-name>
  19.             <param-value>UTF-8</param-value>
  20.         </init-param>
  21.     </filter>
  22.     <filter-mapping>
  23.         <filter-name>encodingFilter</filter-name>
  24.         <url-pattern>/*</url-pattern>
  25.     </filter-mapping>
  26.     <!-- Spring监听器 -->
  27.     <listener>
  28.         <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
  29.     </listener>
  30.     <!-- 防止Spring内存溢出监听器 -->
  31.     <listener>
  32.         <listener-class>org.springframework.web.util.IntrospectorCleanupListener</listener-class>
  33.     </listener>
  35.     <!-- Spring MVC servlet -->
  36.     <servlet>
  37.         <servlet-name>SpringMVC</servlet-name>
  38.         <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
  39.         <init-param>
  40.             <param-name>contextConfigLocation</param-name>
  41.             <param-value>classpath:spring-mvc.xml</param-value>
  42.         </init-param>
  43.         <load-on-startup>1</load-on-startup>
  44.         <async-supported>true</async-supported>
  45.     </servlet>
  46.     <servlet-mapping>
  47.         <servlet-name>SpringMVC</servlet-name>
  48.         <!-- 此处可以可以配置成*.do，对应struts的后缀习惯 -->
  49.         <url-pattern>/</url-pattern>
  50.     </servlet-mapping>
  51.     <welcome-file-list>
  52.         <welcome-file>/index.jsp</welcome-file>
  53.     </welcome-file-list>
  55. </web-app>  

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


  1. /*
  2. Navicat MySQL Data Transfer
  4. Source Server         : local
  5. Source Server Version : 50173
  6. Source Host           : localhost:3306
  7. Source Database       : book_00
  9. Target Server Type    : MYSQL
  10. Target Server Version : 50173
  11. File Encoding         : 65001
  13. Date: 2017-03-10 09:48:12
  14. */
  16. SET FOREIGN_KEY_CHECKS=0;
  18. -- ----------------------------
  19. -- Table structure for `t_user_0000`
  20. -- ----------------------------
  21. DROP TABLE IF EXISTS `t_user_0000`;
  22. CREATE TABLE `t_user_0000` (
  23.   `id` int(11) NOT NULL AUTO_INCREMENT,
  24.   `user_num` varchar(64) NOT NULL,
  25.   `user_name` varchar(16) NOT NULL,
  26.   `password` varchar(64) NOT NULL,
  27.   `age` int(4) NOT NULL,
  28.   PRIMARY KEY (`id`),
  29.   UNIQUE KEY `idx_user_num` (`user_num`)
  30. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
  32. -- ----------------------------
  33. -- Records of t_user_0000
  34. -- ----------------------------
  36. -- ----------------------------
  37. -- Table structure for `t_user_0001`
  38. -- ----------------------------
  39. DROP TABLE IF EXISTS `t_user_0001`;
  40. CREATE TABLE `t_user_0001` (
  41.   `id` int(11) NOT NULL AUTO_INCREMENT,
  42.   `user_num` varchar(64) NOT NULL,
  43.   `user_name` varchar(16) NOT NULL,
  44.   `password` varchar(64) NOT NULL,
  45.   `age` int(4) NOT NULL,
  46.   PRIMARY KEY (`id`),
  47.   UNIQUE KEY `idx_user_num` (`user_num`)
  48. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
  50. -- ----------------------------
  51. -- Records of t_user_0001
  52. -- ----------------------------
  54. -- ----------------------------
  55. -- Table structure for `t_user_0002`
  56. -- ----------------------------
  57. DROP TABLE IF EXISTS `t_user_0002`;
  58. CREATE TABLE `t_user_0002` (
  59.   `id` int(11) NOT NULL AUTO_INCREMENT,
  60.   `user_num` varchar(64) NOT NULL,
  61.   `user_name` varchar(16) NOT NULL,
  62.   `password` varchar(64) NOT NULL,
  63.   `age` int(4) NOT NULL,
  64.   PRIMARY KEY (`id`),
  65.   UNIQUE KEY `idx_user_num` (`user_num`)
  66. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
  68. -- ----------------------------
  69. -- Records of t_user_0002
  70. -- ----------------------------
  72. -- ----------------------------
  73. -- Table structure for `t_user_0003`
  74. -- ----------------------------
  75. DROP TABLE IF EXISTS `t_user_0003`;
  76. CREATE TABLE `t_user_0003` (
  77.   `id` int(11) NOT NULL AUTO_INCREMENT,
  78.   `user_num` varchar(64) NOT NULL,
  79.   `user_name` varchar(16) NOT NULL,
  80.   `password` varchar(64) NOT NULL,
  81.   `age` int(4) NOT NULL,
  82.   PRIMARY KEY (`id`),
  83.   UNIQUE KEY `idx_user_num` (`user_num`)
  84. ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
  86. -- ----------------------------
  87. -- Records of t_user_0003
  88. -- ----------------------------
  90. -- ----------------------------
  91. -- Table structure for `t_user_0004`
  92. -- ----------------------------
  93. DROP TABLE IF EXISTS `t_user_0004`;
  94. CREATE TABLE `t_user_0004` (
  95.   `id` int(11) NOT NULL AUTO_INCREMENT,
  96.   `user_num` varchar(64) NOT NULL,
  97.   `user_name` varchar(16) NOT NULL,
  98.   `password` varchar(64) NOT NULL,
  99.   `age` int(4) NOT NULL,
  100.   PRIMARY KEY (`id`),
  101.   UNIQUE KEY `idx_user_num` (`user_num`)
  102. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
  104. -- ----------------------------
  105. -- Records of t_user_0004

所有代码创建完毕

测试类 TestUserService.java
  1. package com.sub.dt.service;
  3. import com.sub.common.GsonUtils;
  4. import com.sub.dt.dbRouting.DbContextHolder;
  5. import com.sub.dt.pojo.User;
  6. import org.junit.Test;
  7. import org.junit.runner.RunWith;
  8. import org.springframework.beans.factory.annotation.Autowired;
  9. import org.springframework.test.context.ContextConfiguration;
  10. import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
  12. /**
  13.  * Created by supers on 2017/3/9.
  14.  */
  16. @RunWith(SpringJUnit4ClassRunner.class)
  17. @ContextConfiguration("/spring-config.xml")
  18. public class TestUserService {
  20.     @Autowired
  21.     private IUserService userService;
  23.     /**
  24.      * @Description 测试分库分表插入
  25.      * @Autohr supers【weChat:13031016567】
  26.      */
  27.     @Test
  28.     public void testInsertUser(){
  29.         User user = new User();
  30.         user.setUserNum("wergsgdf3243");
  31.         user.setUserName("admin");
  32.         user.setAge(23);
  33.         user.setPassword("adf23");
  34.         int re = userService.insertUser(user);
  35.         System.out.println(DbContextHolder.getDbKey()+"库 "+DbContextHolder.getTableIndex()+"表 的插入结果:"+GsonUtils.toJson(re));
  36.     }
  38.     /**
  39.      * @Description 测试分库分表删除
  40.      * @Autohr supers【weChat:13031016567】
  41.      */
  42.     @Test
  43.     public void testDeleteByuserNum(){
  44.         User user = new User();
  45.         user.setUserNum("wergsgdf3243");
  46.         int re = userService.deleteByuserNum(user);
  47.         System.out.println(DbContextHolder.getDbKey()+"库 "+DbContextHolder.getTableIndex()+"表 的删除结果:"+GsonUtils.toJson(re));
  48.     }
  51.     /**
  52.      * @Description 测试分库分表修改
  53.      * @Autohr supers【weChat:13031016567】
  54.      */
  55.     @Test
  56.     public void testupdateByUserNum(){
  57.         User user = new User();
  58.         user.setUserNum("wergsgdf3243");
  59.         user.setAge(34);
  60.         int re = userService.updateByUserNum(user);
  61.         System.out.println(DbContextHolder.getDbKey()+"库 "+DbContextHolder.getTableIndex()+"表 的更新结果:"+GsonUtils.toJson(re));
  62.     }
  64.     /**
  65.      * @Description 测试分库分表查询
  66.      * @Autohr supers【weChat:13031016567】
  67.      */
  68.     @Test
  69.     public void testQueryUserByNum(){
  70.         User user = new User();
  71.         user.setId(1);
  72.         user.setUserNum("wergsgdf3243");
  73.         User userDb = userService.selectByUserNum(user);
  74.         System.out.println(DbContextHolder.getDbKey()+"库 "+DbContextHolder.getTableIndex()+"表 的查询结果:"+GsonUtils.toJson(userDb));
  75.     }
  76. }


最后奉上 log4j.properties配置

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

 
 
 
 
 





本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。





打开APP，阅读全文并永久保存
查看更多类似文章







猜你喜欢












类似文章




【热】打开小程序，算一算2024你的财运
SSM框架整合（IntelliJ IDEA + maven + Spring + SpringMVC + MyBatis）
java ssh maven pom文件
Dubbo与Zookeeper、SpringMVC整合和使用（负载均衡、容错）配置详解，dubbo-admin 下载
REST方式的CXF WebService实现
maven搭建springmvc+spring+mybatis实例
Dubbo学习之简单环境搭建



更多类似文章 >>













生活服务


















热点新闻


























留言交流


回顶部


联系我们










分享
收藏
导长图
关注

下载文章






绑定账号成功
后续可登录账号畅享VIP特权！


如果VIP功能使用有故障，
可点击这里联系客服！
联系客服