mysql读写分离—spring boot +mybatis

r囧r小猫 2023-06-02 12:56 9阅读 0赞

配置好mysql的主从复制后，读写分离需要在代码层面实现，本实例采用spring boot集成mybatis的方式是实现，数据源连接池使用druid

1、加入maven依赖，配置application.yml文件

pom.xml依赖jar

<dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>2.1.0</version>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
    
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.1.13</version>
            </dependency>
            <dependency>
                <groupId>org.aspectj</groupId>
                <artifactId>aspectjweaver</artifactId>
            </dependency>

application.xml配置数源

spring:
      freemarker:
        suffix: .html
        request-context-attribute: request
      datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        druid:
          master:
            url: jdbc:mysql://192.168.2.131:3306/app_db?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
            username: root
            password: root
            # 从库数据源
          slave:
              # 从数据源开关/默认关闭
            enabled: true
            url: jdbc:mysql://192.168.2.132:3306/app_db?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
            username: root
            password: root
          initialSize: 5
          # 最小连接池数量
          minIdle: 10
          # 最大连接池数量
          maxActive: 20
          # 配置获取连接等待超时的时间
          maxWait: 60000
          # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
          timeBetweenEvictionRunsMillis: 60000
          # 配置一个连接在池中最小生存的时间，单位是毫秒
          minEvictableIdleTimeMillis: 300000
          # 配置一个连接在池中最大生存的时间，单位是毫秒
          maxEvictableIdleTimeMillis: 900000
          # 配置检测连接是否有效
          validationQuery: SELECT 1 FROM DUAL
          testWhileIdle: true
          testOnBorrow: false
          testOnReturn: false
          webStatFilter:
            enabled: true
          statViewServlet:
            enabled: true
            # 设置白名单，不填则允许所有访问
            allow:
            url-pattern: /monitor/druid/*
          filter:
            stat:
              enabled: true
              # 慢SQL记录
              log-slow-sql: true
              slow-sql-millis: 1000
              merge-sql: true
            wall:
              config:
                multi-statement-allow: true
    spring:
      freemarker:
        suffix: .html
        request-context-attribute: request
      datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        druid:
          master:
            url: jdbc:mysql://192.168.2.131:3306/app_db?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
            username: root
            password: root
            # 从库数据源
          slave:
              # 从数据源开关/默认关闭
            enabled: true
            url: jdbc:mysql://192.168.2.132:3306/app_db?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
            username: root
            password: root
          initialSize: 5
          # 最小连接池数量
          minIdle: 10
          # 最大连接池数量
          maxActive: 20
          # 配置获取连接等待超时的时间
          maxWait: 60000
          # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
          timeBetweenEvictionRunsMillis: 60000
          # 配置一个连接在池中最小生存的时间，单位是毫秒
          minEvictableIdleTimeMillis: 300000
          # 配置一个连接在池中最大生存的时间，单位是毫秒
          maxEvictableIdleTimeMillis: 900000
          # 配置检测连接是否有效
          validationQuery: SELECT 1 FROM DUAL
          testWhileIdle: true
          testOnBorrow: false
          testOnReturn: false
          webStatFilter:
            enabled: true
          statViewServlet:
            enabled: true
            # 设置白名单，不填则允许所有访问
            allow:
            url-pattern: /monitor/druid/*
          filter:
            stat:
              enabled: true
              # 慢SQL记录
              log-slow-sql: true
              slow-sql-millis: 1000
              merge-sql: true
            wall:
              config:
                multi-statement-allow: true

2、java代码实现

（1）定义主库从库常量

public interface Constant {
        String MASTER="MASTER";
        String SLAVE="SLAVE";
    }

（2）数据源切换处理，主库从库切换，主库处理写数据，从库处理读数据

public class DynamicDataSourceContextHolder
    {
        public static final Logger log = LoggerFactory.getLogger(DynamicDataSourceContextHolder.class);
    
        /**
         * 使用ThreadLocal维护变量，ThreadLocal为每个使用该变量的线程提供独立的变量副本，
         *  所以每一个线程都可以独立地改变自己的副本，而不会影响其它线程所对应的副本。
         */
        private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();
    
        /**
         * 设置数据源的变量
         */
        public static void setDataSourceType(String dsType)
        {
            log.info("切换到{}数据源", dsType);
            CONTEXT_HOLDER.set(dsType);
        }
    
        /**
         * 获得数据源的变量
         */
        public static String getDataSourceType()
        {
            return CONTEXT_HOLDER.get();
        }
    
        /**
         * 清空数据源变量
         */
        public static void clearDataSourceType()
        {
            CONTEXT_HOLDER.remove();
        }
    }

