美文网首页
spring+mybatis分库分表

spring+mybatis分库分表

作者: donglq | 来源:发表于2017-10-04 22:37 被阅读0次

    自定义注解

    package com.dlq.blog.db.annotation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * 自定义注解
 * @author donglq
 * @date 2017/10/3 23:32
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public @interface Router {

    /**
     * 做路由的字段名
     * @return
     */
    String routerField();

    /**
     * 规则bean名
     * @return
     */
    String ruleBeanName();

}

    路由类型

    package com.dlq.blog.db;

/**
 * 路由类型
 * @author donglq
 * @date 2017/10/4 20:34
 */
public enum DBRouteType {
    DB(1, "分库"),
    TABLE(2, "分表"),
    DBTABLE(3, "分库分表");

    DBRouteType(int code, String desc) {
        this.code = code;
        this.desc = desc;
    }

    public int code;

    public String desc;

}

    上下文工具类

    package com.dlq.blog.db;

/**
 * 工具类，存放当前线程数据源key和表名后缀
 * 使用treadLocal的方式来保证线程安全
 * @author donglq
 * @date 2017/10/3 22:56
 */
public class DBContext {
    
    /**数据库逻辑名**/
    private static final ThreadLocal<String> dbKeyHolder = new ThreadLocal<String>();
    
    /**表明后缀**/
    private static final ThreadLocal<String> tableSuffixHolder = new ThreadLocal<String>();

    public static void setDbKey(String dbKey) {
        dbKeyHolder.set(dbKey);
    }

    public static String getDbKey() {
        return dbKeyHolder.get();
    }

    public static void clearDbKey() {
        dbKeyHolder.remove();
    }

    public static void setTableSuffix(String tableIndex){
        tableSuffixHolder.set(tableIndex);
    }

    public static String getTableSuffix(){
        return tableSuffixHolder.get();
    }
    public static void clearTableSuffix(){
        tableSuffixHolder.remove();
    }

}

    应用上下文

    package com.dlq.blog.db;

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

/**
 * 应用上下文工具类，获取规则实例的时候用
 * @author donglq
 * @date 2017/10/4 21:30
 */
@Component
public class DBApplicationContext implements ApplicationContextAware {

    private ApplicationContext applicationContext;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        this.applicationContext = applicationContext;
    }

    public ApplicationContext getApplicationContext() {
        return applicationContext;
    }
}

    规则接口

    package com.dlq.blog.db.interfaces;

import com.dlq.blog.db.DBRouteType;

import java.util.List;

/**
 * 分库分表规则
 * @author donglq
 * @date 2017/10/4 20:33
 */
public interface DBRule {

    /**
     * 数据源逻辑名
     * @return
     */
    List<String> getDbKeys();

    /**
     * 库数量
     * @return
     */
    int getDbCount();

    /**
     * 每个库中表数量
     * @return
     */
    int getTableCount();

    /**
     * 表后缀样式，如_00
     * @return
     */
    String getTableSuffixStyle();

    /**
     * 路由类型：分库、分表、分库分表
     * @return
     */
    DBRouteType getDBRouteType();

    /**
     * 根据参数值获取用来计算分库分表的数值
     * @param resource
     * @return
     */
    int getResourceCode(Object resource);

}

    路由

    接口
    package com.dlq.blog.db.interfaces;

/**
 * DB路由接口，通过调用该接口来自动判断数据位于哪个库和表
 * @author donglq
 * @date 2017/10/3 23:51
 */
public interface DBRouter {

    public String doRouteByResource(DBRule dbRule, Object resource);

}
    实现
    package com.dlq.blog.db;

import com.dlq.blog.db.interfaces.DBRouter;
import com.dlq.blog.db.interfaces.DBRule;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;

import java.text.DecimalFormat;

/**
 * DB路由实现，确定库名和表名
 * @author donglq
 * @date 2017/10/3 23:53
 */
@Service("dBRouter")
public class DbRouterImpl implements DBRouter {

    /**
     * 根据dbRule和resource确定库表，并存入上下文中
     * @param dbRule 分库分表的一些规则信息
     * @param resource 用来做分库分表规则的字段值
     * @return
     */
    @Override
    public String doRouteByResource(DBRule dbRule, Object resource) {
        String dbKey = null;
        int resourceCode = dbRule.getResourceCode(resource);
        if (dbRule.getDbKeys() != null && dbRule.getDbCount() > 0) {
            long dbIndex = 0;
            long tbIndex = 0;
            if (dbRule.getDBRouteType() == DBRouteType.DBTABLE && dbRule.getTableCount() > 0) {
                //分库分表
                tbIndex = resourceCode % (dbRule.getDbCount() * dbRule.getTableCount());
                String tableIndex = getFormateTableIndex(dbRule.getTableSuffixStyle(), tbIndex);
                DBContext.setTableSuffix(tableIndex);
                dbIndex = tbIndex % dbRule.getDbCount();
                dbKey = dbRule.getDbKeys().get(Long.valueOf(dbIndex).intValue());
                DBContext.setDbKey(dbKey);
            } else if (dbRule.getDBRouteType() == DBRouteType.DB) {
                //分库
                DBContext.setTableSuffix("");
                dbIndex = resourceCode % dbRule.getDbCount();
                dbKey = dbRule.getDbKeys().get(Long.valueOf(dbIndex).intValue());
                DBContext.setDbKey(dbKey);
            } else if (dbRule.getDBRouteType() == DBRouteType.TABLE) {
                //分表
                tbIndex = resourceCode % dbRule.getTableCount();
                String tableIndex = getFormateTableIndex(dbRule.getTableSuffixStyle(), tbIndex);
                DBContext.setTableSuffix(tableIndex);
                DBContext.setDbKey("");
            }
        }
        return dbKey;
    }

