1、引入ShardingSphere依赖
plugins {
id 'java'
id 'org.springframework.boot' version '2.3.4.RELEASE'
}
group = 'com.example'
version = '0.0.1-SNAPSHOT'
repositories {
maven { name 'ali'; url 'https://maven.aliyun.com/repository/public/'} //阿里镜像仓库
mavenCentral()
}
dependencies {
implementation 'org.springframework.boot:spring-boot-starter-web'
implementation group: 'org.apache.shardingsphere', name: 'shardingsphere-jdbc-core', version: '5.4.0'
implementation group: 'org.springframework', name: 'spring-jdbc', version: '5.3.26'
implementation 'com.oracle.database.jdbc:ojdbc8'
implementation group: 'com.zaxxer', name: 'HikariCP', version: '3.4.5'
implementation group: 'com.oracle.database.nls', name: 'orai18n', version: '21.9.0.0'
}
2、实现分库分表策略
import com.zaxxer.hikari.HikariDataSource;
import org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory;
import org.apache.shardingsphere.infra.config.algorithm.AlgorithmConfiguration;
import org.apache.shardingsphere.infra.config.mode.ModeConfiguration;
import org.apache.shardingsphere.mode.repository.standalone.StandalonePersistRepositoryConfiguration;
import org.apache.shardingsphere.sharding.api.config.ShardingRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.rule.ShardingTableRuleConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.keygen.KeyGenerateStrategyConfiguration;
import org.apache.shardingsphere.sharding.api.config.strategy.sharding.StandardShardingStrategyConfiguration;
import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.*;
public class ShardingSphereJdbcConfigUtil {
public static DataSource getDataSource() throws SQLException {
Map<String, DataSource> dataSourceMap = new HashMap<>();
HikariDataSource firstDataSource = new HikariDataSource();
firstDataSource.setDriverClassName("oracle.jdbc.OracleDriver");
firstDataSource.setJdbcUrl("jdbc:oracle:thin:@10.2.21.23:1521/orcl");
firstDataSource.setUsername("DS_1");
firstDataSource.setPassword("DS_1");
dataSourceMap.put("DS_1", firstDataSource);
HikariDataSource secondDataSource = new HikariDataSource();
secondDataSource.setDriverClassName("oracle.jdbc.OracleDriver");
secondDataSource.setJdbcUrl("jdbc:oracle:thin:@10.2.21.23:1521/orcl");
secondDataSource.setUsername("DS_0");
secondDataSource.setPassword("DS_0");
dataSourceMap.put("DS_0", secondDataSource);
Properties databaseAlgorithm = new Properties();
databaseAlgorithm.setProperty("algorithm-expression", "DS_${ USER_ID % 2}");
Properties orderAlgorithm = new Properties();
orderAlgorithm.setProperty("algorithm-expression", "T_ORDER_${ USER_ID % 2}");
ShardingTableRuleConfiguration orderTableRuleConfiguration = new ShardingTableRuleConfiguration("T_ORDER", "DS_${0..1}.T_ORDER_${0..1}");
orderTableRuleConfiguration.setTableShardingStrategy(new StandardShardingStrategyConfiguration("USER_ID", "order_algorithm"));
orderTableRuleConfiguration.setKeyGenerateStrategy(new KeyGenerateStrategyConfiguration("USER_ID", "snowflake"));
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTables().add(orderTableRuleConfiguration);
shardingRuleConfig.getShardingAlgorithms().put("order_algorithm", new AlgorithmConfiguration("INLINE", orderAlgorithm));
shardingRuleConfig.getKeyGenerators().put("snowflake", new AlgorithmConfiguration("SNOWFLAKE", new Properties()));
shardingRuleConfig.setDefaultDatabaseShardingStrategy(new StandardShardingStrategyConfiguration("USER_ID", "database_algorithm"));
shardingRuleConfig.getShardingAlgorithms().put("database_algorithm", new AlgorithmConfiguration("INLINE", databaseAlgorithm));
Properties props = new Properties();
props.setProperty("sql.show", "true");
ModeConfiguration modeConfiguration = new ModeConfiguration("Standalone", new StandalonePersistRepositoryConfiguration("JDBC", new Properties()));
return ShardingSphereDataSourceFactory.createDataSource("DS",modeConfiguration , dataSourceMap, Collections.singleton(shardingRuleConfig), props);
}
}