shardingsphere-jdbc实现分库分表

提供了单独只进行分库和分库分表的两种实现demo.其实两者的区别在于定义TableRuleConfiguration(表的路由规则)时是否设置分库或者分表规则

这里主要是针对的数据分片的分库分表的数据配置 , 若有主从库配置或者其他的场景的可参考官方文档 : https://shardingsphere.apache.org/document/5.1.0/en/user-manual/shardingsphere-jdbc/ . 这里面有api/starter/yaml/properties等各种配置支持.

1.POM配置

<dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!--mybatis-->
<dependency>
   <groupId>org.mybatis.spring.boot</groupId>
   <artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<!--mysql-->
<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
   <groupId>com.alibaba</groupId>
   <artifactId>druid-spring-boot-starter</artifactId>
</dependency>
<!-- shardingsphere -->
<dependency>
   <groupId>org.apache.shardingsphere</groupId>
   <artifactId>sharding-core-api</artifactId>
   <version>4.1.1</version>
</dependency>
<dependency>
   <groupId>org.apache.shardingsphere</groupId>
   <artifactId>sharding-jdbc-core</artifactId>
   <version>4.1.1</version>
</dependency>

2.变量配置.这里变量名可根据自己需求进行命名

 

# ************************** sharding sphere config start ****************************
# 参与分库的表,用逗号分隔,不在此定义的,直接路由到默认库中
SHARDING-DATABASE-TABLE-NAMES=B_USER,T_SP
# 参与分表的表,用逗号分隔
SHARDING-TABLE-NAMES=T_SP

# 公共分库字段
database.sharding.column=TENANT_ID

# 表b_user的路由节点
table.user.actual-data-nodes=cloudtravel_consumer$->{1..2}.b_user

# 表t_sp的路由节点
table.sp.actual-data-nodes=cloudtravel_consumer$->{1..2}.t_sp_$->{0..1}

# 表t_sp分表字段
table.sp.sharding.column=BIZ_ID

# Configure the 1st data source
sharding.datasource1.name=cloudtravel_consumer1
datasource.cloudtravel-consumer1.driver-class-name=com.mysql.cj.jdbc.Driver
datasource.cloudtravel-consumer1.url=jdbc:mysql://localhost:3306/cloudtravel_consumer1?useAffectedRows=true&serverTimezone=UTC&characterEncoding=utf-8
datasource.cloudtravel-consumer1.username=root
datasource.cloudtravel-consumer1.password=root

# Configure the 2nd data source
SHARDING.datasource2.name=cloudtravel_consumer2
datasource.cloudtravel-consumer2.driver-class-name=com.mysql.cj.jdbc.Driver
datasource.cloudtravel-consumer2.url=jdbc:mysql://localhost:3306/cloudtravel_consumer2?useAffectedRows=true&serverTimezone=UTC&characterEncoding=utf-8
datasource.cloudtravel-consumer2.username=root
datasource.cloudtravel-consumer2.password=root

# 是否打印sql语句

