springboot+mybatis实现数据库读写分离
本文不包含数据库主从配置。
实现思路:在项目中配置多数据源,通过代码控制访问哪一个数据源。
spring-jdbc为我们提供了AbstractRoutingDataSource,DataSource的抽象实现,基于查找键,返回不通不同的数据源。编写我们自己的动态数据源类DynamicDataSource继承AbstractRoutingDataSource,实现determineCurrentLookupKey方法。
配置一个spring config类DataSourceConfig,把DynamicDataSource初始化。
配置SqlSessionFactory,注入我们自定义的数据源DynamicDataSource。
通过AOP切入设置所需要的数据源,比如插入或者更新使用主数据源,查询使用从读数据源
#主数据库设置 spring.datasource.master.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8 spring.datasource.master.driverClassName=com.mysql.jdbc.Driver spring.datasource.master.username=root spring.datasource.master.password=root spring.datasource.maseter.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.master.initialSize=5 spring.datasource.master.minIdle=1 spring.datasource.master.maxActive=50 spring.datasource.master.maxWait=60000 spring.datasource.master.minEvictableIdleTimeMillis=300000 #从数据库设置 spring.datasource.slave.url=jdbc:mysql://localhost:3306/test1?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8 spring.datasource.slave.driverClassName=com.mysql.jdbc.Driver spring.datasource.slave.username=root spring.datasource.slave.password=root spring.datasource.slave.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.slave.initialSize=5 spring.datasource.slave.minIdle=1 spring.datasource.slave.maxActive=50 spring.datasource.slave.maxWait=60000 spring.datasource.slave.minEvictableIdleTimeMillis=300000 @Configuration public class DataSourceConfig { @Value("${spring.datasource.master.url}") private String masterUrl; @Value("${spring.datasource.master.driverClassName}") private String masterDriverClassName; @Value("${spring.datasource.master.username}") private String masterUsername; @Value("${spring.datasource.master.password}") private String masterPassword; @Value("${spring.datasource.master.initialSize}") private Integer masterInitialSize; @Value("${spring.datasource.master.minIdle}") private Integer masterMinIdle; @Value("${spring.datasource.master.maxWait}") private Long masterMaxWait; @Value("${spring.datasource.master.maxActive}") private Integer masterMaxActive; @Value("${spring.datasource.master.minEvictableIdleTimeMillis}") private Integer masterMinEvictableIdleTimeMillis; @Value("${spring.datasource.slave.url}") private String slaveUrl; @Value("${spring.datasource.slave.driverClassName}") private String slaveDriverClassName; @Value("${spring.datasource.slave.username}") private String slaveUsername; @Value("${spring.datasource.slave.password}") private String slavePassword; @Value("${spring.datasource.slave.initialSize}") private Integer slaveInitialSize; @Value("${spring.datasource.slave.minIdle}") private Integer slaveMinIdle; @Value("${spring.datasource.slave.maxWait}") private Long slaveMaxWait; @Value("${spring.datasource.slave.maxActive}") private Integer slaveMaxActive; @Value("${spring.datasource.slave.minEvictableIdleTimeMillis}") private Integer slaveMinEvictableIdleTimeMillis; public DataSourceConfig() { System.out.println("#DataSourceConfig#"); } public DataSource master() { System.out.println("# master druid#"); DruidDataSource datasource = new DruidDataSource(); datasource.setUrl(masterUrl); datasource.setDriverClassName(masterDriverClassName); datasource.setUsername(masterUsername); datasource.setPassword(masterPassword); datasource.setInitialSize(masterInitialSize); datasource.setMinIdle(masterMinIdle); datasource.setMaxWait(masterMaxWait); datasource.setMaxActive(masterMaxActive); datasource.setMinEvictableIdleTimeMillis(masterMinEvictableIdleTimeMillis); try { datasource.setFilters("stat,wall"); } catch (SQLException e) { e.printStackTrace(); } return datasource; } public DataSource salve() { System.out.println("# slave slave#"); DruidDataSource datasource = new DruidDataSource(); datasource.setUrl(slaveUrl); datasource.setDriverClassName(slaveDriverClassName); datasource.setUsername(slaveUsername); datasource.setPassword(slavePassword); datasource.setInitialSize(slaveInitialSize); datasource.setMinIdle(slaveMinIdle); datasource.setMaxWait(slaveMaxWait); datasource.setMaxActive(slaveMaxActive); datasource.setMinEvictableIdleTimeMillis(slaveMinEvictableIdleTimeMillis); try { datasource.setFilters("stat,wall"); } catch (SQLException e) { e.printStackTrace(); } return datasource; } public class DynamicDataSource extends AbstractRoutingDataSource { private static final ThreadLocal<DatabaseType> contextHolder = new ThreadLocal<>(); @Override protected Object determineCurrentLookupKey() { return contextHolder.get(); } public static enum DatabaseType { Master,Slave } public static void master() { contextHolder.set(DatabaseType.Master); } public static void slave() { contextHolder.set(DatabaseType.Slave); } } @Bean public DynamicDataSource dynamicDataSource() { DataSource master = master(); DataSource slave = salve(); Map<Object,Object> targetDataSources = new HashMap<>(); targetDataSources.put(DynamicDataSource.DatabaseType.Master, master); targetDataSources.put(DynamicDataSource.DatabaseType.Slave, slave); DynamicDataSource dataSource = new DynamicDataSource(); dataSource.setTargetDataSources(targetDataSources); dataSource.setDefaultTargetDataSource(master); return dataSource; } } @Configuration @AutoConfigureAfter({DataSourceConfig.class}) public class MybatisConfig { @Bean("sqlSessionFactory") public SqlSessionFactory sqlSessionFactory(DynamicDataSource dynamicDataSource) { SqlSessionFactoryBean bean = new SqlSessionFactoryBean(); bean.setDataSource(dynamicDataSource); SqlSessionFactory sqlSessionFactory = null; try { sqlSessionFactory = bean.getObject(); } catch (Exception e) { e.printStackTrace(); } MapperHelper mapperHelper = new MapperHelper(); Config config = new Config(); config.setNotEmpty(true); mapperHelper.setConfig(config); mapperHelper.registerMapper(Mapper.class); mapperHelper.processConfiguration(sqlSessionFactory.getConfiguration()); return sqlSessionFactory; } @Bean public MapperScannerConfigurer scannerConfigurer() { MapperScannerConfigurer configurer = new MapperScannerConfigurer(); configurer.setSqlSessionFactoryBeanName("sqlSessionFactory"); // configurer.setSqlSessionTemplateBeanName("sqlSession"); configurer.setBasePackage("com.luke.hu.dao"); configurer.setMarkerInterface(Mapper.class); return configurer; } } @Aspect @Component public class DataSourceAOP { @Before("execution(* com.luke.hu.dao..*.insert*(..)) || execution(* com.luke.hu.dao..*.update*(..))") public void setWriteDataSourceType() { DynamicDataSource.master(); System.out.println("切换到master"); } @Before("execution(* com.luke.hu.dao..*.select*(..))") public void setReadDataSourceType() { DynamicDataSource.slave(); System.out.println("切换到slave"); } }