springboot2.1入门系列八 springboot集成sharding-jdbc

本文为Spring Boot2.1系列的第八篇，代码可以从github下载 https://github.com/yinww/demo-springboot2.git

当数据量过大的时候有时需要使用分库分表技术，将单表数据拆分到多个库和多个表中，在一定程度上缓解数据库的存取压力。

本章介绍当当的开源分库分表技术sharding-jdbc，演示Spring Boot结合sharding-jdbc实现分库分表。

一、准备数据库

这里以用户user表为例说明分库分表技术，我们创建2个库，每个库中有10个user表，总共就有20个user表。假设1个user表能正常存取500万的数据，那么20个表就能正常存取1亿个用户数据，对于不是特别大的项目足够用了。

创建test_0和test_1两个数据库：

create database test_0;

create database test_1;

分别在两个库中创建十个user表：

create table user_0
(
   id                   bigint not null,
   name            varchar(64),
   primary key (id)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;

create table user_1
(
   id                   bigint not null,
   name            varchar(64),
   primary key (id)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;

create table user_2
(
   id                   bigint not null,
   name            varchar(64),
   primary key (id)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;

create table user_3
(
   id                   bigint not null,
   name            varchar(64),
   primary key (id)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;

create table user_4
(
   id                   bigint not null,
   name            varchar(64),
   primary key (id)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;

create table user_5
(
   id                   bigint not null,
   name            varchar(64),
   primary key (id)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;

create table user_6
(
   id                   bigint not null,
   name            varchar(64),
   primary key (id)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;

create table user_7
(
   id                   bigint not null,
   name            varchar(64),
   primary key (id)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;

create table user_8
(
   id                   bigint not null,
   name            varchar(64),
   primary key (id)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;

create table user_9
(
   id                   bigint not null,
   name            varchar(64),
   primary key (id)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;

二、创建工程demo008

pom.xml的内容为

<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>
    <parent>
        <groupId>com.yinww</groupId>
        <artifactId>demo-springboot2</artifactId>
        <version>0.0.1-SNAPSHOT</version>
    </parent>
    <artifactId>demo008</artifactId>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>io.shardingsphere</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>3.0.0</version>
        </dependency>
    </dependencies>
</project>

三、Java代码

DataSourceConfig： DataSource及分库分表配置类，这是关键

package com.yinww.demo.springboot2.demo008.config;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;

import com.zaxxer.hikari.HikariDataSource;

import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
import io.shardingsphere.api.config.ShardingRuleConfiguration;
import io.shardingsphere.api.config.TableRuleConfiguration;
import io.shardingsphere.api.config.strategy.InlineShardingStrategyConfiguration;
import io.shardingsphere.api.config.strategy.StandardShardingStrategyConfiguration;
import io.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;

@Configuration
public class DataSourceConfig {

    @Autowired
    private Environment env;

    @SuppressWarnings({ "rawtypes", "unchecked" })
    @Bean
    public DataSource getDataSource() throws SQLException {
        // 配置表规则
        TableRuleConfiguration tableRuleConfig = new TableRuleConfiguration();
        tableRuleConfig.setLogicTable(env.getProperty("shardingjdbc.logic-table"));
        String actualDataNdes = env.getProperty("shardingjdbc.actual-data-nodes");
        tableRuleConfig.setActualDataNodes(actualDataNdes.replace("->", ""));

        // 配置分库 + 分表策略
        String databaseShardingColumn = env.getProperty("shardingjdbc.database-strategy.inline.sharding-column");
        String databaseAlgorithmExpression = env.getProperty("shardingjdbc.database-strategy.inline.algorithm-expression");
        String tableShardingColumn = env.getProperty("shardingjdbc.tables.standard.sharding-column");
        tableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration(
                databaseShardingColumn, databaseAlgorithmExpression.replace("->", "")));

        String preciseAlgorithmClassName = env.getProperty("shardingjdbc.tables.standard.precise-algorithm-class-name");
        try {
            PreciseShardingAlgorithm algorithm = (PreciseShardingAlgorithm) Class.forName(preciseAlgorithmClassName).newInstance();
            tableRuleConfig.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration(tableShardingColumn, algorithm));
        } catch (Exception e) {
            e.printStackTrace();
        }

        // 配置分片规则
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        shardingRuleConfig.setDefaultDataSourceName(env.getProperty("shardingjdbc.default-data-source-name"));
        shardingRuleConfig.getTableRuleConfigs().add(tableRuleConfig);

        // 获取数据源对象
        return ShardingDataSourceFactory.createDataSource(getDataSourceMap(), shardingRuleConfig,
                new ConcurrentHashMap(), new Properties());
    }

    private Map<String, DataSource> getDataSourceMap() {
        Map<String, DataSource> dataSourceMap = new HashMap<>();
        String[] names = env.getProperty("shardingjdbc.datasource.names").split(",");
        Properties properties = getProperties();
        for (String name : names) {
            dataSourceMap.put(name, buildDataSource(name, properties));
        }
        return dataSourceMap;
    }

    @SuppressWarnings("unchecked")
    private DataSource buildDataSource(String name, Properties properties) {
        try {
            String prefix = "shardingjdbc.datasource." + name;
            String type = env.getProperty(prefix + ".type");
            Class<DataSource> typeClass = (Class<DataSource>) Class.forName(type);
            String driverClassName = env.getProperty(prefix + ".driver-class-name");
            String url = env.getProperty(prefix + ".url");
            String username = env.getProperty(prefix + ".username");
            String password = env.getProperty(prefix + ".password");
            DataSource dataSource = DataSourceBuilder.create().type(typeClass).driverClassName(driverClassName).url(url)
                    .username(username).password(password).build();
            if (dataSource instanceof HikariDataSource) {
                ((HikariDataSource) dataSource).setDataSourceProperties(properties);
            }
            return dataSource;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    private Properties getProperties() {
        Properties p = new Properties();
        p.put("minimum-idle", env.getProperty("hikari.minimum-idle"));
        p.put("maximum-pool-size", env.getProperty("hikari.maximum-pool-size"));
        p.put("auto-commit", env.getProperty("hikari.auto-commit"));
        p.put("idle-timeout", env.getProperty("hikari.idle-timeout"));
        p.put("max-lifetime", env.getProperty("hikari.max-lifetime"));
        p.put("connection-timeout", env.getProperty("hikari.connection-timeout"));
        p.put("connection-test-query", env.getProperty("hikari.connection-test-query"));
        return p;
    }

}

Demo008PreciseShardingAlgorithm：分片算法类

package com.yinww.demo.springboot2.demo008.sharding;

import java.util.Collection;

import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;

public class Demo008PreciseShardingAlgorithm implements PreciseShardingAlgorithm<Long> {

    @Override
    public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Long> shardingValue) {
        String postfix = "" + (shardingValue.getValue() / 2) % 10;
        for (String tableName : availableTargetNames) {
            if (tableName.endsWith(postfix)) {
                return tableName;
            }
        }
        throw new IllegalArgumentException();
    }

}

主类

package com.yinww.demo.springboot2.demo008;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Demo008Application {

    public static void main(String[] args) {
        SpringApplication.run(Demo008Application.class, args);
    }

}

User类

package com.yinww.demo.springboot2.demo008.domain;

public class User {

    private Long id;
    private String name;

    // gette and setter

}

UserMapper类，提供保存和读取的接口

package com.yinww.demo.springboot2.demo008.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Mapper;

import com.yinww.demo.springboot2.demo008.domain.User;

@Mapper
public interface UserMapper {

    void addUser(User user);

    List<User> getUsers();

}

UserService

package com.yinww.demo.springboot2.demo008.service;

import java.util.List;

import com.yinww.demo.springboot2.demo008.domain.User;

public interface UserService {
    
    void addUser(User user);
    
    List<User> getUsers();

}

UserServiceImpl

package com.yinww.demo.springboot2.demo008.service.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.yinww.demo.springboot2.demo008.domain.User;
import com.yinww.demo.springboot2.demo008.mapper.UserMapper;
import com.yinww.demo.springboot2.demo008.service.UserService;

@Service
public class UserServiceImpl implements UserService {
    
    @Autowired
    private UserMapper userMapper;

    @Override
    public void addUser(User user) {
        userMapper.addUser(user);
    }

    @Override
    public List<User> getUsers() {
        return userMapper.getUsers();
    }

}

UserController

package com.yinww.demo.springboot2.demo008.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;

import com.yinww.demo.springboot2.demo008.domain.User;
import com.yinww.demo.springboot2.demo008.service.UserService;

@RestController
public class UserController {
    
    @Autowired
    private UserService userService;
    
    @ResponseBody
    @RequestMapping("add")
    public Object addUser() {
        for (int i = 0; i < 25; i++) {
            User user = new User();
            user.setId(new Long(i));
            user.setName("name_" + i);
            userService.addUser(user);
        }
        return "ok";
    }
    
    @ResponseBody
    @RequestMapping("get")
    public Object getUsers() {
        List<User> users = userService.getUsers();
        return users;
    }

}

四、配置文件

UserMapper.xml

<?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">

<mapper namespace="com.yinww.demo.springboot2.demo008.mapper.UserMapper">
    <resultMap id="UserResultMap" type="com.yinww.demo.springboot2.demo008.domain.User">
        <id column="id" jdbcType="BIGINT" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
    </resultMap>

    <select id="getUsers" resultMap="UserResultMap">
        select * from user order by id;
    </select>

    <insert id="addUser">
        insert into user (id, name) values(#{id}, #{name})
    </insert>
</mapper>

application.properties

spring.application.name=mail

# ds0 datasource
shardingjdbc.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
shardingjdbc.datasource.ds0.driver-class-name=com.mysql.cj.jdbc.Driver
shardingjdbc.datasource.ds0.url=jdbc:mysql://127.0.0.1:3306/test_0?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=GMT%2B8
shardingjdbc.datasource.ds0.username=root
shardingjdbc.datasource.ds0.password=root

# ds1 datasource
shardingjdbc.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
shardingjdbc.datasource.ds1.driver-class-name=com.mysql.cj.jdbc.Driver
shardingjdbc.datasource.ds1.url=jdbc:mysql://127.0.0.1:3306/test_1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=GMT%2B8
shardingjdbc.datasource.ds1.username=root
shardingjdbc.datasource.ds1.password=root

shardingjdbc.datasource.names=ds0,ds1
shardingjdbc.logic-table=user
shardingjdbc.actual-data-nodes=ds$->{0..1}.user_$->{[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]}
shardingjdbc.database-strategy.inline.sharding-column=id
shardingjdbc.database-strategy.inline.algorithm-expression=ds$->{id % 2}
shardingjdbc.tables.standard.sharding-column=id
shardingjdbc.tables.standard.precise-algorithm-class-name=com.yinww.demo.springboot2.demo008.sharding.Demo008PreciseShardingAlgorithm
shardingjdbc.default-data-source-name=ds0

hikari.minimum-idle=5
hikari.maximum-pool-size=15
hikari.auto-commit=true
hikari.idle-timeout=30000
hikari.max-lifetime=1800000
hikari.connection-timeout=30000
hikari.connection-test-query=SELECT 1

mybatis.type-aliases-package=com.yinww.demo.springboot2.demo008.domain
mybatis.mapper-locations=classpath*:mapper/*.xml

五、运行

执行

java -jar demo008-0.0.1-SNAPSHOT.jar

添加24条数据

获取数据，可以看到插入了24条记录

再查看两个数据库中的20个user表，可以看到表中的数据是按程序的分片算法分配到各表中的。

本文内容到此结束，更多内容可关注公众号：

原文链接：https://blog.csdn.net/bowei026/article/details/86555332