我们在日常工作中还是使用比较多的分库分表组件的，其中比较优秀的就有 Sharding-Jdbc，一开始由当当开源，后来捐献给了 Apache，说一下简单使用，因为原来经常的使用都是基于 xml 跟 properties 组合起来使用，这里主要试下用 Java Config 来配置
首先是通过 Spring Initializr 创建个带 jdbc 的 Spring Boot 项目，然后引入主要的依赖1
2
3
4
5
| <dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core</artifactId>
<version>5.0.0-beta</version>
</dependency>
|
因为前面有聊过 Spring Boot 的自动加载，在这里 spring 就会自己去找 DataSource 的配置，所以要在入口把它干掉1
2
| @SpringBootApplication(exclude = { DataSourceAutoConfiguration.class})
public class ShardingJdbcDemoApplication implements CommandLineRunner {
|
然后因为想在入口跑代码，就实现了下 org.springframework.boot.CommandLineRunner 主要是后面的 Java Config 代码1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
@Configuration
public class MysqlConfig {
@Bean
public DataSource dataSource() throws SQLException {
Map<String, DataSource> dataSourceMap = new HashMap<>();
HikariDataSource dataSource1 = new HikariDataSource();
dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
dataSource1.setJdbcUrl("jdbc:mysql://localhost:3306/sharding");
dataSource1.setUsername("username");
dataSource1.setPassword("password");
dataSourceMap.put("ds0", dataSource1);
ShardingTableRuleConfiguration studentTableRuleConfig = new ShardingTableRuleConfiguration("student", "ds0.student_$->{0..1}");
studentTableRuleConfig.setTableShardingStrategy(new StandardShardingStrategyConfiguration("user_id", "tableShardingAlgorithm"));
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTables().add(studentTableRuleConfig);
Properties tableShardingAlgorithmrProps = new Properties();
tableShardingAlgorithmrProps.setProperty("algorithm-expression", "student_${user_id % 2}");
shardingRuleConfig.getShardingAlgorithms().put("tableShardingAlgorithm", new ShardingSphereAlgorithmConfiguration("INLINE", tableShardingAlgorithmrProps));
return ShardingSphereDataSourceFactory.createDataSource(dataSourceMap, Collections.singleton(shardingRuleConfig), new Properties());
}
}
|
然后我们就可以在使用这个 DataSource 了，先看下这两个表的数据
![]()
![]()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
| @Override
public void run(String... args) {
LOGGER.info("run here");
String sql = "SELECT * FROM student WHERE user_id=? ";
try (
Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, 1001);
ResultSet resultSet = ps.executeQuery();
while (resultSet.next()) {
final int id = resultSet.getInt("id");
final String name = resultSet.getString("name");
final int userId = resultSet.getInt("user_id");
final int age = resultSet.getInt("age");
System.out.println("奇数表 id：" + id + " 姓名：" + name
+ " 用户 id：" + userId + " 年龄：" + age );
System.out.println("=============================");
}
ps.setInt(1, 1000);
resultSet = ps.executeQuery();
while (resultSet.next()) {
final int id = resultSet.getInt("id");
final String name = resultSet.getString("name");
final int userId = resultSet.getInt("user_id");
final int age = resultSet.getInt("age");
System.out.println("偶数表 id：" + id + " 姓名：" + name
+ " 用户 id：" + userId + " 年龄：" + age );
System.out.println("=============================");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
|
看下查询结果
![]()
