A preface.

A few days ago, I wrote how to use Sharding-JDBC for library table and read and write separation examples, I believe that you can feel the powerful sharding-JDBC, and the use of configuration is very clean. Official support for many other functions distributed primary keys, forced routing, and so on. Here is the final version of how to integrate read/write separation function on the basis of separate library and separate table.

X ShardingSphere: SpringBoot 2. X ShardingSphere: Read-write separation

Ii. Actual combat of projects

Configure the primary and secondary databases

Before the configuration, we want to keep the rules of the database and table as before:

If ID mod 2 is odd, it falls into ds0; if id mod 2 is odd, it falls into DS1; if age mod 2 is odd, it falls into user_0; if even, it falls into user_1Copy the code

Read/write separation rules:

Reads fall in the slave library, writes fall in the master libraryCopy the code

Since we use sharding-JDBC Spring Boot Starter, it is still only necessary to configure the data sources for the master and slave libraries in the Properties configuration file

You can see that four data sources are configured respectively for the primary database and the secondary database
sharding.jdbc.datasource.names=master0,master1,master0slave0,master1slave0
# primary first databasesharding.jdbc.datasource.master0.type=com.zaxxer.hikari.HikariDataSource sharding.jdbc.datasource.master0.hikari.driver-class-name=com.mysql.jdbc.Driver Sharding, JDBC datasource. Master0. JDBC - url = JDBC: mysql: / / 192.168.0.4:3306 / ds0? characterEncoding=utf-8&serverTimezone=Asia/Shanghai sharding.jdbc.datasource.master0.username=test
sharding.jdbc.datasource.master0.password=12root
Primary second databasesharding.jdbc.datasource.master1.type=com.zaxxer.hikari.HikariDataSource sharding.jdbc.datasource.master1.hikari.driver-class-name=com.mysql.jdbc.Driver Sharding, JDBC datasource. Master1. JDBC - url = JDBC: mysql: / / 192.168.0.4:3306 / ds1? characterEncoding=utf-8&serverTimezone=Asia/Shanghai sharding.jdbc.datasource.master1.username=test
sharding.jdbc.datasource.master1.password=12root
# from the first databasesharding.jdbc.datasource.master0slave0.type=com.zaxxer.hikari.HikariDataSource sharding.jdbc.datasource.master0slave0.hikari.driver-class-name=com.mysql.jdbc.Driver Sharding, JDBC datasource. Master0slave0. JDBC - url = JDBC: mysql: / / 192.168.0.3:3306 / ds0? characterEncoding=utf-8&serverTimezone=Asia/Shanghai sharding.jdbc.datasource.master0slave0.username=test
sharding.jdbc.datasource.master0slave0.password=12root
# from the first databasesharding.jdbc.datasource.master1slave0.type=com.zaxxer.hikari.HikariDataSource sharding.jdbc.datasource.master1slave0.hikari.driver-class-name=com.mysql.jdbc.Driver Sharding, JDBC datasource. Master1slave0. JDBC - url = JDBC: mysql: / / 192.168.0.3:3306 / ds1? characterEncoding=utf-8&serverTimezone=Asia/Shanghai sharding.jdbc.datasource.master1slave0.username=test
sharding.jdbc.datasource.master1slave0.password=12root

Read/write separation configuration
Round_robin = round_robin = round_robin = round_robin = round_robin
sharding.jdbc.config.masterslave.load-balance-algorithm-type=round_robin
Logical master-slave library names and actual master-slave library mappings
Primary database 0
sharding.jdbc.config.sharding.master-slave-rules.ds0.master-data-source-name=master0
From database 0
sharding.jdbc.config.sharding.master-slave-rules.ds0.slave-data-source-names=master0slave0
Primary database 1
sharding.jdbc.config.sharding.master-slave-rules.ds1.master-data-source-name=master1
From database 1
sharding.jdbc.config.sharding.master-slave-rules.ds1.slave-data-source-names=master1slave0


Configure database and table
# split database (table) configure split database + split table policy row expression sharding policy
# Separate library strategy
sharding.jdbc.config.sharding.default-database-strategy.inline.sharding-column=id
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithm-expression=ds$->{id % 2}
Table splitting depends on the age rowsharding.jdbc.config.sharding.tables.user.actual-data-nodes=ds$->{0.. 1}.user_$->{0.. 1} sharding.jdbc.config.sharding.tables.user.table-strategy.inline.sharding-column=age# sharding algorithm expression
sharding.jdbc.config.sharding.tables.user.table-strategy.inline.algorithm-expression=user_$->{age % 2}

Primary key UUID 18 digits if distributed, there is also a setting to prevent primary key duplication
#sharding.jdbc.config.sharding.tables.user.key-generator-column-name=id

Print operation SQL and database table data, etc
sharding.jdbc.config.props.sql.show=true
spring.main.allow-bean-definition-overriding=true
Copy the code

The configuration of other items remains the same as before

Test three.

1. Query all databases

Open the browser to enter http://localhost:8080/select

2. Insert data

Open your browser and access them separately

