Spring Boot教程十：数据库读写分离

偏执的太偏执、 2021-09-14 18:22 298阅读 0赞

文章实现一主两从的续写分离：工程目录

![70][]

首先pom文件

<dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-web</artifactId>
       <exclusions>
          <exclusion>
             <groupId>org.springframework.boot</groupId>
             <artifactId>spring-boot-starter-logging</artifactId>
          </exclusion>
       </exclusions>
    </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-starter-log4j</artifactId>
       <version>1.3.8.RELEASE</version>
    </dependency>
     <dependency>
       <groupId>com.alibaba</groupId>
       <artifactId>druid</artifactId>
       <version>1.0.18</version>
    </dependency>
    
     <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <version>5.1.25</version>
    </dependency>
    
     <dependency>
       <groupId>org.mybatis.spring.boot</groupId>
       <artifactId>mybatis-spring-boot-starter</artifactId>
       <version>${mybatis.spring.boot.version}</version>
    </dependency>
    
    <dependency>
       <groupId>commons-collections</groupId>
       <artifactId>commons-collections</artifactId>
    </dependency>
     <dependency>
       <groupId>io.springfox</groupId>
       <artifactId>springfox-swagger2</artifactId>
       <version>2.6.1</version>
    </dependency>
    
    <dependency>
       <groupId>io.springfox</groupId>
       <artifactId>springfox-swagger-ui</artifactId>
       <version>2.6.1</version>
    </dependency>
    
     <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-redis</artifactId>
       <version>1.2.8.RELEASE</version>
    </dependency>
    
     <dependency>
       <groupId>com.google.code.gson</groupId>
       <artifactId>gson</artifactId>
    </dependency>
    
     <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-devtools</artifactId>
    </dependency>
    <dependency>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-jdbc</artifactId>
       <optional>true</optional>
    </dependency>
    
     <dependency>
       <groupId>org.springframework</groupId>
       <artifactId>spring-aop</artifactId>
       <version>4.3.12.RELEASE</version>
    </dependency>
    
    <dependency>
       <groupId>org.springframework</groupId>
       <artifactId>spring-aspects</artifactId>
       <version>4.3.12.RELEASE</version>
    </dependency>
    <dependency>
       <groupId>org.aspectj</groupId>
       <artifactId>aspectjrt</artifactId>
       <version>1.6.11</version>
    </dependency>
    <dependency>
       <groupId>org.aspectj</groupId>
       <artifactId>aspectjweaver</artifactId>
       <version>1.6.11</version>
    </dependency>
    <dependency>
       <groupId>cglib</groupId>
       <artifactId>cglib</artifactId>
       <version>2.1_3</version>
    </dependency>
    
    <dependency>
       <groupId>javax.servlet</groupId>
       <artifactId>servlet-api</artifactId>
       <version>2.5</version>
    </dependency>

application.properties配置文件

#根据环境读取配置文件 #application-test.properties：测试环境 #application-dev.properties：开发环境 #application-prod.properties：生产环境 spring.profiles.active=dev #数据源类型 spring.datasource.type=com.alibaba.druid.pool.DruidDataSource #从库数据源个数 spring.datasource.readSize=2 # Mybatis配置 spring.mybatis.configLocation=mybatis/mybatis.xml # mapper路径 spring.mybatis.mapperLocations=mapper/*.xml

application-dev.properties配置文件

