由于很多项目使用云数据库,为了可靠性,一般会采用一主多备的方案,这时JDBC访问数据库,就需要数据源支持读写分离,把写操作落到主库,读操作落到备库上去。提到读写分离大家肯定会想到sharding-sphere, 虽然sharding-jdbc确实也能达到目的,但sharding-jdbc毕竟是为分库分表等复杂场景而设计,杀鸡何须宰牛刀?何况sharding-jdbc要底层解析SQL语句,性能消耗也不小,所以这里我自己做了个轻量级的读写分离数据源,现分享给大家。
1、动态数据源类
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.transaction.support.TransactionSynchronizationManager;
import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;
@Data
@Slf4j
public class DynamicDataSource extends AbstractRoutingDataSource {
private DataSource master;
private List<DataSource> slaves = new ArrayList<>();
private AtomicInteger pointer = new AtomicInteger(-1);
@Override
protected DataSource determineTargetDataSource() {
boolean readonly = TransactionSynchronizationManager.isCurrentTransactionReadOnly();
log.debug("isCurrentTransactionReadOnly: {}", readonly);
if (readonly) {
if (slaves.size() > 1) {
int i = pointer.incrementAndGet();
if (i == Integer.MAX_VALUE) {
pointer.set(0);
i = 0;
}
return slaves.get(i % slaves.size());
}
return slaves.get(0);
}
return master;
}
@Override
protected Object determineCurrentLookupKey() {
return null;
}
}
本类有一个master和一组slaves数据源,master连到主库,能读写,这里只用作写数据,slaves则是一组只读数据源,连接到从库。
这里有一个关键点,就是事务必须清晰的指明,是只读还是读写,这一般在service类中声明,如:
@Service
@Transactional(readOnly = true) //本类的方法缺省情况下是只读事务,将被分配从数据源
public class BatchServiceImpl implements IBatchService {
@Override //本方法是只读
public PBatchEntity getBatchInfo(Long id) {
Optional<PBatchEntity> batchEntity = batchRepository.findById(id);
return batchEntity.get();
}
@Override
@Transactional(readOnly = false) //本方法是读写事务,只能分配主数据源
public boolean deleteBatch(Long batchId) {
}
...
}
2、修复Spring-jdbc的bug
(注:由于我已经报告这个问题给spring,原问题链接,
他们已经在如下版本中修复了:
v4.3.29.RELEASE,v5.0.19.RELEASE,v5.2.9.RELEASE,v5.1.18.RELEASE)
spring-jdbc有个bug,就是在选择数据源的时候, boolean readonly = TransactionSynchronizationManager.isCurrentTransactionReadOnly() 读取不到真正的值,所以用创建如下代码
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.support.TransactionSynchronizationManager;
import javax.sql.DataSource;
/**
* 共享数据库连接。只读事务下,不提交事务
*
* @author WeiZhou
*
*/
@SuppressWarnings("serial")
public class DynaDataSourceTransactionManager extends DataSourceTransactionManager {
public DynaDataSourceTransactionManager() {
super();
}
public DynaDataSourceTransactionManager(DataSource dataSource) {
super(dataSource);
}
@Override
protected void doBegin(Object transaction, TransactionDefinition definition) {
TransactionSynchronizationManager.setCurrentTransactionReadOnly(definition.isReadOnly());
super.doBegin(transaction, definition);
}
}
3、配置类
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.ObjectProvider;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.transaction.TransactionManagerCustomizers;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Configuration
public class DataSourceConfig {
@Bean
DataSourceTransactionManager transactionManager(DataSource dataSource,
ObjectProvider<TransactionManagerCustomizers> transactionManagerCustomizers) {
DataSourceTransactionManager transactionManager = new DynaDataSourceTransactionManager(dataSource);
transactionManagerCustomizers.ifAvailable((customizers) -> customizers.customize(transactionManager));
return transactionManager;
}
@ConfigurationProperties(prefix = "spring.datasources")
@Bean(name = "dsConfig")
public Map<String, Map<String, String>> hdfsConfig() {
return new HashMap<>();
}
@Bean
public DataSource createDataSource(@Qualifier("dsConfig") Map<String, Map<String, String>> confMap) {
DynamicDataSource dds = new DynamicDataSource();
for (Map.Entry<String, Map<String, String>> ent : confMap.entrySet()) {
Map<String, String> confs = ent.getValue();
DataSource ds = DataSourceBuilder.create()
.driverClassName(confs.get("driver-class-name"))
.password(confs.get("password"))
.url(confs.get("url"))
.username(confs.get("username"))
.type(HikariDataSource.class).build();
if ("master".equals(ent.getKey())) {
dds.setMaster(ds);
} else {
dds.getSlaves().add(ds);
}
}
return dds;
}
}
本类实例化我们自己的数据源,取代Spring的默认数据源,创建DynaDataSourceTransactionManager实例,取代Spring默认的DataSourceTransactionManager。
4、配置
application.properties中配置多数据源:
spring.datasources.master.driver-class-name=org.opengauss.Driver
spring.datasources.master.url=jdbc:opengauss://localhost:25432/postgres
spring.datasources.master.username=gaussdb
spring.datasources.master.password=Enmo@123
spring.datasources.node.url=jdbc:opengauss://localhost:25432/postgres
spring.datasources.node.username=readonly
spring.datasources.node.password=read@123
spring.datasources.node.driver-class-name=org.opengauss.Driver
好了,这么简单就能实现读写分离,你还会为了读写分离使用sharding-jdbc吗?