这两天抽空搞了一下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 generatorConfigurationPUBLIC "-//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 {@Autowiredprivate 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);@Resourceprivate UserDAO userDAO;@Overridepublic boolean addUser() {User user = JMockData.mock(User.class);int i = userDAO.insertSelective(user);if(i == 1){return true;}return false;}@Overridepublic List<User> findUserList() {List<User> userList = userDAO.findUserList();return userList;}@Overridepublic 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: 8018spring:application:name: bj-sharding-jdbcmain:allow-bean-definition-overriding: trueprofiles:# rw-读写分离配置 table-数据分表+读写分离 dt-分库分表+读写分离active: dt
mybatis:mapper-locations: classpath:/top/qrainly/**/dao/**/*.xml
读写分离配置–>application-rw.yml
sharding:jdbc:dataSource:names: db-test0,db-test1db-test0:type: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMTusername: rootpassword: 123456maxPoolSize: 20db-test1:type: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3307/bj_sharding?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMTusername: rootpassword: 123456maxPoolSize: 20config:# 仅配置读写分离时打开此配置masterslave:# 配置从库选择策略，提供轮询与随机，这里选择用轮询//random 随机 //round_robin 轮询load-balance-algorithm-type: round_robinname: db1s2master-data-source-name: db-test0slave-data-source-names: db-test1props:sql:# 开启SQL显示，默认值: false，注意：仅配置读写分离时不会打印日志！！！show: true
数据分表+读写分离配置–>application-table.yml
sharding:jdbc:dataSource:names: db-test0,db-test1db-test0:type: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMTusername: rootpassword: 123456maxPoolSize: 20db-test1:type: com.alibaba.druid.pool.DruidDataSourcedriverClassName: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3307/bj_sharding?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMTusername: rootpassword: 123456maxPoolSize: 20config:# 配置数据分表sharding:tables:user:table-strategy:standard:sharding-column: idprecise-algorithm-class-name: top.qrainly.sharding.jdbc.config.MyPreciseShardingAlgorithm# 读取ds_0数据源的user_0、user_1、user_2、user_3actual-data-nodes: ds_0.user_$->{0..3}master-slave-rules:ds_0:master-data-source-name: db-test0slave-data-source-names: db-test1props: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# 主库0ds-master-0:password: 123456type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3306/bj_sharding?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMTusername: root# 主库0-从库0ds-master-0-slave-0:password: 123456type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3307/bj_sharding?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMTusername: root# 主库1ds-master-1:password: 123456type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3306/bj_sharding1?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMTusername: root# 主库1-从库0ds-master-1-slave-0:password: 123456type: com.alibaba.druid.pool.DruidDataSourcedriver-class-name: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3307/bj_sharding1?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMTusername: rootconfig:sharding:tables:user:table-strategy:inline:sharding-column: idalgorithm-expression: user_$->{id % 5}key-generator-column-name: idactual-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-0master-data-source-name: ds-master-1ds_0:slave-data-source-names: ds-master-0-slave-0master-data-source-name: ds-master-0
注：分库分表配置下需要在@SpringBootApplication上添加参数exclude={DataSourceAutoConfiguration.class}
ok，切换spring.profiles.active在不同配置模式下耍吧！
持续更新中…