搭建环境:
Springboot
+mybatis-plus
+Sharding-JDBC
+Druid连接池
- 创建一个工程并导入相关依赖
依赖:
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.18</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- ShardingSphere -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core</artifactId>
<version>5.0.0-alpha</version>
</dependency>
</dependencies>
-
按照水平分表的方式,创建数据库和数据表
(1)创建数据库course_db
(2)在数据库创建两张表course_1和course_2
(3)约定规则:如果添加课程id是偶数把数据添加course_1,如果奇数添加到course_2
sql代码如下:
注:创建数据库的没写
CREATE TABLE course_1(
cid BIGINT(20) PRIMARY KEY,
cname VARCHAR(50) NOT NULL,
user_id BIGINT(20) NOT NULL,
cstatus VARCHAR(10) NOT NULL
);
CREATE TABLE course_2 (
cid BIGINT(20) PRIMARY KEY,
cname VARCHAR(50) NOT NULL,
user_id BIGINT(20) NOT NULL,
cstatus VARCHAR(10) NOT NULL
);
编写代码实现:
实体类:
@Data
public class Course {
private Long cid;
private String cname;
private Long userId;
private String cstatus;
}
mybatis-plus的mapper接口:
@Repository
public interface CourseMapper extends BaseMapper<Course> {
}
然后在application.properties配置文件里面进行配置(最好不要用yaml)
去官网找具体的配置方法:
使用Java API 的方式来运行:
**
使用Java API
**
public class main {
public static void main(String[] args) throws Exception{
// 配置真实数据源
Map<String, DataSource> dataSourceMap = new HashMap<>();
// 配置第 1 个数据源
HikariDataSource dataSource1 = new HikariDataSource();
dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
dataSource1.setJdbcUrl("jdbc:mysql://localhost:3306/course_db");
dataSource1.setUsername("root");
dataSource1.setPassword("root");
dataSourceMap.put("m0", dataSource1);
// 配置 t_order 表规则
ShardingTableRuleConfiguration orderTableRuleConfig = new ShardingTableRuleConfiguration("course", "m0.course_${1..2}");
// 配置分表策略
orderTableRuleConfig.setTableShardingStrategy(new StandardShardingStrategyConfiguration("cid", "tableShardingAlgorithm"));
// 配置分片规则
ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
shardingRuleConfig.getTables().add(orderTableRuleConfig);
// 配置分表算法
Properties tableShardingAlgorithmrProps = new Properties();
tableShardingAlgorithmrProps.setProperty("algorithm-expression", "course_${cid % 2 + 1 }");
shardingRuleConfig.getShardingAlgorithms().put("tableShardingAlgorithm", new ShardingSphereAlgorithmConfiguration("INLINE", tableShardingAlgorithmrProps));
// 创建 ShardingSphereDataSource
DataSource dataSource = ShardingSphereDataSourceFactory.createDataSource(dataSourceMap, Collections.singleton(shardingRuleConfig), new Properties());
Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement();
statement.execute("INSERT INTO course (cid,cname,user_id,cstatus) VALUES(2,21334,53443,4)");
}
}
执行SQL是在最后一行,这时的cid为2 ,那么就到course_1里去,经实践,确实这样:
使用yaml方式(配置文件方式配置)
这个方式我找bug超级久,可能很多地方都会造成错误
依赖:
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.3</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
parent的版本是2.4.3
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.18</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-configuration-processor</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- ShardingSphere -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.0.0-alpha</version>
</dependency>
</dependencies>
这些是所有依赖
然后配置文件application.yaml 里面配置数据源和分片规则:
spring:
shardingsphere:
# 数据源配置
datasource:
common:
# 数据库驱动类名
driver-class-name: com.mysql.cj.jdbc.Driver
# 数据库连接池类名称
type: com.zaxxer.hikari.HikariDataSource
# 数据源名称,多数据源以逗号分隔
names: db0
db0:
jdbc-url: jdbc:mysql://127.0.0.1:3306/course_db
username: root
password: root
# 规则配置
rules:
sharding:
# 分片算法配置
sharding-algorithms:
table-inline:
# 分片算法类型 https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/configuration/built-in-algorithm/sharding/
type: INLINE
props:
# 分片算法的行表达式
algorithm-expression: course_$->{ cid % 2 + 1 }
# 分布式序列算法配置
key-generators:
snowflake:
# 分布式序列算法(雪花算法:SNOWFLAKE; UUID:UUID)(注:UUID没有props配置) https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/configuration/built-in-algorithm/keygen/
type: SNOWFLAKE
# 分布式序列算法属性配置
props:
# 工作机器唯一标识
worker-id: 1
tables:
# 逻辑表名称
course:
# 行表达式标识符可以使用 ${...} 或 $->{...},但前者与 Spring 本身的属性文件占位符冲突,因此在 Spring 环境中使用行表达式标识符建议使用 $->{...}
actual-data-nodes: db0.course_$->{1..2}
# 分表策略
table-strategy:
standard:
# 分片列名称
sharding-column: cid
# 分片算法名称
sharding-algorithm-name: table-inline
# 属性配置
props:
# 展示修改以后的sql语句
sql-show: true
实体类:
import lombok.Data;
@Data
public class Course {
private Long cid;
private String cname;
private Long userId;
private String cstatus;
}
使用的mybatis-plus的mapper接口:
@Mapper
public interface courseMapper extends BaseMapper<Course> {
}
最后测试类:
@SpringBootTest
class ApplicationTests {
@Resource
DataSource dataSource;
@Autowired
courseMapper courseMapper;
@Test
void contextLoads() throws Exception{
Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement();
statement.execute("INSERT INTO course (cid,cname,user_id,cstatus) VALUES(4,21334,53443,4)");
}
@Test
void usermybatis(){
Course course=new Course();
course.setCid(6L);
course.setCname("mybatis-plus");
course.setUserId(100L);
course.setCstatus("hello");
courseMapper.insert(course);
}
}
可以看到不管是直接使用Datasource还是使用mybtais-plus都成功了。
最后提供既分库又分表的配置文件写法:
spring:
shardingsphere:
# 数据源配置
datasource:
common:
# 数据库驱动类名
driver-class-name: com.mysql.cj.jdbc.Driver
# 数据库连接池类名称
type: com.zaxxer.hikari.HikariDataSource
# 数据源名称,多数据源以逗号分隔
names: db1,db2
db1:
jdbc-url: jdbc:mysql://127.0.0.1:3306/course_db01
username: root
password: root
db2:
jdbc-url: jdbc:mysql://127.0.0.1:3306/course_db02
username: root
password: root
# 规则配置
rules:
sharding:
# 分片算法配置
sharding-algorithms:
database-inline:
# 分片算法类型 https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/configuration/built-in-algorithm/sharding/
type: INLINE
props:
# 分片算法的行表达式
algorithm-expression: db$->{ cid % 2 + 1 }
table-inline:
type: INLINE
props:
algorithm-expression: course_$->{ cid % 2 + 1 }
# 分布式序列算法配置
key-generators:
snowflake:
# 分布式序列算法(雪花算法:SNOWFLAKE; UUID:UUID)(注:UUID没有props配置) https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/configuration/built-in-algorithm/keygen/
type: SNOWFLAKE
# 分布式序列算法属性配置
props:
# 工作机器唯一标识
worker-id: 1
tables:
# 逻辑表名称
course:
# 行表达式标识符可以使用 ${...} 或 $->{...},但前者与 Spring 本身的属性文件占位符冲突,因此在 Spring 环境中使用行表达式标识符建议使用 $->{...}
actual-data-nodes: db$->{1..2}.course_$->{1..2}
# 分表策略
database-strategy:
standard:
# 分片列名称
sharding-column: cid
# 分片算法名称
sharding-algorithm-name: database-inline
table-strategy:
standard:
sharding-column: cid
sharding-algorithm-name: table_inline
# 属性配置
props:
# 展示修改以后的sql语句
sql-show: true