    /**
     * @Description 格式化表名后缀，将1格式化为_01
     */
    private static String getFormateTableIndex(String style, long tbIndex) {
        String tableIndex = null;
        DecimalFormat df = new DecimalFormat();
        if (StringUtils.isEmpty(style)) {
            return "";
        }
        df.applyPattern(style);
        tableIndex = df.format(tbIndex);
        return tableIndex;
    }

}

    AOP拦截器

    package com.dlq.blog.db;

import com.dlq.blog.db.annotation.Router;
import com.dlq.blog.db.interfaces.DBRouter;
import com.dlq.blog.db.interfaces.DBRule;
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.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.lang.reflect.Method;

/**
 * AOP拦截器：根据方法参数中的某个字段来判断这条记录是存在几库几表
 * @author donglq
 * @date 2017/10/3 23:37
 */
@Aspect
@Service
public class DBRouterInterceptor {

    @Autowired
    private DBRouter dBRouter;

    @Autowired
    private DBApplicationContext dbApplicationContext;

    @Pointcut("@annotation(com.dlq.blog.db.annotation.Router)")
    public void aopPoint() {
    }

    @Before("aopPoint()")
    public Object doRoute(JoinPoint jp) throws Throwable {
        System.out.println("aop before");
        boolean result = true;
        //根据JoinPoint jp 获取方法名称和参数
        Method method = getMethod(jp);
        //获取注解
        Router router = method.getAnnotation(Router.class);
        //做路由的字段
        String routeField = router.routerField();
        //规则bean名称
        String ruleBeanName = router.ruleBeanName();
        DBRule dbRule = dbApplicationContext.getApplicationContext().getBean(ruleBeanName, DBRule.class);
        Object[] args = jp.getArgs();
        if (args != null && args.length > 0) {
            for (int i = 0; i < args.length; i++) {
                //通过反射得到对象args[i] 的 routeField 字段的值
                String routeFieldValue = BeanUtils.getProperty(args[i], routeField);
                if (StringUtils.isNotEmpty(routeFieldValue)) {
                    dBRouter.doRouteByResource(dbRule ,routeFieldValue);
                    break;
                }
            }
        }
        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();
    }

}

    动态数据源

    package com.dlq.blog.db;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.lang.Nullable;

/**
 * 动态数获取当前据源
 * @author donglq
 * @date 2017/10/3 22:59
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

    /**
     * 获取当前数据源
     * @return
     */
    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        return DBContext.getDbKey();
    }
}

    测试

    规则实现
    package com.dlq.blog.db.rules;

import com.dlq.blog.db.DBRouteType;
import com.dlq.blog.db.interfaces.DBRule;
import org.springframework.stereotype.Component;

import java.util.ArrayList;
import java.util.List;

/**
 * 用户分库分表规则
 * @author donglq
 * @date 2017/10/4 21:21
 */
@Component("userRule")
public class UserRule implements DBRule {

    @Override
    public List<String> getDbKeys() {
        List<String> list = new ArrayList<>(4);
        list.add("user_00");
        list.add("user_01");
        list.add("user_02");
        list.add("user_03");
        return list;
    }

    @Override
    public int getDbCount() {
        return 4;
    }

    @Override
    public int getTableCount() {
        return 2;
    }

    @Override
    public String getTableSuffixStyle() {
        return "_00";
    }

    @Override
    public DBRouteType getDBRouteType() {
        return DBRouteType.DBTABLE;
    }

    /**
     * 根据身份证前6位分库分表
     * @param resource
     * @return
     */
    @Override
    public int getResourceCode(Object resource) {
        return Integer.valueOf(((String)resource).substring(0, 6));
    }
}
    DAO接口
    package com.dlq.blog.dao;

import org.apache.ibatis.annotations.Param;

/**
 * @author donglq
 * @date 2017/10/4 10:13
 */
public interface UserDao {

    Object insert(@Param("user") User user);

    User select(@Param("user") User user);

}

package com.dlq.blog.dao;

