spring+mybatis分库分表
2017-10-04 本文已影响0人
donglq
自定义注解
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>
