spring-boot mariadb读写分离，spring-bootmariadb

和通数据库htsjk.Com2019-07-19 01:17 来源:未知阅读:1328 评论 402 热度4

标签：

spring-boot mariadb读写分离，spring-bootmariadb

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

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

《mariaDB在linux7中的安装》

《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了