/**
 * @author donglq
 * @date 2017/10/4 10:15
 */
public class User {

    private int id;

    private String firstname;

    private String lastname;

    private int gender;

    private String idcard;

    private String address;

    private String tableIndex;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getFirstname() {
        return firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public int getGender() {
        return gender;
    }

    public void setGender(int gender) {
        this.gender = gender;
    }

    public String getIdcard() {
        return idcard;
    }

    public void setIdcard(String idcard) {
        this.idcard = idcard;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getTableIndex() {
        return tableIndex;
    }

    public void setTableIndex(String tableIndex) {
        this.tableIndex = tableIndex;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", firstname='" + firstname + '\'' +
                ", lastname='" + lastname + '\'' +
                ", gender=" + gender +
                ", idcard='" + idcard + '\'' +
                ", address='" + address + '\'' +
                ", tableIndex='" + tableIndex + '\'' +
                '}';
    }
}
    服务类
    package com.dlq.blog.service;

import com.dlq.blog.dao.User;
import com.dlq.blog.dao.UserDao;
import com.dlq.blog.db.DBContext;
import com.dlq.blog.db.annotation.Router;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;

/**
 * @author donglq
 * @date 2017/10/4 12:26
 */
@Service
public class UserService {

    @Resource
    UserDao userDao;

    @Router(routerField = "idcard", ruleBeanName = "userRule")
    public String insert(User user) {
        user.setTableIndex(DBContext.getTableSuffix());
        userDao.insert(user);
        return "success";
    }

    @Router(routerField = "idcard", ruleBeanName = "userRule")
    public User get(User user) {
        user.setTableIndex(DBContext.getTableSuffix());
        return userDao.select(user);
    }

}
    配置文件
    <?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:db.properties"/>
    </bean>

    <!--配置多数据源-->
    <bean id="user_00" class="org.apache.ibatis.datasource.pooled.PooledDataSource">
        <property name="driver" value="${driver}"/>
        <property name="url" value="${jdbc.mysql.user_00}"/>
        <property name="username" value="${jdbc.mysql.username}"/>
        <property name="password" value="${jdbc.mysql.password}"/>
    </bean>

    <bean id="user_01" class="org.apache.ibatis.datasource.pooled.PooledDataSource">
        <property name="driver" value="${driver}"/>
        <property name="url" value="${jdbc.mysql.user_01}"/>
        <property name="username" value="${jdbc.mysql.username}"/>
        <property name="password" value="${jdbc.mysql.password}"/>
    </bean>

    <bean id="user_02" class="org.apache.ibatis.datasource.pooled.PooledDataSource">
        <property name="driver" value="${driver}"/>
        <property name="url" value="${jdbc.mysql.user_02}"/>
        <property name="username" value="${jdbc.mysql.username}"/>
        <property name="password" value="${jdbc.mysql.password}"/>
    </bean>

    <bean id="user_03" class="org.apache.ibatis.datasource.pooled.PooledDataSource">
        <property name="driver" value="${driver}"/>
        <property name="url" value="${jdbc.mysql.user_03}"/>
        <property name="username" value="${jdbc.mysql.username}"/>
        <property name="password" value="${jdbc.mysql.password}"/>
    </bean>

    <!-- 动态获取数据源 -->
    <bean id="mysqlDynamicDataSource" class="com.dlq.blog.db.DynamicDataSource">
        <property name="targetDataSources">
            <!-- 标识符类型 -->
            <map>
                <entry key="user_00" value-ref="user_00"/>
                <entry key="user_01" value-ref="user_01"/>
                <entry key="user_02" value-ref="user_02"/>
                <entry key="user_03" value-ref="user_03"/>
            </map>
        </property>
    </bean>

    <!--事务-->
    <bean id="transactionManager"
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="mysqlDynamicDataSource"></property>
    </bean>

    <bean id="transactionTemplate" class="org.springframework.transaction.support.TransactionTemplate">
        <property name="transactionManager" ref="transactionManager"></property>
        <property name="propagationBehaviorName" value="PROPAGATION_REQUIRED"></property>
    </bean>

    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="mysqlDynamicDataSource"/>
        <!-- 自动扫描mapping.xml文件 -->
        <property name="mapperLocations" value="classpath*:mybatis/mapper/*.xml"></property>
    </bean>

    <!-- DAO接口所在包名，Spring会自动查找其下的类 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.dlq.blog.dao"/>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"></property>
    </bean>

</beans>

    相关文章

      网友评论

          本文标题：spring+mybatis分库分表

          本文链接：https://www.haomeiwen.com/subject/iffhyxtx.html

          延伸阅读

          深度阅读

          • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

          栏目导航

          热点阅读

          关于我们|服务条款|联系我们|spring+mybatis分库分表|投稿指南|网站地图|RSS订阅|排版工具|手机版

          提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

          Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

          备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

          本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

          所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！