This article introduces SpringBoot to use Dangdang Sharding-JDBC for database table.

1. About Sharding – JDBC

Sharding-jdbc is a horizontal extension framework for relational databases developed by Dangdang. It has been donated to Apache and can be seen on Github at: Shardingsphere.apache.org/document/cu…

Shardingsphere document address is: shardingsphere.apache.org/document/cu… .

It does not appear that you can download dependencies from the Maven repository at this time. You will need to manually download the source code and package it for use, so this article will use dangdang’s dependencies.

2. Scenario

2.1 database

Let’s walk through the scenario for this article, which creates two databases, database0 and database1. Each database creates two data tables, GOOds_0 and GOOds_1, as shown in the figure. Here the blue represents the table in database0, and the red represents the table in database1. The Green Goods table is a virtual table.

2.2 depots

The sample library for this article is simple, based on the size of the goods_id field in the database table, using database0 if goods_id is greater than 20, otherwise using database1.

Table 2.3

The sample is relatively simple. According to the value of the goods_type field in the database table, the goods_1 table is used for odd numbers, and the GOOds_0 table is used for even numbers.

2.4 Code Flow

The process goes something like this: in the application we operate on the virtual table goods, but when we actually operate on the database, we match and operate according to our database partition rules.

3. Code implementation

This article uses SpringBoot2.0.3, SpringData-JPA, Druid connection pool, and Dangdang’s Sharding – JDBC.

3.1 build SQL table

The SQL to create the tables and databases is shown below.