mybatis.mapper-locations=classpath:mapper/*.xml


# ************************** sharding sphere config end ****************************

3. 引入配置信息

package com.cloudtravel.shardingsphere.db;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.annotation.Order;

@Configuration
@Order(10)
public class DataSourceConfigBase {

    @Value("${sharding.datasource1.name}")
    private String datasourceName1;

    @Value("${SHARDING.datasource2.name}")
    private String datasourceName2;

    @Value("${mybatis.mapper-locations}")
    private String mapperLocations;

    @Value("${database.sharding.column}")
    private String databaseShardingColumnDefault;

    @Value("${SHARDING-DATABASE-TABLE-NAMES}")
    private String shardingDatabaseTableNames;

    @Value("${SHARDING-TABLE-NAMES}")
    private String shardingTableNames;

    @Value("${table.user.actual-data-nodes}")
    private String userActualDataNodes;

    @Value("${table.sp.actual-data-nodes}")
    private String spActualDataNodes;

    @Value("${table.sp.sharding.column}")
    private String tableSpShardingColumn;

    public String getDatasourceName1() {
        return datasourceName1;
    }

    public String getDatasourceName2() {
        return datasourceName2;
    }

    public String getMapperLocations() {
        return mapperLocations;
    }

    public String getDatabaseShardingColumnDefault() {
        return databaseShardingColumnDefault;
    }

    public String getShardingDatabaseTableNames() {
        return shardingDatabaseTableNames;
    }

    public String getShardingTableNames() {
        return shardingTableNames;
    }

    public String getUserActualDataNodes() {
        return userActualDataNodes;
    }

    public String getSpActualDataNodes() {
        return spActualDataNodes;
    }

    public String getTableSpShardingColumn() {
        return tableSpShardingColumn;
    }
}

 

4.基础配置类

@Configuration
@MapperScan(basePackages = "com.cloudtravel.shardingsphere.dao" , sqlSessionTemplateRef = "testSqlSessionTemplate")
@Order(11)
public class DataSourceConfig {

    @Autowired
    private TableShardingAlgorithm tableShardingAlgorithmCom;

    @Autowired
    private DatabaseShardingAlgorithm databaseShardingAlgorithm;

    @Autowired
    DataSourceConfigBase dataSourceConfigBase;

    @Autowired
    Environment environment;

    /**
     * 设置数据源
     * @return
     * @throws SQLException
     */
    @Primary
    @Bean(name = "shardingDataSource")
    DataSource getShardingDataSource() throws SQLException {
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();

        // 设置默认的分库策略
        shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(
                new StandardShardingStrategyConfiguration(
                        dataSourceConfigBase.getDatabaseShardingColumnDefault(),
                        databaseShardingAlgorithm
                )
        );
        //  配置表规则
        shardingRuleConfig.getTableRuleConfigs().
                addAll(Arrays.asList(
                        getBUserRuleConfiguration() ,
                        getTSpRuleConfiguration()
                        )
                );
        // 设置默认数据库
        shardingRuleConfig.setDefaultDataSourceName(dataSourceConfigBase.getDatasourceName1());

        return ShardingDataSourceFactory.createDataSource(createDataSourceMap(), shardingRuleConfig, new Properties());
    }


    /**
     * 获取sqlSessionFactory实例
     * @param shardingDataSource
     * @return
     * @throws Exception
     */
    @Bean("SqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(@Qualifier("shardingDataSource") DataSource shardingDataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(shardingDataSource);
        bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(dataSourceConfigBase.getMapperLocations()));
        return bean.getObject();
    }

    @Bean
    @Primary
    public SqlSessionTemplate testSqlSessionTemplate(@Qualifier("SqlSessionFactory")SqlSessionFactory sqlSessionFactory)  {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    /**
     * 需要手动配置事务管理器
     * @param shardingDataSource
     * @return
     */
    @Bean
    public DataSourceTransactionManager transactitonManager(@Qualifier("shardingDataSource") DataSource shardingDataSource) {
        return new DataSourceTransactionManager(shardingDataSource);
    }

    /**
     * 具体表进行分库分表的规则配置
     * @return
     */
    private TableRuleConfiguration getBUserRuleConfiguration() {
        TableRuleConfiguration orderTableRuleConfig=new TableRuleConfiguration("b_user",
                dataSourceConfigBase.getUserActualDataNodes());
        orderTableRuleConfig.setDatabaseShardingStrategyConfig(
                new StandardShardingStrategyConfiguration(
                        dataSourceConfigBase.getDatabaseShardingColumnDefault(),
                        databaseShardingAlgorithm
                )
        );
        return orderTableRuleConfig;
    }

    /**
     * 具体表进行分库分表的规则配置
     * @return
     */
    private TableRuleConfiguration getTSpRuleConfiguration() {
        TableRuleConfiguration orderTableRuleConfig=new TableRuleConfiguration("t_sp",
                dataSourceConfigBase.getSpActualDataNodes());
        orderTableRuleConfig.setDatabaseShardingStrategyConfig(
                new StandardShardingStrategyConfiguration(
                        dataSourceConfigBase.getDatabaseShardingColumnDefault(),
                        databaseShardingAlgorithm
                )
        );
        orderTableRuleConfig.setTableShardingStrategyConfig(
                new ComplexShardingStrategyConfiguration(
                        dataSourceConfigBase.getTableSpShardingColumn(),
                        tableShardingAlgorithmCom
                )
        );
        return orderTableRuleConfig;
    }


    public DataSource druidDataSource1() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(environment.getProperty("datasource.cloudtravel-consumer1.url"));
        dataSource.setUsername(environment.getProperty("datasource.cloudtravel-consumer1.username"));
        dataSource.setPassword(environment.getProperty("datasource.cloudtravel-consumer1.password"));
        dataSource.setDriverClassName(environment.getProperty("datasource.cloudtravel-consumer1.driver-class-name"));
        return dataSource;
    }

    public DataSource druidDataSource2() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(environment.getProperty("datasource.cloudtravel-consumer2.url"));
        dataSource.setUsername(environment.getProperty("datasource.cloudtravel-consumer2.username"));
        dataSource.setPassword(environment.getProperty("datasource.cloudtravel-consumer2.password"));
        dataSource.setDriverClassName(environment.getProperty("datasource.cloudtravel-consumer2.driver-class-name"));
        return dataSource;
    }

    /**
     * 多库的连接配置
     * @return
     */
    private Map<String, DataSource> createDataSourceMap() {
        Map<String, DataSource> result = new HashMap<>();
        result.put(dataSourceConfigBase.getDatasourceName1(),druidDataSource1());
        result.put(dataSourceConfigBase.getDatasourceName2(), druidDataSource2());
        return result;
    }
}

