spring-boot mariadb读写分离

野性酷女 2022-06-08 05:55 236阅读 0赞

在总结这篇文章之前，先把之前相关的文章列出来

[《spring-boot系列——与mybaits整合》][spring-boot_mybaits]

[《mariaDB在linux7中的安装》][mariaDB_linux7]

[《linux7下mariadb主从搭建》][linux7_mariadb]

思路

思路其实很简单，一句话能说清楚：多数据源配置+aop自定义配置切换数据源。这样就可以实现主从读写分离了。

注意事项：线程安全

pom

之前文章用的是1.3.5.RELEASE版本的spring-boot。现在用更新一点的1.5.3.RELEASE版本，这样必须要依赖github的分页工具

<dependency>  
            <groupId>org.springframework.boot</groupId>  
            <artifactId>spring-boot-starter-web</artifactId>  
        </dependency>  
        
        <dependency>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-test</artifactId>
    		<scope>test</scope>
    	</dependency>
    	
    	<dependency>  
            <groupId>org.springframework.boot</groupId>  
            <artifactId>spring-boot-devtools</artifactId>  
            <optional>true</optional>  
    	</dependency> 
    	
    	<dependency>  
            <groupId>org.springframework.boot</groupId>  
            <artifactId>spring-boot-starter-actuator</artifactId>  
        </dependency>  
        
        <dependency>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-aop</artifactId>
    	</dependency>	
    		  	
        <dependency>
        	<groupId>org.springframework.boot</groupId>
        	<artifactId>spring-boot-autoconfigure</artifactId>
        </dependency>	    
        
        <dependency>
    	    <groupId>javax.servlet</groupId>
    	    <artifactId>javax.servlet-api</artifactId>
    	    <scope>provided</scope>	
    	</dependency>	
    	
    	<dependency>
    		<groupId>org.springframework.boot</groupId>
    		<artifactId>spring-boot-starter-jdbc</artifactId>
    	</dependency>
    	
    	<dependency>
          <groupId>org.mybatis.spring.boot</groupId>
          <artifactId>mybatis-spring-boot-starter</artifactId>
          <version>1.1.1</version>
        </dependency>
        
        <dependency>
          <groupId>tk.mybatis</groupId>
          <artifactId>mapper-spring-boot-starter</artifactId>
          <version>1.1.0</version>
        </dependency>	
        
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.1.0</version>
            <exclusions>
                <exclusion>
                    <artifactId>mybatis-spring-boot-starter</artifactId>
                    <groupId>org.mybatis.spring.boot</groupId>
                </exclusion>
            </exclusions>
        </dependency>		 
        
        <dependency>
    	    <groupId>com.alibaba</groupId>
    	    <artifactId>druid</artifactId>
    	    <version>1.0.24</version>
    	</dependency>
    	
    	<dependency>
    		<groupId>mysql</groupId>
    		<artifactId>mysql-connector-java</artifactId>
    	</dependency>

多数据源配置

1、整合阿里巴巴的连接池druid，由于druid不是spring-boot中集成的，所以需要单独配置，当然配置方式也就很灵活了，想怎么弄就怎么弄。这里举个例子

主yml如下：

spring: 
      profiles: 
        active: dev

开发环境yml如下：