# Server settings (ServerProperties) #端口 server.port=8082 server.address=127.0.0.1 #server.sessionTimeout=30 #访问路径名称 server.contextPath=/test #关闭springboot自带的ioc spring.mvc.favicon.enabled = false #主数据源配置 spring.datasource.driverClassName=com.mysql.jdbc.Driver spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test spring.datasource.username=root spring.datasource.password=123456 # 初始化大小，最小，最大 spring.datasource.initialSize=5 spring.datasource.minIdle=5 spring.datasource.maxActive=20 # 配置获取连接等待超时的时间 spring.datasource.maxWait=60000 # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 spring.datasource.timeBetweenEvictionRunsMillis=60000 # 配置一个连接在池中最小生存的时间，单位是毫秒 spring.datasource.minEvictableIdleTimeMillis=300000 spring.datasource.validationQuery=SELECT 1 FROM DUAL spring.datasource.testWhileIdle=true spring.datasource.testOnBorrow=false spring.datasource.testOnReturn=false # 打开PSCache，并且指定每个连接上PSCache的大小 spring.datasource.poolPreparedStatements=true spring.datasource.maxPoolPreparedStatementPerConnectionSize=20 # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙 spring.datasource.filters=stat,wall,logback # 通过connectProperties属性来打开mergeSql功能；慢SQL记录 spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 # 合并多个DruidDataSource的监控数据 spring.datasource.useGlobalDataSourceStat=true # 从数据源 #spring.slave.type=com.alibaba.druid.pool.DruidDataSource spring.slave.driverClassName=com.mysql.jdbc.Driver spring.slave.url=jdbc:mysql://127.0.0.1:3306/webdb spring.slave.username=root spring.slave.password=123456 spring.slave.initialSize=5 spring.slave.minIdle=5 spring.slave.maxActive=20 # 配置获取连接等待超时的时间 spring.slave.maxWait=60000 # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 spring.slave.timeBetweenEvictionRunsMillis=60000 # 配置一个连接在池中最小生存的时间，单位是毫秒 spring.slave.minEvictableIdleTimeMillis=300000 spring.slave.validationQuery=SELECT 1 FROM DUAL spring.slave.testWhileIdle=true spring.slave.testOnBorrow=false spring.slave.testOnReturn=false # 打开PSCache，并且指定每个连接上PSCache的大小 spring.slave.poolPreparedStatements=true spring.slave.maxPoolPreparedStatementPerConnectionSize=20 # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙 spring.slave.filters=stat,wall,logback # 通过connectProperties属性来打开mergeSql功能；慢SQL记录 spring.slave.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 # 合并多个DruidDataSource的监控数据 spring.slave.useGlobalDataSourceStat=true #spring.read2.type=com.alibaba.druid.pool.DruidDataSource spring.read2.driverClassName=com.mysql.jdbc.Driver spring.read2.url=jdbc:mysql://127.0.0.1:3306/jfinal spring.read2.username=root spring.read2.password=123456 spring.read2.initialSize=5 spring.read2.minIdle=5 spring.read2.maxActive=20 # 配置获取连接等待超时的时间 spring.read2.maxWait=60000 # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 spring.read2.timeBetweenEvictionRunsMillis=60000 # 配置一个连接在池中最小生存的时间，单位是毫秒 spring.read2.minEvictableIdleTimeMillis=300000 spring.read2.validationQuery=SELECT 1 FROM DUAL spring.read2.testWhileIdle=true spring.read2.testOnBorrow=false spring.read2.testOnReturn=false # 打开PSCache，并且指定每个连接上PSCache的大小 spring.read2.poolPreparedStatements=true spring.read2.maxPoolPreparedStatementPerConnectionSize=20 # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙 spring.read2.filters=stat,wall,logback # 通过connectProperties属性来打开mergeSql功能；慢SQL记录 spring.read2.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 # 合并多个DruidDataSource的监控数据 spring.read2.useGlobalDataSourceStat=true

拦截器配置：

