SpringBoot2整合Sharding-jdbc分库分表案例

最新推荐文章于 2024-07-29 06:30:00 发布

夏木炎

最新推荐文章于 2024-07-29 06:30:00 发布

阅读量414

点赞数

文章标签： java spring spring boot

本文链接：https://blog.csdn.net/xm393392625/article/details/130131802

版权

一、案例环境

1）、pom依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <modelVersion>4.0.0</modelVersion>

    <artifactId>zhq_test_sharding</artifactId>

    <dependencies>
        <!-- jpa -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- 引入shardingjdbc依赖信息 -->
        <dependency>
            <groupId>io.shardingjdbc</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>2.0.3</version>
        </dependency>
        <dependency>
            <groupId>com.dangdang</groupId>
            <artifactId>sharding-jdbc-self-id-generator</artifactId>
            <version>1.4.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.12</version>
        </dependency>

    </dependencies>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>


</project>

2）、全局配置

###数据库访问连接
spring:
  jdbc:
    db0:
      password: root
      className: com.mysql.jdbc.Driver
      url: jdbc:mysql://127.0.0.1:3307/%s?characterEncoding=utf-8
      username: root
  jpa:
    database: mysql
    show-sql: true
    hibernate:
    ## 自己建表
      ddl-auto: none
  application:
    name: sharding-jdbc-first

3）、测试代码entity->repository->controller

package com.zhq.entity;

import lombok.Data;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Data
@Entity
@Table(name = "t_order")
public class OrderEntity {
	@Id
	private Long orderId;

	private Long userId;

}

package com.zhq.repository;

import com.zhq.entity.OrderEntity;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;


public interface OrderRepository extends CrudRepository<OrderEntity, Long> {
	@Query(value = "SELECT order_id ,user_id  FROM t_order  where order_id in (?1);", nativeQuery = true)
	public List<OrderEntity> findExpiredOrderState(List<String> bpIds);
}

package com.zhq.controller;


import com.zhq.entity.OrderEntity;
import com.zhq.repository.OrderRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.ArrayList;
import java.util.List;

/**
 * @author Mr.Zheng
 * @Program: parent
 * @Description:  Controller
 * @date 2020-05-14 10:28
 */
@RestController
public class OrderController {

	@Autowired
	private OrderRepository orderRepository;


	/**
	 * 查询所有的订单信息
	 * @return List<OrderEntity>
	 */
	@RequestMapping("/getOrderAll")
	public List<OrderEntity> getOrderAll() {
		return (List<OrderEntity>) orderRepository.findAll();
	}


	/**
	 *  使用in条件查询
	 * @return List<OrderEntity>
	 */
	@RequestMapping("/inOrder")
	public List<OrderEntity> inOrder() {
		List<String> ids = new ArrayList<>();
		ids.add("2");
		ids.add("3");
		ids.add("4");
		ids.add("5");
		return orderRepository.findExpiredOrderState(ids);

	}


	/**
	 * 增加
	 * @param orderEntity
	 * @return String
	 */
	@RequestMapping("/insertOrder")
	public String insertOrder(OrderEntity orderEntity) {
		for (int i = 0; i < 10; i++) {
			OrderEntity order = new OrderEntity();
			order.setOrderId((long) i);
			order.setUserId((long) i);
			orderRepository.save(order);
		}
		return "success";
	}

}

package com.zhq;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;

/**
 * @author Mr.Zheng
 * @Program: parent
 * @Description:
 * @date 2020-05-14 10:27
 */
@SpringBootApplication
@EnableJpaRepositories(basePackages = "com.zhq.repository")
public class AppTestSharding {
    public static void main(String[] args) {
        SpringApplication.run(AppTestSharding.class,args);
    }
}

二、单库分表案例

1）、创建db_0数据库然后创建创建表t_order_0、t_order_1

