读写分离常见有俩种方式
1 第一种方式比较常用就是定义2个数据库连接,一个是Master,另一个是Slave。更新数据时我们取Master,查询数据时取Slave。太过简单不做介绍。
2 第二种方数据源式动态切换,将数据源动态织入到程序中,达到动态选择读取主库还是从库的目的。主要使用的技术是:annotation,Spring AOP ,反射。下面介绍这种方式
首先创建DatabaseConfiguration
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.testdatasource.third.configuration.datasource; import com.alibaba.druid.pool.DruidDataSource; import com.baomidou.mybatisplus.MybatisConfiguration; import com.baomidou.mybatisplus.entity.GlobalConfiguration; import com.baomidou.mybatisplus.mapper.LogicSqlInjector; import com.baomidou.mybatisplus.spring.MybatisSqlSessionFactoryBean; import com.testdatasource.common.enums.DatasourceType; import com.testdatasource.third.configuration.properties.DruidProperties; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.type.JdbcType; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Qualifier; import org.springframework.boot.autoconfigure.EnableAutoConfiguration; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.context.ApplicationContext; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.context.annotation.Primary; import org.springframework.core.io.support.PathMatchingResourcePatternResolver; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; import javax.sql.DataSource; import java.util.HashMap; import java.util.Map; @Configuration @EnableAutoConfiguration public class DatabaseConfiguration { @Autowired private ApplicationContext appContext; @Autowired private DruidProperties druidProperties; @Bean(name = "readDatasource") @Primary @ConfigurationProperties(prefix = "read.datasource") public DataSource readDatasource() { return getReadDruidDataSource(); } /** * 写入数据源连接池配置 */ @Bean(name = "writeDatasource") @ConfigurationProperties(prefix = "write.datasource") public DataSource writeDatasource() { return getWriteDruidDataSource(); } private DruidDataSource getReadDruidDataSource() { DruidDataSource dataSource = new DruidDataSource(); druidProperties.configR(dataSource); return dataSource; } private DruidDataSource getWriteDruidDataSource() { DruidDataSource dataSource = new DruidDataSource(); druidProperties.configW(dataSource); return dataSource; } //初始化数据源bean,这个bean很重要,后面事务控制也会用到 @Bean public AbstractRoutingDataSource roundRobinDataSouceProxy(@Qualifier("readDatasource")DataSource read, @Qualifier("writeDatasource") DataSource write) { DynamicDataSource proxy = new DynamicDataSource(); Map<Object, Object> targetDataSources = new HashMap<Object, Object>(); targetDataSources.put( DatasourceType.read.getName(), read); targetDataSources.put(DatasourceType.write.getName(), write); proxy.setDefaultTargetDataSource(read); proxy.setTargetDataSources(targetDataSources); return proxy; } //初始化SqlSessionFactory,将自定义的多数据源ReadWriteSplitRoutingDataSource类实例注入到工厂中 @Bean public SqlSessionFactory sqlSessionFactory(@Qualifier("readDatasource")DataSource read, @Qualifier("writeDatasource") DataSource write) throws Exception { MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean(); sqlSessionFactory. setDataSource(this.roundRobinDataSouceProxy(read, write)); sqlSessionFactory.setMapperLocations( new PathMatchingResourcePatternResolver().getResources("classpath:/mapper/*Mapper.xml")); MybatisConfiguration configuration = new MybatisConfiguration(); configuration.setJdbcTypeForNull(JdbcType.NULL); configuration.setMapUnderscoreToCamelCase(true); configuration.setCacheEnabled(false); sqlSessionFactory.setConfiguration(configuration); sqlSessionFactory.setGlobalConfig(globalConfiguration()); return sqlSessionFactory.getObject(); } @Bean public GlobalConfiguration globalConfiguration() { GlobalConfiguration conf = new GlobalConfiguration(new LogicSqlInjector()); conf.setLogicDeleteValue("-1"); conf.setLogicNotDeleteValue("1"); conf.setIdType(0); conf.setDbColumnUnderline(true); conf.setRefresh(true); return conf; } }
创建 DataSourceContextHolder
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.testdatasource.third.configuration.datasource; import com.testdatasource.common.enums.DatasourceType; /** * ClassName:DataSourceContextHolder * * @Description : 当前线程数据源 */ public class DataSourceContextHolder { /** * 默认数据源 */ public static final String DEFAULT_DS = DatasourceType.read.getName(); private static final ThreadLocal<String> contextHolder = new ThreadLocal<>(); // 设置数据源名 public static void setDB(String dbType) { contextHolder.set(dbType); } // 获取数据源名 public static String getDB() { return (contextHolder.get()); } // 清除数据源名 public static void clearDB() { contextHolder.remove(); } }
创建 DynamicDataSource
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.testdatasource.third.configuration.datasource; import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource; /** * ClassName:DynamicDataSource * * @Description : 动态数据源 */ public class DynamicDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return DataSourceContextHolder.getDB(); } }
创建 MybatisPlusConfig
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.testdatasource.third.configuration.datasource; import com.baomidou.mybatisplus.plugins.PaginationInterceptor; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; /** * ClassName:MybatisPlusConfig * @Description : mybatis分页插件 * @version */ @Configuration public class MybatisPlusConfig { /** * mybatis-plus分页插件<br> */ @Bean public PaginationInterceptor paginationInterceptor() { PaginationInterceptor paginationInterceptor = new PaginationInterceptor(); paginationInterceptor.setLocalPage(true);// 开启 PageHelper 的支持 return paginationInterceptor; } }
创建 MyDataSourceTransactionManagerAutoConfiguration
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.testdatasource.third.configuration.datasource; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration; import org.springframework.context.ApplicationContext; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import org.springframework.jdbc.datasource.DataSourceTransactionManager; import org.springframework.transaction.annotation.EnableTransactionManagement; import javax.sql.DataSource; /** * * 多数据源事物 */ @Configuration @EnableTransactionManagement public class MyDataSourceTransactionManagerAutoConfiguration extends DataSourceTransactionManagerAutoConfiguration { @Autowired private ApplicationContext appContext; /** * 自定义事务 * MyBatis自动参与到spring事务管理中,无需额外配置,只要org.mybatis.spring.SqlSessionFactoryBean引用的数据源与DataSourceTransactionManager引用的数据源一致即可,否则事务管理会不起作用。 * @return */ @Bean(name = "transactionManager") public DataSourceTransactionManager transactionManagers() { return new DataSourceTransactionManager((DataSource)appContext.getBean("roundRobinDataSouceProxy")); } }
数据源枚举类
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.testdatasource.common.enums; /** * ClassName:DatasourceType * * @Description :数据库读写类型 * @version */ public enum DatasourceType { write("write"), read("read"); private String name; private DatasourceType(String name) { this.name = name(); } public String getName() { return this.name; } public void setName(String name) { this.name = name; } }
数据源配置类
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
import com.alibaba.druid.pool.DruidDataSource; import org.springframework.beans.factory.annotation.Value; import org.springframework.boot.context.properties.ConfigurationProperties; import org.springframework.stereotype.Component; import java.sql.SQLException; /** * ClassName:DruidProperties * * @Description : * druid连接池配置文件属性,说明:这个类中包含了许多默认配置,若这些配置符合您的情况,您可以不用管,若不符合,建议不要修改本类,建议直接在"application.yml"中配置即可 * </p> */ @Component @ConfigurationProperties(prefix = "spring.datasource") public class DruidProperties { @Value("${spring.datasource.read_url}") private String rurl; @Value("${spring.datasource.read_username}") private String rusername; @Value("${spring.datasource.read_password}") private String rpassword; @Value("${spring.datasource.write_url}") private String wurl; @Value("${spring.datasource.write_username}") private String wusername; @Value("${spring.datasource.write_password}") private String wpassword; private String driverClassName = "com.mysql.jdbc.Driver"; private Integer initialSize = 2; private Integer minIdle = 1; private Integer maxActive = 20; private Integer maxWait = 60000; private Integer timeBetweenEvictionRunsMillis = 60000; private Integer minEvictableIdleTimeMillis = 300000; private String validationQuery = "SELECT 1 FROM DUAL"; private Boolean testWhileIdle = true; private Boolean testOnBorrow = false; private Boolean testOnReturn = false; private Boolean poolPreparedStatements = true; private Integer maxPoolPreparedStatementPerConnectionSize = 20; private String filters = "stat"; public void configR(DruidDataSource dataSource) { dataSource.setUrl(rurl); dataSource.setUsername(rusername); // dataSource.setPassword(AESUtil.decrypt(rpassword, null)); dataSource.setPassword(AESUtils.AESDecode(null,rpassword)); dataSource.setDriverClassName(driverClassName); dataSource.setInitialSize(initialSize); // 定义初始连接数 dataSource.setMinIdle(minIdle); // 最小空闲 dataSource.setMaxActive(maxActive); // 定义最大连接数 dataSource.setMaxWait(maxWait); // 最长等待时间 // 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); // 配置一个连接在池中最小生存的时间,单位是毫秒 dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); dataSource.setValidationQuery(validationQuery); dataSource.setTestWhileIdle(testWhileIdle); dataSource.setTestOnBorrow(testOnBorrow); dataSource.setTestOnReturn(testOnReturn); // 打开PSCache,并且指定每个连接上PSCache的大小 dataSource.setPoolPreparedStatements(poolPreparedStatements); dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize); try { dataSource.setFilters(filters); } catch (SQLException e) { e.printStackTrace(); } } public void configW(DruidDataSource dataSource) { dataSource.setUrl(wurl); dataSource.setUsername(wusername); dataSource.setPassword(AESUtils.AESDecode(null,wpassword)); dataSource.setDriverClassName(driverClassName); dataSource.setInitialSize(initialSize); // 定义初始连接数 dataSource.setMinIdle(minIdle); // 最小空闲 dataSource.setMaxActive(maxActive); // 定义最大连接数 dataSource.setMaxWait(maxWait); // 最长等待时间 // 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis); // 配置一个连接在池中最小生存的时间,单位是毫秒 dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis); dataSource.setValidationQuery(validationQuery); dataSource.setTestWhileIdle(testWhileIdle); dataSource.setTestOnBorrow(testOnBorrow); dataSource.setTestOnReturn(testOnReturn); // 打开PSCache,并且指定每个连接上PSCache的大小 dataSource.setPoolPreparedStatements(poolPreparedStatements); dataSource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize); try { dataSource.setFilters(filters); } catch (SQLException e) { e.printStackTrace(); } } public String getRurl() { return rurl; } public void setRurl(String rurl) { this.rurl = rurl; } public String getRusername() { return rusername; } public void setRusername(String rusername) { this.rusername = rusername; } public String getRpassword() { return rpassword; } public void setRpassword(String rpassword) { this.rpassword = rpassword; } public String getWurl() { return wurl; } public void setWurl(String wurl) { this.wurl = wurl; } public String getWusername() { return wusername; } public void setWusername(String wusername) { this.wusername = wusername; } public String getWpassword() { return wpassword; } public void setWpassword(String wpassword) { this.wpassword = wpassword; } public String getDriverClassName() { return driverClassName; } public void setDriverClassName(String driverClassName) { this.driverClassName = driverClassName; } public Integer getInitialSize() { return initialSize; } public void setInitialSize(Integer initialSize) { this.initialSize = initialSize; } public Integer getMinIdle() { return minIdle; } public void setMinIdle(Integer minIdle) { this.minIdle = minIdle; } public Integer getMaxActive() { return maxActive; } public void setMaxActive(Integer maxActive) { this.maxActive = maxActive; } public Integer getMaxWait() { return maxWait; } public void setMaxWait(Integer maxWait) { this.maxWait = maxWait; } public Integer getTimeBetweenEvictionRunsMillis() { return timeBetweenEvictionRunsMillis; } public void setTimeBetweenEvictionRunsMillis(Integer timeBetweenEvictionRunsMillis) { this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis; } public Integer getMinEvictableIdleTimeMillis() { return minEvictableIdleTimeMillis; } public void setMinEvictableIdleTimeMillis(Integer minEvictableIdleTimeMillis) { this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis; } public String getValidationQuery() { return validationQuery; } public void setValidationQuery(String validationQuery) { this.validationQuery = validationQuery; } public Boolean getTestWhileIdle() { return testWhileIdle; } public void setTestWhileIdle(Boolean testWhileIdle) { this.testWhileIdle = testWhileIdle; } public Boolean getTestOnBorrow() { return testOnBorrow; } public void setTestOnBorrow(Boolean testOnBorrow) { this.testOnBorrow = testOnBorrow; } public Boolean getTestOnReturn() { return testOnReturn; } public void setTestOnReturn(Boolean testOnReturn) { this.testOnReturn = testOnReturn; } public Boolean getPoolPreparedStatements() { return poolPreparedStatements; } public void setPoolPreparedStatements(Boolean poolPreparedStatements) { this.poolPreparedStatements = poolPreparedStatements; } public Integer getMaxPoolPreparedStatementPerConnectionSize() { return maxPoolPreparedStatementPerConnectionSize; } public void setMaxPoolPreparedStatementPerConnectionSize(Integer maxPoolPreparedStatementPerConnectionSize) { this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize; } public String getFilters() { return filters; } public void setFilters(String filters) { this.filters = filters; } }
annotation类作用于强制写库或者读库
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.testdatasource.common.annotation; import com.testdatasource.common.enums.DatasourceType; import java.lang.annotation.ElementType; import java.lang.annotation.Retention; import java.lang.annotation.RetentionPolicy; /** * ClassName:DS * @Description :自定义切换数据源注解 * @version */ @Target(ElementType.METHOD) @Retention(RetentionPolicy.RUNTIME) public @interface DS { DatasourceType value() default DatasourceType.write; }
切面类DynamicDataSourceAspect 用于辅助自定义切面注解切换数据源
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
package com.testdatasource.third.aspect; import com.testdatasource.common.annotation.DS; import com.testdatasource.third.configuration.datasource.DataSourceContextHolder; import org.aspectj.lang.JoinPoint; import org.aspectj.lang.annotation.After; 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.core.annotation.Order; import org.springframework.stereotype.Component; import java.lang.reflect.Method; /** * ClassName:DynamicDataSourceAspect * * @Description : 自定义切面注解切换数据源 * @version */ @Aspect @Component @Order(value = 2) public class DynamicDataSourceAspect { @Pointcut("@annotation(com.testdatasource.common.annotation.DS)") public void ds() { } @Before("ds()") public void beforeSwitchDS(JoinPoint point) { // 获得当前访问的class Class<?> className = point.getTarget().getClass(); // 获得访问的方法名 String methodName = point.getSignature().getName(); // 得到方法的参数的类型 Class[] argClass = ((MethodSignature) point.getSignature()).getParameterTypes(); String dataSource = DataSourceContextHolder.DEFAULT_DS; try { // 得到访问的方法对象 Method method = className.getMethod(methodName, argClass); // 判断是否存在@DS注解 if (method.isAnnotationPresent(DS.class)) { DS annotation = method.getAnnotation(DS.class); // 取出注解中的数据源名 dataSource = annotation.value().getName(); } } catch (Exception e) { e.printStackTrace(); } // 切换数据源 DataSourceContextHolder.setDB(dataSource); } @After("ds()") public void afterSwitchDS(JoinPoint point) { DataSourceContextHolder.clearDB(); } }
yml配置
spring: datasource: type: com.alibaba.druid.pool.xa.DruidXADataSource read_url: jdbc:mysql://localhost:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&pinGlobalTxToPhysicalConnection=true read_username: root read_password: root write_url: jdbc:mysql://localhost:3306/test?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&pinGlobalTxToPhysicalConnection=true write_username: root write_password: root
好了 这样配置就没问题了。
有不懂的,或者更好的见解可以随时交流!每天都会看的。