spring-boot 速成(9) druid+mybatis 多数据源及读写分离的处理

时间：2023-03-08 23:23:01
spring-boot 速成(9) druid+mybatis 多数据源及读写分离的处理

按上节继续学习，稍微复杂的业务系统，一般会将数据库按业务拆开，比如产品系统的数据库放在product db中，订单系统的数据库放在order db中...，然后，如果量大了，可能每个库还要考虑做读、写分离，以进一步提高系统性能，下面就来看看如何处理：

核心思路：配置多个数据源，然后利用RoutingDataSource结合AOP来动态切不同的库。

要解决的问题：

1、配置文件中，多数据源的配置节点如何设计？

 druid:

     type: com.alibaba.druid.pool.DruidDataSource

     study:

       master: #study库的主库

         url: jdbc:mysql://localhost:/study?useSSL=false&characterEncoding=UTF-&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true

         driver-class-name: com.mysql.cj.jdbc.Driver

         username: root

         password: A1b2c3@def.com

         initial-size:

         min-idle:

         max-active:

         test-on-borrow: true

       slave: #study库的从库

         url: jdbc:mysql://localhost:/study_slave?useSSL=false&characterEncoding=UTF-&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true

         driver-class-name: com.mysql.cj.jdbc.Driver

         username: root

         password: A1b2c3@def.com

         initial-size:

         min-idle:

         max-active:

         test-on-borrow: true

     product:

       master: #product库的主库

         url: jdbc:mysql://localhost:/product?useSSL=false&characterEncoding=UTF-&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true

         driver-class-name: com.mysql.cj.jdbc.Driver

         username: root

         password: A1b2c3@def.com

         initial-size:

         min-idle:

         max-active:

         test-on-borrow: true

       slave: #product库的从库

         url: jdbc:mysql://localhost:/product_slave?useSSL=false&characterEncoding=UTF-&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true

         driver-class-name: com.mysql.cj.jdbc.Driver

         username: root

         password: A1b2c3@def.com

         initial-size:

         min-idle:

         max-active:

         test-on-borrow: true

上面的配置写法供参数，如果slave节点数要扩展，按这个格式，改造成slave1,slave2... 自行扩展。

2、配置类如何设计？

 package com.cnblogs.yjmyzz.db.config;

 /**

  * Created by jimmy on 6/18/17.

  */

 import com.cnblogs.yjmyzz.db.datasource.DbContextHolder;

 import com.cnblogs.yjmyzz.db.datasource.MasterSlaveRoutingDataSource;

 import org.springframework.beans.factory.annotation.Value;

 import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;

 import org.springframework.boot.context.properties.ConfigurationProperties;

 import org.springframework.context.annotation.Bean;

 import org.springframework.context.annotation.Configuration;

 import org.springframework.context.annotation.Primary;

 import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

 import org.springframework.transaction.annotation.EnableTransactionManagement;

 import javax.sql.DataSource;

 import java.util.HashMap;

 import java.util.Map;

 @Configuration

 @EnableTransactionManagement

 public class DataSourceConfiguration {

     @Value("${druid.type}")

     private Class<? extends DataSource> dataSourceType;

     @Bean(name = "studyMasterDataSource")

     @ConfigurationProperties(prefix = "druid.study.master")

     public DataSource studyMasterDataSource() {

         return DataSourceBuilder.create().type(dataSourceType).build();

     }

     @Bean(name = "studySlaveDataSource")

     @ConfigurationProperties(prefix = "druid.study.slave")

     public DataSource studySlaveDataSource1() {

         return DataSourceBuilder.create().type(dataSourceType).build();

     }

     @Bean(name = "productMasterDataSource")

     @ConfigurationProperties(prefix = "druid.product.master")

     public DataSource productMasterDataSource() {

         return DataSourceBuilder.create().type(dataSourceType).build();

     }

     @Bean(name = "productSlaveDataSource")

     @ConfigurationProperties(prefix = "druid.product.slave")

     public DataSource productSlaveDataSource1() {

         return DataSourceBuilder.create().type(dataSourceType).build();

     }

     @Bean(name = "dataSource")

     @Primary

     public AbstractRoutingDataSource dataSource() {

         MasterSlaveRoutingDataSource proxy = new MasterSlaveRoutingDataSource();

         Map<Object, Object> targetDataResources = new HashMap<>();

         targetDataResources.put(DbContextHolder.DbType.PRODUCT_MASTER, productMasterDataSource());

         targetDataResources.put(DbContextHolder.DbType.PRODUCT_SLAVE, productSlaveDataSource1());

         targetDataResources.put(DbContextHolder.DbType.STUDY_MASTER, studyMasterDataSource());

         targetDataResources.put(DbContextHolder.DbType.STUDY_SLAVE, studySlaveDataSource1());

         proxy.setDefaultTargetDataSource(productMasterDataSource());

         proxy.setTargetDataSources(targetDataResources);

         proxy.afterPropertiesSet();

         return proxy;

     }

 }

