zhangdaiscott 发表于 2021-12-25 21:58:21

JeecgBoot集成Sharding-JDBC

1. 环境描述本次测试基于JeecgBoot 2.4.6，测试代码在Jeecg-boot-module-system中编写。
2. 引入坐标<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>
3. 配置yml文件datasource:
    druid:
      stat-view-servlet:
      enabled: true
      loginUsername: admin
      loginPassword: 123456
      allow:
      web-stat-filter:
      enabled: true
    dynamic:
      druid: # 全局druid参数，绝大部分值和默认保持一致。(现已支持的参数如下,不清楚含义不要乱设置)
      # 连接池的配置信息
      # 初始化大小，最小，最大
      initial-size: 5
      min-idle: 5
      maxActive: 20
      # 配置获取连接等待超时的时间
      maxWait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
      timeBetweenEvictionRunsMillis: 60000
      # 配置一个连接在池中最小生存的时间，单位是毫秒
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      # 打开PSCache，并且指定每个连接上PSCache的大小
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      # 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
      filters: stat,wall,slf4j
      # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
      connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
      primary: master # 设置默认的数据源或者数据源组,默认值即为master
      strict: false   # 严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
      datasource:
      master:
          url: jdbc:mysql://127.0.0.1:3306/jeecg-boot?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
          username: root
          password: root
          driver-class-name: com.mysql.cj.jdbc.Driver
          # 多数据源配置
          #multi-datasource1:
          #url: jdbc:mysql://localhost:3306/jeecg-boot2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
          #username: root
          #password: root
          #driver-class-name: com.mysql.cj.jdbc.Driver
            # 指定默认数据源名称
shardingsphere:
    props:
      sql:
      show: true
    dataSource:
      names: ds0
      ds0:
      type: com.alibaba.druid.pool.DruidDataSource
      driverClassName: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3306/jeecg-boot?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
      username: root
      password: root
    sharding:
      tables:
      sys_log:
          logicTable: sys_log
          actualDataNodes: ds0.sys_log$->{1..2}
          tableStrategy:
            inline:
            shardingColumn: id
            algorithmExpression: sys_log$->{id % 2 + 1}
          keyGenerator:
            type: SNOWFLAKE
            column: id
            worker:
            id: 1
注意：在jeecgboot原有数据源的基础上，增加了primary节点，用来设置默认的数据源。
4. 建立数据表在jeecgboot默认的数据库中，将sys_log表复制两份，分别命名为sys_log1和sys_log2
5. 添加配置类在config目录下，添加配置类DataSourceConfiguration和DataSourceHealthConfig其中DataSourceConfiguration.java配置类代码如下：package org.jeecg.config;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceAutoConfiguration;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.context.annotation.Primary;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;

@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class})

public class DataSourceConfiguration {
    /**
   * 分表数据源名称
   */
    public static final String SHARDING_DATA_SOURCE_NAME = "sharding";
    /**
   * 动态数据源配置项
   */
    @Autowired
    private DynamicDataSourceProperties dynamicDataSourceProperties;

    @Lazy
    @Resource
    DataSource shardingDataSource;

    /**
   * 将shardingDataSource放到了多数据源（dataSourceMap）中
   * 注意有个版本的bug，3.1.1版本 不会进入loadDataSources 方法，这样就一直造成数据源注册失败
   */
    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
      Map<String, DataSourceProperty> datasourceMap = dynamicDataSourceProperties.getDatasource();
      return new AbstractDataSourceProvider() {
            @Override
            public Map<String, DataSource> loadDataSources() {
                Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
                // 将 shardingjdbc 管理的数据源也交给动态数据源管理
                dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
                return dataSourceMap;
            }
      };
    }

    /**
   * 将动态数据源设置为首选的
   * 当spring存在多个数据源时, 自动注入的是首选的对象
   * 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了
   *
   * @return
   */
    @Primary
    @Bean
    public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
      DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
      dataSource.setPrimary(dynamicDataSourceProperties.getPrimary());
      dataSource.setStrict(dynamicDataSourceProperties.getStrict());
      dataSource.setStrategy(dynamicDataSourceProperties.getStrategy());
      dataSource.setProvider(dynamicDataSourceProvider);
      dataSource.setP6spy(dynamicDataSourceProperties.getP6spy());
      dataSource.setSeata(dynamicDataSourceProperties.getSeata());
      return dataSource;
    }
}DataSourceHealthConfig.java配置类代码如下：package org.jeecg.config;

