项目地址:https://github.com/baojingyu/spring-boot-jpa-dynamic-datasource
本项目使用 SpringBoot 和 SpringData JPA 实现多数据源,动态数据源的切换
前段时间写 MySQL 主从复制的技术分享,我就在想,既然主从复制都搭建好了,不写个 Demo 玩玩读写分离,好像差点意思哼~ 于是就有了这么个 Demo Project。
一、什么是读写分离?
读写分离其实就是将数据库分为主从库,简单点说是主库处理事务性增、改、删操作,多个从库处理查询操作。主库的事务性操作导致的变更通过BinLog 日志同步到集群中的从库。
二、为什么要读写分离?
其实最主要是减轻数据库的压力。数据库的写操作比较耗时,如果没有进行读写分离,写操作将会影响到读操作的效率。
三、什么时候要读写分离?
如果程序读多写少,那么可以考虑读写分离,利用数据库主从同步,能减轻数据库压力,提高性能。
四、读写分离方案
数据库读远大于写,查询多的情况,那就得考虑主库负责写操作,多个从库负责读操作,另外结合 Redis 等缓存来配合分担数据库的读操作。
五、代码层面进行读写分离
代码环境是SpringBoot + SpringData JPA + Druib连接池。想要读写分离就需要配置多个数据源,在进行写操作时选择写的数据源,读操作时选择读的数据源。其中有两个关键点:
- 如何切换数据源
- 如何根据不同的方法选择正确的数据源
1)、如何切换数据源
通常用 SpringBoot 时都是使用它的默认配置,只需要在配置文件中定义好连接属性就行了,但是现在我们需要自己来配置了,Spring 是支持多数据源的,多个datasource放在一个HashMapTargetDataSource中,通过dertermineCurrentLookupKey获取key来决定要使用哪个数据源。因此我们的目标就很明确了,建立多个datasource放到TargetDataSource中,同时重写dertermineCurrentLookupKey方法来决定使用哪个key。
用户自定义设置数据库路由
SpringBoot 提供了 AbstractRoutingDataSource 根据用户定义的规则选择当前的数据库,这样我们可以在执行查询之前,设置读取从库,在执行完成后,恢复到主库。AbstractRoutingDataSource 就是DataSource 的抽象,基于 lookupKey 的方式在多个数据库中进行切换。重点关注setTargetDataSources,setDefaultTargetDataSource,determineCurrentLookupKey三个方法。那么AbstractRoutingDataSource就是Spring读写分离的关键了。
实现可动态路由的数据源,在每次数据库查询操作前执行。
2)、如何选择数据源
事务一般是注解在Service层的,因此在开始这个service方法调用时要确定数据源,有什么通用方法能够在开始执行一个方法前做操作呢?相信你已经想到了那就是切面 。怎么切有两种办法:
注解式,定义一个只读注解,被该数据标注的方法使用读库 方法名,根据方法名写切点,比如getXXX用读库,setXXX用写库
六、部分代码
DataSourcesConfig
/**
* @author jingyu.bao
* @version 1.0
* @className DataSourceConfig
* @description
* @date 7/5/2020 20:09
**/
@EnableTransactionManagement
@Configuration
public class DataSourceConfig {
@Value("${spring.datasource.druid.master.name}")
private String masterName;
@Value("${spring.datasource.druid.master.url}")
private String masterUrl;
@Value("${spring.datasource.druid.master.username}")
private String masterUsername;
@Value("${spring.datasource.druid.master.password}")
private String masterPassword;
@Value("${spring.datasource.druid.master.driver-class-name}")
private String masterDriverClassName;
@Value("${spring.datasource.druid.slave.name}")
private String slaveName;
@Value("${spring.datasource.druid.slave.url}")
private String slaveUrl;
@Value("${spring.datasource.druid.slave.username}")
private String slaveUsername;
@Value("${spring.datasource.druid.slave.password}")
private String slavePassword;
@Value("${spring.datasource.druid.slave.driver-class-name}")
private String slaveDriverClassName;
@Value("${spring.datasource.druid.initial-size}")
private String initialSize;
@Value("${spring.datasource.druid.min-idle}")
private String minIdle;
@Value("${spring.datasource.druid.max-active}")
private String maxActive;
@Value("${spring.datasource.druid.max-wait}")
private String maxWait;
@Value("${spring.datasource.druid.time-between-eviction-runs-millis}")
private String timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.druid.min-evictable-idle-time-millis}")
private String minEvictableIdleTimeMillis;
@Value("${spring.datasource.druid.validation-query}")
private String validationQuery;
@Value("${spring.datasource.druid.test-while-idle}")
private String testWhileIdle;
@Value("${spring.datasource.druid.test-on-borrow}")
private String testOnBorrow;
@Value("${spring.datasource.druid.test-on-return}")
private String testOnReturn;
@Value("${spring.datasource.druid.filters}")
private String filters;
@Value("{spring.datasource.druid.filter.stat.log-slow-sql}")
private String logSlowSql;
@Value("{spring.datasource.druid.filter.stat.slow-sql-millis}")
private String slowSqlMillis;
@Value("${spring.datasource.druid.type}")
private String type;
@Value("${spring.datasource.druid.stat-view-servlet.login-username}")
private String loginUserName;
@Value("${spring.datasource.druid.stat-view-servlet.login-password}")
private String password;
@Bean(name = "masterDataSource")
public DataSource masterDataSource() {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(masterUrl);
datasource.setUsername(masterUsername);
datasource.setPassword(masterPassword);
datasource.setDriverClassName(masterDriverClassName);
//configuration
if (!StringUtils.isEmpty(initialSize)) {
datasource.setInitialSize(Integer.parseInt(initialSize));
}
if (!StringUtils.isEmpty(minIdle)) {
datasource.setMinIdle(Integer.parseInt(minIdle));
}
if (!StringUtils.isEmpty(maxActive)) {
datasource.setMaxActive(Integer.parseInt(maxActive));
}
if (!StringUtils.isEmpty(maxWait)) {
datasource.setMaxWait(Integer.parseInt(maxWait));
}
if (!StringUtils.isEmpty(timeBetweenEvictionRunsMillis)) {
datasource.setTimeBetweenEvictionRunsMillis(Integer.parseInt(timeBetweenEvictionRunsMillis));
}
if (!StringUtils.isEmpty(minEvictableIdleTimeMillis)) {
datasource.setMinEvictableIdleTimeMillis(Integer.parseInt(minEvictableIdleTimeMillis));
}
if (!StringUtils.isEmpty(validationQuery)) {
datasource.setValidationQuery(validationQuery);
}
if (!StringUtils.isEmpty(testWhileIdle)) {
datasource.setTestWhileIdle(Boolean.parseBoolean(testWhileIdle));
}
if (!StringUtils.isEmpty(testOnBorrow)) {
datasource.setTestOnBorrow(Boolean.parseBoolean(testOnBorrow));
}
if (!StringUtils.isEmpty(testOnReturn)) {
datasource.setTestOnReturn(Boolean.parseBoolean(testOnReturn));
}
try {
datasource.setFilters(filters);
} catch (SQLException e) {
e.printStackTrace();
}
return datasource;
}
@Bean(name = "slaveDataSource")
public DataSource slaveDataSource() {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(masterUrl);
datasource.setUsername(masterUsername);
datasource.setPassword(masterPassword);
datasource.setDriverClassName(masterDriverClassName);
//configuration
if (!StringUtils.isEmpty(initialSize)) {
datasource.setInitialSize(Integer.parseInt(initialSize));
}
if (!StringUtils.isEmpty(minIdle)) {
datasource.setMinIdle(Integer.parseInt(minIdle));
}
if (!StringUtils.isEmpty(maxActive)) {
datasource.setMaxActive(Integer.parseInt(maxActive));
}
if (!StringUtils.isEmpty(maxWait)) {
datasource.setMaxWait(Integer.parseInt(maxWait));
}
if (!StringUtils.isEmpty(timeBetweenEvictionRunsMillis)) {
datasource.setTimeBetweenEvictionRunsMillis(Integer.parseInt(timeBetweenEvictionRunsMillis));
}
if (!StringUtils.isEmpty(minEvictableIdleTimeMillis)) {
datasource.setMinEvictableIdleTimeMillis(Integer.parseInt(minEvictableIdleTimeMillis));
}
if (!StringUtils.isEmpty(validationQuery)) {
datasource.setValidationQuery(validationQuery);
}
if (!StringUtils.isEmpty(testWhileIdle)) {
datasource.setTestWhileIdle(Boolean.parseBoolean(testWhileIdle));
}
if (!StringUtils.isEmpty(testOnBorrow)) {
datasource.setTestOnBorrow(Boolean.parseBoolean(testOnBorrow));
}
if (!StringUtils.isEmpty(testOnReturn)) {
datasource.setTestOnReturn(Boolean.parseBoolean(testOnReturn));
}
try {
datasource.setFilters(filters);
} catch (SQLException e) {
e.printStackTrace();
}
return datasource;
}
@Primary
@Bean
public DynamicRoutingDataSource dynamicDataSource(@Qualifier(value = "masterDataSource") DataSource masterDataSource, @Qualifier(value = "slaveDataSource") DataSource slaveDataSource) {
Map<Object, Object> targetDataSources = new HashMap<>(2);
targetDataSources.put(DynamicRoutingDataSourceContext.MASTER, masterDataSource);
targetDataSources.put(DynamicRoutingDataSourceContext.SLAVE, slaveDataSource);
DynamicRoutingDataSource dynamicRoutingDataSource = new DynamicRoutingDataSource();
//设置数据源
dynamicRoutingDataSource.setTargetDataSources(targetDataSources);
//设置默认选择的数据源
dynamicRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
dynamicRoutingDataSource.afterPropertiesSet();
return dynamicRoutingDataSource;
}
@Bean
public ServletRegistrationBean statViewServlet() {
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
//设置ip白名单
servletRegistrationBean.addInitParameter("allow", "");
//设置ip黑名单,优先级高于白名单
servletRegistrationBean.addInitParameter("deny", "");
//设置控制台管理用户
servletRegistrationBean.addInitParameter("loginUsername", loginUserName);
servletRegistrationBean.addInitParameter("loginPassword", password);
//是否可以重置数据
servletRegistrationBean.addInitParameter("resetEnable", "false");
return servletRegistrationBean;
}
@Bean
public FilterRegistrationBean statFilter() {
//创建过滤器
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
//设置过滤器过滤路径
filterRegistrationBean.addUrlPatterns("/*");
//忽略过滤的形式
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
DynamicRoutingDataSource
/**
* @author jingyu.bao
* @version 1.0
* @className DynamicRoutingDataSource
* @description
* @date 7/5/2020 21:22
**/
public class DynamicRoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
Object lookupKey = DynamicRoutingDataSourceContext.getRoutingDataSource();
System.err.println(Thread.currentThread().getName() + " determineCurrentLookupKey : " + lookupKey);
return lookupKey;
}
}
DynamicRoutingDataSourceContext
/**
* @author jingyu.bao
* @version 1.0
* @className DynamicRoutingDataSourceContext
* @description
* @date 7/5/2020 20:16
**/
public class DynamicRoutingDataSourceContext {
public static final String MASTER = "master";
public static final String SLAVE = "slave";
private static final ThreadLocal<Object> threadLocalDataSource = new ThreadLocal<>();
public static void setRoutingDataSource(Object dataSource) {
if (dataSource == null) {
throw new NullPointerException();
}
threadLocalDataSource.set(dataSource);
// System.err.println(Thread.currentThread().getName()+" set RoutingDataSource : " + dataSource);
}
public static Object getRoutingDataSource() {
Object dataSourceType = threadLocalDataSource.get();
if (dataSourceType == null) {
threadLocalDataSource.set(DynamicRoutingDataSourceContext.MASTER);
return getRoutingDataSource();
}
// System.err.println(Thread.currentThread().getName()+" get RoutingDataSource : " + dataSourceType);
return dataSourceType;
}
public static void removeRoutingDataSource() {
threadLocalDataSource.remove();
// System.err.println(Thread.currentThread().getName()+" remove RoutingDataSource");
}
}
RoutingAopAspect
/**
* @author jingyu.bao
* @version 1.0
* @className RoutingAopAspect
* @description
* @date 7/5/2020 20:21
**/
@Order(0)
@Aspect
@Component
public class RoutingAopAspect {
@Around("@annotation(targetDataSource)")
public Object routingWithDataSource(ProceedingJoinPoint joinPoint, TargetDataSource targetDataSource) throws Throwable {
try {
DynamicRoutingDataSourceContext.setRoutingDataSource(targetDataSource.value());
return joinPoint.proceed();
} finally {
DynamicRoutingDataSourceContext.removeRoutingDataSource();
}
}
}
TargetDataSource
/**
* @author jingyu.bao
* @version 1.0
* @className TargetDataSource
* @description
* @date 7/5/2020 20:40
**/
@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(value = RetentionPolicy.RUNTIME)
@Documented
public @interface TargetDataSource {
String value();
}
UserInfoServiceImpl
/**
* @author jingyu.bao
* @version 1.0
* @className UserServiceImpl
* @description
* @date 7/5/2020 21:39
**/
@Service
public class UserInfoServiceImpl implements IUserInfoService {
@Autowired
private IUserInfoRepository userInfoRepository;
@TargetDataSource(value = "slave")
@Override
public List<UserInfoEntity> findAll() {
return userInfoRepository.findAll();
}
@Transactional
@Override
public UserInfoEntity save(UserInfoEntity userInfoEntity) {
return userInfoRepository.save(userInfoEntity);
}
@TargetDataSource(value = "slave")
@Override
public UserInfoEntity findById(Long id) {
Optional<UserInfoEntity> userInfoEntity = userInfoRepository.findById(id);
return userInfoEntity.isPresent() ? userInfoEntity.get() : null;
}
@Override
public List<UserInfoEntity> findAllMaster() {
return userInfoRepository.findAll();
}
@Transactional
@Override
public void saveAll(ArrayList<UserInfoEntity> list) {
userInfoRepository.saveAll(list);
}
}
application.properties
server.port=8080
server.tomcat.max-threads=3000
server.tomcat.max-connections=20000
server.tomcat.uri-encoding=UTF-8
server.tomcat.accept-count=800
# 自定义线程池参数
fxea.threadPool.coreThreadNum=5
fxea.threadPool.maxThreadNum=25
# 这个参数是在建表的时候,将默认的存储引擎切换为 InnoDB 用的
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.database=mysql
# 配置在日志中打印出执行的 SQL 语句信息。
spring.jpa.show-sql=true
# 第一次建表create,后面用update,要不然每次重启都会新建表
spring.jpa.hibernate.ddl-auto=create
# Druid
spring.datasource.druid.type=com.alibaba.druid.pool.DruidDataSource
#初始化连接大小
spring.datasource.druid.initial-size=10
#最小连接池数量
spring.datasource.druid.min-idle=10
#最大连接池数量
spring.datasource.druid.max-active=100
#配置获取连接等待超时的时间
spring.datasource.druid.max-wait=60000
#配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
spring.datasource.druid.time-between-eviction-runs-millis=60000
#配置一个连接在池中最小生存的时间,单位是毫秒
spring.datasource.druid.min-evictable-idle-time-millis=300000
#测试连接
spring.datasource.druid.validation-query=SELECT 'x'
#申请连接的时候检测,建议配置为true,不影响性能,并且保证安全性
spring.datasource.druid.test-while-idle=true
#获取连接时执行检测,建议关闭,影响性能
spring.datasource.druid.test-on-borrow=false
#归还连接时执行检测,建议关闭,影响性能
spring.datasource.druid.test-on-return=false
#druid 用户
spring.datasource.druid.stat-view-servlet.login-username=admin
#druid 密码
spring.datasource.druid.stat-view-servlet.login-password=admin
#配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
spring.datasource.druid.filters=stat,wall
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=1
# Db Master
spring.datasource.druid.master.name=master
spring.datasource.druid.master.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.master.url=jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
spring.datasource.druid.master.username=root
spring.datasource.druid.master.password=123456
# Db Slaves
spring.datasource.druid.slave.name=slave
spring.datasource.druid.slave.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.slave.url=jdbc:mysql://localhost:3307/test?useSSL=false&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
spring.datasource.druid.slave.username=root
spring.datasource.druid.slave.password=123456
项目地址:https://github.com/baojingyu/spring-boot-jpa-dynamic-datasource
点个关注,和我一起共同进步吧!