SpringBoot日常：集成shareingsphere-jdbc
文章目录
- pom依赖
- application.yml配置
- log4j2.xml
- 实体类
- Mapper
- Service
- controller
- 调用插入接口
- 调用查询接口
本章内容我们来聊聊如何将shareingsphere-jdbc集成到我们自己的springboot项目中，本章采用的shareingsphere-jdbc版本是5.1.2，springboot项目是2.6.13，下面是具体的集成步骤
pom依赖
引入相关的依赖，包括数据库驱动、mybatis、以及springboot-web等依赖
<?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>
<groupId>com.cys</groupId>
<artifactId>test-shareingsphere</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>test-shareingsphere</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>17</java.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<spring-boot.version>2.6.13</spring-boot.version>
<shardingsphere.version>4.1.1</shardingsphere.version>
<mybatis-plus-boot-starter.version>3.5.3.1</mybatis-plus-boot-starter.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
<version>${spring-boot.version}</version>
<exclusions>
<exclusion>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.17.2</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-slf4j-impl</artifactId>
<version>2.17.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<version>${spring-boot.version}</version>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>${spring-boot.version}</version>
<exclusions><!-- 去掉springboot默认配置 -->
<exclusion>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>5.1.2</version>
<exclusions>
<exclusion>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis-plus-boot-starter.version}</version>
</dependency>
</dependencies>
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-dependencies</artifactId>
<version>${spring-boot.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>17</source>
<target>17</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>${spring-boot.version}</version>
<configuration>
<mainClass>com.cys.testshareingsphere.TestShareingsphereApplication</mainClass>
<skip>true</skip>
</configuration>
<executions>
<execution>
<id>repackage</id>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
application.yml配置
在启动配置配置好相关的信息，这里我们用了ds0和ds1两个数据源作为分库
spring:
profiles:
active: dev
application:
name: test-shareingsphere # 应用名称，对应 Nacos 中的 Data ID
shardingsphere:
datasource:
names: ds0,ds1
ds0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://192.168.110.178:3307/cys?useSSL=false&useUnicode=true&characterEncoding=utf-8&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
username: root
password: 121212s4!
ds1:
type: com.zaxxer.hikari.HikariDataSource #com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
# 如果是用HikariDataSource则key为jdbc-url 如果用druid 则key 为url
url: jdbc:mysql://192.168.110.178:3307/cys1?useSSL=false&useUnicode=true&characterEncoding=utf-8&useLegacyDatetimeCode=false&serverTimezone=Asia/Shanghai
username: root
password: 121212s4!
rules:
sharding:
tables:
user: # 逻辑表名
actual-data-nodes: ds${0..1}.user_${0..1}
database-strategy: # 分库策略
standard:
sharding-column: id
sharding-algorithm-name: database-inline
#指定分表策略和分表算法，根据id%分表总数来进行分表
table-strategy:
standard:
sharding-column: id
sharding-algorithm-name: table-inline
#指定主键生成策略，使用雪花算法进行生成 如果使用雪花算法主键则需要使用bigInt类型，实体类主键需要使用long类型
key-generate-strategy:
column: id
key-generator-name: snowflake
sharding-algorithms:
database-inline:
type: INLINE
props:
algorithm-expression: ds${id % 2}
table-inline:
type: INLINE
props:
algorithm-expression: user_${id % 2}
props:
sql-show: true
server:
port: 9006
logging:
config: classpath:log4j2.xml
level:
root: info
org.apache.shardingsphere: debug
log4j2.xml
这里把log配置也列出来，主要用到了sql日志打印
<?xml version="1.0" encoding="UTF-8"?>
<Configuration status="WARN">
<Appenders>
<Console name="Console" target="SYSTEM_OUT">
<PatternLayout pattern="%d{yyyy-MM-dd HH:mm:ss} [%t] %-5level %logger{36} - %msg%n"/>
</Console>
</Appenders>
<Loggers>
<!-- 设置 ShardingSphere SQL 日志为 DEBUG -->
<Logger name="org.apache.shardingsphere" level="DEBUG" additivity="false">
<AppenderRef ref="Console"/>
</Logger>
<Root level="INFO">
<AppenderRef ref="Console"/>
</Root>
</Loggers>
</Configuration>
实体类
@Data
@TableName(value="user")
public class UserEntity implements Serializable {
@TableId(type = IdType.ASSIGN_ID)
private Long id;
private String name;
}
Mapper
mapper 接口，集成mybatis的基础接口
@Mapper
public interface UserMapper extends BaseMapper<UserEntity> {
@Select("select * from user where id = #{id}")
UserEntity selectMyUser(Long id);
}
Service
service 具体的一个逻辑，分别是获取数据和插入数据的两个方法
@Service
@Slf4j
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
public void getUser(Long id) {
UserEntity userEntity = userMapper.selectMyUser(id);
System.out.println(userEntity);
}
@Override
public void createUser(String name) {
UserEntity userEntity = new UserEntity();
userEntity.setName(name);
userMapper.insert(userEntity);
}
}
controller
controller开放两个查询和写入的接口，用于测试调用
@RestController
public class UserController {
@Autowired
private UserService userService;
@GetMapping("/getUser")
public String getUser(@RequestParam("id") Long id, HttpServletRequest request) {
userService.getUser(id);
return "getUser";
}
@GetMapping("/createUser")
public String getUser(@RequestParam("name") String name, HttpServletRequest request) {
userService.createUser(name);
return "true";
}
}
调用插入接口
查看数据库数据
日志片段中可以看出，根据对应id执行分片算法，然后找到具体的数据表，然后执行插入操作
调用查询接口
日志片段中可以看出，根据对应id执行分片算法，然后找到具体的数据表，然后执行查询
