Springboot 整合mybatis，实现多数据读写分离分库分表

2023-09-11 阅读 19 评论 0

摘要：mybatis多数据源配置（本文示例为两个），方便实现数据库的读写分离，分库分表功能本文基于springboot2进行的配置，如版本为springboot1系列则需修改yml的配置（在文末附带） mybatis实现多数据源的主要逻辑是：将这两个

mybatis多数据源配置（本文示例为两个），方便实现数据库的读写分离，分库分表功能

本文基于springboot2进行的配置，如版本为springboot1系列则需修改yml的配置（在文末附带）

mybatis实现多数据源的主要逻辑是：
将这两个数据源分别注入Spring容易中，通过mybatis的配置为aop事务管理器和mybatis手动指定一个明确的数据源；
通过threadpool将数据源设置到每一个线程中（这样子可以防止同一线程执行不同的数据源造成脏数据的产生）；
设置aop事务注解，实现借助自定义注解（readonly）注解的方式来控制程序在读取数据的时候操作指定数据源。

第一步提前定义一个枚举类型，用于区分不同数据源

public enum DBTypeEnum {MASTER, SLAVE1;}

bootstraptable获取所有数据？第二步定义自定义切换路由

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.lang.Nullable;
//获取路由key,根据路由自动切换类型
public class MyRoutingDataSource extends AbstractRoutingDataSource {@Nullable//可以传入空值        //不能传入空值NotNull@Overrideprotected Object determineCurrentLookupKey() {//获取操作数据库参数类型return DBContextHolder.getDBType();}}

第三步：yml配置数据源，叫个druid管理

#指定数据源druid:type: com.alibaba.druid.pool.DruidDataSource
#主库master:url: jdbc:mysql://192.168.1.199:3306/mail-1.0?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=CONVERT_TO_NULL&useUnicode=true&userSSL=false&serverTimezone=GMT%2B8driver-class-name: com.mysql.cj.jdbc.Driverusername: rootpassword: rootinitialSize: 5minIdle: 1maxActive: 100maxWait: 60000timeBetweenEvictionRUnMillis: 60000minEvictableIdeleTimeMillis: 300000validationQuery: SELECT 1 FROM DUALtestWhileIdle: truetestOnBorrow: falsetestOnReturn: falsepoolPreparedStatements: truemaxPoolPreparedStatementPerConnectionSize: 20filters: commons-log.connection-logger-name: stat,wall,log4juseGlobalDataSourceStat: true 
#从库slave:  url: jdbc:mysql://192.168.1.199:3306/mail-1.0?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=CONVERT_TO_NULL&useUnicode=true&userSSL=false&serverTimezone=GMT%2B8driver-class-name: com.mysql.cj.jdbc.Driverusername: rootpassword: rootinitialSize: 5minIdle: 1maxActive: 100maxWait: 60000timeBetweenEvictionRUnMillis: 60000minEvictableIdeleTimeMillis: 300000validationQuery: SELECT 1 FROM DUALtestWhileIdle: truetestOnBorrow: falsetestOnReturn: falsepoolPreparedStatements: truemaxPoolPreparedStatementPerConnectionSize: 20filters: commons-log.connection-logger-name: stat,wall,log4juseGlobalDataSourceStat: true

mybatis中间表，第四步：读取配置文件，将数据源注入到spring容器中，


import java.sql.SQLException;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;import javax.sql.DataSource;import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.transaction.annotation.EnableTransactionManagement;import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;/**** spring中配置多数据源* * @author Administrator**/
@Configuration
@EnableTransactionManagement // 开启事务
public class DataSourceConfiguration {private static Logger logger = LoggerFactory.getLogger(DataSourceConfiguration.class);@Value("${druid.type}")private Class<? extends DataSource> dataSourceType;// 主数据源注入到bean中@Bean("masterDataSource")@Primary // 相同类型数据源，优先选择该数据源作为连接对象@ConfigurationProperties(prefix = "druid.master") // yml中对应属性前缀public DataSource masterDataSource() throws SQLException {DataSource masterDataSource = DataSourceBuilder.create().type(dataSourceType).build();logger.info("==== MASTER ====" + masterDataSource);return masterDataSource;}// 从数据源注入到bean中@Bean("slaveDataSource")@ConfigurationProperties(prefix = "druid.slave") // yml中对应属性前缀public DataSource slaveDataSource() {DataSource slaveDataSource = DataSourceBuilder.create().type(dataSourceType).build();logger.info("==== SLAVE ====" + slaveDataSource);return slaveDataSource;}/*// 动态路由数据源，根据需求转换具体使用哪个数据源@SuppressWarnings("unchecked")@Beanpublic DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,@Qualifier("slaveDataSource") DataSource slave1DataSource) {//mybatis提供的底层map类SoftHashMap targetDataSources = new ClassLoaderRepository.SoftHashMap();//设置键值对，区分使用哪个数据源targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource);//继承了AbstractRoutingDataSource的类MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);//设置默认的数据源myRoutingDataSource.setTargetDataSources(targetDataSources);//装入存有主从数据源的mapreturn myRoutingDataSource;}*/// 下面的1和2是配置Druid的监控// 自己手写的servlet注入到spring容器中执行方法// 将druid的servlet注入到spring容器中@Beanpublic ServletRegistrationBean druidServlet() {/** 方式1，暂时不用，使用方式2 ServletRegistrationBean<StatViewServlet> reg = new* ServletRegistrationBean<StatViewServlet>(); reg.setServlet(new* StatViewServlet()); reg.addUrlMappings("/druid/*");//过滤时候开放地址* reg.addInitParameter("allow","");//默认就是允许所有访问* reg.addInitParameter("deny","");//黑名单的IP* logger.info("druid console manager init"); return reg;*/ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");Map<String, String> initParams = new HashMap<>();initParams.put("loginUsername", "admin");// 登录druid监控的账户initParams.put("loginPassword", "admin");// 登录druid监控的密码//initParams.put("allow", "");// 默认就是允许所有访问initParams.put("allow", "192.168.233.1");// 默认就是允许所有访问initParams.put("deny", "192.168.233.111");// 黑名单的IPbean.setInitParameters(initParams);logger.info("druid console manager init");return bean;}// druid配置web监听filter
@Bean
public FilterRegistrationBean<WebStatFilter> filterRegistrationBean() {FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<WebStatFilter>();filterRegistrationBean.setFilter(new WebStatFilter());filterRegistrationBean.setUrlPatterns(Arrays.asList("/*"));// setUrlPatterns()将一个arrays转换为listMap<String, String> initParams = new HashMap<>();initParams.put("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");filterRegistrationBean.setInitParameters(initParams);//未使用和上一步同理//filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");logger.info("druid file register : {}" + filterRegistrationBean);return filterRegistrationBean;}}

第五步：同时存在多个数据源，为mybatis设置一个明确的数据源

import java.util.List;import javax.annotation.Resource;
import javax.sql.DataSource;import org.apache.ibatis.mapping.DatabaseIdProvider;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.aspectj.apache.bcel.util.ClassLoaderRepository;
import org.aspectj.apache.bcel.util.ClassLoaderRepository.SoftHashMap;
import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer;
import org.mybatis.spring.boot.autoconfigure.MybatisAutoConfiguration;
import org.mybatis.spring.boot.autoconfigure.MybatisProperties;
import org.springframework.beans.factory.ObjectProvider;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ResourceLoader;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
//由于Spring容器中现在有2个数据源，所以我们需要为事务管理器和MyBatis手动指定一个明确的数据源
//@EnableTransactionManagement
@Configuration
@AutoConfigureAfter(value={DataSourceConfiguration.class})//配置数据源当DataSourceConfiguration加载完成之后加载
public class MyBatisConfig extends MybatisAutoConfiguration{//继承mybatisautoconfiguration功能类似mybatis.xmlpublic MyBatisConfig(MybatisProperties properties, ObjectProvider<Interceptor[]> interceptorsProvider,ResourceLoader resourceLoader, ObjectProvider<DatabaseIdProvider> databaseIdProvider,ObjectProvider<List<ConfigurationCustomizer>> configurationCustomizersProvider) {super(properties, interceptorsProvider, resourceLoader, databaseIdProvider, configurationCustomizersProvider);}/*@Resource(name = "myRoutingDataSource")private DataSource myRoutingDataSource;*/@Resource(name = "slaveDataSource")private DataSource slaveDataSource;@Resource(name = "masterDataSource")private DataSource masterDataSource;//sqlsessionfactory管理mybatis选择哪一个，现有factory后注入数据源@Bean(name = "sqlSessionFactory")public SqlSessionFactory sqlSessionFactory() throws Exception {/*SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();sqlSessionFactoryBean.setDataSource(myRoutingDataSource);sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));return sqlSessionFactoryBean.getObject();*/return super.sqlSessionFactory(myRoutingDataSource());}public AbstractRoutingDataSource myRoutingDataSource() {//mybatis提供的底层map类SoftHashMap targetDataSources = new ClassLoaderRepository.SoftHashMap();//设置键值对，区分使用哪个数据源targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);targetDataSources.put(DBTypeEnum.SLAVE1, slaveDataSource);//继承了AbstractRoutingDataSource的类MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);//设置默认的数据源myRoutingDataSource.setTargetDataSources(targetDataSources);//装入存有主从数据源的mapreturn myRoutingDataSource;}}

java读取clob字段。第六步：设置线程池为每个线程都添加操作数据源信息，防止同一线程操作不同数据源产生脏数据

import java.util.concurrent.atomic.AtomicInteger;import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
//接下来，通过ThreadLocal将数据源设置到每个线程上下文中
public class DBContextHolder {public static Logger logger = LoggerFactory.getLogger(DBContextHolder.class);//为防止由于线程不安全造成服务器异常，默认是当前线程，返回类型为DBTypeEnum中的一种private static final ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<DBTypeEnum>();private static final AtomicInteger counter = new AtomicInteger(-1);//为线程池变量设置一个类型public static void setDBType(DBTypeEnum dbType) {if(dbType==null) {//线程异常，抛出空指针throw new NullPointerException();}contextHolder.set(dbType);}//获取线程类型public static DBTypeEnum getDBType() {//若得到类型为null返回master主类型，否则返回得到得类型return contextHolder.get()==null?DBTypeEnum.MASTER:contextHolder.get();}//清除类型，不影响下一个线程操作public static void clearDBType() {contextHolder.remove();}public static void master() {setDBType(DBTypeEnum.MASTER);logger.info("切换到master");}public static void slave() {logger.info("切换到slave数据源");//  轮询，切换数据库，            多次查询？？？int index = counter.getAndIncrement() % 2;if (counter.get() > 9999) {counter.set(-1);}if (index == 0) {setDBType(DBTypeEnum.SLAVE1);logger.info("执行slave1数据源");}}
}

第七步添加aop注解


import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.Ordered;
import org.springframework.stereotype.Component;
//默认情况下，所有的查询都走从库，插入/修改/删除走主库。我们通过方法名来区分操作类型（CRUD)
@Aspect    //Spring只支持XML方式而没有实现注解的方式（也叫AspectJ方式）的AOP，所以要使用@Aspect注解，只能引入AspectJ相关的 jar 包 aopalliance-1.0.jar 和 aspectjweaver.jar，这个坑把我给坑惨了。
@Component ///（把普通pojo实例化到spring容器中，相当于配置文件中的<bean id="" class=""/>）
public class DataSourceAop implements Ordered {public static Logger logger = LoggerFactory.getLogger(DataSourceAop.class);@Around(value = "@annotation(readOnlyConnection)")//将注解添加到readonluconnection实体类上public Object proceed(ProceedingJoinPoint proceedingJoinPoint,ReadOnlyConnection readOnlyConnection) throws Throwable{try {logger.info("---------set database connection 2 read only---------");//强制让其读取只读从数据库DBContextHolder.setDBType(DBTypeEnum.SLAVE1);Object result = proceedingJoinPoint.proceed();//proceed让注解上的方法执行完毕return result;} catch (Exception e) {e.printStackTrace();logger.info("---------切换只读数据库时发生异常    ---------");return null;}finally {DBContextHolder.clearDBType();logger.info("---------cleat DataBaseType     ---------");}}@Overridepublic int getOrder() {// TODO Auto-generated method stubreturn 0;}
/*@Pointcut("!@annotation(com.springboot.mariadb.annotation.Master) " +"&& (execution(* com.cjs.example.service..*.select*(..)) " +"|| execution(* com.cjs.example.service..*.get*(..)))")public void readPointcut() {}@Pointcut("@annotation(com.springboot.mariadb.annotation.Master) " +"|| execution(* com.cjs.example.service..*.insert*(..)) " +"|| execution(* com.cjs.example.service..*.add*(..)) " +"|| execution(* com.cjs.example.service..*.update*(..)) " +"|| execution(* com.cjs.example.service..*.edit*(..)) " +"|| execution(* com.cjs.example.service..*.delete*(..)) " +"|| execution(* com.cjs.example.service..*.remove*(..))")public void writePointcut() {}@Before("readPointcut()")public void read() {DBContextHolder.slave();}@Before("writePointcut()")public void write() {DBContextHolder.master();}//* 另一种写法：if...else...  判断哪些需要读从数据库，其余的走主数据库//    @Before("execution(* com.cjs.example.service.impl.*.*(..))")
//    public void before(JoinPoint jp) {
//        String methodName = jp.getSignature().getName();
//
//        if (StringUtils.startsWithAny(methodName, "get", "select", "find")) {
//            DBContextHolder.slave();
//        }else {
//            DBContextHolder.master();
//        }
//    }*/
}

第八步设置aop注解


import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;//注解类，用于强制读取从
//有一般情况就有特殊情况，特殊情况是某些情况下我们需要强制读主库，
//针对这种情况，我们定义一个主键，用该注解标注的就读主库
@Target({ElementType.METHOD,ElementType.TYPE})//仅用于方法上的注解,,,，，默认类型为TYPE类型
@Retention(RetentionPolicy.RUNTIME)//运行策略，在运行时执行
public @interface ReadOnlyConnection {}

第九步禁止spring启动自带的DataSource数据源

添加此配置类到springapplication同目录下

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Configurable;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;@EnableWebMvc        //启动springmvc
@Configurable    //配置类
//将spring boot自带的DataSourceAutoConfiguration禁掉，因为它会读取application.properties文件的spring.datasource.*属性并自动配置单数据源
@SpringBootApplication(exclude = DataSourceAutoConfiguration.class,scanBasePackages="com.example.demo.*")//全局扫描
@MapperScan(basePackages="com.example.demo.mapper")//scan DAO
public class MainConfig {}

调用注解类操作数据源示例（添加@ReadOnlyConnection注解即可，其他均无需考虑）

  @AutowiredHappyMapper happymapper;@Override@ReadOnlyConnectionpublic Msg DeptContent() {PageHelper.startPage(1,3);List<Department> dp = happymapper.selectAll();//PageInfo<Department> pageInfo = new PageInfo<Department>(dp,3);for( Department s : dp) {System.out.println("Department:"+s.getName());}System.out.println("Department:---------");return this.selectAllStatus();}