spring+mybatis分庫分表

自定義注解

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();

    /**
     * 規(guī)則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;

/**
 * 工具類，存放當前線程數(shù)據(jù)源key和表名后綴
 * 使用treadLocal的方式來保證線程安全
 * @author donglq
 * @date 2017/10/3 22:56
 */
public class DBContext {
    
    /**數(shù)據(jù)庫邏輯名**/
    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;

/**
 * 應用上下文工具類，獲取規(guī)則實例的時候用
 * @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;
    }
}

規(guī)則接口

package com.dlq.blog.db.interfaces;

import com.dlq.blog.db.DBRouteType;

import java.util.List;

/**
 * 分庫分表規(guī)則
 * @author donglq
 * @date 2017/10/4 20:33
 */
public interface DBRule {

    /**
     * 數(shù)據(jù)源邏輯名
     * @return
     */
    List<String> getDbKeys();

    /**
     * 庫數(shù)量
     * @return
     */
    int getDbCount();

    /**
     * 每個庫中表數(shù)量
     * @return
     */
    int getTableCount();

    /**
     * 表后綴樣式，如_00
     * @return
     */
    String getTableSuffixStyle();

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

    /**
     * 根據(jù)參數(shù)值獲取用來計算分庫分表的數(shù)值
     * @param resource
     * @return
     */
    int getResourceCode(Object resource);

}

路由

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

/**
 * DB路由接口，通過調用該接口來自動判斷數(shù)據(jù)位于哪個庫和表
 * @author donglq
 * @date 2017/10/3 23:51
 */
public interface DBRouter {

    public String doRouteByResource(DBRule dbRule, Object resource);

}
實現(xiàn)
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路由實現(xiàn)，確定庫名和表名
 * @author donglq
 * @date 2017/10/3 23:53
 */
@Service("dBRouter")
public class DbRouterImpl implements DBRouter {

    /**
     * 根據(jù)dbRule和resource確定庫表，并存入上下文中
     * @param dbRule 分庫分表的一些規(guī)則信息
     * @param resource 用來做分庫分表規(guī)則的字段值
     * @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攔截器：根據(jù)方法參數(shù)中的某個字段來判斷這條記錄是存在幾庫幾表
 * @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;
        //根據(jù)JoinPoint jp 獲取方法名稱和參數(shù)
        Method method = getMethod(jp);
        //獲取注解
        Router router = method.getAnnotation(Router.class);
        //做路由的字段
        String routeField = router.routerField();
        //規(guī)則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();
    }

}

動態(tài)數(shù)據(jù)源

package com.dlq.blog.db;

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

/**
 * 動態(tài)數(shù)獲取當前據(jù)源
 * @author donglq
 * @date 2017/10/3 22:59
 */
public class DynamicDataSource extends AbstractRoutingDataSource {

    /**
     * 獲取當前數(shù)據(jù)源
     * @return
     */
    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        return DBContext.getDbKey();
    }
}

測試

規(guī)則實現(xiàn)
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;

/**
 * 用戶分庫分表規(guī)則
 * @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;
    }

    /**
     * 根據(jù)身份證前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>

    <!--配置多數(shù)據(jù)源-->
    <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>

    <!-- 動態(tài)獲取數(shù)據(jù)源 -->
    <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>
最后編輯于 ：
?著作權歸作者所有,轉載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內(nèi)容

  • Spring Cloud為開發(fā)人員提供了快速構建分布式系統(tǒng)中一些常見模式的工具（例如配置管理，服務發(fā)現(xiàn)，斷路器，智...
    卡卡羅2017閱讀 136,639評論 19贊 139
  • 上一篇介紹了讀寫分離，這一篇來說一下分庫分表，廢話不多說直接上代碼。1.首先配置文件,這里配置三個庫 在每個庫下邊...
    布衣小販java閱讀 6,130評論 2贊 8
  • Android 自定義View的各種姿勢1 Activity的顯示之ViewRootImpl詳解 Activity...
    passiontim閱讀 179,172評論 25贊 708
  • 一聲驚雷，瞬間冷卻了整座熱到讓人昏迷的城市。 窗外雨勢很大，此刻的我陷入獨屬于夏日的昏睡。雨聲入夢，枕頭上已經(jīng)潮濕...
    深夜雪碧閱讀 259評論 0贊 0
  • 2010年搬到這個小區(qū)的時候，房子前面有一顆古樹，是銀杏樹，這是當年的皇家園林，據(jù)說我們這個樓當年的位置是個大殿，...
    開心的靈通閱讀 182評論 0贊 0

友情鏈接更多精彩內(nèi)容