CREATE TABLE `t_order_0` (
  `order_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE `t_order_1` (
  `order_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2）、单库分表核心配置代码

方式一：自定义配置，通过类形式自己实现

package com.zhq.config;


import com.alibaba.druid.pool.DruidDataSource;
import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.TableShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.id.generator.IdGenerator;
import com.dangdang.ddframe.rdb.sharding.id.generator.self.CommonSelfIdGenerator;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

/**
 * @author Mr.Zheng
 * @Program: parent
 * @Description: 数据源相关配置信息
 * @date 2020-05-14 10:28
 */
@Configuration
public class DataSourceConfig {

    @Value("${spring.jdbc.db0.className}")
    private String className;
    @Value("${spring.jdbc.db0.url}")
    private String url;
    @Value("${spring.jdbc.db0.username}")
    private String username;
    @Value("${spring.jdbc.db0.password}")
    private String password;

    @Bean
    public IdGenerator getIdGenerator() {
        return new CommonSelfIdGenerator();
    }

    @Bean
    public DataSource getDataSource() {
        return buildDataSource();
    }

    private DataSource buildDataSource() {
        // 1.设置分库映射
        Map<String, DataSource> dataSourceMap = new HashMap<>(2);
        dataSourceMap.put("db_0", createDataSource("db_0"));
        /*
         * 设置默认db为db_0，也就是为那些没有配置分库分表策略的指定的默认库
         * 如果只有一个库，也就是不需要分库的话，map里只放一个映射就行了，只有一个库时不需要指定默认库，
         * 但2个及以上时必须指定默认库，否则那些没有配置策略的表将无法操作数据
         */
        DataSourceRule rule = new DataSourceRule(dataSourceMap, "db_0");
        // 2.设置分表映射，将t_order_0和t_order_1两个实际的表映射到t_order逻辑表
        TableRule orderTableRule = TableRule.builder("t_order").actualTables(Arrays.asList("t_order_0", "t_order_1"))
                .dataSourceRule(rule).build();
        // 3.具体的分库分表策略
        ShardingRule shardingRule = ShardingRule.builder().dataSourceRule(rule)
                .tableRules(Arrays.asList(orderTableRule))
                // 根据userid分片字段
                .tableShardingStrategy(new TableShardingStrategy("user_id", new TableShardingAlgorithm())).build();
        // 创建数据源
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule);
        return dataSource;
    }

    private DataSource createDataSource(String dataSourceName) {
        // 使用druid连接数据库
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(className);
        druidDataSource.setUrl(String.format(url, dataSourceName));
        druidDataSource.setUsername(username);
        druidDataSource.setPassword(password);
        return druidDataSource;
    }


}

package com.zhq.config;

import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm;

import java.util.Collection;

/**
 * @author Mr.Zheng
 * @Program: parent
 * @Description: 自定义取模算法分表规则
 * @date 2020-05-14 10:31
 */
public class TableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {
    /**
     * sql 中关键字 匹配符为 =的时候，表的路由函数
     * @param availableTargetNames 表名
     * @param shardingValue 分表键
     * @return String 表名
     */
    @Override
    public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
        for (String tableName : availableTargetNames) {
           //分表键取模表个数，匹配表名后缀
            if(tableName.endsWith(shardingValue.getValue()%availableTargetNames.size()+"")){
                return tableName;
            }

        }
        throw new IllegalArgumentException();
    }

    @Override
    public Collection<String> doInSharding(Collection<String> collection, ShardingValue<Long> shardingValue) {
        return null;
    }

    @Override
    public Collection<String> doBetweenSharding(Collection<String> collection, ShardingValue<Long> shardingValue) {
        return null;
    }
}

方式二：修改全局配置文件，通过默认配置

	<dependency>
			<groupId>io.shardingsphere</groupId>
			<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
			<version>3.0.0.M3</version>
		</dependency>

spring:
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: none
    database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
sharding:
  jdbc:
  ####ds1
    datasource:
      names: db0
      ds1:
        password: root
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3307/db_0
        username: root
    config:
      sharding:
        tables:
          t_order:
            table-strategy:
              inline:
              #### 根据userid 进行分片
                sharding-column: user_id
                algorithm-expression: db_1.t_order_$->{user_id % 2}
            actual-data-nodes: db1.t_order_$->{0..1}
        props:
          sql: 
          ### 开启分片日志
            show: true

3）、测试是否成功

访问：http://127.0.0.1:8080/insertOrder

访问：http://127.0.0.1:8080/getOrderAll

