非常好用的数据库, mybatis 高可用集群工具

数据库, mybatis 集群工具

介绍 (码云地址 https://gitee.com/devg/devg-dbcluster)

数据库, mybatis 集群工具,业务系统中主库挂了,切换到从库,保证了业务系统的高可用

核心代码

1. 创建sqlSessionFactory
   @Bean(name = “sqlSessionFactory”)
   public SqlSessionFactory sqlSessionFactorys() {
   try {
   SqlSessionFactoryBean sessionFactoryBean = new SqlSessionFactoryBean();
   sessionFactoryBean.setDataSource(roundRobinDataSouceProxy());
   // 读取配置
   sessionFactoryBean.setTypeAliasesPackage(devgDbclusterConfig.getMybatisConfig().getBasePackage());
   //设置mapper.xml文件所在位置
   Resource[] resources = new PathMatchingResourcePatternResolver()
   .getResources(devgDbclusterConfig.getMybatisConfig().getMapperLocations());
   sessionFactoryBean.setMapperLocations(resources);
   //设置mybatis-config.xml配置文件位置
   sessionFactoryBean.setConfigLocation(
   new DefaultResourceLoader().getResource(devgDbclusterConfig.getMybatisConfig().getConfigLocation()));
   //添加分页插件、打印sql插件
   return sessionFactoryBean.getObject();
   } catch (IOException e) {
   log.error(“mybatis resolver mapper*xml is error”, e);
   return null;
   } catch (Exception e) {
   log.error(“mybatis sqlSessionFactoryBean create error”, e);
   return null;
   }
   }

2. 数据库路由配置(把所有数据库都放在路由中)
   @Bean(name = “roundRobinDataSouceProxy”)
   public AbstractRoutingDataSource roundRobinDataSouceProxy() {
   Map<Object, Object> targetDataSources = new HashMap<Object, Object>();
   targetDataSources.put(“masterDataSource”, masterDataSource);
   targetDataSources.put(“slaveDataSource”, slaveDataSource);
   AbstractRoutingDataSource proxy = new AbstractRoutingDataSource() {
   @Override
   protected Object determineCurrentLookupKey() {
   String dsType = dataSourceMap.get(dataSourceType);
   if (null == dsType || dsType.equals("")) {
   dsType = “masterDataSource”;
   dataSourceMap.putIfAbsent(dataSourceType, dsType);
   }
   log.info(“use DataSource:”+dsType);
   return dsType;
   }
   };
   proxy.setDefaultTargetDataSource(masterDataSource);
   proxy.setTargetDataSources(targetDataSources);
   return proxy;
   }

3. sqlSessionTemplate配置
   @Bean
   public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
   return new SqlSessionTemplate(sqlSessionFactory);
   }

4. 事务管理配置
   @Bean
   public PlatformTransactionManager annotationDrivenTransactionManager() {
   return new DataSourceTransactionManager(roundRobinDataSouceProxy());
   }

配置文件(application.yml)

    spring:
       datasource:
         type: com.alibaba.druid.pool.DruidDataSource
         master:
           username: xxxxxx
           password: xxxxxx
           driverClassName: com.mysql.cj.jdbc.Driver
           url: jdbc:mysql://xxxx.xxxx.xxxx.xxxx:3306/monitor?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false
           initialSize: 10
           minIdle: 10
           maxActive: 50
           maxWait: 60000
           timeBetweenEvictionRunsMillis: 60000
           minEvictableIdleTimeMillis: 300000
           validationQuery: SELECT 1 FROM DUAL
           testWhileIdle: true
           testOnBorrow: false
           testOnReturn: false
           poolPreparedStatements: true
           filters: stat,wall,log4j
           maxPoolPreparedStatementPerConnectionSize: 20
           connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
         slave:
           username: xxxxxx
           password: xxxxxx
           driverClassName: com.mysql.cj.jdbc.Driver
           url: jdbc:mysql://xxxx.xxxx.xxxx.xxxx:3306/monitor?autoReconnect=true&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&useSSL=false
           initialSize: 10
           minIdle: 10
           maxActive: 50
           maxWait: 60000
           timeBetweenEvictionRunsMillis: 60000
           minEvictableIdleTimeMillis: 300000
           validationQuery: SELECT 1 FROM DUAL
           testWhileIdle: true
           testOnBorrow: false
           testOnReturn: false
           poolPreparedStatements: true
           filters: stat,wall,log4j
           maxPoolPreparedStatementPerConnectionSize: 20
           connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500


   devg:
      dbcluster:
        mybatis-config:
          mapperLocations: classpath*:com/org/devg/**/*.xml           # mybatis 映射路径
          configLocation: classpath:mybatis/mybatis.cfg.xml           # mybatis配置文件路径
          basePackage: com.org.devg                                   # mybatis扫描包
        master-config:                                                # db master节点配置
          host: xxxx.xxxx.xxxx.xxxx                                   # db master host                                       
          port: 3306                                                  # db master port
          timeOut: 3                                                  # db master socket 连接超时时长(单位秒)
          checkIntervalSecond: 5                                     # db master 间隔检查时长(单位秒)
          checkErrorCount: 3                                          # db master 检查失败次数,超过该次数切换到slave节点

jar包引用pom.xml

      <dependencies>
         <dependency>
           <groupId>org.mybatis.spring.boot</groupId>
           <artifactId>mybatis-spring-boot-starter</artifactId>
           <version>xxxxx</version>
         </dependency>
         <dependency>
           <groupId>mysql</groupId>
           <artifactId>mysql-connector-java</artifactId>
           <version>xxxx</version>
         </dependency>
         <dependency>
           <groupId>com.alibaba</groupId>
           <artifactId>druid</artifactId>
           <version>xxxx</version>
         </dependency>
         <dependency>
           <groupId>com.org.devg</groupId>
           <artifactId>dbcluster</artifactId>
           <version>1.0-SNAPSHOT</version>
         </dependency>
      </dependencies>