springboot mybatis jpa 实现读写分离
环境:springboot2.2.11.RELEASE+MySQL+JPA+MyBatis
这里不使用第三方的,比如:mycat,sharedsphere。
自己实现一个比较简单的方案。
实现方案:利用AOP拦截业务方法,比如:find,select,query等这类的方法通过从(读)库获取数据,其它的都从主(write)库操作。
实战!
- application.yml配置读写库
master用来写操作,slave*用来读操作。
master:
datasource:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/testjpa?serverTimezone=GMT%2B8
username: root
password: xxxxx
type: com.zaxxer.hikari.HikariDataSource
hikari:
minimumIdle: 10
maximumPoolSize: 200
autoCommit: true
idleTimeout: 30000
poolName: MasterDatabookHikariCP
maxLifetime: 1800000
connectionTimeout: 30000
connectionTestQuery: SELECT 1
slave1:
datasource:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/account?serverTimezone=GMT%2B8
username: root
password: xxxxxx
type: com.zaxxer.hikari.HikariDataSource
hikari:
minimumIdle: 10
maximumPoolSize: 200
autoCommit: true
idleTimeout: 30000
poolName: Slave1DatabookHikariCP
maxLifetime: 1800000
connectionTimeout: 30000
connectionTestQuery: SELECT 1 FROM DUAL
slave2:
datasource:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/storage?serverTimezone=GMT%2B8
username: root
password: xxxxxx
type: com.zaxxer.hikari.HikariDataSource
hikari:
minimumIdle: 10
maximumPoolSize: 200
autoCommit: true
idleTimeout: 30000
poolName: Slave2DatabookHikariCP
maxLifetime: 1800000
connectionTimeout: 30000
connectionTestQuery: SELECT 1 FROM DUAL
---
mybatis:
config-location: classpath:MyBatis-conf.xml
type-aliases-package: com.pack.domain
mapper-locations:
- classpath:/com/pack/mapper/*.xml
- 数据源配置属性文件
BaseDataSourceProperties这是一个基类,这类的代码是完全复制spring中DataSourceProperties中的属性的,因为代码比较多,我这里就不复制出来了,对应的主库,2个从库的属性配置都是继承BaseDataSourceProperties。代码如下:
@Component @ConfigurationProperties(prefix = "master.datasource") public class MasterDataSourceProperties extends BaseDataSourceProperties { } @Component @ConfigurationProperties(prefix = "slave1.datasource") public class Slave1DataSourceProperties extends BaseDataSourceProperties { } @Component @ConfigurationProperties(prefix = "slave2.datasource") public class Slave2DataSourceProperties extends BaseDataSourceProperties { }
这里分别对应主从3个库的属性配置。
- 数据源配置
@Configuration public class HikariDataSourceConfig { @Bean @ConfigurationProperties(prefix = "master.datasource.hikari") public HikariDataSource masterDataSource(MasterDataSourceProperties properties) { HikariDataSource dataSource = createDataSource(properties, HikariDataSource.class); if (StringUtils.hasText(properties.getName())) { dataSource.setPoolName(properties.getName()); } return dataSource; } @Bean @ConfigurationProperties(prefix = "slave1.datasource.hikari") public HikariDataSource slave1DataSource(Slave1DataSourceProperties properties) { HikariDataSource dataSource = createDataSource(properties, HikariDataSource.class); if (StringUtils.hasText(properties.getName())) { dataSource.setPoolName(properties.getName()); } return dataSource; } @Bean @ConfigurationProperties(prefix = "slave2.datasource.hikari") public HikariDataSource slave2DataSource(Slave2DataSourceProperties properties) { HikariDataSource dataSource = createDataSource(properties, HikariDataSource.class); if (StringUtils.hasText(properties.getName())) { dataSource.setPoolName(properties.getName()); } return dataSource; } @SuppressWarnings("unchecked") protected static <T> T createDataSource(BaseDataSourceProperties properties, Class<? extends DataSource> type) { return (T) properties.initializeDataSourceBuilder().type(type).build(); } @Bean @Primary @DependsOn({"masterDataSource", "slave1DataSource", "slave2DataSource"}) public AbstractRoutingDataSource routingDataSource(@Qualifier("masterDataSource")DataSource masterDataSource, @Qualifier("slave1DataSource")DataSource slave1DataSource, @Qualifier("slave2DataSource")DataSource slave2DataSource) { BaseRoutingDataSource ds = new BaseRoutingDataSource() ; Map<Object, Object> targetDataSources = new HashMap<>(2) ; targetDataSources.put(DbEnum.MASTER, masterDataSource) ; targetDataSources.put(DbEnum.SLAVE1, slave1DataSource) ; targetDataSources.put(DbEnum.SLAVE2, slave2DataSource) ; ds.setDefaultTargetDataSource(masterDataSource) ; ds.setTargetDataSources(targetDataSources) ; return ds ; } }
这里的数据源配置也是从springboot中复制出来的创建数据源方式。
这里主要的是routingDataSource Bean的配置,创建的是AbstractRoutingDataSource Bean带路由功能的数据源Bean。AbstractRoutingDataSource类中有个Map 集合是用来存数据源的
源码:
看到这里的Map应该能知道肯定是通过Key来确定使用哪个数据源。所以在这个类中有一个抽象的方法是让子类来实现的,也就是返回具体的Key。
源码:
- 自定义AbstractRoutingDataSource
public class BaseRoutingDataSource extends AbstractRoutingDataSource { @Override protected Object determineCurrentLookupKey() { return DataSourceHolder.get() ; } }
DataSourceHolder这个类中通过ThreadLocal来绑定当前线程的上下文(使用的数据源)
public class DataSourceHolder { private static ThreadLocal<Integer> context = new ThreadLocal<>() ; // 0: 写,1:读 public static DbEnum get() { Integer type = context.get() ; return type == null || type % 2 == 0 ? DbEnum.MASTER : DbEnum.slave() ; } public static void set(Integer type) { context.set(type) ; } }
DbEnum类中定义了数据源类型及获取方法
public enum DbEnum { MASTER, SLAVE1, SLAVE2 ; private static List<DbEnum> slaves = new ArrayList<>() ; private static AtomicInteger slaveCount = new AtomicInteger(0) ; static { slaves.add(SLAVE1) ; slaves.add(SLAVE2) ; } public static DbEnum slave() { int index = slaveCount.get() % slaves.size() ; DbEnum ret = slaves.get(index) ; slaveCount.incrementAndGet() ; return ret ; } }
在这个枚举类中,定义了一个静态方法通过轮询的方式来获取不同的从(读)库。
- JPA配置
public class EntityManagerFactoryConfig { @Configuration @EnableJpaRepositories(basePackages = {"com.pack.repository" }) static class MasterEntityManagerFactory { @Resource(name = "routingDataSource") private DataSource routingDataSource; @Bean @Primary public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder builder) { Map<String, Object> properties = new HashMap<>(); properties.put("hibernate.hbm2ddl.auto", "update"); properties.put("hibernate.id.new_generator_mappings", true); properties.put("hibernate.physical_naming_strategy", SpringPhysicalNamingStrategy.class.getName()) ; return builder.dataSource(routingDataSource).packages("com.pack.domain").persistenceUnit("master") .properties(properties).build(); } @Bean @Primary public PlatformTransactionManager transactionManager(EntityManagerFactoryBuilder builder) { JpaTransactionManager tm = new JpaTransactionManager(entityManagerFactory(builder).getObject()); return tm; } } }
注意了这里注入的就是routingDataSource Bean 。
- MyBatis配置
public class SqlSessionFactoryConfig { @Configuration @MapperScan(basePackages = {"com.pack.mapper"}) static class MasterSqlSessionFactory { @Resource private BaseMybatisProperties properties; @Autowired(required = false) private Interceptor[] interceptors; @Autowired private ResourceLoader resourceLoader = new DefaultResourceLoader(); @Autowired(required = false) private DatabaseIdProvider databaseIdProvider; @Bean public SqlSessionFactory sqlSessionFactory(@Qualifier("routingDataSource") DataSource dataSource) throws Exception { SqlSessionFactoryBean factory = new SqlSessionFactoryBean(); factory.setDataSource(dataSource); factory.setVfs(SpringBootVFS.class); if (StringUtils.hasText(this.properties.getConfigLocation())) { factory.setConfigLocation(this.resourceLoader.getResource(this.properties.getConfigLocation())); } factory.setConfiguration(properties.getConfiguration()); if (!ObjectUtils.isEmpty(this.interceptors)) { factory.setPlugins(this.interceptors); } if (this.databaseIdProvider != null) { factory.setDatabaseIdProvider(this.databaseIdProvider); } if (StringUtils.hasLength(this.properties.getTypeAliasesPackage())) { factory.setTypeAliasesPackage(this.properties.getTypeAliasesPackage()); } if (StringUtils.hasLength(this.properties.getTypeHandlersPackage())) { factory.setTypeHandlersPackage(this.properties.getTypeHandlersPackage()); } if (!ObjectUtils.isEmpty(this.properties.resolveMapperLocations())) { factory.setMapperLocations(this.properties.resolveMapperLocations()); } return factory.getObject(); } @Bean public DataSourceTransactionManager masterTransactionManager(@Qualifier("routingDataSource") DataSource dataSource) { return new DataSourceTransactionManager(dataSource); } } }
到此所有的数据源配置,JPA,My环境:springboot2.2.11.RELEASE+MySQL+JPA+MyBatis
这里不使用第三方的,比如:mycat,sharedsphere。
自己实现一个比较简单的方案。
实现方案:利用AOP拦截业务方法,比如:find,select,query等这类的方法通过从(读)库获取数据,其它的都从主(write)库操作。
测试:
保存数据:
结果:
写库配置的是testjpa,这里写入成功。
查询应该走从库,account和storage库,这里为了区分是从两个库中查询的数据我专门把两个库的数据做了修改以示区分。
先看数据:
查询:
成功!
完毕!!!