http://localhost:8080/insert?id=1&name=lhd&age=12
http://localhost:8080/insert?id=2&name=lhd&age=13
http://localhost:8080/insert?id=3&name=lhd&age=14
http://localhost:8080/insert?id=4&name=lhd&age=15
Copy the code

Console printing

The result is the same as the previous one. According to the sharding algorithm and sharding strategy, different ids and ages are dropped into different database tables to achieve the sub-database table

3. Query all data

Open the browser to enter http://localhost:8080/select

Console printing

Problem 4.

1. It is impossible to know which data source it is going to
As you may have noticed, sql.show= is configured for read/write separation and database/table integrationtrueBut the console doesn't end up printing out which data source it's executing from or fromCopy the code
2. Read and write separation

Read/write separation process

Get master-slave configuration rules, data encapsulation into MasterSlaveDataSource according to ShardingMasterSlaveRouter routing calculation, get sqlRouteResult. GetRouteUnits unit list (), Then add the results addAll and need to get connected, when you return to execute each RouteUnits here according to polling load balancing algorithm RoundRobinMasterSlaveLoadBalanceAlgorithm get from the data source, Get connected after began to perform specific SQL queries, through PreparedStatementHandler. Here the execute () to get the results The results returned after mergingCopy the code

MasterSlaveDataSource.class

package io.shardingsphere.shardingjdbc.jdbc.core.datasource;

import io.shardingsphere.api.ConfigMapContext;
import io.shardingsphere.api.config.rule.MasterSlaveRuleConfiguration;
import io.shardingsphere.core.constant.properties.ShardingProperties;
import io.shardingsphere.core.rule.MasterSlaveRule;
import io.shardingsphere.shardingjdbc.jdbc.adapter.AbstractDataSourceAdapter;
import io.shardingsphere.shardingjdbc.jdbc.core.connection.MasterSlaveConnection;
import io.shardingsphere.transaction.api.TransactionTypeHolder;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;
import java.util.Map;
import java.util.Properties;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class MasterSlaveDataSource extends AbstractDataSourceAdapter {
    private static final Logger log = LoggerFactory.getLogger(MasterSlaveDataSource.class);
    private final DatabaseMetaData databaseMetaData;
    private final MasterSlaveRule masterSlaveRule;
    private final ShardingProperties shardingProperties;

    public MasterSlaveDataSource(Map<String, DataSource> dataSourceMap, MasterSlaveRuleConfiguration masterSlaveRuleConfig, Map<String, Object> configMap, Properties props) throws SQLException {
        super(dataSourceMap);
        this.databaseMetaData = this.getDatabaseMetaData(dataSourceMap);
        if(! configMap.isEmpty()) { ConfigMapContext.getInstance().getConfigMap().putAll(configMap); } this.masterSlaveRule = new MasterSlaveRule(masterSlaveRuleConfig); This. shardingProperties = new shardingProperties (null == props? new Properties() : props); } public MasterSlaveDataSource(Map<String, DataSource> dataSourceMap, MasterSlaveRule MasterSlaveRule, Map<String, Object> configMap, Properties props) throws SQLException { super(dataSourceMap); this.databaseMetaData = this.getDatabaseMetaData(dataSourceMap);if(! configMap.isEmpty()) { ConfigMapContext.getInstance().getConfigMap().putAll(configMap); } this.masterSlaveRule = masterSlaveRule; this.shardingProperties = new ShardingProperties(null == props ? new Properties() : props); } private DatabaseMetaData getDatabaseMetaData(Map<String, DataSource> dataSourceMap) throws SQLException { Connection connection = ((DataSource)dataSourceMap.values().iterator().next()).getConnection(); Throwable var3 = null; DatabaseMetaData var4; try { var4 = connection.getMetaData(); } catch (Throwable var13) { var3 = var13; throw var13; } finally {if(connection ! = null) {if (var3 != null) {
                    try {
                        connection.close();
                    } catch (Throwable var12) {
                        var3.addSuppressed(var12);
                    }
                } else{ connection.close(); }}}return var4;
    }

    public final MasterSlaveConnection getConnection() {
        return new MasterSlaveConnection(this, this.getShardingTransactionalDataSources().getDataSourceMap(), TransactionTypeHolder.get());
    }

    public DatabaseMetaData getDatabaseMetaData() {
        return this.databaseMetaData;
    }

    public MasterSlaveRule getMasterSlaveRule() {
        return this.masterSlaveRule;
    }

    public ShardingProperties getShardingProperties() {
        returnthis.shardingProperties; }}Copy the code

Master/slave rule MasterSlaveRule. Class configured in the configuration file

