ShardingSphere + MySQL implementation sub database
1, About ShardingSphere
ShardingSphere is an ecosystem composed of a set of open-source distributed database middleware solutions. It is composed of sharding JDBC, sharding proxy and sharding sidecar (planned). They all provide standardized data fragmentation, distributed transaction and database governance functions, which can be applied to various application scenarios such as Java isomorphism, heterogeneous language, cloud native and so on.
- Sharding JDBC is positioned as a lightweight Java framework and provides additional services in the JDBC layer of Java. It uses the client to directly connect to the database and provides services in the form of jar package without additional deployment and dependency. It can be understood as an enhanced jdbc driver and is fully compatible with JDBC and various ORM frameworks.
- Sharding proxy is positioned as a transparent database proxy. It provides a server version that encapsulates the database binary protocol to support heterogeneous languages. At present, the MySQL/PostgreSQL version is provided first. It can use any access client compatible with MySQL/PostgreSQL protocol (such as MySQL Command Client, MySQL Workbench, Navicat, etc.) to operate data, which is more friendly to DBA.
Architecture diagram
2, Project configuration
Intention introduction: two databases: ds0 and ds1. Each database has two tables: order and order_item, just according to order_id sub database.
1. POM introduces ShardingSphere database and table dependency
<shardingsphere.version>4.1.1</shardingsphere.version>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${shardingsphere.version}</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>${shardingsphere.version}</version>
</dependency>
2. YAML configuration
Sharding JDBC can be configured through Java, YAML and Spring namespace. For details, please refer to the configuration manual on the official website of ShardingSphere ShardingSphere official website configuration manual , with detailed configuration instructions.
Since only sub databases are involved and no sub tables are involved, the following data sources are configured through YAML:
spring:
shardingsphere:
sharding:
binding-tables: order,order_item
datasource:
names: ds0,ds1
ds0:
auto: true
url: jdbc:mysql://localhost:3306/ds0?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=Asia/Shanghai
username:
password:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
initial-size: 10
max-active: 100
min-idle: 2
validation-query: SELECT 1
test-on-borrow: false
test-while-idle: true
ds1:
auto: true
url: jdbc:mysql://localhost:3306/ds1?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=Asia/Shanghai
username:
password:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
initial-size: 10
max-active: 100
min-idle: 2
validation-query: SELECT 1
test-on-borrow: false
test-while-idle: true
3. Sharding slice configuration related class codes:
DataSourceConfig.java: get data source
DatabaseShardingAlgorithm.java: database sorting algorithm
MybatisConfig.java: sub database configuration
3.1 DataSourceConfig.java
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import com.alibaba.druid.pool.DruidDataSource;
@Configuration
public class DataSourceConfig {
@Bean
@Qualifier("ds0")
@ConfigurationProperties("spring.shardingsphere.datasource.ds0")
public DataSource ds0DataSource() {
return new DruidDataSource();
}
@Bean
@Qualifier("ds1DataSource")
@ConfigurationProperties("spring.shardingsphere.datasource.ds1")
public DataSource ds1DataSource() {
return new DruidDataSource();
}
......Other data sources
}
3.2 DatabaseShardingAlgorithm.java
import java.util.Collection;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingAlgorithm;
import org.apache.shardingsphere.api.sharding.standard.PreciseShardingValue;
import com.jiaoyan.tiku.enumeration.DataSourceEnum;
import lombok.extern.slf4j.Slf4j;
@Slf4j
public class DatabaseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {
/**
* Sharding.
*
* @param collection
* Data source (Library) name collection or real table name collection.
*
* @param preciseShardingValue
* The value of the slice key.
*
* @return The name of the data source (Library) to be used by the routed SQL or the name of the real table to be used by the routed SQL
* Note: here is the name of the data source (Library) to be used by the routed SQL.
*/
@Override
public String doSharding(Collection<String> collection, PreciseShardingValue<Integer> preciseShardingValue) {
//log.info("DatabaseShardingAlgorithm collection:{}, preciseShardingValue: {}", collection, preciseShardingValue);
return preciseShardingValue.getValue() % 2;
}
// public class DemoDatabaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {
//
// @Override
// public String doEqualSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) {
//
// for (String each : databaseNames) {
// if (each.endsWith(Long.parseLong(shardingValue.getValue().toString()) % 2 + "")) {
// return each;
// }
// }
// throw new IllegalArgumentException();
// }
//
// @Override
// public Collection<String> doInSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) {
// Collection<String> result = new LinkedHashSet<>(databaseNames.size());
// for (Long value : shardingValue.getValues()) {
// for (String tableName : databaseNames) {
// if (tableName.endsWith(value % 2 + "")) {
// result.add(tableName);
// }
// }
// }
// return result;
// }
//
// @Override
// public Collection<String> doBetweenSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) {
// Collection<String> result = new LinkedHashSet<>(databaseNames.size());
// Range<Long> range = (Range<Long>) shardingValue.getValueRange();
// for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
// for (String each : databaseNames) {
// if (each.endsWith(i % 2 + "")) {
// result.add(each);
// }
// }
// }
// return result;
// }
}
3.3 MybatisConfig.java
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.shardingsphere.api.config.sharding.ShardingRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.TableRuleConfiguration;
import org.apache.shardingsphere.api.config.sharding.strategy.StandardShardingStrategyConfiguration;
import org.apache.shardingsphere.shardingjdbc.api.ShardingDataSourceFactory;
import org.assertj.core.util.Lists;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ResourceLoader;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import com.google.common.collect.Maps;
import com.jiaoyan.tiku.enumeration.DataSourceEnum;
@Configuration
@ComponentScan(basePackageClasses = MybatisConfig.class)
@MapperScan(basePackages = "com.shopping.dao", sqlSessionTemplateRef = "sqlSessionTemplate")
@EnableTransactionManagement
@AutoConfigureAfter({DataSourceConfig.class})
public class MybatisConfig {
private static final String MAPPER_LOCATION = "classpath:mapper/**/*.xml";
private static final String TYPE_ALIAS_PACKAGE = "com.shopping.dao";
private static final String MYBATIS_CONFIG_LOCATION = "classpath:mybatis/mybatis.xml";
private static final String DATABASE_PREFIX = "ds";
private static final String ORDER_ID = "order_id";
@Resource(name = "ds0DataSource")
private DataSource ds0DataSource;
@Resource(name = "ds1DataSource")
private DataSource ds1DataSource;
@Autowired
private ResourceLoader resourceLoader;
@Value("${spring.shardingsphere.sharding.binding-tables}")
private String tables;
@Value("${spring.shardingsphere.datasource.names}")
private String databases;
@Bean
public DataSource dataSource() throws SQLException {
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getBindingTableGroups().add(tables);
shardingRuleConfig.getTableRuleConfigs().addAll(getTableRule());
shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(
new StandardShardingStrategyConfiguration(ORDER_ID, new DatabaseShardingAlgorithm()));
return ShardingDataSourceFactory.createDataSource(roundRobinDataSourceProxy(), shardingRuleConfig, new Properties());
}
private List<TableRuleConfiguration> getTableRule() {
String[] tableNameList = tables.split(",");
String[] databaseNameList = databases.split(",");
int databaseSize = databaseNameList.length;
List<TableRuleConfiguration> tableRuleList = Lists.newArrayList();
for (String tableName : tableNameList) {
tableRuleList.add(getTableRule(tableName, databaseSize));
}
return tableRuleList;
}
private TableRuleConfiguration getTableRule(String tableName, int databaseSize) {
// ds${0..1}.tableName
return new TableRuleConfiguration(tableName, DATABASE_PREFIX + "${0.." + (databaseSize - 1) + "}." + tableName);
}
public Map<String, DataSource> roundRobinDataSourceProxy() {
Map<String, DataSource> targetDataSourceMap = Maps.newHashMap();
targetDataSourceMap.put(DATABASE_PREFIX + DataSourceEnum.ds0.getIndex(), ds0DataSource);
targetDataSourceMap.put(DATABASE_PREFIX + DataSourceEnum.ds1.getIndex(), ds1DataSource);
return targetDataSourceMap;
}
@Bean
public DataSourceTransactionManager transactionManager() throws SQLException {
return new DataSourceTransactionManager(dataSource());
}
@Bean
public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
@Bean
public SqlSessionFactory sqlSessionFactory() throws Exception {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setTypeAliasesPackage(TYPE_ALIAS_PACKAGE);
bean.setConfigLocation(resourceLoader.getResource(MYBATIS_CONFIG_LOCATION));
bean.setDataSource(dataSource());
bean.setFailFast(true);
//Add XML directory
ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
try {
bean.setMapperLocations(resolver.getResources(MAPPER_LOCATION));
return bean.getObject();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
4. Startup class configuration
Because the DataSource is customized, you need to exclude the DataSource provided by SpringBoot by default:
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
3, Pit avoidance Guide
1,Java -version 1.8.0_291 version, startup error, change to 1.8.0_ 211 is no problem.
4, Reference documents
1. ShardingSphere configuration manual: https://shardingsphere.apache.org/document/legacy/4.x/document/cn/manual/sharding-jdbc/configuration/
2. Shardingsphere example project Git address: https://github.com/geomonlin/incubator-shardingsphere-example
3. Sub database & sub table of ShardingSphere: https://blog.csdn.net/justry_deng/article/details/90815986