import org.springframework.beans.factory.ObjectProvider;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.actuate.autoconfigure.jdbc.DataSourceHealthContributorAutoConfiguration;
import org.springframework.boot.actuate.health.AbstractHealthIndicator;
import org.springframework.boot.actuate.jdbc.DataSourceHealthIndicator;
import org.springframework.boot.jdbc.metadata.DataSourcePoolMetadataProvider;
import org.springframework.context.annotation.Configuration;
import org.springframework.util.StringUtils;
import javax.sql.DataSource;
import java.util.Map;

@Configuration
public class DataSourceHealthConfig extends DataSourceHealthContributorAutoConfiguration {

    @Value("${spring.datasource.dbcp2.validation-query:select 1}")
    private String defaultQuery;


    public DataSourceHealthConfig(Map<String, DataSource> dataSources, ObjectProvider<DataSourcePoolMetadataProvider> metadataProviders) {
      super(dataSources, metadataProviders);
    }

    @Override
    protected AbstractHealthIndicator createIndicator(DataSource source) {
      DataSourceHealthIndicator indicator = (DataSourceHealthIndicator) super.createIndicator(source);
      if (!StringUtils.hasText(indicator.getQuery())) {
            indicator.setQuery(defaultQuery);
      }
      return indicator;
    }
}
6. 接口编写Mapperpackage org.jeecg.modules.shardingjdbc.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
* Created by sunh.
*/
@Mapper
@Repository
public interface SysLogShardingMapper {

    /**
   * 插入日志
   * @param type
   * @param content
   * @param operateType
   * @return
   */

    @Insert("insert into sys_log(log_type,log_content,operate_type)values( #{type},#{content},#{operateType})")
    int insertLog( @Param("type") int type, @Param("content") String content, @Param("operateType") int operateType);

}Servicepackage org.jeecg.modules.shardingjdbc.service;

public interface SysLogShardingService {
    int insertLog( int type, String content, int operateType);
}ServiceImplpackage org.jeecg.modules.shardingjdbc.service.Impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import lombok.extern.slf4j.Slf4j;
import org.jeecg.config.DataSourceConfiguration;
import org.jeecg.modules.shardingjdbc.mapper.SysLogShardingDao;
import org.jeecg.modules.shardingjdbc.service.SysLogShardingService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Slf4j
@Service
@DS(DataSourceConfiguration.SHARDING_DATA_SOURCE_NAME)
public class SysLogShardingServiceImpl implements SysLogShardingService {
    @Autowired
    private SysLogShardingDao sysLogShardingDao;

    @Override
    public int insertLog(int type, String content, int operateType) {
      int affectedRows = sysLogShardingDao.insertLog( type,content, operateType);
      return affectedRows;
    }
}
7. 测试用例package org.jeecg.modules.shardingjdbc.controller;
import org.jeecg.common.api.vo.Result;
import org.jeecg.modules.shardingjdbc.mapper.SysLogShardingDao;
import org.jeecg.modules.shardingjdbc.service.SysLogShardingService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

/**
* 测试sharding-jdbc
*/
@RestController
@RequestMapping("/sys_log")
public class SysLogShardingController {
    @Autowired
    private SysLogShardingService sysLogShardingService;
    @GetMapping("/test1")
    public Result<?> TestMongoDb(){
      for(int i=1;i<20;i++){
            sysLogShardingService.insertLog( i,"jeecgboot",i);
      }
      return Result.OK("存入成功");
    }
}
8. 测试结果https://oscimg.oschina.net/oscnet/up-79cf9c6f126f8e8fd2e74a4713b591f5f98.png https://oscimg.oschina.net/oscnet/up-3e6ca8f441320bb7001f4523e7e6ea5ddee.png
本文转载自：https://blog.csdn.net/u013473447/article/details/121951020

水果奶娘99 发表于 2022-11-25 15:17:46

使用高质量的现代远摄增距镜所损失的幸运飞艇直播，澳洲幸运20走势图，福彩双色球结果影像质量已经比过去小得多了。所以，你可以根据价格和便利方面的优点，决定是否在可接受的影像质量方面作出有限的让步
页: [1]
查看完整版本: JeecgBoot集成Sharding-JDBC