参考这个，一看就明，不说多(注：@Primary一定要在动态数据源上，否则事务回滚无效！）

3、根据什么来切换db?

有很多选择，

a、用约定的方法前缀，比如：get/query/list开头的约定为读从库，其它为主库，但是这样还要考虑不同业务库的切换（即：何时切换到product库，何时切换到order库，可以再用不同的Scanner来处理，略复杂）

b、用自定义注解来处理，比如 @ProductMaster注解，表示切换到product的master库，这样同时把业务库，以及主还是从，一次性解决了，推荐这种。

spring-boot 速成(9) druid+mybatis 多数据源及读写分离的处理

这里，我定义了4个注解，代表product，study二个库的主及从。

4、aop在哪里拦截,如何拦截？

service层和mapper层都可以拦截，推荐在服务层拦截，否则如果一个业务方法里，即有读又有写，还得考虑如果遇到事务，要考虑的东西更多。

当然，如果拦截特定的注解，就不用过多考虑在哪个层，只认注解就行（当然，注解还是建议打在服务层上）。

dubbo-starter的一个小坑：spring boot中，只有managed bean才能用aop拦截，而dubbo-starter中的@service注解不是spring中的注解（是阿里package下的自定义注解），生成的service provider实例，aop拦截不到，解决办法，再加一个注解让spring认识它，参考：

spring-boot 速成(9) druid+mybatis 多数据源及读写分离的处理

Aop拦截类的参考代码如下：

 package com.cnblogs.yjmyzz.db.aspect;

 import com.cnblogs.yjmyzz.db.annotation.ProductMaster;

 import com.cnblogs.yjmyzz.db.annotation.ProductSlave;

 import com.cnblogs.yjmyzz.db.annotation.StudyMaster;

 import com.cnblogs.yjmyzz.db.annotation.StudySlave;

 import com.cnblogs.yjmyzz.db.datasource.DbContextHolder;

 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;

 @Aspect

 @Component

 public class MasterSlaveAspect implements Ordered {

     public static final Logger logger = LoggerFactory.getLogger(MasterSlaveAspect.class);

     /**

      * 切换到product主库

      *

      * @param proceedingJoinPoint

      * @param productMaster

      * @return

      * @throws Throwable

      */

     @Around("@annotation(productMaster)")

     public Object proceed(ProceedingJoinPoint proceedingJoinPoint, ProductMaster productMaster) throws Throwable {

         try {

             logger.info("set database connection to product-master only");

             DbContextHolder.setDbType(DbContextHolder.DbType.PRODUCT_MASTER);

             Object result = proceedingJoinPoint.proceed();

             return result;

         } finally {

             DbContextHolder.clearDbType();

             logger.info("restore database connection");

         }

     }

     /**

      * 切换到product从库

      *

      * @param proceedingJoinPoint

      * @param productSlave

      * @return

      * @throws Throwable

      */

     @Around("@annotation(productSlave)")

     public Object proceed(ProceedingJoinPoint proceedingJoinPoint, ProductSlave productSlave) throws Throwable {

         try {

             logger.info("set database connection to product-slave only");

             DbContextHolder.setDbType(DbContextHolder.DbType.PRODUCT_SLAVE);

             Object result = proceedingJoinPoint.proceed();

             return result;

         } finally {

             DbContextHolder.clearDbType();

             logger.info("restore database connection");

         }

     }

     /**

      * 切换到study主库

      *

      * @param proceedingJoinPoint

      * @param studyMaster

      * @return

      * @throws Throwable

      */

     @Around("@annotation(studyMaster)")

     public Object proceed(ProceedingJoinPoint proceedingJoinPoint, StudyMaster studyMaster) throws Throwable {

         try {

             logger.info("set database connection to study-master only");

             DbContextHolder.setDbType(DbContextHolder.DbType.STUDY_MASTER);

             Object result = proceedingJoinPoint.proceed();

             return result;

         } finally {

             DbContextHolder.clearDbType();

             logger.info("restore database connection");

         }

     }

     /**

      * 切换到study从库

      *

      * @param proceedingJoinPoint

      * @param studySlave

      * @return

      * @throws Throwable

      */

     @Around("@annotation(studySlave)")

     public Object proceed(ProceedingJoinPoint proceedingJoinPoint, StudySlave studySlave) throws Throwable {

         try {

             logger.info("set database connection to study-slave only");

             DbContextHolder.setDbType(DbContextHolder.DbType.STUDY_SLAVE);

             Object result = proceedingJoinPoint.proceed();

             return result;

         } finally {

             DbContextHolder.clearDbType();

             logger.info("restore database connection");

         }

     }

     @Override

     public int getOrder() {

         return 0;

     }

 }

5、其它事项

启用类上，一定要排除spring-boot自带的datasource配置，即：

 @SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})

 @EnableAspectJAutoProxy

 @ComponentScan("com.cnblogs.yjmyzz")

 @MapperScan(basePackages = "com.cnblogs.yjmyzz.dao.mapper")

 public class ServiceProvider {

     public static void main(String[] args) {

         SpringApplication.run(ServiceProvider.class, args);

     }

 }

