【shardingjdbc】springboot配置说明

---

前言

      Sharding-JDBC是以jar的形式提供服务，无需单独部署，可理解为增强版的 JDBC驱动，完全兼容JDBC和各种ORM框架。此完成一个最原始，最简单的案例，请大家参考。

---

一、Sharding-JDBC分表示例

      如上图所以，springboot工程中通过for循环向t_user表插入数据。通过Sharding-JDBC进行分表配置，将数据依次插入t_user_1和t_user_2表。
1、实体User.java

@TableName("t_user")
public class User
{

	private Long id;
	private String name;
	private Integer groupId;
	//get set省略
}

2、UserMapper.java

@Mapper
public interface UserMapper extends BaseMapper<User>
{
}

3、启动类Application.java

@SpringBootApplication
@MapperScan("com.test.mapper")
public class Application
{
	public static void main(String[] args) {
		SpringApplication.run(Application.class, args);
	}
}

4、配置文件
如果不指定主键的信息时，主键默认使用的SNOWFLAKE生成的。

#配置数据源  名字为m1 和数据源m1的信息
spring.shardingsphere.datasource.names=m1
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root


#m1数据源的t_user表可以分为t_user_1表和t_user_2表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m1.t_user_$->{1..2}

#根据id列进行分表
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column= id
#分表规则是 id%2+1
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression= t_user_$->{id%2+1}

# 打印日志
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true

5、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">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>shardingSphereTest</artifactId>
    <version>1.0-SNAPSHOT</version>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.22</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>
    </dependencies>
    <build>
        <finalName>${project.artifactId}</finalName>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

6、数据库信息

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `group_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

7、测试

@RunWith (SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class ShardingTest
{
	@Resource
	private UserMapper userMapper;
	@Test
	public void Test1(){
		 for(int i= 0 ;i<50;i++){
		 	User user = new User ();
		 	user.setName ("name_"+i);
		 	user.setGroupId (i);
		 	userMapper.insert (user);
		 }
	}
}

二、分库分表配置

就如上java代码。通过group_id进行分库，id进行分表将数据分别插入mysql_1和mysql_2的t_user_1和t_user_2表中。配置如下。代码无需修改。

# 分库分表配置


#配置数据源
spring.shardingsphere.datasource.names=m1,m2
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=root


spring.shardingsphere.datasource.m2.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m2.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m2.url=jdbc:mysql://localhost:3306/test2?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m2.username=root
spring.shardingsphere.datasource.m2.password=root


#m1数据源的t_user表可以分为t_user_1表和t_user_2表
spring.shardingsphere.sharding.tables.t_user.actual-data-nodes=m$->{1..2}.t_user_$->{1..2}

# 数据库选择配置
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.sharding-column= group_id
#分表规则是 id%2+1
spring.shardingsphere.sharding.tables.t_user.database-strategy.inline.algorithm-expression=m$->{group_id%2+1}


#根据id列进行分表
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.sharding-column= id
#分表规则是 id%2+1
spring.shardingsphere.sharding.tables.t_user.table-strategy.inline.algorithm-expression=t_user_$->{id%2+1}

# 打印日志
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true

---

      以上是最简单的单库分表配置，另外还有分库分表、代码中实现分表、hinit指定分表、广播表、绑定表、读写分离配置，直接到git地址拉取代码即可，每个分支都是针对每个功能纯净的配置，亲测有效。

源码地址：git地址
视频教程：b站地址