sharding-jdbc实战

2023-12-29 17:24:58

sharding-jdbc实战

一、数据库准备工作

1、准备两台机器，分别装上mysql环境，然后在两台机器上各自创建一个库(sharding-jdbc1和sharding-jdbc2)

2、在两个库中分别创建两张表(position和position_detail)，创建语句如下:

create table `position`(
`id` bigint(11) primary key auto_increment,
`name` varchar(64),
`salary` varchar(50),
`city` varchar(64)
)engine=innodb charset = utf8mb4;

create table `position_detail`(
`id` bigint(11) primary key auto_increment,
`pid` int(11),
`description` text
)engine=innodb charset=utf8mb4;

二、java代码环境搭建

1、创建一个maven工程(mysql-example)，在该工程下面创建模块(sharding-jdbc-example)，结构如下图所示

2、引入依赖，在父工程的pom.xml中，定义版本相关信息

<?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>mysql-example</artifactId>
    <packaging>pom</packaging>
    <version>1.0-SNAPSHOT</version>
    <modules>
        <module>sharding-jdbc-example</module>
    </modules>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
                <version>2.2.5.RELEASE</version>
            </dependency>

            <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
                <version>2.3.0.RELEASE</version>
            </dependency>

            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <version>2.2.5.RELEASE</version>
                <scope>test</scope>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.48</version>
            </dependency>
            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
                <version>4.1.0</version>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <includeSystemScope>true</includeSystemScope>
                </configuration>
            </plugin>
        </plugins>
    </build>


</project>

子工程(sharding-jdbc-example)的pom.xml中引入相关依赖

<?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>
        <artifactId>mysql-example</artifactId>
        <groupId>org.example</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>sharding-jdbc-example</artifactId>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>2.3.0.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
        </dependency>
    </dependencies>


</project>

3、创建sharding-jdbc的配置文件

#datasource 指定数据库
spring.shardingsphere.datasource.names=ds0,ds1

#指定数据库连接池
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
#指定驱动
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
#mysql链接地址
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/sharding-jdbc1
#用户名
spring.shardingsphere.datasource.ds0.username=root
#密码
spring.shardingsphere.datasource.ds0.password=root

spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/sharding-jdbc2
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=root

#sharding-database 
#根据id进行分片
spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
#对id进行取模
spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=ds$->{id % 2}

4、在application.properties中引入sharding-jdbc的配置文件

#将sharding-jdbc的配置文件引入
spring.profiles.active=sharding-database
#将sharding-jdbc的执行信息打印
spring.shardingsphere.props.sql.show=true

5、创建数据库实体(Position)

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

@Entity
@Table(name="position")
public class Position {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @Column(name="name")
    private String name;

    @Column(name="salary")
    private String salary;

    @Column(name="city")
    private String city;

    public long getId() {
        return id;
    }

    public void setId(long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSalary() {
        return salary;
    }

    public void setSalary(String salary) {
        this.salary = salary;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }
}

6、创建position表的dao

import com.fxkj.entity.Position;
import org.springframework.data.jpa.repository.JpaRepository;

public interface PositionRepository extends JpaRepository<Position,Long> {
}

三、测试

1、编写单位测试类

import com.fxkj.Application;
import com.fxkj.entity.Position;
import com.fxkj.repository.PositionRepository;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class TestShardingDatabase {

    @Autowired
    private PositionRepository positionRepository;

    @Test
    public void testAdd(){
        for(int i=1;i<=20;i++){
            Position position = new Position();
            position.setId(i);
            position.setCity("guangzhou");
            position.setName("fxkj");
            position.setSalary("50000");
            positionRepository.save(position);
        }
    }

}

执行了之后查看数据库，发现两个库中的position表分别插入对应规则的数据(1,3,5,7,9,11,13,15,17,19)、(2,4,6,8,10,12,14,16,18,20)

码农公寓

sharding-jdbc实战

一、数据库准备工作

二、java代码环境搭建

三、测试

相关文章