（3）动态数据源路由

public class DynamicDataSource extends AbstractRoutingDataSource
    {
        public DynamicDataSource(DataSource defaultTargetDataSource, Map<Object, Object> targetDataSources)
        {
            super.setDefaultTargetDataSource(defaultTargetDataSource);
            super.setTargetDataSources(targetDataSources);
            super.afterPropertiesSet();
        }
    
        @Override
        protected Object determineCurrentLookupKey()
        {
            //如果有多个从库，可以使用负载均衡算法，实现具体从库的路由
            return DynamicDataSourceContextHolder.getDataSourceType();
        }
    }

（4）druid 配置属性，实例化数据源

@Configuration
    public class DruidProperties
    {
        @Value("${spring.datasource.druid.initialSize}")
        private int initialSize;
    
        @Value("${spring.datasource.druid.minIdle}")
        private int minIdle;
    
        @Value("${spring.datasource.druid.maxActive}")
        private int maxActive;
    
        @Value("${spring.datasource.druid.maxWait}")
        private int maxWait;
    
        @Value("${spring.datasource.druid.timeBetweenEvictionRunsMillis}")
        private int timeBetweenEvictionRunsMillis;
    
        @Value("${spring.datasource.druid.minEvictableIdleTimeMillis}")
        private int minEvictableIdleTimeMillis;
    
        @Value("${spring.datasource.druid.maxEvictableIdleTimeMillis}")
        private int maxEvictableIdleTimeMillis;
    
        @Value("${spring.datasource.druid.validationQuery}")
        private String validationQuery;
    
        @Value("${spring.datasource.druid.testWhileIdle}")
        private boolean testWhileIdle;
    
        @Value("${spring.datasource.druid.testOnBorrow}")
        private boolean testOnBorrow;
    
        @Value("${spring.datasource.druid.testOnReturn}")
        private boolean testOnReturn;
    
        public DruidDataSource dataSource(DruidDataSource datasource)
        {
            /** 配置初始化大小、最小、最大 */
            datasource.setInitialSize(initialSize);
            datasource.setMaxActive(maxActive);
            datasource.setMinIdle(minIdle);
            /** 配置获取连接等待超时的时间 */
            datasource.setMaxWait(maxWait);
            /** 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 */
            datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
            /** 配置一个连接在池中最小、最大生存的时间，单位是毫秒 */
            datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
            datasource.setMaxEvictableIdleTimeMillis(maxEvictableIdleTimeMillis);
            /**
             * 用来检测连接是否有效的sql，要求是一个查询语句，常用select 'x'。如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
             */
            datasource.setValidationQuery(validationQuery);
            /** 建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。 */
            datasource.setTestWhileIdle(testWhileIdle);
            /** 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。 */
            datasource.setTestOnBorrow(testOnBorrow);
            /** 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。 */
            datasource.setTestOnReturn(testOnReturn);
            return datasource;
        }
    }

