shardingjdbc学习

shardingjdbc分片实战

分库环境准备

创建库，表
create project作为父工程
加依赖
<properties>
        <project.build.sourceEncoding>UTF-8 </project.build.sourceEncoding>
        <project.compile.sourceEncoding>UTF-8 </project.compile.sourceEncoding>
        <shardingsphere.version>4.1.0</shardingsphere.version>
        <springboot.version>2.2.5.RELEASE</springboot.version>
    </properties>

    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
                <version>${springboot.version}</version>
            </dependency>

            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
                <version>${springboot.version}</version>
            </dependency>

            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <version>${springboot.version}</version>
            </dependency>

            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.47</version>
            </dependency>

            <dependency>
                <groupId>org.apache.shardingsphere</groupId>
                <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
                <version>${shardingsphere.version}</version>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>11</source>
                    <target>11</target>
                    <testSource>11</testSource>
                    <testTarget>11</testTarget>
                </configuration>
                <version>3.8.1</version>
            </plugin>
        </plugins>
    </build>

create moudle

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

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
        </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>

步骤

创建实体
创建继承jpa接口
创建启动类
创建配置文件application.properties

spring.profiles.active=sharding-database
spring.shardingsphere.props.sql.show=true

创建配置文件application-sharding-database.properties

#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
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/shardingjdbc1?serverTimezone=GMT%2B8&useSSL=false
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/shardingjdbc2?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=root

#sharding-database
spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=ds${id % 2}

#id
spring.shardingsphere.sharding.tables.position.key-generator.column=id
spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE

测试

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

    @Resource
    private PositionRepository positionRepository;

    @Test
    public void test(){
        for (int i = 0; i < 20; i++) {
            Position p = new Position();
            p.setId(Long.valueOf(i));
            p.setName("lcx"+i);
            p.setSalary("1000000");
            p.setCity("guanzhou");
            positionRepository.save(p);
        }
    }
}

分表

创建明细表
创建实体，dao接口
加配置

spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.sharding-column=pid
spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.algorithm-expression=ds${pid % 2}


spring.shardingsphere.sharding.tables.position_detail.key-generator.column=id
spring.shardingsphere.sharding.tables.position_detail.key-generator.type=SNOWFLAKE

注意：这里的pid是关联主表id，跟id一致，配置不要写错

广播表

加表，加实体，加dao接口
加配置

#boardcast
spring.shardingsphere.sharding.broadcast-tables=city
spring.shardingsphere.sharding.tables.city.key-generator.column=id
spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE

既分库又分表

建表，实体，接口
配置根据公司id分库，主键id分表

spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.sharding-column=company_id
spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.algorithm-expression=ds${company_id % 2}
spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.algorithm-expression=b_order${id % 2}
spring.shardingsphere.sharding.tables.b_order.actual-data-nodes=ds${0..1}.b_order${0..1}
spring.shardingsphere.sharding.tables.b_order.key-generator.column=id
spring.shardingsphere.sharding.tables.b_order.key-generator.type=SNOWFLAKE

测试
@Test
    @Repeat(100) //循环100次
    public void test5(){}

读写分离+分库分表

写主，读从

#datasource
spring.shardingsphere.datasource.names=master,slave0

spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://localhost:3306/shardingjdbc1?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=root

spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://localhost:3306/shardingjdbc2?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=root

#master-slave
spring.shardingsphere.masterslave.name=datasource
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0
spring.shardingsphere.masterslave.load-balance-algorithm-type=ROUND_ROBIN

spring.shardingsphere.sharding.tables.city.key-generator.column=id
spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE

强制路由

自定义类

public class MyHintShardingAlgorithm implements HintShardingAlgorithm<Long> {


    @Override
    public Collection<String> doSharding(Collection<String> collection, HintShardingValue<Long> hintShardingValue) {
        Collection<String> result = new ArrayList<>();
        for (String s : collection) {
            for (Long value : hintShardingValue.getValues()) {
                if (s.endsWith(String.valueOf(value % 2))){
                    result.add(s);
                }
            }
        }

        return result;
    }
}

配置文件引入

#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
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/shardingjdbc1?serverTimezone=GMT%2B8&useSSL=false
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/shardingjdbc2?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=root

#hint
spring.shardingsphere.sharding.tables.city.database-strategy.hint.algorithm-class-name=com.lcx.hint.MyHintShardingAlgorithm

其余步骤差不多，这里可以强制查询哪个库。

加密

主要是配置

#datasource
spring.shardingsphere.datasource.names=ds0

spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/shardingjdbc1?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=root

#encrypt
#明文列
spring.shardingsphere.encrypt.tables.c_user.columns.pwd.plain-column=pwd_plain 
#密文列
spring.shardingsphere.encrypt.tables.c_user.columns.pwd.cipher-column=pwd_cipher

#加密算法
spring.shardingsphere.encrypt.encryptors.lcx_pwd.type=aes
spring.shardingsphere.encrypt.encryptors.lcx_pwd.props.aes.key.value=1234

spring.shardingsphere.encrypt.tables.c_user.columns.pwd.encryptor=lcx_pwd

#查询是否带密文
spring.shardingsphere.props.query.with.cipher.column=true 

spring.shardingsphere.sharding.tables.c_user.key-generator.column=id
spring.shardingsphere.sharding.tables.c_user.key-generator.type=SNOWFLAKE