1200字范文 > Springboot 整合mybatis 实现多数据读写分离分库分表

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

时间：2023-07-02 06:30:03

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

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

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

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

public enum DBTypeEnum {MASTER, SLAVE1;}

第二步定义自定义切换路由

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

第四步：读取配置文件，将数据源注入到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@Beanpublic 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;}}

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

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.ponent;//默认情况下，所有的查询都走从库，插入/修改/删除走主库。我们通过方法名来区分操作类型（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 DAOpublic 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();}