CREATE TABLE `t_order0` (
`id` bigint(20) unsigned NOT NULL COMMENT '主键id',
`config_id` bigint(20) unsigned NOT NULL COMMENT '',
`order_name` varchar(111) NOT NULL COMMENT '',
`tenant_name` varchar(111) NOT NULL COMMENT '',
PRIMARY KEY (`id`)
);
CREATE TABLE `t_order1` (
`id` bigint(20) unsigned NOT NULL COMMENT '主键id',
`config_id` bigint(20) unsigned NOT NULL COMMENT '',
`order_name` varchar(111) NOT NULL COMMENT '',
`tenant_name` varchar(111) NOT NULL COMMENT '',
PRIMARY KEY (`id`)
);
CREATE TABLE `t_order_item0` (
`id` bigint(20) unsigned NOT NULL COMMENT '主键id',
`order_id` bigint(20) unsigned NOT NULL COMMENT '',
`dict_id` bigint(20) unsigned NOT NULL COMMENT '',
`item_name` varchar(111) NOT NULL COMMENT '',
`tenant_name` varchar(111) NOT NULL COMMENT '',
PRIMARY KEY (`id`)
);
CREATE TABLE `t_order_item1` (
`id` bigint(20) unsigned NOT NULL COMMENT '主键id',
`order_id` bigint(20) unsigned NOT NULL COMMENT '',
`dict_id` bigint(20) unsigned NOT NULL COMMENT '',
`item_name` varchar(111) NOT NULL COMMENT '',
`tenant_name` varchar(111) NOT NULL COMMENT '',
PRIMARY KEY (`id`)
);
CREATE TABLE `t_config` (
`id` bigint(20) unsigned NOT NULL COMMENT '主键id',
`config_name` varchar(111) NOT NULL COMMENT '',
PRIMARY KEY (`id`)
);
CREATE TABLE `t_dict` (
`id` bigint(20) unsigned NOT NULL COMMENT '主键id',
`dict_name` varchar(111) NOT NULL COMMENT '',
PRIMARY KEY (`id`)
);
CREATE TABLE `t_user` (
`id` bigint(20) unsigned NOT NULL COMMENT '主键id',
`username` varchar(111) NOT NULL COMMENT '',
PRIMARY KEY (`id`)
);
insert into t_config values (1,'配置1');
insert into t_config values (2,'配置2');
insert into t_dict values (1,'字典1');
insert into t_dict values (2,'字典2');
insert into t_user values (1,'用户1');
insert into t_user values (2,'用户2');
MyConfig
@Configuration
@ConditionalOnProperty(prefix = "spring.shardingsphere", name = "enabled", havingValue = "false")
public class MyConfig {
@Bean("dsmsxf")
public DataSource dsmsxf(){
DruidDataSource ds = new DruidDataSource();
ds.setUrl("jdbc:mysql://127.0.0.1:3306/order_db0?useUnicode=true&characterEncoding=utf-8&useSSL=false");
ds.setUsername("root");
ds.setPassword("123456");
ds.setDriverClassName("com.mysql.jdbc.Driver");
return ds;
}
@Bean("dst001")
public DataSource dst001(){
DruidDataSource ds = new DruidDataSource();
ds.setUrl("jdbc:mysql://127.0.0.1:3306/order_db1?useUnicode=true&characterEncoding=utf-8&useSSL=false");
ds.setUsername("root");
ds.setPassword("123456");
ds.setDriverClassName("com.mysql.jdbc.Driver");
return ds;
}
@Bean("dst002")
public DataSource dst002(){
DruidDataSource ds = new DruidDataSource();
ds.setUrl("jdbc:mysql://127.0.0.1:3306/order_db2?useUnicode=true&characterEncoding=utf-8&useSSL=false");
ds.setUsername("root");
ds.setPassword("123456");
ds.setDriverClassName("com.mysql.jdbc.Driver");
return ds;
}
@Bean("dataSource")
public DataSource shardingDataSource(@Qualifier("dsmsxf")DataSource dsmsxf,@Qualifier("dst001") DataSource dst001,@Qualifier("dst002")DataSource dst002) throws SQLException {
// 配置真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>(3);
dataSourceMap.put("dsmsxf", dsmsxf);
dataSourceMap.put("dst001", dst001);
dataSourceMap.put("dst002", dst002);
// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.setDefaultDataSourceName("dsmsxf");
shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("tenant_name", new MyPreciseShardingAlgorithm()));
shardingRuleConfig.setDefaultTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("id", new MyPreciseShardingAlgorithm1()));
shardingRuleConfig.getTableRuleConfigs().add(orderRuleConfig());
shardingRuleConfig.getTableRuleConfigs().add(orderItemRuleConfig());
shardingRuleConfig.getBindingTableGroups().add("t_order,t_order_item");
shardingRuleConfig.getBroadcastTables().add("t_dict,t_config");
Properties p = new Properties();
p.setProperty("sql.show",Boolean.TRUE.toString());
// 获取数据源对象
DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, p);
return dataSource;
}
private TableRuleConfiguration orderRuleConfig() {
TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration("t_order","dsmsxf.t_order$->{0..1},dst001.t_order$->{0..1},dst002.t_order$->{0..1}");
tableRuleConfig.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE", "id"));
// tableRuleConfig.setTableShardingStrategyConfig();
// tableRuleConfig.setDatabaseShardingStrategyConfig();
return tableRuleConfig;
}
private TableRuleConfiguration orderItemRuleConfig() {
TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration("t_order_item","dsmsxf.t_order_item$->{0..1},dst001.t_order_item$->{0..1},dst002.t_order_item$->{0..1}");
tableRuleConfig.setKeyGeneratorConfig(new KeyGeneratorConfiguration("SNOWFLAKE", "id"));
tableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("order_id", new MyPreciseShardingAlgorithm1()));
// tableRuleConfig.setDatabaseShardingStrategyConfig();
return tableRuleConfig;
}
}
MyPreciseShardingAlgorithm
public class MyPreciseShardingAlgorithm implements PreciseShardingAlgorithm<String> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<String> shardingValue) {
String dbName = "ds";
String val = shardingValue.getValue();
dbName += val;
for (String each : availableTargetNames) {
if (each.equals(dbName)) {
return each;
}
}
throw new IllegalArgumentException();
}
}
MyPreciseShardingAlgorithm1
public class MyPreciseShardingAlgorithm1 implements PreciseShardingAlgorithm<Long> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
String tbname=shardingValue.getLogicTableName();
Long value = shardingValue.getValue();
tbname+=value%2;
return tbname;
}
}
SwaggerConfig
@Configuration
@EnableSwagger2
public class SwaggerConfig {
@Bean
public Docket createRestApi() {
return new Docket(DocumentationType.SWAGGER_2)
.groupName("分库分表").apiInfo(apiInfo())
.protocols(Sets.newHashSet("http"))
.select().apis(RequestHandlerSelectors.basePackage("com..controller"))
.build().enable(true);
}
private ApiInfo apiInfo() {
return new ApiInfoBuilder().title("分库分表 - API").description("").termsOfServiceUrl("").version("5.0.0").build();
}
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace参数为对应的mapper类的全路径-->
<mapper namespace="com..mapper.OrderMapper">
<!--resultMap定义数据库表字段和Java对象属性映射关系，如果是多表操作或者数据库字段和Java属性字段不一致时必须配置-->
<resultMap id="orderMap" type="com..model.Order">
<id column="id" property="id"/>
<result column="order_name" property="orderName"/>
<result column="tenant_name" property="tenantName"/>
</resultMap>
<insert id="insert" parameterType="com..model.Order" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
insert into t_order(order_name,tenant_name,config_id) values (#{orderName},#{tenantName},#{configId})
</insert>
<insert id="insertItem" parameterType="com..model.OrderItem">
insert into t_order_item(order_id,item_name,tenant_name,dict_id) values
(#{data.orderId},#{data.itemName},#{data.tenantName},#{data.dictId})
</insert>
<insert id="createDict">
insert into t_dict(id,dict_name) values (#{id},#{dictName})
</insert>
<insert id="createConfig">
insert into t_config(id,config_name)values(#{id},#{configName})
</insert>
<insert id="saveUser">
insert into t_user(id,username) values (#{id},#{username})
</insert>
<sql id="selectField">
o.id as orderId,toi.id as id,o.tenant_name as tenantName,o.order_name as orderName,
toi.item_name as itemName,tc.config_name as configName,td.dict_name as dictName
</sql>
<select id="findAll" resultType="com..model.OrderVo">
select <include refid="selectField"/>
from t_order o left join t_order_item toi on o.id=toi.order_id
left join t_config tc on o.config_id=tc.id
left join t_dict td on toi.dict_id=td.id order by toi.id desc
</select>
<select id="findByTenantName" resultType="com..model.OrderVo">
select <include refid="selectField"/>
from t_order o left join t_order_item toi on o.id=toi.order_id
left join t_config tc on o.config_id=tc.id
left join t_dict td on toi.dict_id=td.id
where o.tenant_name=#{tenantName} order by toi.id desc
</select>
<select id="findAllUser" resultType="com..model.User">
select * from t_user
</select>
</mapper>
Application
/**
* 注意：不加exclude={DataSourceAutoConfiguration.class}使用class方式进行配置时，启动要报错
*/
@SpringBootApplication(exclude={DataSourceAutoConfiguration.class})
@MapperScan("com.test.mybatis")
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
model
@Data
public class Order implements Serializable {
private Long id;
private Long configId;
private String orderName;
// 订单项
List<OrderItem> orderItems;
private String tenantName;
}
@Data
public class OrderItem implements Serializable {
private Long orderId;
private Long id;
private Long dictId;
private String itemName;
private String tenantName;
}
@Data
public class OrderVo {
private Long orderId;
private String orderName;
private String tenantName;
private String dictName;
private String configName;
private Long id;
private String itemName;
}
@Data
public class ResponseData<T> {
private String code;
private String message;
private T data;
public static ResponseData success(){
ResponseData result = new ResponseData();
result.code="0";
return result;
}
public static <T> ResponseData success(T object){
ResponseData result = new ResponseData();
result.code="0";
result.data=object;
return result;
}
}
@Data
public class User {
private Long id;
private String username;
}
该博客介绍了如何在SpringBoot项目中集成ShardingSphere-JDBC，实现数据库的分库分表功能。配置了多个数据源，并定义了分库分表策略，包括自定义分片算法，以及数据源、表的逻辑和实际数据节点映射。同时，展示了相关配置文件，如application.properties和MyConfig类，用于管理数据源、分片规则和读写分离。此外，还包含了MyPreciseShardingAlgorithm和MyPreciseShardingAlgorithm1两个自定义分片算法类。
扫一扫
866
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