server:
      port: 8080
    
    spring:
      http:
        encoding:
          charset:
            UTF-8
          enabled:
            true
          force:
            true     
            
      output: 
        ansi: 
          enabled: always
         
    #  datasource: 
    #    url: jdbc:mysql://192.168.160.66:3306/demo?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&pinGlobalTxToPhysicalConnection=true&autoReconnect=true
    #    username: root
    #    password: nmamtf
    #    driver-class-name: com.mysql.jdbc.Driver
    #    type: com.alibaba.druid.pool.DruidDataSource
      
    druid:  
        type: com.alibaba.druid.pool.DruidDataSource
        master:
            url: jdbc:mysql://192.168.160.66:3306/demo?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
            #url: jdbc:mysql://192.168.160.66:3306/demo?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&pinGlobalTxToPhysicalConnection=true&autoReconnect=true
            driver-class-name: com.mysql.jdbc.Driver
            username: root
            password: nmamtf
            initialSize: 5
            minIdle: 1
            maxActive: 100
            maxWait: 60000
            timeBetweenEvictionRunsMillis: 60000
            minEvictableIdleTimeMillis: 300000
            validationQuery: SELECT 1 FROM DUAL
            testWhileIdle: true
            testOnBorrow: false
            testOnReturn: false
            poolPreparedStatements: true
            maxPoolPreparedStatementPerConnectionSize: 20
            filters: stat,wall,log4j
            useGlobalDataSourceStat: true
        slave:
            url: jdbc:mysql://192.168.160.88:3306/demo?characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true 
            #url: jdbc:mysql://192.168.160.88:3306/demo?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&pinGlobalTxToPhysicalConnection=true&autoReconnect=true
            driver-class-name: com.mysql.jdbc.Driver
            username: root
            password: nmamtf
            initialSize: 5
            minIdle: 1
            maxActive: 100
            maxWait: 60000
            timeBetweenEvictionRunsMillis: 60000
            minEvictableIdleTimeMillis: 300000
            validationQuery: select 1 from dual
            testWhileIdle: true
            testOnBorrow: false
            testOnReturn: false
            poolPreparedStatements: true
            maxPoolPreparedStatementPerConnectionSize: 20
            filters: stat,wall,log4j
            useGlobalDataSourceStat: true 
            
    mybatis:
        #configLocation: classpath:application_mysql.xml
        type-aliases-package: com.wlf.demo
        mapper-locations: classpath:mapping/*.xml 
        
    pagehelper: 
        helperDialect: mysql
        reasonable: true
        supportMethodsArguments: true
        params: count=countSql

有一点需要注意，因为引入了github的分页插件，如果在mybatis再配置interceptor进行分页的话，是会报错的。所以这里注释了configLocation中的application.mysql.xml。该xml中是一个分页的interceptor

java config如下

import java.sql.SQLException;
    
    import javax.sql.DataSource;
    
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.beans.factory.annotation.Value;
    import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.boot.web.servlet.FilterRegistrationBean;
    import org.springframework.boot.web.servlet.ServletRegistrationBean;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.context.annotation.Primary;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    
    import com.alibaba.druid.support.http.StatViewServlet;
    import com.alibaba.druid.support.http.WebStatFilter;
    
    @Configuration
    @EnableTransactionManagement
    public class DataSourceConfig {
    
    	private static Logger LOGGER = LoggerFactory.getLogger(DataSourceConfig.class);
    	
    	@Value("${druid.type}")
    	private Class<? extends DataSource> dataSourceType;
    	
    	@Bean(name = "masterDataSource")
    	@Primary
    	@ConfigurationProperties(prefix = "druid.master") 
    	public DataSource masterDataSource() throws SQLException{
    		DataSource masterDataSource = DataSourceBuilder.create().type(dataSourceType).build();
    		LOGGER.info("-----------------主库数据源配置-------------------", masterDataSource);
    		return masterDataSource;
    	}
     
    	@Bean(name = "slaveDataSource")
    	@ConfigurationProperties(prefix = "druid.slave")
    	public DataSource slaveDataSource(){
    		DataSource slaveDataSource = DataSourceBuilder.create().type(dataSourceType).build();
    		LOGGER.info("-----------------从库数据源配置--------------------", slaveDataSource);
    		return slaveDataSource;
    	}
    	
    }

2、切换数据源的容器，注意线程安全

public class DataBaseContextHolder {
    
    	public enum DataBaseType {
    		MASTER, SLAVE
    	}
    	
    	private static final ThreadLocal<DataBaseType> contextHolder = new ThreadLocal<DataBaseType>();
    	
    	public static void setDataBaseType(DataBaseType dataBaseType) {
    		if(dataBaseType == null) throw new NullPointerException();
    		contextHolder.set(dataBaseType);
    	}
    	
    	public static DataBaseType getDataBaseType(){
    		return contextHolder.get() == null ? DataBaseType.MASTER : contextHolder.get();
    	}
    
    	public static void clearDataBaseType(){
    		contextHolder.remove();
    	}
    	
    }

3、数据源路由的实现

通过继承AbstractRoutingDataSource，重写spring的数据源路由

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    
    public class RoutingDataSource extends AbstractRoutingDataSource {
    
    	@Override
    	protected Object determineCurrentLookupKey() {
    		return DataBaseContextHolder.getDataBaseType();
    	}
    
    }

4、将动态路由配置到sessionFactory之中

这里有两个思路：正常思路为直接new SqlSessionFactoryBean()的方式进行java config配置

import java.util.HashMap;
    import java.util.Map;
    
    import javax.annotation.Resource;
    import javax.sql.DataSource;
    
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.aspectj.apache.bcel.util.ClassLoaderRepository;
    import org.aspectj.apache.bcel.util.ClassLoaderRepository.SoftHashMap;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.boot.autoconfigure.MybatisAutoConfiguration;
    import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.boot.autoconfigure.AutoConfigureAfter;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    
    import com.wlf.demo.util.DataBaseContextHolder;
    import com.wlf.demo.util.RoutingDataSource;
    
    @Configuration
    @AutoConfigureAfter({DataSourceConfig.class})
    public class MariadbConfig {
    
    	private static Logger LOGGER = LoggerFactory.getLogger(MariadbConfig.class);
    	
    	@Resource(name="masterDataSource")
    	private DataSource masterDataSource;
    	
    	@Resource(name="slaveDataSource")
    	private DataSource slaveDataSource;
    	
    	@Bean
        @ConfigurationProperties(prefix = "mybatis")
        public SqlSessionFactoryBean sqlSessionFactoryBean(DataSource dynamicDataSource) throws Exception {
            SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
            sqlSessionFactoryBean.setDataSource(dynamicDataSource);
            return sqlSessionFactoryBean;
        }
    	
    	@Bean(name = "dynamicDataSource")
        public DataSource dataSource() {
    		RoutingDataSource dynamicDataSource = new RoutingDataSource();
            // 默认数据源
            dynamicDataSource.setDefaultTargetDataSource(masterDataSource);
    
            // 配置多数据源
            Map<Object, Object> dsMap = new HashMap(2);
            dsMap.put(DataBaseContextHolder.DataBaseType.MASTER, masterDataSource);
            dsMap.put(DataBaseContextHolder.DataBaseType.SLAVE, slaveDataSource);
            dynamicDataSource.setTargetDataSources(dsMap);
            return dynamicDataSource;
        }
    	
    }

还有一种思路是通过MybatisAutoConfiguration类中的方法构建sessionFactory，然后进行Java config配置

import java.util.HashMap;
    import java.util.Map;
    
    import javax.annotation.Resource;
    import javax.sql.DataSource;
    
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.aspectj.apache.bcel.util.ClassLoaderRepository;
    import org.aspectj.apache.bcel.util.ClassLoaderRepository.SoftHashMap;
    import org.mybatis.spring.SqlSessionFactoryBean;
    import org.mybatis.spring.boot.autoconfigure.MybatisAutoConfiguration;
    import org.mybatis.spring.boot.autoconfigure.SpringBootVFS;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.boot.autoconfigure.AutoConfigureAfter;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    
    import com.wlf.demo.util.DataBaseContextHolder;
    import com.wlf.demo.util.RoutingDataSource;
    
    @Configuration
    @AutoConfigureAfter({DataSourceConfig.class})
    public class MariadbConfig extends MybatisAutoConfiguration {
    
    	private static Logger LOGGER = LoggerFactory.getLogger(MariadbConfig.class);
    	
    	@Resource(name="masterDataSource")
    	private DataSource masterDataSource;
    	
    	@Resource(name="slaveDataSource")
    	private DataSource slaveDataSource;
    	
    	@Bean(name="sqlSessionFactory")
    	public SqlSessionFactory sqlSessionFactory() throws Exception {
    		return super.sqlSessionFactory(roundRobinDataSourceProxy());
    	}
    	
    	public AbstractRoutingDataSource roundRobinDataSourceProxy(){
    		
    		RoutingDataSource proxy = new RoutingDataSource();
    		//proxy.
    		SoftHashMap targetDataSource = new ClassLoaderRepository.SoftHashMap(); 
    		targetDataSource.put(DataBaseContextHolder.DataBaseType.MASTER, masterDataSource);
    		targetDataSource.put(DataBaseContextHolder.DataBaseType.SLAVE, slaveDataSource);
    		//默认数据源
    		proxy.setDefaultTargetDataSource(masterDataSource);
    		//装入两个主从数据源
    		proxy.setTargetDataSources(targetDataSource);
    		return proxy;
    		
    	}
    	
    }

注意：该配置必须在数据源配置后再执行

5、自定义注释

import java.lang.annotation.ElementType;
    import java.lang.annotation.Retention;
    import java.lang.annotation.RetentionPolicy;
    import java.lang.annotation.Target;
    
    @Target({ElementType.METHOD, ElementType.TYPE})
    @Retention(RetentionPolicy.RUNTIME)
    public @interface ReadOnlyConnection {
    
    }

6、aop

import org.aspectj.lang.ProceedingJoinPoint;
    import org.aspectj.lang.annotation.Around;
    import org.aspectj.lang.annotation.Aspect;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    import org.springframework.core.Ordered;
    import org.springframework.stereotype.Component;
    
    import com.wlf.demo.annotation.ReadOnlyConnection;
    import com.wlf.demo.util.DataBaseContextHolder;
    
    @Aspect
    @Component
    public class ReadOnlyConnectionInterceptor implements Ordered {
    
    	public static final Logger LOGGER = LoggerFactory.getLogger(ReadOnlyConnectionInterceptor.class);
    	
    	@Around("@annotation(readOnlyConnection)")
    	public Object proceed(ProceedingJoinPoint proceedingJoinPoint, ReadOnlyConnection readOnlyConnection) throws Throwable {
    		try{
    			LOGGER.info("---------------设置为从库---------------");
    			DataBaseContextHolder.setDataBaseType(DataBaseContextHolder.DataBaseType.SLAVE);
    			Object result = proceedingJoinPoint.proceed();
    			return result;
    		} finally {
    			DataBaseContextHolder.clearDataBaseType();
    			LOGGER.info("---------------清空数据库设置---------------");
    		}
    	}
    	
    	@Override
    	public int getOrder() {
    		return 0;
    	}
    
    }

使用方式，在读从库的方法上标注自定义注释，比如：

import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.List;
    
    import javax.annotation.Resource;
    import javax.sql.DataSource;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.transaction.annotation.Transactional;
    
    import com.wlf.demo.annotation.ReadOnlyConnection;
    import com.wlf.demo.dao.UserDao;
    import com.wlf.demo.model.User;
    import com.wlf.demo.service.UserService;
    
    @Service
    public class UserServiceImpl implements UserService {
    
    	@Autowired
    	private UserDao userDao;
    	
    	@Override
    	@ReadOnlyConnection
    	public List<User> getList() {
    		List<User> users = userDao.findList();
    		return users;
    	}
    
    	@Override
    	@Transactional
    	public int insert(User user) {
    		int rows = userDao.insert(user);
    		return rows;
    	}
    
    }

这样，mariadb读写分离就ok了

[spring-boot_mybaits]: http://blog.csdn.net/guduyishuai/article/details/57920134
[mariaDB_linux7]: http://blog.csdn.net/guduyishuai/article/details/77933083
[linux7_mariadb]: http://blog.csdn.net/guduyishuai/article/details/77983380