package io.shardingsphere.core.rule; import com.google.common.base.Preconditions; import io.shardingsphere.api.algorithm.masterslave.MasterSlaveLoadBalanceAlgorithm; import io.shardingsphere.api.algorithm.masterslave.MasterSlaveLoadBalanceAlgorithmType; import io.shardingsphere.api.config.rule.MasterSlaveRuleConfiguration; import java.util.Collection; Public class MasterSlaveRule {// name (ds0, ds1) private final String name; // masterDataSourceName (ds_master_0 and ds_master_1) private final String masterDataSourceName; Private final Collection<String> slaveDataSourceNames; / / private final master-slave library load balancing algorithm MasterSlaveLoadBalanceAlgorithm loadBalanceAlgorithm; / / master-slave library routing configuration private final MasterSlaveRuleConfiguration MasterSlaveRuleConfiguration;Copy the code

Polling load balance calculation algorithm RoundRobinMasterSlaveLoadBalanceAlgorithm. Class

package io.shardingsphere.api.algorithm.masterslave; import java.util.List; import java.util.concurrent.ConcurrentHashMap; import java.util.concurrent.atomic.AtomicInteger; // Polling load balancing policies, According to each slave node visited balanced public final class RoundRobinMasterSlaveLoadBalanceAlgorithm implements MasterSlaveLoadBalanceAlgorithm { private static final ConcurrentHashMap<String, AtomicInteger> COUNT_MAP = new ConcurrentHashMap(); publicRoundRobinMasterSlaveLoadBalanceAlgorithm() {
    }

    public String getDataSource(String name, String masterDataSourceName, List<String> slaveDataSourceNames) {
        AtomicInteger count = COUNT_MAP.containsKey(name) ? (AtomicInteger)COUNT_MAP.get(name) : new AtomicInteger(0);
        COUNT_MAP.putIfAbsent(name, count);
        count.compareAndSet(slaveDataSourceNames.size(), 0);
        return(String)slaveDataSourceNames.get(Math.abs(count.getAndIncrement()) % slaveDataSourceNames.size()); }}Copy the code

ShardingMasterSlaveRouter.class

// // Source code recreated from a .class file by IntelliJ IDEA // (powered by Fernflower decompiler) // package io.shardingsphere.core.routing.router.masterslave; import io.shardingsphere.core.constant.SQLType; import io.shardingsphere.core.hint.HintManagerHolder; import io.shardingsphere.core.routing.RouteUnit; import io.shardingsphere.core.routing.SQLRouteResult; import io.shardingsphere.core.rule.MasterSlaveRule; import java.beans.ConstructorProperties; import java.util.ArrayList; import java.util.Collection; import java.util.Iterator; import java.util.LinkedList; public final class ShardingMasterSlaveRouter { private final Collection<MasterSlaveRule> masterSlaveRules; SQLRouteResult (SQLRouteResult SQLRouteResult) {Iterator var2 = this.masterSlaveRules.iterator();while(var2.hasNext()) {
            MasterSlaveRule each = (MasterSlaveRule)var2.next();
            this.route(each, sqlRouteResult);
        }

        returnsqlRouteResult; } private void route(MasterSlaveRule MasterSlaveRule, SQLRouteResult sqlRouteResult) { Collection<RouteUnit> toBeRemoved = new LinkedList(); Collection<RouteUnit> toBeAdded = new LinkedList(); Iterator var5 = sqlRouteResult.getRouteUnits().iterator();while(var5.hasNext()) {
            RouteUnit each = (RouteUnit)var5.next();
            if (masterSlaveRule.getName().equalsIgnoreCase(each.getDataSourceName())) {
                toBeRemoved.add(each);
                if (this.isMasterRoute(sqlRouteResult.getSqlStatement().getType())) {
                    MasterVisitedManager.setMasterVisited();
                    toBeAdded.add(new RouteUnit(masterSlaveRule.getMasterDataSourceName(), each.getSqlUnit()));
                } else{ toBeAdded.add(new RouteUnit(masterSlaveRule.getLoadBalanceAlgorithm().getDataSource(masterSlaveRule.getName(), masterSlaveRule.getMasterDataSourceName(), new ArrayList(masterSlaveRule.getSlaveDataSourceNames())), each.getSqlUnit())); }}} / / routing removed (the query Remove all the main library) sqlRouteResult. GetRouteUnits () removeAll (toBeRemoved); / / add from library/main library specific events setting sqlRouteResult. GetRouteUnits (). The addAll (toBeAdded); } private Boolean isMasterRoute(SQLType SQLType) {returnSQLType.DQL ! = sqlType || MasterVisitedManager.isMasterVisited() || HintManagerHolder.isMasterRouteOnly(); } @ConstructorProperties({"masterSlaveRules"}) public ShardingMasterSlaveRouter(Collection<MasterSlaveRule> masterSlaveRules) { this.masterSlaveRules = masterSlaveRules; }}Copy the code

Note: The rule for judging whether the master library is the master library is:

private boolean isMasterRoute(SQLType sqlType) {
        returnSQLType.DQL ! = sqlType || MasterVisitedManager.isMasterVisited() || HintManagerHolder.isMasterRouteOnly(); }Copy the code

SQL language judgment

SQL languages are divided into four categories: data query language DQL, data manipulation language DML, data definition language DDL, data control language DCL.Copy the code

Through breakpoints, the final SQL route for querying all data is

At present, read and write separation and sub-library sub-table is completed

Source code analysis is wrong, if there are mistakes please point out

Download the source code: github.com/LiHaodong88…