5.分表逻辑实现

/**
 *分表逻辑
 */
@Service
public class TableShardingAlgorithm implements ComplexKeysShardingAlgorithm {

    @Value("${SHARDING-TABLE-NAMES}")
    private String SHARDING_TABLE_NAMES;

    @Override
    public Collection<String> doSharding(Collection collection, ComplexKeysShardingValue complexKeysShardingValue) {
        List<String> tables = new ArrayList<>();
        Long bizId = (Long) ((List) complexKeysShardingValue.getColumnNameAndShardingValuesMap().get("biz_id")).get(0);
        String tableName = complexKeysShardingValue.getLogicTableName();
        String physicsTable = tableName  + "_";
        physicsTable += String.valueOf(bizId % 2);
        tables.add(physicsTable);
        System.out.println(tableName + "走分表,bizId = " + bizId + "路由到表" + physicsTable);
        return tables;
    }
}

6.dataSource配置--分库逻辑实现

/**
 * 分库逻辑
 */
@Service
public class DatabaseShardingAlgorithm implements PreciseShardingAlgorithm {

    @Autowired
    private DataSourceConfigBase dataSourceConfigBase;

    @Override
    public String doSharding(Collection collection, PreciseShardingValue preciseShardingValue) {
        String dataSource = null;
        String tableName = preciseShardingValue.getLogicTableName();
        Integer value = StringUtils.isNotEmpty(preciseShardingValue.getValue().toString()) ?
                Integer.parseInt(preciseShardingValue.getValue().toString()) : 0;
        if (value % 2 == 0) {
            dataSource = dataSourceConfigBase.getDatasourceName1();
        } else {
            dataSource = dataSourceConfigBase.getDatasourceName2();
        }
        System.out.println(tableName + "走分库,tenantId = " + value + "进入" + dataSource);
        return dataSource;
    }
}

7. seata配置

resources下创建seata.config配置注册到SeaTa管理服务的应用名+分布式事务的服务组名

client {
    application.id = cloudtravel-shardingsphere
    transaction.service.group = sp-user-seata-group
}

 

8.业务测试

package com.cloudtravel.shardingsphere.service.impl;

import com.cloudtravel.shardingsphere.common.service.ShardBaseUserService;
import com.cloudtravel.shardingsphere.dao.TUserModelMapper;
import com.cloudtravel.shardingsphere.model.TUserModel;
import org.apache.shardingsphere.transaction.annotation.ShardingTransactionType;
import org.apache.shardingsphere.transaction.core.TransactionType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class ShardBaseUserServiceImpl implements ShardBaseUserService {

    @Autowired
    TUserModelMapper userModelMapper;


    @Override
//    @GlobalTransactional(name = "sp-user-seata-group" , rollbackFor = Exception.class)
    @Transactional //引入事务支持
    @ShardingTransactionType(TransactionType.BASE)//引入分布式柔性事务支持,不需分布式事务的话只用@Transactional即可
    public Long addUser(Long tenantId) {
        TUserModel model = new TUserModel();
        model.setTenantId(tenantId.toString());
        model.setBizId("1");
        model.setUserType(1);
        model.setUserName("test1");
        model.setIdNumber("231");
        model.setIdNumType(0);
        userModelMapper.insertSelective(model);
//        throw new RuntimeException("test");
        return model.getId();
    }
}

 补充 : 莫了个大名 . 看人家都说引入seata-sharding-at包就可以实现分布式事务 . 这两天调试发现不行了