框架：
- Spring boot
- ShardingSphere
- Mybatis
Pom：
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
Mysql
- Schema 后缀是0 - xx不限 例如：sharding0、sharding1
- Table 后缀是0 - xx不限
-- auto-generated definition
create table s_user0
(
id int auto_increment
primary key,
name varchar(20) null comment '名字',
age varchar(3) null comment '年龄'
);
代码中数据库结构：
sharding0
-->> s_user0
-->> s_user1
sharding1
-->> s_user0
-->> s_user1
application.properties
# 应用名称
spring.application.name=learn_shardingsphere
# 应用服务 WEB 访问端口
server.port=8085
# 数据源 sharding01 sharding02
spring.shardingsphere.datasource.names=sharding0,sharding1
# 第一个数据库
spring.shardingsphere.datasource.sharding0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.sharding0.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.sharding0.jdbc-url=jdbc:mysql://localhost:3306/sharding0?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.sharding0.username=root
spring.shardingsphere.datasource.sharding0.password=yihongjian
# 第二个数据库
spring.shardingsphere.datasource.sharding1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.sharding1.driver-class-name=com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.sharding1.jdbc-url=jdbc:mysql://localhost:3306/sharding1?serverTimezone=GMT%2B8&useSSL=false
spring.shardingsphere.datasource.sharding1.username=root
spring.shardingsphere.datasource.sharding1.password=yihongjian
# 水平拆分的数据库(表) 配置分库 + 分表策略 行表达式分片策略
# 分库策略
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression=sharding$->{id % 2}
# 分表策略 其中 s_user 为逻辑表 分表主要取决于s_user.id的行
spring.shardingsphere.sharding.tables.s_user.actual-data-nodes=sharding$->{0..1}.s_user$->{0..1}
spring.shardingsphere.sharding.tables.s_user.table-strategy.inline.sharding-column=age
spring.shardingsphere.sharding.tables.s_user.table-strategy.inline.algorithm-expression=s_user$->{Integer.valueOf(age) % 2}
# 打印执行的数据库以及语句
spring.shardingsphere.props.sql.show=true
spring.main.allow-bean-definition-overriding=true
logging.level.com.sharding.demo.mapper=DEBUG
# mybatis
mybatis.mapper-locations=classpath:/mappers/*.xml
Controller
package com.sharding.demo.controller;
import com.sharding.demo.entity.User;
import com.sharding.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserService userService;
@PostMapping("/save")
public String save(@RequestBody User user) {
userService.save(user);
return "success";
}
@GetMapping("/findAll")
public List<User> findAll() {
return userService.findAll();
}
}
Bean
package com.sharding.demo.entity;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class User {
private Long id;
private String name;
private String age;
}
Service
package com.sharding.demo.service.impl;
import com.sharding.demo.entity.User;
import com.sharding.demo.mapper.UserMapper;
import com.sharding.demo.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public void delete(Long id) {
userMapper.delete(id);
}
@Override
public int update(User user) {
return userMapper.update(user);
}
@Override
public List<User> findAll() {
return userMapper.findAll();
}
@Override
public void save(User user) {
userMapper.save(user);
}
}
Mapper
package com.sharding.demo.mapper;
import com.sharding.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface UserMapper {
void delete(Long id);
int update(User user);
List<User> findAll();
void save(User user);
}
UserMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sharding.demo.mapper.UserMapper">
<resultMap id="BaseResultMap" type="com.sharding.demo.entity.User">
<id column="id" jdbcType="BIGINT" property="id"/>
<result column="name" jdbcType="VARCHAR" property="name"/>
<result column="age" jdbcType="VARCHAR" property="age"/>
</resultMap>
<sql id="Base_Column_List">
id, name, age
</sql>
<insert id="save" parameterType="com.sharding.demo.entity.User">
insert into s_user (id, name, age) value (#{id}, #{name}, #{age})
</insert>
<select id="findAll" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from s_user
</select>
<delete id="delete" parameterType="java.lang.Long">
delete from s_user
where id = #{id,jdbcType=BIGINT}
</delete>
<update id="update" parameterType="com.sharding.demo.entity.User">
update s_user
set
name = #{name,jdbcType=VARCHAR},
age = #{age,jdbcType=VARCHAR}
where id = #{id,jdbcType=BIGINT}
</update>
</mapper>
启动Console
. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v2.3.7.RELEASE)
WARNING: Illegal reflective access by org.codehaus.groovy.reflection.CachedClass$3$1 (file:/Users/yihongjian/.m2/repository/org/codehaus/groovy/groovy/2.4.5/groovy-2.4.5-indy.jar) to method java.lang.Object.finalize()
WARNING: Please consider reporting this to the maintainers of org.codehaus.groovy.reflection.CachedClass$3$1
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
2021-12-26 15:13:01.055 INFO 14323 --- [ restartedMain] .e.DevToolsPropertyDefaultsPostProcessor : Devtools property defaults active! Set 'spring.devtools.add-properties' to 'false' to disable
2021-12-26 15:13:01.055 INFO 14323 --- [ restartedMain] .e.DevToolsPropertyDefaultsPostProcessor : For additional web related logging consider setting the 'logging.level.web' property to 'DEBUG'
2021-12-26 15:13:01.739 INFO 14323 --- [ restartedMain] trationDelegate$BeanPostProcessorChecker : Bean 'stringToNoneShardingStrategyConfigurationConverter' of type [org.apache.shardingsphere.spring.boot.converter.StringToNoneShardingStrategyConfigurationConverter] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2021-12-26 15:13:01.754 INFO 14323 --- [ restartedMain] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.sharding-org.apache.shardingsphere.shardingjdbc.spring.boot.sharding.SpringBootShardingRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.sharding.SpringBootShardingRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2021-12-26 15:13:01.756 INFO 14323 --- [ restartedMain] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.masterslave-org.apache.shardingsphere.shardingjdbc.spring.boot.masterslave.SpringBootMasterSlaveRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.masterslave.SpringBootMasterSlaveRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2021-12-26 15:13:01.758 INFO 14323 --- [ restartedMain] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.encrypt-org.apache.shardingsphere.shardingjdbc.spring.boot.encrypt.SpringBootEncryptRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.encrypt.SpringBootEncryptRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2021-12-26 15:13:01.760 INFO 14323 --- [ restartedMain] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere.shadow-org.apache.shardingsphere.shardingjdbc.spring.boot.shadow.SpringBootShadowRuleConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.shadow.SpringBootShadowRuleConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2021-12-26 15:13:01.762 INFO 14323 --- [ restartedMain] trationDelegate$BeanPostProcessorChecker : Bean 'spring.shardingsphere-org.apache.shardingsphere.shardingjdbc.spring.boot.common.SpringBootPropertiesConfigurationProperties' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.common.SpringBootPropertiesConfigurationProperties] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2021-12-26 15:13:01.943 INFO 14323 --- [ restartedMain] trationDelegate$BeanPostProcessorChecker : Bean 'org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration' of type [org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration$$EnhancerBySpringCGLIB$$f93fcc61] is not eligible for getting processed by all BeanPostProcessors (for example: not eligible for auto-proxying)
2021-12-26 15:13:02.160 INFO 14323 --- [ restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat initialized with port(s): 8085 (http)
2021-12-26 15:13:02.169 INFO 14323 --- [ restartedMain] o.apache.catalina.core.StandardService : Starting service [Tomcat]
2021-12-26 15:13:02.169 INFO 14323 --- [ restartedMain] org.apache.catalina.core.StandardEngine : Starting Servlet engine: [Apache Tomcat/9.0.41]
2021-12-26 15:13:02.224 INFO 14323 --- [ restartedMain] o.a.c.c.C.[Tomcat].[localhost].[/] : Initializing Spring embedded WebApplicationContext
2021-12-26 15:13:02.224 INFO 14323 --- [ restartedMain] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1169 ms
2021-12-26 15:13:02.380 INFO 14323 --- [ restartedMain] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2021-12-26 15:13:02.507 INFO 14323 --- [ restartedMain] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
2021-12-26 15:13:02.521 INFO 14323 --- [ restartedMain] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Starting...
2021-12-26 15:13:02.525 INFO 14323 --- [ restartedMain] com.zaxxer.hikari.HikariDataSource : HikariPool-2 - Start completed.
2021-12-26 15:13:02.526 INFO 14323 --- [ restartedMain] com.zaxxer.hikari.HikariDataSource : HikariPool-3 - Starting...
2021-12-26 15:13:02.530 INFO 14323 --- [ restartedMain] com.zaxxer.hikari.HikariDataSource : HikariPool-3 - Start completed.
2021-12-26 15:13:02.607 INFO 14323 --- [ restartedMain] o.a.s.core.log.ConfigurationLogger : ShardingRuleConfiguration:
defaultDatabaseStrategy:
inline:
algorithmExpression: sharding$->{id % 2}
shardingColumn: id
tables:
s_user:
actualDataNodes: sharding$->{0..1}.s_user$->{0..1}
logicTable: s_user
tableStrategy:
inline:
algorithmExpression: s_user$->{Integer.valueOf(age) % 2}
shardingColumn: age
s_student:
actualDataNodes: sharding$->{0..1}.s_student$->{0..1}
logicTable: s_student
tableStrategy:
inline:
algorithmExpression: s_student$->{Integer.valueOf(age) % 2}
shardingColumn: age
2021-12-26 15:13:02.608 INFO 14323 --- [ restartedMain] o.a.s.core.log.ConfigurationLogger : Properties:
sql.show: 'true'
2021-12-26 15:13:02.627 INFO 14323 --- [ restartedMain] ShardingSphere-metadata : Loading 2 logic tables' meta data.
2021-12-26 15:13:02.693 INFO 14323 --- [ restartedMain] ShardingSphere-metadata : Meta data load finished, cost 85 milliseconds.
2021-12-26 15:13:03.055 INFO 14323 --- [ restartedMain] o.s.s.concurrent.ThreadPoolTaskExecutor : Initializing ExecutorService 'applicationTaskExecutor'
2021-12-26 15:13:03.199 INFO 14323 --- [ restartedMain] o.s.b.d.a.OptionalLiveReloadServer : LiveReload server is running on port 35729
2021-12-26 15:13:03.223 INFO 14323 --- [ restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer : Tomcat started on port(s): 8085 (http) with context path ''
2021-12-26 15:13:03.233 INFO 14323 --- [ restartedMain] com.sharding.demo.DemoApplication : Started DemoApplication in 2.596 seconds (JVM running for 9.623)
Postman 测试结果