@Configuration
    public class DruidConfig {
    
        @Autowired
        private DruidProperties druidProperties;
    
        @Bean
        @ConfigurationProperties("spring.datasource.druid.master")
        public DataSource  masterDataSource(){
            DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
            return druidProperties.dataSource(dataSource);
        }
    
        @Bean
        @ConfigurationProperties("spring.datasource.druid.slave")
        @ConditionalOnProperty(prefix = "spring.datasource.druid.slave", name = "enabled", havingValue = "true")
        public DataSource  slaveDataSource(){
            DruidDataSource dataSource = DruidDataSourceBuilder.create().build();
            return druidProperties.dataSource(dataSource);
        }
    
        @Bean(name = "dynamicDataSource")
        @Primary
        public DynamicDataSource dataSource()
        {
            Map<Object, Object> targetDataSources = new HashMap<>(2);
            targetDataSources.put(Constant.MASTER, masterDataSource());
            targetDataSources.put(Constant.SLAVE,slaveDataSource());
            return new DynamicDataSource(masterDataSource(), targetDataSources);
        }
    
        @Bean
        public SqlSessionFactory  sqlSessionFactory() throws  Exception{
            SqlSessionFactoryBean  sqlSessionFactoryBean=new SqlSessionFactoryBean();
            sqlSessionFactoryBean.setDataSource(dataSource());
            ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
            sqlSessionFactoryBean.setMapperLocations(resolver.getResources("classpath:mapper/*.xml"));
            sqlSessionFactoryBean.setTypeAliasesPackage("cn.com.fcw.mysqlproxy");
            return sqlSessionFactoryBean.getObject();
        }
    
        /**
         * 开启事务管理
         * @return
         */
        @Bean
        public DataSourceTransactionManager dataSourceTransactionManager(){
            return  new DataSourceTransactionManager(dataSource());
        }
    }

（5）利用AOP技术操作具体的读写分离业务

/**
     * 自定义多数据源切换注解
     * 
     * @author inquiry
     */
    @Target(ElementType.METHOD)
    @Retention(RetentionPolicy.RUNTIME)
    public @interface RwDataSource
    {
        /**
         * 切换数据源名称
         */
        String value() default Constant.MASTER;
    }

@Aspect
    @Order(1)
    @Component
    public class DataSourceAspect
    {
        protected Logger logger = LoggerFactory.getLogger(getClass());
    
        @Pointcut("@annotation(cn.com.fcw.mysqlproxy.config.RwDataSource)")
        public void dsPointCut() { }
    
        @Around("dsPointCut()")
        public Object around(ProceedingJoinPoint point) throws Throwable
        {
            MethodSignature signature = (MethodSignature) point.getSignature();
    
            Method method = signature.getMethod();
    
            RwDataSource dataSource = method.getAnnotation(RwDataSource.class);
    
            if (dataSource!=null)
            {
                DynamicDataSourceContextHolder.setDataSourceType(dataSource.value());
            }
    
            try
            {
                return point.proceed();
            }
            finally
            {
                // 销毁数据源 在执行方法之后
                DynamicDataSourceContextHolder.clearDataSourceType();
            }
        }
    }

（6）读写分离具体实现，将注解使用在service的具体方法上

@Service
    public class UserServiceImpl {
    
        @Autowired
        private UserMapper userMapper;
    
        @RwDataSource
        public BaseBean insert(UserEntity userEntity){
            Integer res = userMapper.insert(userEntity);
            return BaseBean.getBaseBean(res,res);
        }
        @RwDataSource
        public BaseBean update(UserEntity userEntity){
            Integer res = userMapper.update(userEntity);
            return BaseBean.getBaseBean(res,res);
        }
    
        @RwDataSource
        public BaseBean delete(Integer id){
            Integer res = userMapper.delete(id);
            return BaseBean.getBaseBean(res,res);
        }
    
        @RwDataSource(Constant.SLAVE)
        public BaseBean list(){
            List<UserEntity> userEntities = userMapper.selectUserList();
            return BaseBean.getBaseBean(userEntities,0);
        }
    }