ShardingSphere+达梦数据库分表操作

背景

随着数字经济时代的全面到来，数据量呈现爆炸式增长，传统单机数据库在性能、扩展性和可用性方面面临严峻挑战。分布式数据库技术应运而生，成为解决海量数据存储与处理的关键方案。在这一背景下，Apache ShardingSphere作为一款开源的分布式数据库中间件生态圈，与国产数据库领军品牌达梦数据库(DM Database)的结合，为国内企业提供了自主可控的高性能数据管理解决方案。

ShardingSphere作为Apache顶级项目，提供了包括数据分片、读写分离、分布式事务和数据库治理等核心功能，其独特优势在于：
1、对应用透明的数据库水平扩展能力
2、兼容多种数据库协议，降低迁移成本
3、灵活的插件化架构设计
4、完善的分布式事务支持
达梦数据库作为国产数据库的代表产品，具有：
1、完全自主知识产权，符合国家信创要求
2、高性能事务处理能力
3、完善的SQL标准支持
4、强大的Oracle兼容性
5、全面的安全防护机制

依赖引用

    <properties><java.version>1.8</java.version><project.build.sourceEncoding>UTF-8</project.build.sourceEncoding><project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding><spring-boot.version>2.7.6</spring-boot.version><alibaba.druid.version>1.2.6</alibaba.druid.version></properties><dependencies><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter</artifactId></dependency><dependency><groupId>com.dameng</groupId><artifactId>DmJdbcDriver18</artifactId><version>8.1.3.140</version></dependency><dependency><groupId>org.apache.shardingsphere</groupId><artifactId>sharding-jdbc-spring-boot-starter</artifactId><version>4.1.1</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>${alibaba.druid.version}</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.4.2</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.20</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency></dependencies>

数据库脚本

CREATE TABLE "TEST"."GOODS_1"
("GID" BIGINT,"GNAME" VARCHAR(50),"USER_ID" BIGINT,"GSTATUS" VARCHAR(50)
) STORAGE(ON "MAIN", CLUSTERBTR) ;CREATE TABLE "TEST"."GOODS_2"
("GID" BIGINT,"GNAME" VARCHAR(50),"USER_ID" BIGINT,"GSTATUS" VARCHAR(50)
) STORAGE(ON "MAIN", CLUSTERBTR) ;

关键代码

application.properties配置文件

mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImplspring.main.allow-bean-definition-overriding=truespring.shardingsphere.props.sql.show=true
spring.shardingsphere.datasource.names=g1spring.shardingsphere.datasource.g1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.g1.driver-class-name=dm.jdbc.driver.DmDriver
spring.shardingsphere.datasource.g1.url=jdbc:dm://192.168.1.10:5236/TEST
spring.shardingsphere.datasource.g1.username=SYSDBA
spring.shardingsphere.datasource.g1.password=********spring.shardingsphere.sharding.tables.goods.actual-data-nodes=g1.goods_$->{1..2}
spring.shardingsphere.sharding.tables.goods.key-generator.column=gid
spring.shardingsphere.sharding.tables.goods.key-generator.type=SNOWFLAKEspring.shardingsphere.sharding.tables.goods.table-strategy.inline.sharding-column=gid
spring.shardingsphere.sharding.tables.goods.table-strategy.inline.algorithm-expression=goods_$->{gid % 2 + 1}

实体类

package org.example.shardingdm.pojo;import lombok.Data;@Data
public class Goods {private Long gid;private String gname;private Long userId;private String gstatus;
}

Mapper接口

package org.example.shardingdm.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.example.shardingdm.pojo.Goods;
import org.springframework.stereotype.Repository;@Repositorypublic interface GoodsMapper extends BaseMapper<Goods> {}

测试类