@Aspect
    @Order(-1)// 保证该AOP在@Transactional之前执行 @Component
    public class DataSourceAop {
        private static Logger logger = Logger.getLogger(DataSourceAop.class);
        @Before("execution(* com.ganinfo.*.mapper..*.select*(..)) || execution(* com.ganinfo.*.mapper..*.get*(..))|| execution(* com.ganinfo.*.mapper..*.query*(..))")
        public void setReadDataSourceType() {
            DataSourceContextHolder.read();
            logger.info("dataSource 切换到：Read");
        }
    
        @Before("execution(* com.ganinfo.*.mapper..*.insert*(..)) || execution(* com.ganinfo.*.mapper..*.update*(..))")
        public void setWriteDataSourceType() {
            DataSourceContextHolder.write();
            logger.info("dataSource 切换到：Write");
        }

public class DataSourceContextHolder {
        private static Logger logger = Logger.getLogger(DataSourceContextHolder.class);
        private static final ThreadLocal<String> local = new ThreadLocal<String>();
    
        public static ThreadLocal<String> getLocal() {
            return local;
        }
    
        /** * 读可能是多个库 */ public static void read() {
            logger.debug("读操作-----");
            local.set(DataSourceType.read.getType());
        }
    
        /** * 写只有一个库 */ public static void write() {
            logger.debug("写操作-----");
            local.set(DataSourceType.write.getType());
        }
    
        public static String getJdbcType() {
            return local.get();
        }
    }

@Configuration
    @EnableTransactionManagement
    public class DataSourceTransactionManager extends DataSourceTransactionManagerAutoConfiguration {
        private static Logger logger = Logger.getLogger(DataSourceContextHolder.class);
        /** * 自定义事务 * MyBatis自动参与到spring事务管理中，无需额外配置，只要org.mybatis.spring.SqlSessionFactoryBean引用的数据源与DataSourceTransactionManager引用的数据源一致即可，否则事务管理会不起作用。 * @return */ @Resource(name = "writeDataSource")
        private DataSource dataSource;
    
        @Bean(name = "transactionManager")
        public org.springframework.jdbc.datasource.DataSourceTransactionManager transactionManagers() {
            logger.info("-------------------- transactionManager init ---------------------");
            return new org.springframework.jdbc.datasource.DataSourceTransactionManager(dataSource);
        }
    }

/** * @author Shuyu.Wang * @package:com.ganinfo.datasource * @className:MyAbstractRoutingDataSource * @description:多数据源切换 * @date 2018-01-28 14:47 **/ public class MyAbstractRoutingDataSource extends AbstractRoutingDataSource {
    
        private final int dataSourceNumber;
        private AtomicInteger count = new AtomicInteger(0);
    
        public MyAbstractRoutingDataSource(int dataSourceNumber) {
            this.dataSourceNumber = dataSourceNumber;
        }
    
        @Override
        protected Object determineCurrentLookupKey() {
            String typeKey = DataSourceContextHolder.getJdbcType();
            // 写 if (typeKey.equals(DataSourceType.write.getType())) {
                return DataSourceType.write.getType();
            }
            // 读简单负载均衡 int number = count.getAndAdd(1);
            int lookupKey = number % dataSourceNumber;
            return new Integer(lookupKey);
        }
    }

/** * @package:com.ganinfo.config * @className:DataBaseConfiguration * @description:读取mybatis.properties配置文件 * @author Shuyu.Wang * @date 2018-01-28 13:48 **/ @Configuration
    //@PropertySource("classpath:mybatis/mybatis.properties") public class DataBaseConfiguration {
        private Logger logger = Logger.getLogger(DataBaseConfiguration.class);
        @Value("${spring.datasource.type}")
        private Class<? extends DataSource> dataSourceType;
    
        @Bean(name="writeDataSource", destroyMethod = "close", initMethod="init")
        @Primary
        @ConfigurationProperties(prefix = "spring.datasource")
        public DataSource writeDataSource() {
            logger.info("-------------------- writeDataSource init ---------------------");
            DataSource dataSource=DataSourceBuilder.create().type(dataSourceType).build();
            return dataSource;
        }
        /** * 有多少个从库就要配置多少个 * @return */ @Bean(name = "readDataSource1")
        @ConfigurationProperties(prefix = "spring.slave")
        public DataSource readDataSourceOne(){
            logger.info("-------------------- readDataSourceOne init --------------------");
            return DataSourceBuilder.create().type(dataSourceType).build();
        }
    
        @Bean(name = "readDataSource2")
        @ConfigurationProperties(prefix = "spring.read2")
        public DataSource readDataSourceTwo() {
            logger.info("-------------------- readDataSourceTwo init --------------------");
            return DataSourceBuilder.create().type(dataSourceType).build();
        }
        @Bean("readDataSources")
        public List<DataSource> readDataSources(){
            List<DataSource> dataSources=new ArrayList<>();
            dataSources.add(readDataSourceOne());
            dataSources.add(readDataSourceTwo());
            return dataSources;
        }
    }

这样就实现读写分离了，业务代码正常写就可以了，程序会自动实现读写分离。

代码地址：http://download.csdn.net/download/wang\_shuyu/10248395

[70]: /images/20210901/7ec2fee91af945809c10f81d222947d2.png