第1行：@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})

6、日志中如何输出格式化且带参数值的sql?

一般的sql输出是这样的：

spring-boot 速成(9) druid+mybatis 多数据源及读写分离的处理

我们可以把它变成下面这样：

spring-boot 速成(9) druid+mybatis 多数据源及读写分离的处理

是不是更友好！

方法：加一个mybtais的拦截器即可

package com.cnblogs.yjmyzz.db.interceptor;

import com.cnblogs.yjmyzz.util.PrettySQLFormatter;

import org.apache.ibatis.cache.CacheKey;

import org.apache.ibatis.executor.Executor;

import org.apache.ibatis.mapping.BoundSql;

import org.apache.ibatis.mapping.MappedStatement;

import org.apache.ibatis.mapping.ParameterMapping;

import org.apache.ibatis.plugin.*;

import org.apache.ibatis.reflection.MetaObject;

import org.apache.ibatis.session.Configuration;

import org.apache.ibatis.session.ResultHandler;

import org.apache.ibatis.session.RowBounds;

import org.apache.ibatis.type.TypeHandlerRegistry;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.List;

import java.util.Properties;

/**

 * Created by 菩提树下的杨过(http://yjmyzz.cnblogs.com/) on 28/07/2017.

 */

@Intercepts({

        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),

        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),

        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class})})

public class MybatisInterceptor implements Interceptor {

    private static Logger logger = LoggerFactory.getLogger(MybatisInterceptor.class);

    private Properties properties;

    private final static SimpleDateFormat sdt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    @Override

    public Object intercept(Invocation invocation) throws Throwable {

        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];

        Object parameter = null;

        if (invocation.getArgs().length > 1) {

            parameter = invocation.getArgs()[1];

        }

        String sqlId = mappedStatement.getId();

        BoundSql boundSql = mappedStatement.getBoundSql(parameter);

        Configuration configuration = mappedStatement.getConfiguration();

        Object returnValue;

        long start = System.currentTimeMillis();

        returnValue = invocation.proceed();