检查表：

三、多库分表案例

1）、创建db_0、db_0数据库然后创建创建表t_order

CREATE TABLE `t_order` (
  `order_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2）、多库分表核心配置代码

package com.zhq.config;


import com.alibaba.druid.pool.DruidDataSource;
import com.dangdang.ddframe.rdb.sharding.api.ShardingDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.rule.DataSourceRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.ShardingRule;
import com.dangdang.ddframe.rdb.sharding.api.rule.TableRule;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.DatabaseShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.id.generator.IdGenerator;
import com.dangdang.ddframe.rdb.sharding.id.generator.self.CommonSelfIdGenerator;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

/**
 * @author Mr.Zheng
 * @Program: parent
 * @Description: 数据源相关配置信息
 * @date 2020-05-14 10:28
 */
@Configuration
public class DataSourceConfig {

    @Value("${spring.jdbc.db0.className}")
    private String className;
    @Value("${spring.jdbc.db0.url}")
    private String url;
    @Value("${spring.jdbc.db0.username}")
    private String username;
    @Value("${spring.jdbc.db0.password}")
    private String password;

    @Bean
    public IdGenerator getIdGenerator() {
        return new CommonSelfIdGenerator();
    }

    @Bean
    public DataSource getDataSource() {
        return buildDataSource();
    }

    private DataSource buildDataSource() {
        // 1.设置分库映射
        Map<String, DataSource> dataSourceMap = new HashMap<>(2);
        dataSourceMap.put("db_0", createDataSource("db_0"));
        dataSourceMap.put("db_1", createDataSource("db_1"));
        /*
         * 设置默认db为db_0，也就是为那些没有配置分库分表策略的指定的默认库
         * 如果只有一个库，也就是不需要分库的话，map里只放一个映射就行了，只有一个库时不需要指定默认库，
         * 但2个及以上时必须指定默认库，否则那些没有配置策略的表将无法操作数据
         */
        DataSourceRule rule = new DataSourceRule(dataSourceMap, "db_0");
        // 2.设置分库映射
        TableRule orderTableRule = TableRule.builder("t_order").dataSourceRule(rule).build();
        // 3.具体的分库分表策略
        ShardingRule shardingRule = ShardingRule.builder().dataSourceRule(rule)
                .tableRules(Arrays.asList(orderTableRule))
                // 根据userid分片字段，分库
                .databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new DatabaseShardingAlgorithm()))
                .build();
        // 创建数据源
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule);
        return dataSource;
    }

    private DataSource createDataSource(String dataSourceName) {
        // 使用druid连接数据库
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(className);
        druidDataSource.setUrl(String.format(url, dataSourceName));
        druidDataSource.setUsername(username);
        druidDataSource.setPassword(password);
        return druidDataSource;
    }


}

package com.zhq.config;

import com.dangdang.ddframe.rdb.sharding.api.ShardingValue;
import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm;

import java.util.Collection;

/**
 * @author Mr.Zheng
 * @Program: parent
 * @Description: 自定义分库规则
 * @date 2020-05-14 11:29
 */
public class DatabaseShardingAlgorithm  implements SingleKeyDatabaseShardingAlgorithm<Long> {
    @Override
    public String doEqualSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
        for (String dataSource : availableTargetNames) {
            System.out.println("dataSource:" + dataSource + ",----" + shardingValue.getValue());
            //根据分库键取模数据库个数，分配到对应后缀的数据
            if (dataSource.endsWith(shardingValue.getValue() %availableTargetNames.size() + "")) {
                return dataSource;
            }
        }
        throw new IllegalArgumentException();

    }

    @Override
    public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
        return null;
    }

    @Override
    public Collection<String> doBetweenSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
        return null;
    }
}

3）、测试是否成功

重复单库分表验证步骤

夏木炎

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot2整合Sharding-jdbc分库分表案例

1）、创建db_0数据库然后创建创建表t_order_0、t_order_1。3）、测试代码entity->repository->controller。1）、创建db_0、db_0数据库然后创建创建表t_order。2）、单库分表核心配置代码。2）、多库分表核心配置代码。重复单库分表验证步骤。
复制链接

扫一扫