概述
搭建环境
Springboot2.3.2.RELEASE + Tkmybatis + Sharding-jdbc + Druid
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.2.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.study</groupId>
<artifactId>shardingjdbcdemo</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>shardingjdbcdemo</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.14</version>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.20</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<!-- tkmybatis -->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>2.1.5</version>
</dependency>
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper</artifactId>
<version>4.1.5</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>
</project>
建库、建表
库、表设计:
- 创建数据库course_db。
- 在数据库中创建两种表course_1和course_2。
- 约定规则:如果添加课程id是偶数把数据添加到course_1,奇数添加到course_2。
新建数据库course_db。
新建表course_1和course_2。
CREATE TABLE `course_1` (
`cid` bigint(20) NOT NULL,
`cname` varchar(50) NOT NULL,
`user_id` bigint(20) NOT NULL,
`cstatus` varchar(10) NOT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
代码结构
package com.study.shardingjdbcdemo.entity;
import lombok.Data;
@Data
@Table(name = "course")
public class Course {
@Id
private Long cid;
private String cname;
@Column(name = "user_id")
private Long userId;
private String cstatus;
}
package com.study.shardingjdbcdemo.mapper;
import com.study.shardingjdbcdemo.entity.Course;
import org.springframework.stereotype.Repository;
import tk.mybatis.mapper.common.Mapper;
@Repository
public interface CourseMapper extends Mapper<Course> {
}
package com.study.shardingjdbcdemo;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.study.shardingjdbcdemo.mapper")
public class ShardingjdbcdemoApplication {
public static void main(String[] args) {
SpringApplication.run(ShardingjdbcdemoApplication.class, args);
}
}
package com.study.shardingjdbcdemo;
import com.study.shardingjdbcdemo.mapper.CourseMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
class ShardingjdbcdemoApplicationTests {
@Autowired
private CourseMapper courseMapper;
@Test
void contextLoads() {
}
}
配置Sharding-JDBC分片策略
application.properties
# 配置分片策略
# 配置数据源
spring.shardingsphere.datasource.names=m1
spring.shardingsphere.datasource.m1.type=com.alibaba.druid.pool.DruidDataSource
spring.shardingsphere.datasource.m1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.m1.url=jdbc:mysql://localhost:3306/course_db?serverTimezone=GMT%2B8
spring.shardingsphere.datasource.m1.username=root
spring.shardingsphere.datasource.m1.password=123456
# 指定course表分布情况,配置表在哪个数据库中,表名称都是什么
spring.shardingsphere.sharding.tables.course.actual-data-nodes=m1.course_$->{1..2}
# 指定course主键cid生成策略SNOWFLAKE
spring.shardingsphere.sharding.tables.course.key-generator.column=cid
spring.shardingsphere.sharding.tables.course.key-generator.type=SNOWFLAKE
# 指定分片策略, cid是偶数添加到course_1表中,cid是奇数添加到course_2
spring.shardingsphere.sharding.tables.course.table-strategy.inline.sharding-column=cid
spring.shardingsphere.sharding.tables.course.table-strategy.inline.algorithm-expression=course_$->{cid % 2 + 1}
# 打开sql输出日志
spring.shardingsphere.props.sql.show=true
测试
测试用例
package com.study.shardingjdbcdemo;
import com.study.shardingjdbcdemo.entity.Course;
import com.study.shardingjdbcdemo.mapper.CourseMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
@SpringBootTest
class ShardingjdbcdemoApplicationTests {
@Autowired
private CourseMapper courseMapper;
@Test
void add() {
for (int i = 0; i < 10; i++) {
Course course = new Course();
course.setCname("java");
course.setUserId(100L);
course.setCstatus("normal");
// 注意使用insertSelective,否则会报错,因为sharding-jdbc会自己生成主键字段并拼接到sql中,如果sql语句中带有cid主键就会重复报错
courseMapper.insertSelective(course);
}
}
@Test
void query() {
List<Course> courses = courseMapper.selectAll();
courses.forEach(System.out::println);
}
}
测试报错
The bean 'dataSource', defined in class path resource [org/apache/shardingsphere/shardingjdbc/spring/boot/SpringBootConfiguration.class], could not be registered. A bean with that name has already been defined in class path resource [com/alibaba/druid/spring/boot/autoconfigure/DruidDataSourceAutoConfigure.class] and overriding is disabled.
Action:
Consider renaming one of the beans or enabling overriding by setting spring.main.allow-bean-definition-overriding=true
解决方案
application.properties中添加配置
# 解决一个Course实体类不能对应两张表的问题
spring.main.allow-bean-definition-overriding=true
版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。
文章由极客之音整理,本文链接:https://www.bmabk.com/index.php/post/100040.html