        long end = System.currentTimeMillis();

        long time = (end - start);

        if (time > 1) {

            String sql = getSql(configuration, boundSql, sqlId, time);

            logger.debug("mapper method ==> " + sql.split("\\^")[0] + "\n，" + PrettySQLFormatter.getPrettySql(sql.split("\\^")[1]) + "\n\n，" + "sql execute time ==> " + time + " ms\n\n");

        }

        return returnValue;

    }

    @Override

    public Object plugin(Object target) {

        return Plugin.wrap(target, this);

    }

    @Override

    public void setProperties(Properties properties) {

        this.properties = properties;

    }

    public static String getSql(Configuration configuration, BoundSql boundSql, String sqlId, long time) {

        String sql = showSql(configuration, boundSql);

        StringBuilder str = new StringBuilder(100);

        str.append(sqlId);

        str.append("^");

        str.append(sql);

        str.append("^");

        str.append(time);

        str.append("ms");

        return str.toString();

    }

    private static String getParameterValue(Object obj) {

        String value;

        if (obj instanceof String) {

            value = "'" + obj.toString() + "'";

        } else if (obj instanceof Date) {

            value = "'" + sdt.format(obj) + "'";

        } else {

            if (obj != null) {

                value = obj.toString();

            } else {

                value = "";

            }

        }

        return value;

    }

    public static String showSql(Configuration configuration, BoundSql boundSql) {

        Object parameterObject = boundSql.getParameterObject();

        List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();

        String sql = boundSql.getSql().replaceAll("[\\s]+", " ");

        if (parameterMappings.size() > 0 && parameterObject != null) {

            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();

            if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {

                sql = sql.replaceFirst("\\?", getParameterValue(parameterObject));

            } else {

                MetaObject metaObject = configuration.newMetaObject(parameterObject);

                for (ParameterMapping parameterMapping : parameterMappings) {

                    String propertyName = parameterMapping.getProperty();

                    if (metaObject.hasGetter(propertyName)) {

                        Object obj = metaObject.getValue(propertyName);

                        sql = sql.replaceFirst("\\?", getParameterValue(obj));

                    } else if (boundSql.hasAdditionalParameter(propertyName)) {

                        Object obj = boundSql.getAdditionalParameter(propertyName);

                        sql = sql.replaceFirst("\\?", getParameterValue(obj));

                    }

                }

            }

        }

        return sql;

    }

}

这里面还用了hibernate的一个小工具，用于格式化sql

package com.cnblogs.yjmyzz.util;

import org.hibernate.engine.jdbc.internal.FormatStyle;

public class PrettySQLFormatter {

    public static void print(String sql) {

        System.out.println(FormatStyle.BASIC.getFormatter().format(sql));

    }

    public static void print(String remark, String sql) {

        System.out.println(remark

                + FormatStyle.BASIC.getFormatter().format(sql));

    }

    public static String getPrettySql(String sql) {

        return FormatStyle.BASIC.getFormatter().format(sql);

    }

    public static String getPrettySql(String remark, String sql) {

        return remark + FormatStyle.BASIC.getFormatter().format(sql);

    }

    public static void main(String[] args) {

        System.out.println(getPrettySql("select * from MyUser as A join MyFriend as B on A.id = B.pid where B.name like ? "));

    }

}

接下来，把这个拦截器配置在mybatis-config.xml里

 <?xml version="1.0" encoding="UTF-8"?>

 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

         "http://mybatis.org/dtd/mybatis-3-config.dtd">

 <configuration>

     <settings>

         <setting name="cacheEnabled" value="true"/>

     </settings>

     <plugins>

         <plugin interceptor="com.cnblogs.yjmyzz.db.interceptor.MybatisInterceptor">

         </plugin>

     </plugins>

 </configuration>

最后在application.yml里指定mybatis-config.xml所在的路径：

spring-boot 速成(9) druid+mybatis 多数据源及读写分离的处理

示例源码见：https://github.com/yjmyzz/spring-boot-dubbo-demo （dubbox2.8.5-multi-ds分支）