CREATE DATABASE database0;
USE database0;
DROP TABLE IF EXISTS `goods_0`;
CREATE TABLE `goods_0` (
  `goods_id` bigint(20) NOT NULL,
  `goods_name` varchar(100) COLLATE utf8_bin NOT NULL,
  `goods_type` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
DROP TABLE IF EXISTS `goods_1`;
CREATE TABLE `goods_1` (
  `goods_id` bigint(20) NOT NULL,
  `goods_name` varchar(100) COLLATE utf8_bin NOT NULL,
  `goods_type` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE DATABASE database1;
USE database1;
DROP TABLE IF EXISTS `goods_0`;
CREATE TABLE `goods_0` (
  `goods_id` bigint(20) NOT NULL,
  `goods_name` varchar(100) COLLATE utf8_bin NOT NULL,
  `goods_type` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

DROP TABLE IF EXISTS `goods_1`;
CREATE TABLE `goods_1` (
  `goods_id` bigint(20) NOT NULL,
  `goods_name` varchar(100) COLLATE utf8_bin NOT NULL,
  `goods_type` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
Copy the code

3.2 Dependent Files

Create a new project and add the sharding- Jdbc-core dependencies and DruID connection pool to dangdang. The complete POM is shown below.

<? 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 > < the parent > < groupId > org. Springframework. Boot < / groupId > < artifactId > spring - the boot - starter - parent < / artifactId > < version >. 2.0.3 RELEASE < / version > < relativePath / > <! -- lookup parent from repository --> </parent> <groupId>com.dalaoyang</groupId> < artifactId > springboot2_shardingjdbc_fkfb < / artifactId > < version > 0.0.1 - the SNAPSHOT < / version > <name>springboot2_shardingjdbc_fkfb</name> <description>springboot2_shardingjdbc_fkfb</description> <properties> </groupId> </groupId> </groupId> </groupId> </groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-devtools</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope>  </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <! -- lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <! -- druid --> <dependency> <groupId>com.alibaba</groupId> <artifactId> <version>1.1.9</version> </dependency> <! -- sharding-jdbc --> <dependency> <groupId>com.dangdang</groupId> <artifactId>sharding-jdbc-core</artifactId> </dependencies> </dependencies> </dependencies> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>Copy the code

3.3 Configuration Information

In the configuration information, you configure information for both databases and a simple configuration for JPA.

# # Jpa configuration
spring.jpa.database=mysql
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none

## Database configuration
## Address of database database0database0.url=jdbc:mysql://localhost:3306/database0? characterEncoding=utf8&useSSL=false
## User name for database database0
database0.username=root
## Password for database database0
database0.password=root
## database0 driver
database0.driverClassName=com.mysql.jdbc.Driver
## Name of database database0
database0.databaseName=database0

## Address of database1database1.url=jdbc:mysql://localhost:3306/database1? characterEncoding=utf8&useSSL=false
## User name for database database1
database1.username=root
## Password for database1
database1.password=root
## database1 driver
database1.driverClassName=com.mysql.jdbc.Driver
Name of database database1
database1.databaseName=database1

Copy the code

3.4 start the class

Start the class joined the @ EnableAutoConfiguration out database configuration automatically, using the @ EnableTransactionManagement open transaction, using the @ EnableConfigurationProperties annotations to join configuration entity, The complete startup class code is shown.

package com.dalaoyang;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@SpringBootApplication
@EnableAutoConfiguration(exclude={DataSourceAutoConfiguration.class})
@EnableTransactionManagement(proxyTargetClass = true) @EnableConfigurationProperties public class Springboot2ShardingjdbcFkfbApplication { public static void main(String[] args) { SpringApplication.run(Springboot2ShardingjdbcFkfbApplication.class, args); }}Copy the code

3.5 Entity class and database operation layer

There is nothing to say here, it is simple entity and Repository, but add between method and in method in Repository to test, as shown in the code below.

Goods Entity.

package com.dalaoyang.entity;

import lombok.Data;

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

/**
 * @author yangyang
 * @date 2019/1/29
 */
@Entity
@Table(name="goods")
@Data
public class Goods {
    @Id
    private Long goodsId;

    private String goodsName;

    private Long goodsType;
}

Copy the code

GoodsRepository class.

package com.dalaoyang.repository;

import com.dalaoyang.entity.Goods;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

/**
 * @author yangyang
 * @date 2019/1/29
 */
public interface GoodsRepository extends JpaRepository<Goods, Long> {

    List<Goods> findAllByGoodsIdBetween(Long goodsId1,Long goodsId2);

    List<Goods> findAllByGoodsIdIn(List<Long> goodsIds);
}
Copy the code

3.6 Database Configuration

This article uses two entities to receive database information and create a data source, or in other ways. First look at the code for the Database0Config and Database1Config classes.

Database0Config class.

package com.dalaoyang.database;

import com.alibaba.druid.pool.DruidDataSource;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;

/**
 * @author yangyang
 * @date 2019/1/30
 */
@Data
@ConfigurationProperties(prefix = "database0")
@Component
public class Database0Config {
    private String url;
    private String username;
    private String password;
    private String driverClassName;
    private String databaseName;

    public DataSource createDataSource() {
        DruidDataSource result = new DruidDataSource();
        result.setDriverClassName(getDriverClassName());
        result.setUrl(getUrl());
        result.setUsername(getUsername());
        result.setPassword(getPassword());
        returnresult; }}Copy the code

Database1Config class.

package com.dalaoyang.database;

import com.alibaba.druid.pool.DruidDataSource;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;

/**
 * @author yangyang
 * @date 2019/1/30
 */
@Data
@ConfigurationProperties(prefix = "database1")
@Component
public class Database1Config {
    private String url;
    private String username;
    private String password;
    private String driverClassName;
    private String databaseName;

    public DataSource createDataSource() {
        DruidDataSource result = new DruidDataSource();
        result.setDriverClassName(getDriverClassName());
        result.setUrl(getUrl());
        result.setUsername(getUsername());
        result.setPassword(getPassword());
        returnresult; }}Copy the code

Next, create DataSourceConfig to create data sources and use the database and table policy. The database and table policy calls the algorithm class and the algorithm class. The DataSourceConfig code is shown below.

package com.dalaoyang.database;


import com.dalaoyang.config.DatabaseShardingAlgorithm;
import com.dalaoyang.config.TableShardingAlgorithm;
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.api.strategy.table.TableShardingStrategy;
import com.dangdang.ddframe.rdb.sharding.keygen.DefaultKeyGenerator;
import com.dangdang.ddframe.rdb.sharding.keygen.KeyGenerator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

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

/**
 * @author yangyang
 * @date 2019/1/29
 */
@Configuration
public class DataSourceConfig {

    @Autowired
    private Database0Config database0Config;

    @Autowired
    private Database1Config database1Config;

    @Autowired
    private DatabaseShardingAlgorithm databaseShardingAlgorithm;

    @Autowired
    private TableShardingAlgorithm tableShardingAlgorithm;

    @Bean
    public DataSource getDataSource() throws SQLException {
        returnbuildDataSource(); } private DataSource buildDataSource() throws SQLException {// set Map<String, DataSource> dataSourceMap = new HashMap<>(2); / / add two databases database0 and database1 dataSourceMap. Put (database0Config. GetDatabaseName (), database0Config. CreateDataSource ()); dataSourceMap.put(database1Config.getDatabaseName(), database1Config.createDataSource()); / / set the default database DataSourceRule DataSourceRule = new DataSourceRule (dataSourceMap, database0Config getDatabaseName ()); TableRule orderTableRule = TableRule. Builder (); // Select * from 'Goods' where' Goods' = 'Goods';"goods")
                .actualTables(Arrays.asList("goods_0"."goods_1")) .dataSourceRule(dataSourceRule) .build(); ShardingRule ShardingRule = ShardingRule. Builder ().datasourcerule (dataSourceRule) .tableRules(Arrays.asList(orderTableRule)) .databaseShardingStrategy(new DatabaseShardingStrategy("goods_id", databaseShardingAlgorithm))
                .tableShardingStrategy(new TableShardingStrategy("goods_type", tableShardingAlgorithm)).build();
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(shardingRule);
        return dataSource;
    }


    @Bean
    public KeyGenerator keyGenerator() {
        returnnew DefaultKeyGenerator(); }}Copy the code

3.7 Database table sorting algorithm

Because here is simply depots table sample, so the depots classes implement SingleKeyDatabaseShardingAlgorithm classes here, using a single shard key data source subdivision algorithm, the need to rewrite the three methods, respectively is:

• DoEqualSharding: The rule for == in SQL.
• DoInSharding: The rule for IN in SQL.
• DoBetweenSharding: Rule between in SQL.

Depots rules of this article is based on use the database0 value is greater than 20, the remaining use database1, so simple if, else done, depots algorithm class DatabaseShardingAlgorithm code as shown below.

package com.dalaoyang.config; import com.dalaoyang.database.Database0Config; import com.dalaoyang.database.Database1Config; import com.dangdang.ddframe.rdb.sharding.api.ShardingValue; import com.dangdang.ddframe.rdb.sharding.api.strategy.database.SingleKeyDatabaseShardingAlgorithm; import com.google.common.collect.Range; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Component; import java.util.Collection; import java.util.LinkedHashSet; /** ** select * from * where ** ** @author yangyang * @date 2019/1/30 */ @Component public class DatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> { @Autowired private Database0Config database0Config; @Autowired private Database1Config database1Config; @Override public StringdoEqualSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
        Long value = shardingValue.getValue();
        if (value <= 20L) {
            return database0Config.getDatabaseName();
        } else {
            return database1Config.getDatabaseName();
        }
    }

    @Override
    public Collection<String> doInSharding(Collection<String> availableTargetNames, ShardingValue<Long> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());
        for (Long value : shardingValue.getValues()) {
            if (value <= 20L) {
                result.add(database0Config.getDatabaseName());
            } else{ result.add(database1Config.getDatabaseName()); }}return result;
    }

    @Override
    public Collection<String> doBetweenSharding(Collection<String> availableTargetNames,
                                                ShardingValue<Long> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(availableTargetNames.size());
        Range<Long> range = shardingValue.getValueRange();
        for (Long value = range.lowerEndpoint(); value <= range.upperEndpoint(); value++) {
            if (value <= 20L) {
                result.add(database0Config.getDatabaseName());
            } else{ result.add(database1Config.getDatabaseName()); }}returnresult; }}Copy the code

Table and depots are similar, is not the same class does not implement, implement the SingleKeyTableShardingAlgorithm class, parity strategy use value table, table algorithm class TableShardingAlgorithm as shown in code listing.

package com.dalaoyang.config; import com.dangdang.ddframe.rdb.sharding.api.ShardingValue; import com.dangdang.ddframe.rdb.sharding.api.strategy.table.SingleKeyTableShardingAlgorithm; import com.google.common.collect.Range; import org.springframework.stereotype.Component; import java.util.Collection; import java.util.LinkedHashSet; /** * select * from * where ** * @author yangyang * @date 2019/1/30 */ @Component public class TableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> { @Override public StringdoEqualSharding(final Collection<String> tableNames, final ShardingValue<Long> shardingValue) {
        for (String each : tableNames) {
            if (each.endsWith(shardingValue.getValue() % 2 + "")) {
                return each;
            }
        }
        throw new IllegalArgumentException();
    }

    @Override
    public Collection<String> doInSharding(final Collection<String> tableNames, final ShardingValue<Long> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(tableNames.size());
        for (Long value : shardingValue.getValues()) {
            for (String tableName : tableNames) {
                if (tableName.endsWith(value % 2 + "")) { result.add(tableName); }}}return result;
    }

    @Override
    public Collection<String> doBetweenSharding(final Collection<String> tableNames,
                                                final ShardingValue<Long> shardingValue) {
        Collection<String> result = new LinkedHashSet<>(tableNames.size());
        Range<Long> range = shardingValue.getValueRange();
        for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
            for (String each : tableNames) {
                if (each.endsWith(i % 2 + "")) { result.add(each); }}}returnresult; }}Copy the code

3.8 the Controller

Next, create a Controller for test. The save method uses to insert 40 pieces of data. According to our rules, 20 pieces of data will be inserted into each library. The Controller class code is shown below.

package com.dalaoyang.controller;

import com.dalaoyang.entity.Goods;
import com.dalaoyang.repository.GoodsRepository;
import com.dangdang.ddframe.rdb.sharding.keygen.KeyGenerator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

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

/**
 * @author yangyang
 * @date 2019/1/29
 */
@RestController
public class GoodsController {

    @Autowired
    private KeyGenerator keyGenerator;

    @Autowired
    private GoodsRepository goodsRepository;

    @GetMapping("save")
    public String save() {for(int i= 1 ; i <= 40 ; i ++){
            Goods goods = new Goods();
            goods.setGoodsId((long) i);
            goods.setGoodsName( "shangpin" + i);
            goods.setGoodsType((long) (i+1));
            goodsRepository.save(goods);
        }
        return "success";
    }

    @GetMapping("select")
    public String select() {return goodsRepository.findAll().toString();
    }

    @GetMapping("delete")
    public void delete(){
         goodsRepository.deleteAll();
    }

    @GetMapping("query1")
    public Object query1() {return goodsRepository.findAllByGoodsIdBetween(10L, 30L);
    }

    @GetMapping("query2")
    public Object query2(){
        List<Long> goodsIds = new ArrayList<>();
        goodsIds.add(10L);
        goodsIds.add(15L);
        goodsIds.add(20L);
        goodsIds.add(25L);
        returngoodsRepository.findAllByGoodsIdIn(goodsIds); }}Copy the code

4. Test

Start the application, the browser visit http://localhost:8080/save or HTTP request tool, as shown, returns success.

In the next test query methods, visit http://localhost:8080/select, as shown, you can see insert data no problem.

Then take a look at the database, starting with database0, as shown in the figure, where each table has ten pieces of data, as shown below.

Next, look at Database1, as shown below.

From the above figures, we can see that the sub-database sub-table has been inserted according to our strategy. As for the other tests, both queries and deletes can be successful.

5 source

Source address: gitee.com/dalaoyang/s…