这篇文章主要介绍“如何用springboot+mybatis+Sharding jdbc实现的分库分表、读写分离”，在日常操作中，相信很多人在如何用springboot+mybatis+Sharding jdbc实现的分库分表、读写分离问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”如何用springboot+mybatis+Sharding jdbc实现的分库分表、读写分离”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
1、新建一个spring boot工程，添加依赖
<dependencies> <!--核心服务--> <dependency> <groupId>top.qrainly</groupId> <artifactId>bj_core</artifactId> <version>0.0.1-SNAPSHOT</version> </dependency> <!--mybatis--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency> <!--druid--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.10</version> </dependency> <!--sharding-jdbc--> <dependency> <groupId>io.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>3.1.0.M1</version> </dependency> <!--jmockdata--> <dependency> <groupId>com.github.jsonzou</groupId> <artifactId>jmockdata</artifactId> <version>4.1.2</version> </dependency> </dependencies>
这里友情推荐一下，依赖里用到了朋友开源的一个工具插件JMockData，此乃开发测试之利器，强烈推荐！！！
2、在master库执行sql脚本，创建用户表
DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(12) NOT NULL AUTO_INCREMENT, `username` varchar(12) NOT NULL, `password` varchar(30) NOT NULL, PRIMARY KEY (`id`), KEY `idx-username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `user_0`; CREATE TABLE `user_0` ( `id` int(12) NOT NULL AUTO_INCREMENT, `username` varchar(12) NOT NULL, `password` varchar(30) NOT NULL, PRIMARY KEY (`id`), KEY `idx-username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `user_1`; CREATE TABLE `user_1` ( `id` int(12) NOT NULL AUTO_INCREMENT, `username` varchar(12) NOT NULL, `password` varchar(30) NOT NULL, PRIMARY KEY (`id`), KEY `idx-username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `user_2`; CREATE TABLE `user_2` ( `id` int(12) NOT NULL AUTO_INCREMENT, `username` varchar(12) NOT NULL, `password` varchar(30) NOT NULL, PRIMARY KEY (`id`), KEY `idx-username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `user_3`; CREATE TABLE `user_3` ( `id` int(12) NOT NULL AUTO_INCREMENT, `username` varchar(12) NOT NULL, `password` varchar(30) NOT NULL, PRIMARY KEY (`id`), KEY `idx-username` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `user_4`; CREATE TABLE `user_4` ( `id` INT(12) NOT NULL AUTO_INCREMENT, `username` VARCHAR(12) NOT NULL, `password` VARCHAR(30) NOT NULL, PRIMARY KEY (`id`), KEY `idx-username` (`username`) ) ENGINE=INNODB DEFAULT CHARSET=utf8;
配置主从复制的内容请参考windows上mysql的主从配置
3、配置生成dao/domain文件 mybatis-generator配置
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE generatorConfiguration PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN" "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd"> <generatorConfiguration> <context id="DB2Tables" targetRuntime="MyBatis3"> <commentGenerator> <property name="suppressDate" value="true"/> <property name="suppressAllComments" value="true"/> </commentGenerator> <!--数据库链接地址账号密码--> <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&characterEncoding=utf-8&useSSL=false&allowMultiQueries=true" userId="root" password="123456"> </jdbcConnection> <javaTypeResolver> <property name="forceBigDecimals" value="false"/> </javaTypeResolver> <!--生成Model类存放位置--> <javaModelGenerator targetPackage="top.qrainly.sharding.jdbc.domain" targetProject="src/main/java"> <property name="enableSubPackages" value="true"/> <property name="trimStrings" value="true"/> </javaModelGenerator> <!--生成映射文件存放位置--> <sqlMapGenerator targetPackage="top.qrainly.sharding.jdbc.dao" targetProject="src/main/java"> <property name="enableSubPackages" value="true"/> </sqlMapGenerator> <!--生成Dao类存放位置--> <!-- 客户端代码，生成易于使用的针对Model对象和XML配置文件 的代码 type="ANNOTATEDMAPPER",生成Java Model 和基于注解的Mapper对象 type="MIXEDMAPPER",生成基于注解的Java Model 和相应的Mapper对象 type="XMLMAPPER",生成SQLMap XML文件和独立的Mapper接口 --> <javaClientGenerator type="XMLMAPPER" targetPackage="top.qrainly.sharding.jdbc.dao" targetProject="src/main/java"> <property name="enableSubPackages" value="true"/> </javaClientGenerator> <!--生成对应表及类名--> <table tableName="user" domainObjectName="User" mapperName="UserDAO" enableCountByExample="true" enableUpdateByExample="true" enableDeleteByExample="true" enableSelectByExample="true" selectByExampleQueryId="true"></table> </context> </generatorConfiguration>
4、提供一个查询和添加的接口
controller
/**
* @author v_liuwen
* @date 2019-07-10
*/
@RestController
@RequestMapping(value = "/user")
@Slf4j
public class UserController {
@Autowired
private IUserService iUserService;
@GetMapping("/list")
public JsonResult<List<User>> list() {
List<User> userList = iUserService.findUserList();
return JsonResult.okJsonResultWithData(userList);
}
@GetMapping("/add")
public JsonResult<Boolean> add(@RequestParam(name = "user",required = false) User user) {
log.info("新增用户信息-->{}", JSONObject.toJSONString(user));
boolean result = iUserService.addUser();
return JsonResult.okJsonResultWithData(result);
}
@GetMapping("/batchAdd")
public JsonResult<Boolean> batchAdd() {
boolean result = iUserService.batchAddUser();
return JsonResult.okJsonResultWithData(result);
}
}service
/**
* @author v_liuwen
* @date 2019-07-10
*/
@Service
@Slf4j
public class IUserServiceImpl implements IUserService {
private AtomicInteger num = new AtomicInteger(1);
@Resource
private UserDAO userDAO;
@Override
public boolean addUser() {
User user = JMockData.mock(User.class);
int i = userDAO.insertSelective(user);
if(i == 1){
return true;
}
return false;
}
@Override
public List<User> findUserList() {
List<User> userList = userDAO.findUserList();
return userList;
}
@Override
public boolean batchAddUser() {
try{
for (int i =100;i<150;i++){
User user = JMockData.mock(User.class);
user.setId(num.getAndIncrement());
userDAO.insertSelective(user);
}
return true;
}catch (Exception e){
log.error("批量插入失败 失败原因-->{}",e.getMessage());
return false;
}
}
}5、配置文件(重点在这里)
基础配置-->application.yml
server: port: 8018 spring: application: name: bj-sharding-jdbc main: allow-bean-definition-overriding: true profiles: # rw-读写分离配置 table-数据分表+读写分离 dt-分库分表+读写分离 active: dt mybatis: mapper-locations: classpath:/top/qrainly/**/dao/**/*.xml
读写分离配置-->application-rw.yml
sharding: jdbc: dataSource: names: db-test0,db-test1 db-test0: type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT username: root password: 123456 maxPoolSize: 20 db-test1: type: com.alibaba.druid.pool.DruidDataSource driverClassName: com.mysql.jdbc.Driver url: jdbc:mysql://localhost:3307/bj_sharding?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT username: root password: 123456 maxPoolSize: 20 config: # 仅配置读写分离时打开此配置 masterslave: # 配置从库选择策略，提供轮询与随机，这里选择用轮询//random 随机 //round_robin 轮询 load-balance-algorithm-type: round_robin name: db1s2 master-data-source-name: db-test0 slave-data-source-names: db-test1 props: sql: # 开启SQL显示，默认值: false，注意：仅配置读写分离时不会打印日志！！！ show: true
数据分表+读写分离配置-->application-table.yml
sharding:
jdbc:
dataSource:
names: db-test0,db-test1
db-test0:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
username: root
password: 123456
maxPoolSize: 20
db-test1:
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3307/bj_sharding?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
username: root
password: 123456
maxPoolSize: 20
config:
# 配置数据分表
sharding:
tables:
user:
table-strategy:
standard:
sharding-column: id
precise-algorithm-class-name: top.qrainly.sharding.jdbc.config.MyPreciseShardingAlgorithm
# 读取ds_0数据源的user_0、user_1、user_2、user_3
actual-data-nodes: ds_0.user_$->{0..3}
master-slave-rules:
ds_0:
master-data-source-name: db-test0
slave-data-source-names: db-test1
props:
sql:
# 开启SQL显示，默认值: false，注意：仅配置读写分离时不会打印日志！！！
show: true分库分表+读写分离配置-->application-dt.yml
---
sharding:
jdbc:
datasource:
names: ds-master-0,ds-master-1,ds-master-0-slave-0,ds-master-1-slave-0
# 主库0
ds-master-0:
password: 123456
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
username: root
# 主库0-从库0
ds-master-0-slave-0:
password: 123456
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3307/bj_sharding?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
username: root
# 主库1
ds-master-1:
password: 123456
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/bj_sharding1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
username: root
# 主库1-从库0
ds-master-1-slave-0:
password: 123456
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3307/bj_sharding1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT
username: root
config:
sharding:
tables:
user:
table-strategy:
inline:
sharding-column: id
algorithm-expression: user_$->{id % 5}
key-generator-column-name: id
actual-data-nodes: ds_$->{0..1}.user_$->{0..4}
default-database-strategy:
inline:
# 置的分库的字段，本案例是根据id进行分
sharding-column: id
# 置的分库的逻辑，根据id%2进行分
algorithm-expression: ds_$->{id % 2}
master-slave-rules:
ds_1:
slave-data-source-names: ds-master-1-slave-0
master-data-source-name: ds-master-1
ds_0:
slave-data-source-names: ds-master-0-slave-0
master-data-source-name: ds-master-0注：分库分表配置下需要在@SpringBootApplication上添加参数exclude={DataSourceAutoConfiguration.class}
ok，切换spring.profiles.active在不同配置模式下耍吧！
到此，关于“如何用springboot+mybatis+Sharding jdbc实现的分库分表、读写分离”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