package org.example.shardingdm;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import org.example.shardingdm.mapper.GoodsMapper;
import org.example.shardingdm.pojo.Goods;
import org.example.shardingdm.utils.SnowflakeUtil;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;@SpringBootTest
class SharingJdbcTestApplicationTests {@AutowiredGoodsMapper goodsMapper;@Testvoid addGoods() {Goods good = new Goods();good.setGid(SnowflakeUtil.getId());good.setGname("小米手机");good.setUserId(100L);good.setGstatus("已发布");goodsMapper.insert(good);Goods good2 = new Goods();good.setGid(SnowflakeUtil.getId());good2.setGname("华为手机");good2.setUserId(99L);good2.setGstatus("已发布");goodsMapper.insert(good2);}@Testvoid addGoods2() {for (int i = 0; i < 10; i++){Goods good = new Goods();good.setGid(SnowflakeUtil.getId());good.setGname("小米手机" + i);good.setUserId(100L);good.setGstatus("已发布");goodsMapper.insert(good);}}@Testvoid getGood3(){QueryWrapper<Goods> queryWrapper = new QueryWrapper<>();//此处根据实际gid值修改，还会查询goods表queryWrapper.eq("gid",1354449944638820361l);Goods good = goodsMapper.selectOne(queryWrapper);System.out.println(good.toString());}
}

结果

执行测试用例后，可以看到数据成功插入到了到各个表中

2025-03-26 13:50:48.810  INFO 7072 --- [           main] o.e.s.SharingJdbcTestApplicationTests    : Started SharingJdbcTestApplicationTests in 5.062 seconds (JVM running for 5.991)
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@78422efb] was not registered for synchronization because synchronization is not active
JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@af7e376] will not be managed by Spring
==>  Preparing: INSERT INTO goods ( gid, gname, user_id, gstatus ) VALUES ( ?, ?, ?, ? )
==> Parameters: 1354452535414263808(Long), 小米手机(String), 100(Long), 已发布(String)
2025-03-26 13:50:49.247  INFO 7072 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO goods  ( gid,
gname,
user_id,
gstatus )  VALUES  ( ?,
?,
?,
? )
2025-03-26 13:50:49.247  INFO 7072 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@662be9f7, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4962b41e), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@4962b41e, columnNames=[gid, gname, user_id, gstatus], insertValueContexts=[InsertValueContext(parametersCount=4, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=63, stopIndex=63, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=66, stopIndex=66, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=69, stopIndex=69, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=72, stopIndex=72, parameterMarkerIndex=3)], parameters=[1354452535414263808, 小米手机, 100, 已发布])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=gid, generated=false, generatedValues=[1354452535414263808])])
2025-03-26 13:50:49.248  INFO 7072 --- [           main] ShardingSphere-SQL                       : Actual SQL: g1 ::: INSERT INTO goods_1  ( gid,
gname,
user_id,
gstatus )  VALUES  (?, ?, ?, ?) ::: [1354452535414263808, 小米手机, 100, 已发布]
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@78422efb]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@486e9d1d] was not registered for synchronization because synchronization is not active
JDBC Connection [org.apache.shardingsphere.shardingjdbc.jdbc.core.connection.ShardingConnection@6aa5974e] will not be managed by Spring
==>  Preparing: INSERT INTO goods ( gname, user_id, gstatus ) VALUES ( ?, ?, ? )
==> Parameters: 华为手机(String), 99(Long), 已发布(String)
2025-03-26 13:50:49.286  INFO 7072 --- [           main] ShardingSphere-SQL                       : Logic SQL: INSERT INTO goods  ( gname,
user_id,
gstatus )  VALUES  ( ?,
?,
? )
2025-03-26 13:50:49.286  INFO 7072 --- [           main] ShardingSphere-SQL                       : SQLStatement: InsertStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@4c319d52, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@72fbf94d), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@72fbf94d, columnNames=[gname, user_id, gstatus], insertValueContexts=[InsertValueContext(parametersCount=3, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=58, stopIndex=58, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=61, stopIndex=61, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=64, stopIndex=64, parameterMarkerIndex=2), DerivedParameterMarkerExpressionSegment(super=ParameterMarkerExpressionSegment(startIndex=0, stopIndex=0, parameterMarkerIndex=3))], parameters=[华为手机, 99, 已发布])], generatedKeyContext=Optional[GeneratedKeyContext(columnName=gid, generated=true, generatedValues=[1111652666846478336])])
2025-03-26 13:50:49.286  INFO 7072 --- [           main] ShardingSphere-SQL                       : Actual SQL: g1 ::: INSERT INTO goods_1  ( gname,
user_id,
gstatus , gid)  VALUES  (?, ?, ?, ?) ::: [华为手机, 99, 已发布, 1111652666846478336]
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@486e9d1d]
2025-03-26 13:50:49.315  INFO 7072 --- [ionShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closing ...
2025-03-26 13:50:49.317  INFO 7072 --- [ionShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closed

注意事项

sharding-jdbc-spring-boot-starter这里注意使用4.1.1的版本，其他版本在后续进行验证。