1. 環(huán)境描述

本次測(cè)試基于JeecgBoot 2.4.6奔穿，測(cè)試代碼在Jeecg-boot-module-system中編寫机蔗。

2. 引入坐標(biāo)

<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參數(shù)前联，絕大部分值和默認(rèn)保持一致。(現(xiàn)已支持的參數(shù)如下,不清楚含義不要亂設(shè)置)
        # 連接池的配置信息
        # 初始化大小稿黍，最小暇番，最大
        initial-size: 5
        min-idle: 5
        maxActive: 20
        # 配置獲取連接等待超時(shí)的時(shí)間
        maxWait: 60000
        # 配置間隔多久才進(jìn)行一次檢測(cè)，檢測(cè)需要關(guān)閉的空閑連接吞琐，單位是毫秒
        timeBetweenEvictionRunsMillis: 60000
        # 配置一個(gè)連接在池中最小生存的時(shí)間捆探，單位是毫秒
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        # 打開PSCache然爆，并且指定每個(gè)連接上PSCache的大小
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        # 配置監(jiān)控統(tǒng)計(jì)攔截的filters站粟，去掉后監(jiān)控界面sql無法統(tǒng)計(jì)，'wall'用于防火墻
        filters: stat,wall,slf4j
        # 通過connectProperties屬性來打開mergeSql功能曾雕；慢SQL記錄
        connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
      primary: master # 設(shè)置默認(rèn)的數(shù)據(jù)源或者數(shù)據(jù)源組,默認(rèn)值即為master
      strict: false   # 嚴(yán)格匹配數(shù)據(jù)源,默認(rèn)false. true未匹配到指定數(shù)據(jù)源時(shí)拋異常,false使用默認(rèn)數(shù)據(jù)源
      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
          # 多數(shù)據(jù)源配置
          #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
            # 指定默認(rèn)數(shù)據(jù)源名稱
  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原有數(shù)據(jù)源的基礎(chǔ)上奴烙，增加了primary節(jié)點(diǎn)，用來設(shè)置默認(rèn)的數(shù)據(jù)源剖张。

4. 建立數(shù)據(jù)表

在jeecgboot默認(rèn)的數(shù)據(jù)庫中切诀，將sys_log表復(fù)制兩份，分別命名為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 {
    /**
     * 分表數(shù)據(jù)源名稱
     */
    public static final String SHARDING_DATA_SOURCE_NAME = "sharding";
    /**
     * 動(dòng)態(tài)數(shù)據(jù)源配置項(xiàng)
     */
    @Autowired
    private DynamicDataSourceProperties dynamicDataSourceProperties;
 
    @Lazy
    @Resource
    DataSource shardingDataSource;
 
    /**
     * 將shardingDataSource放到了多數(shù)據(jù)源（dataSourceMap）中
     * 注意有個(gè)版本的bug幅虑，3.1.1版本 不會(huì)進(jìn)入loadDataSources 方法，這樣就一直造成數(shù)據(jù)源注冊(cè)失敗
     */
    @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 管理的數(shù)據(jù)源也交給動(dòng)態(tài)數(shù)據(jù)源管理
                dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
                return dataSourceMap;
            }
        };
    }
 
    /**
     * 將動(dòng)態(tài)數(shù)據(jù)源設(shè)置為首選的
     * 當(dāng)spring存在多個(gè)數(shù)據(jù)源時(shí), 自動(dòng)注入的是首選的對(duì)象
     * 設(shè)置為主要的數(shù)據(jù)源之后顾犹，就可以支持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. 接口編寫

Mapper

package 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);
 
}

Service

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

ServiceImpl

package 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. 測(cè)試用例

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;

/**
 * 測(cè)試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. 測(cè)試結(jié)果

image

image