原创转载请注明出处：https://www.cnblogs.com/agilestyle/p/15116426.html
Project Directory
Maven Dependency
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>org.fool</groupId> <artifactId>hellosharding-table-rw</artifactId> <version>1.0-SNAPSHOT</version> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.3.12.RELEASE</version> <relativePath/> </parent> <properties> <maven.compiler.source>8</maven.compiler.source> <maven.compiler.target>8</maven.compiler.target> </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>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.3.1</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.2.6</version> </dependency> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>4.1.1</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-generator</artifactId> <version>3.4.1</version> </dependency> <dependency> <groupId>org.freemarker</groupId> <artifactId>freemarker</artifactId> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
DB Schema
schema.sql
CREATE TABLE `tb_user` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(30) DEFAULT NULL, `password` varchar(30) DEFAULT NULL, `age` int(10) unsigned DEFAULT NULL, `create_by` varchar(30) NOT NULL, `update_by` varchar(30) NOT NULL, `create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `update_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `tb_user0` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(30) DEFAULT NULL, `password` varchar(30) DEFAULT NULL, `age` int(10) unsigned DEFAULT NULL, `create_by` varchar(30) NOT NULL, `update_by` varchar(30) NOT NULL, `create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `update_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `tb_user1` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(30) DEFAULT NULL, `password` varchar(30) DEFAULT NULL, `age` int(10) unsigned DEFAULT NULL, `create_by` varchar(30) NOT NULL, `update_by` varchar(30) NOT NULL, `create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `update_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `tb_user2` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(30) DEFAULT NULL, `password` varchar(30) DEFAULT NULL, `age` int(10) unsigned DEFAULT NULL, `create_by` varchar(30) NOT NULL, `update_by` varchar(30) NOT NULL, `create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `update_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Note：tb_user 只是用来做 MyBatis ORM 使用，另外需要额外创建两个库 test_master、test_slave
application.properties
server.port=8080 logging.level.org.fool.shardingtablerw=debug mybatis.mapper-locations=classpath:mapper/**/*.xml spring.shardingsphere.datasource.names=master,slave0 # master spring.shardingsphere.datasource.master.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.master.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.master.url=jdbc:mysql://localhost:3306/test_master?characterEncoding=utf-8&serverTimezone=Asia/Shanghai spring.shardingsphere.datasource.master.username=root spring.shardingsphere.datasource.master.password=123456 # slave spring.shardingsphere.datasource.slave0.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.cj.jdbc.Driver spring.shardingsphere.datasource.slave0.url=jdbc:mysql://localhost:3306/test_slave?characterEncoding=utf-8&serverTimezone=Asia/Shanghai spring.shardingsphere.datasource.slave0.username=root spring.shardingsphere.datasource.slave0.password=123456 # sharding table spring.shardingsphere.sharding.tables.tb_user.actual-data-nodes=master.tb_user$->{0..2} spring.shardingsphere.sharding.tables.tb_user.table-strategy.inline.sharding-column=id spring.shardingsphere.sharding.tables.tb_user.table-strategy.inline.algorithm-expression=tb_user$->{id % 3} spring.shardingsphere.sharding.tables.tb_user.key-generator.column=id spring.shardingsphere.sharding.tables.tb_user.key-generator.type=SNOWFLAKE ## R/W Splitting spring.shardingsphere.sharding.master-slave-rules.master.master-data-source-name=master spring.shardingsphere.sharding.master-slave-rules.master.slave-data-source-names=slave0 spring.shardingsphere.props.sql.show=true
Note: 这里为了方便查看测试效果，并没有实现主从部署，而是在同一个服务器实例中创建两个数据库来模拟主从数据库。
MyBatis-Plus Code Generator
CodeGenerator.java
package org.fool.shardingtablerw.generator; import com.baomidou.mybatisplus.annotation.DbType; import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.core.toolkit.StringPool; import com.baomidou.mybatisplus.generator.AutoGenerator; import com.baomidou.mybatisplus.generator.InjectionConfig; import com.baomidou.mybatisplus.generator.config.DataSourceConfig; import com.baomidou.mybatisplus.generator.config.FileOutConfig; import com.baomidou.mybatisplus.generator.config.GlobalConfig; import com.baomidou.mybatisplus.generator.config.PackageConfig; import com.baomidou.mybatisplus.generator.config.StrategyConfig; import com.baomidou.mybatisplus.generator.config.TemplateConfig; import com.baomidou.mybatisplus.generator.config.po.TableInfo; import com.baomidou.mybatisplus.generator.config.rules.DateType; import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy; import com.baomidou.mybatisplus.generator.engine.FreemarkerTemplateEngine; import java.util.ArrayList; import java.util.List; public class CodeGenerator { private static final String BASE_PACKAGE = "org.fool.shardingtablerw"; private static final String DB_URL = "jdbc:mysql://localhost:3306/test_master?useUnicode=true&useSSL=false&characterEncoding=utf8"; private static final String[] INCLUDE_TABLES = {"tb_user"}; public static void main(String[] args) { // 代码生成器 AutoGenerator mpg = new AutoGenerator(); // 全局配置 GlobalConfig gc = new GlobalConfig(); String projectPath = System.getProperty("user.dir"); gc.setOutputDir(projectPath + "/src/main/java"); gc.setAuthor("admin"); gc.setOpen(false); gc.setFileOverride(true); gc.setIdType(IdType.AUTO); gc.setDateType(DateType.ONLY_DATE); mpg.setGlobalConfig(gc); // 数据源配置 DataSourceConfig dsc = new DataSourceConfig(); dsc.setUrl(DB_URL); dsc.setDriverName("com.mysql.cj.jdbc.Driver"); dsc.setUsername("root"); dsc.setPassword("123456"); dsc.setDbType(DbType.MYSQL); mpg.setDataSource(dsc); // 包配置 PackageConfig pc = new PackageConfig(); pc.setParent(BASE_PACKAGE); pc.setEntity("entity"); pc.setMapper("mapper"); mpg.setPackageInfo(pc); // 自定义配置 InjectionConfig cfg = new InjectionConfig() { @Override public void initMap() { // to do nothing } }; // 如果模板引擎是 freemarker String templatePath = "/templates/mapper.xml.ftl"; // 自定义输出配置 List<FileOutConfig> focList = new ArrayList<>(); // 自定义配置会被优先输出 focList.add(new FileOutConfig(templatePath) { @Override public String outputFile(TableInfo tableInfo) { // 自定义输出文件名 ， 如果你 Entity 设置了前后缀、此处注意 xml 的名称会跟着发生变化！！ return projectPath + "/src/main/resources/mapper/" + pc.getModuleName() + "/" + tableInfo.getEntityName() + "Mapper" + StringPool.DOT_XML; } }); /* cfg.setFileCreate(new IFileCreate() { @Override public boolean isCreate(ConfigBuilder configBuilder, FileType fileType, String filePath) { // 判断自定义文件夹是否需要创建 checkDir("调用默认方法创建的目录，自定义目录用"); if (fileType == FileType.MAPPER) { // 已经生成 mapper 文件判断存在，不想重新生成返回 false return !new File(filePath).exists(); } // 允许生成模板文件 return true; } }); */ cfg.setFileOutConfigList(focList); mpg.setCfg(cfg); // 配置模板 TemplateConfig templateConfig = new TemplateConfig(); templateConfig.setService(null); templateConfig.setServiceImpl(null); templateConfig.setController(null); templateConfig.setXml(null); mpg.setTemplate(templateConfig); // 策略配置 StrategyConfig strategy = new StrategyConfig(); strategy.setNaming(NamingStrategy.underline_to_camel); strategy.setColumnNaming(NamingStrategy.underline_to_camel); strategy.setEntityLombokModel(true); strategy.setEntitySerialVersionUID(false); strategy.setInclude(INCLUDE_TABLES); strategy.setTablePrefix("tb_"); mpg.setStrategy(strategy); mpg.setTemplateEngine(new FreemarkerTemplateEngine()); mpg.execute(); } }
Note: User.java、UserMapper.java、UserMapper.xml 运行 CodeGenerator 的 main 方法后自动生成。
SRC
Application.java
package org.fool.shardingtablerw; import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication @MapperScan("org.fool.shardingtablerw.mapper") public class Application { public static void main(String[] args) { SpringApplication.run(Application.class, args); } }
UserService.java
package org.fool.shardingtablerw.service; import com.baomidou.mybatisplus.core.toolkit.Wrappers; import lombok.extern.slf4j.Slf4j; import org.fool.shardingtablerw.entity.User; import org.fool.shardingtablerw.mapper.UserMapper; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import java.util.List; @Service @Slf4j public class UserService { @Autowired private UserMapper userMapper; @Transactional(rollbackFor = Exception.class) public int addUser(User user) { user.setCreateBy("admin"); user.setUpdateBy("admin"); log.info("user: {}", user); return userMapper.insert(user); } public List<User> getAllUsers() { return userMapper.selectList(Wrappers.lambdaQuery()); } }
UserController.java
package org.fool.shardingtablerw.controller; import lombok.extern.slf4j.Slf4j; import org.fool.shardingtablerw.entity.User; import org.fool.shardingtablerw.service.UserService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RestController; import java.util.List; import java.util.Random; @RestController @Slf4j public class UserController { @Autowired private UserService userService; @GetMapping("/get") public List<User> get() { return userService.getAllUsers(); } @PostMapping("/add/{count}") public String add(@PathVariable int count) { int result = 0; for (int i = 0; i < count; i++) { User user = new User(); user.setUsername("username" + i); user.setPassword("password" + i); user.setAge(new Random().nextInt(100)); result += userService.addUser(user); } return result == count ? "SUCCESS" : "FAILURE"; } }
Test
add
curl --location --request POST 'http://localhost:8080/add/100'
Note: add to master
get
http://localhost:8080/get
Note：逻辑 SQL 是一条，实际上拆分成了 3 条 SQL 分别去查询 test_slave 数据库中的 3 张表
Reference
欢迎点赞关注和收藏
