理论参考：{{MySQL 高级框架实战理论笔记}}

GitHub 代码：lane-mysql-42

项目创建与初步实现

创建父项目 mysql-example 只是为了管理依赖

创建 module sharding-jdbc-example

SQL 语句

分别创建两个 db 为 lane1 和 lane2 ，分别执行下面的 sql

  2. CREATE TABLE `position` (
  3.   `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  4.   `name` varchar(256) DEFAULT NULL,
  5.   `salary` varchar(50) DEFAULT NULL,
  6.   `city` varchar(256) DEFAULT NULL,
  7.   PRIMARY KEY (`Id`)
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  11. CREATE TABLE `position_detail` (
  12.   `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  13.   `pid` bigint(11) NOT NULL DEFAULT '0',
  14.   `description` text DEFAULT NULL,
  15.   PRIMARY KEY (`Id`)
  16. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

引入依赖

父模块

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0"
  3.          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4.          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  5.     <modelVersion>4.0.0</modelVersion>
  7.     <groupId>com.galaxy</groupId>
  8.     <artifactId>mysql-example</artifactId>
  9.     <packaging>pom</packaging>
  10.     <version>1.0-SNAPSHOT</version>
  11.     <modules>
  12.         <module>sharding-jdbc-example</module>
  13.     </modules>
  14.     <properties>
  15.         <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  16.         <project.compile.sourceEncoding>UTF-8</project.compile.sourceEncoding>
  17.         <shardingsphere.version>4.1.0</shardingsphere.version>
  18.         <springboot.version>2.2.9.RELEASE</springboot.version>
  20.     </properties>
  21.     <dependencyManagement>
  22.     <dependencies>
  23.         <dependency>
  24.             <groupId>org.springframework.boot</groupId>
  25.             <artifactId>spring-boot-starter-jdbc</artifactId>
  26.             <version>${springboot.version}</version>
  27.         </dependency>
  28.         <dependency>
  29.             <groupId>org.springframework.boot</groupId>
  30.             <artifactId>spring-boot-starter-data-jpa</artifactId>
  31.             <version>${springboot.version}</version>
  32.         </dependency>
  33.         <dependency>
  34.             <groupId>org.springframework.boot</groupId>
  35.             <artifactId>spring-boot-starter-data-jpa</artifactId>
  36.             <version>2.1.6.RELEASE</version>
  37.         </dependency>
  38.         <dependency>
  39.             <groupId>org.springframework.boot</groupId>
  40.             <artifactId>spring-boot-starter-test</artifactId>
  41.             <version>${springboot.version}</version>
  42.             <scope>test</scope>
  43.         </dependency>
  44.         <dependency>
  45.             <groupId>mysql</groupId>
  46.             <artifactId>mysql-connector-java</artifactId>
  47.             <version>5.1.48</version>
  48.         </dependency>
  50.         <dependency>
  51.             <groupId>org.apache.shardingsphere</groupId>
  52.             <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
  53.             <version>${shardingsphere.version}</version>
  54.         </dependency>
  55.     </dependencies>
  56.     </dependencyManagement>
  57.     <build>
  58.             <plugins>
  59.                 <plugin>
  60.                     <groupId>org.apache.maven.plugins</groupId>
  61.                     <artifactId>maven-compiler-plugin</artifactId>
  62.                     <version>3.8.1</version>
  63.                     <configuration>
  64.                         <source>11</source>
  65.                         <target>11</target>
  66.                         <testSource>11</testSource>
  67.                         <testTarget>11</testTarget>
  68.                     </configuration>
  69.                 </plugin>
  70.             </plugins>
  71.     </build>
  72. </project>

子模块

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0"
  3.          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4.          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  5.     <parent>
  6.         <artifactId>mysql-example</artifactId>
  7.         <groupId>com.galaxy</groupId>
  8.         <version>1.0-SNAPSHOT</version>
  9.     </parent>
  10.     <modelVersion>4.0.0</modelVersion>
  12.     <artifactId>sharding-jdbc-example</artifactId>
  13.     <dependencies>
  14.         <dependency>
  15.             <groupId>org.springframework.boot</groupId>
  16.             <artifactId>spring-boot-starter-jdbc</artifactId>
  18.         </dependency>
  19.         <dependency>
  20.             <groupId>org.springframework.boot</groupId>
  21.             <artifactId>spring-boot-starter-data-jpa</artifactId>
  22.         </dependency>
  23.         <dependency>
  24.             <groupId>org.springframework.boot</groupId>
  25.             <artifactId>spring-boot-starter-test</artifactId>
  26.             <scope>test</scope>
  27.         </dependency>
  28.         <dependency>
  29.             <groupId>mysql</groupId>
  30.             <artifactId>mysql-connector-java</artifactId>
  31.         </dependency>
  33.         <dependency>
  34.             <groupId>org.apache.shardingsphere</groupId>
  35.             <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
  36.         </dependency>
  37.     </dependencies>
  39. </project>

配置文件

application.properties

  1. spring.profiles.active=sharding-database
  2. spring.shardingsphere.props.sql.show=true

application-sharding-database.properties

  1. #datasource
  2. spring.shardingsphere.datasource.names=ds0,ds1
  4. spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
  5. spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
  6. spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/lane1?useUnicode=true&characterEncoding=utf8
  7. spring.shardingsphere.datasource.ds0.username=root
  8. spring.shardingsphere.datasource.ds0.password=root
  10. spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
  11. spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
  12. spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/lane2?useUnicode=true&characterEncoding=utf8
  13. spring.shardingsphere.datasource.ds1.username=root
  14. spring.shardingsphere.datasource.ds1.password=root
  16. #sharding-database
  17. #分片策略inline 分片键id 表达式id/2=ds0、ds1
  18. spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
  19. spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=ds$->{id % 2}
  20. #id 生成策略
  21. spring.shardingsphere.sharding.tables.position.key-generator.column=id
  22. spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE

Java 代码

entity

  1. package com.galaxy.entity;
  3. import javax.persistence.*;
  4. import java.io.Serializable;
  6. /**
  7.  * @author lane
  8.  * @date 2021年07月20日 下午7:12
  9.  */
  11. @Entity
  12. @Table(name="position")
  13. public class Position implements Serializable {
  15.     @Id
  16.     @Column(name = "id")
  17.  //使用sharding jdbc指定主键生成，不指定则是数据库默认auto_increment
  18.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  19.     private long id;
  21.     @Column(name = "name")
  22.     private String name;
  24.     @Column(name = "salary")
  25.     private String salary;
  27.     @Column(name = "city")
  28.     private String city;
  30.     public long getId() {
  31.         return id;
  32.     }
  34.     public void setId(long id) {
  35.         this.id = id;
  36.     }
  38.     public String getName() {
  39.         return name;
  40.     }
  42.     public void setName(String name) {
  43.         this.name = name;
  44.     }
  46.     public String getSalary() {
  47.         return salary;
  48.     }
  50.     public void setSalary(String salary) {
  51.         this.salary = salary;
  52.     }
  54.     public String getCity() {
  55.         return city;
  56.     }
  58.     public void setCity(String city) {
  59.         this.city = city;
  60.     }
  61. }

repository

  1. package com.galaxy.repository;
  3. import com.galaxy.entity.Position;
  4. import org.springframework.data.jpa.repository.JpaRepository;
  5. import org.springframework.data.jpa.repository.Query;
  6. import org.springframework.data.repository.query.Param;
  8. /**
  9.  * @author lane
  10.  * @date 2021年07月20日 下午7:36
  11.  */
  12. public interface PositionRepository  extends JpaRepository<Position,Long> {
  15. }

启动类

  1. package com.galaxy;
  3. import org.springframework.boot.SpringApplication;
  4. import org.springframework.boot.autoconfigure.SpringBootApplication;
  6. /**
  7.  * @author lane
  8.  * @date 2021年07月20日 下午7:31
  9.  */
  10. @SpringBootApplication
  11. public class RunBootApplication {
  13.     /*public static void main(String[] args) {
  14.         SpringApplication.run(RunBootApplication.class,args);
  15.     }*/
  16. }

测试类

  1. package dao;
  3. import com.galaxy.RunBootApplication;
  4. import com.galaxy.entity.Position;
  5. import com.galaxy.repository.PositionRepository;
  6. import org.junit.Test;
  7. import org.junit.runner.RunWith;
  8. import org.springframework.beans.factory.annotation.Autowired;
  9. import org.springframework.boot.test.context.SpringBootTest;
  10. import org.springframework.test.context.junit4.SpringRunner;
  12. /**
  13.  * @author lane
  14.  * @date 2021年07月20日 下午7:31
  15.  */
  16. @RunWith(SpringRunner.class)
  17. @SpringBootTest(classes = RunBootApplication.class)
  18. public class TestShardingDatabase {
  19.     @Autowired
  20.     private PositionRepository positionRepository;
  22.     @Test
  23.     public void test0(){
  24.         for (int i=1;i<20;i++){
  25.             Position position =new Position();
  26.          //   position.setId(i);
  27.             position.setCity("beijing");
  28.             position.setName("zhangsan"+i);
  29.             position.setSalary("20000");
  31.             positionRepository.save(position);
  32.         }
  36.     }
  38. }

测试结果

Sharding JDBC 实战 - 图1

自定义主键生成策略

添加 Java 代码实现类

  1. package com.galaxy.id;
  2. import org.apache.shardingsphere.core.strategy.keygen.SnowflakeShardingKeyGenerator;
  3. import org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator;
  4. import java.util.Properties;
  6. /**
  7.  * 仍然选择雪花片算法
  8.  * @author lane
  9.  * @date 2021年07月21日 上午10:41
  10.  */
  12. public class MyLaneId implements ShardingKeyGenerator {
  14.     private  SnowflakeShardingKeyGenerator snow = new SnowflakeShardingKeyGenerator();
  16.     @Override
  17.     public Comparable<?> generateKey() {
  18.         System.out.println("------执行了自定义主键生成器-------");
  19.         return snow.generateKey();
  20.     }
  22.     @Override
  23.     public String getType() {
  24.         return "LANEKEY";
  25.     }
  27.     @Override
  28.     public Properties getProperties() {
  29.         return null;
  30.     }
  32.     @Override
  33.     public void setProperties(Properties properties) {
  35.     }
  36. }

添加实现的 spi 内容

在 resources 下 新建 META-INF 文件夹及其子文件夹 service，之后添加文件名称为 org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator

内容为 com.galaxy.id.MyLaneId 的文件

Sharding JDBC 实战 - 图2

添加配置信息

  1. #id
  2. spring.shardingsphere.sharding.tables.position.key-generator.column=id
  3. #spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
  4. spring.shardingsphere.sharding.tables.position.key-generator.type=LANEKEY

执行结果

Sharding JDBC 实战 - 图3

对应绑定表同库生成

对于 position 表和 position_detail 表有绑定关系，我们希望的是 db1 生成的 position 和 positionDetail 实现一一对应关系而不是出现 db1 的 position 和 db2 的 positionDetail 出现对应关系，这样不利于我们进行关联查询，下面是具体实现同库关联表一一绑定生成

Java 代码

positionDetail

  1. package com.galaxy.entity;
  3. import javax.persistence.*;
  4. import java.io.Serializable;
  6. /**
  7.  * @author lane
  8.  * @date 2021年07月21日 上午11:12
  9.  */
  10. @Entity
  11. @Table(name = "position_detail")
  12. public class PositionDetail implements Serializable {
  14.     @Id
  15.     @Column(name = "id")
  16.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  17.     private long id;
  19.     @Column(name = "pid")
  20.     private long pid;
  22.     @Column(name = "description")
  23.     private String description;
  25.     public long getId() {
  26.         return id;
  27.     }
  29.     public void setId(long id) {
  30.         this.id = id;
  31.     }
  33.     public long getPid() {
  34.         return pid;
  35.     }
  37.     public void setPid(long pid) {
  38.         this.pid = pid;
  39.     }
  41.     public String getDescription() {
  42.         return description;
  43.     }
  45.     public void setDescription(String description) {
  46.         this.description = description;
  47.     }
  48. }

repository

  1. package com.galaxy.repository;
  3. import com.galaxy.entity.PositionDetail;
  4. import org.springframework.data.jpa.repository.JpaRepository;
  6. /**
  7.  * @author lane
  8.  * @date 2021年07月21日 上午11:13
  9.  */
  10. public interface PositionDetailRepository extends JpaRepository<PositionDetail,Long> {
  11. }
  1. package com.galaxy.repository;
  3. import com.galaxy.entity.Position;
  4. import org.springframework.data.jpa.repository.JpaRepository;
  5. import org.springframework.data.jpa.repository.Query;
  6. import org.springframework.data.repository.query.Param;
  8. /**
  9.  * @author lane
  10.  * @date 2021年07月20日 下午7:36
  11.  */
  12. public interface PositionRepository  extends JpaRepository<Position,Long> {
  14.     @Query(nativeQuery = true,value = "" +
  15.             "select p.id,p.name,p.salary,p.city,pd.description from position p join position_detail pd on(p.id=pd.pid) where p.id=:id")
  16.     public Object findPositionsById(@Param("id") long id);
  18. }

配置信息

  2. #sharding-database
  3. #分片策略inline 分片键id 表达式id/2=ds0、ds1
  4. spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
  5. spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=ds$->{id % 2}
  7. spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.sharding-column=id
  8. spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.algorithm-expression=ds$->{id % 2}
  10. #id
  11. spring.shardingsphere.sharding.tables.position.key-generator.column=id
  12. spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
  13. #spring.shardingsphere.sharding.tables.position.key-generator.type=LANEKEY
  15. spring.shardingsphere.sharding.tables.position_detail.key-generator.column=id
  16. spring.shardingsphere.sharding.tables.position_detail.key-generator.type=SNOWFLAKE

测试代码

  1.   @Test
  2.     public void testAdd2(){
  3.         for (int i=1;i<=20;i++){
  4.             Position position = new Position();
  5.             position.setName("lisi"+i);
  6.             position.setSalary("1000000");
  7.             position.setCity("shanghai");
  8.             positionRepository.save(position);
  10.             PositionDetail positionDetail = new PositionDetail();
  11.             positionDetail.setPid(position.getId());
  12.             positionDetail.setDescription("this is a message "+i);
  13.             positionDetailRepository.save(positionDetail);
  14.         }
  15.     }
  17.     @Test
  18.     public void testLoad(){
  19.         Object object = positionRepository.findPositionsById(624565543113850880L);
  20.         Object[] position = (Object[])object;
  21.         System.out.println(position[0]+" "+position[1]+" "+position[2]+" "+position[3]+" "+position[4]);
  22.     }

测试结果

Sharding JDBC 实战 - 图4

Sharding JDBC 实战 - 图5

广播表实现

广播表是要求每个库里面的表结构和数据完全一样，一般是配置信息或者固定信息，每次操作都对每个库中的表做同样的操作

SQL 建表语句

  1. CREATE TABLE `city` (
  2.   `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(256) DEFAULT NULL,
  4.   `province` varchar(256) DEFAULT NULL,
  5.   PRIMARY KEY (`Id`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Java 代码

city

  1. package com.galaxy.entity;
  3. import javax.persistence.*;
  4. import java.io.Serializable;
  6. /**
  7.  * @author lane
  8.  * @date 2021年07月21日 上午11:37
  9.  */
  10. @Entity
  11. @Table(name = "city")
  12. public class City implements Serializable {
  14.     @Id
  15.     @Column(name = "id")
  16.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  17.     private long id;
  19.     @Column(name = "name")
  20.     private String name;
  22.     @Column(name = "province")
  23.     private String province;
  25.     public long getId() {
  26.         return id;
  27.     }
  29.     public void setId(long id) {
  30.         this.id = id;
  31.     }
  33.     public String getName() {
  34.         return name;
  35.     }
  37.     public void setName(String name) {
  38.         this.name = name;
  39.     }
  41.     public String getProvince() {
  42.         return province;
  43.     }
  45.     public void setProvince(String province) {
  46.         this.province = province;
  47.     }
  48. }

repository

  1. package com.galaxy.repository;
  3. import com.galaxy.entity.City;
  4. import com.galaxy.entity.PositionDetail;
  5. import org.springframework.data.jpa.repository.JpaRepository;
  7. /**
  8.  * @author lane
  9.  * @date 2021年07月21日 上午11:13
  10.  */
  11. public interface CityRepository extends JpaRepository<City,Long> {
  12. }

test

  1.   @Test
  2.     public void testBroadCast(){
  3.         City city = new City();
  4.         city.setName("beijing");
  5.         city.setProvince("beijing");
  6.         cityRepository.save(city);
  7.     }

配置信息

  1. #BoardCast
  2. spring.shardingsphere.sharding.broadcast-tables=city
  3. spring.shardingsphere.sharding.tables.city.key-generator.column=id
  4. spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE

执行结果

Sharding JDBC 实战 - 图6

分库分表实现

对于公司来说会有一个订单表 BOrder，将其以公司 ID 进行分库，以 order ID 进行分表实现数据均匀存储

SQL 创建

在库 lane1 创建表 b_order0 和表 b_order1

在库 lane2 创建表 b_order0 和表 b_order1

  2. CREATE TABLE `b_order`(
  3.     `id`              bigint(20)   NOT NULL AUTO_INCREMENT,
  4.     `is_del`          bit(1)       NOT NULL DEFAULT 0 COMMENT '是否被删除',
  5.     `company_id`      int(11)      NOT NULL COMMENT '公司ID',
  6.     `position_id`     bigint(11)      NOT NULL COMMENT '职位ID',
  7.     `user_id`         int(11)      NOT NULL COMMENT '用户id',
  8.     `publish_user_id` int(11)      NOT NULL COMMENT '职位发布者id',
  9.     `resume_type`     int(2)       NOT NULL DEFAULT 0 COMMENT '简历类型：0 附件 1 在线',
  10.     `status`          varchar(256) NOT NULL COMMENT '投递状态 投递状态 WAIT-待处理 AUTO_FILTER-自动过滤 PREPARE_CONTACT-待沟通 REFUSE-拒绝 ARRANGE_INTERVIEW-通知面试',
  11.     `create_time`     datetime     NOT NULL COMMENT '创建时间',
  12.     `operate_time`    datetime     NOT NULL COMMENT '操作时间',
  13.     `work_year`       varchar(100)          DEFAULT NULL COMMENT '工作年限',
  14.     `name`            varchar(256)          DEFAULT NULL COMMENT '投递简历人名字',
  15.     `position_name`   varchar(256)          DEFAULT NULL COMMENT '职位名称',
  16.     `resume_id`        int(10)               DEFAULT NULL COMMENT '投递的简历id（在线和附件都记录，通过resumeType进行区别在线还是附件）',
  17.     PRIMARY KEY (`id`),
  18.     KEY `index_createTime` (`create_time`),
  19.     KEY `index_companyId_status` (`company_id`, `status`(255), `is_del`),
  20.     KEY `i_comId_pub_ctime` (`company_id`, `publish_user_id`, `create_time`),
  21.     KEY `index_companyId_positionId` (`company_id`, `position_id`) USING BTREE
  22. ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

Java 实现

BOrder

  1. package com.galaxy.entity;
  2. import javax.persistence.*;
  3. import java.io.Serializable;
  4. import java.util.Date;
  5. /**
  6.  * @author lane
  7.  * @date 2021年07月21日 下午12:12
  8.  */
  10. @Entity
  11. //注意是逻辑表名
  12. @Table(name = "b_order")
  13. public class BOrder implements Serializable {
  15.     @Id
  16.     @Column(name = "id")
  17.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  18.     private long id;
  20.     @Column(name = "is_del")
  21.     private Boolean isDel;
  23.     @Column(name = "company_id")
  24.     private Integer companyId;
  26.     @Column(name = "position_id")
  27.     private long positionId;
  29.     @Column(name = "user_id")
  30.     private Integer userId;
  32.     @Column(name = "publish_user_id")
  33.     private Integer publishUserId;
  35.     @Column(name = "resume_type")
  36.     private Integer resumeType;
  38.     @Column(name = "status")
  39.     private String status;
  41.     @Column(name = "create_time")
  42.     private Date createTime;
  44.     @Column(name = "operate_time")
  45.     private Date operateTime;
  47.     @Column(name = "work_year")
  48.     private String workYear;
  50.     @Column(name = "name")
  51.     private String name;
  53.     @Column(name = "position_name")
  54.     private String positionName;
  56.     @Column(name = "resume_id")
  57.     private Integer resumeId;
  59.     public long getId() {
  60.         return id;
  61.     }
  63.     public void setId(long id) {
  64.         this.id = id;
  65.     }
  67.     public Boolean getDel() {
  68.         return isDel;
  69.     }
  71.     public void setDel(Boolean del) {
  72.         isDel = del;
  73.     }
  75.     public Integer getCompanyId() {
  76.         return companyId;
  77.     }
  79.     public void setCompanyId(Integer companyId) {
  80.         this.companyId = companyId;
  81.     }
  83.     public long getPositionId() {
  84.         return positionId;
  85.     }
  87.     public void setPositionId(long positionId) {
  88.         this.positionId = positionId;
  89.     }
  91.     public Integer getUserId() {
  92.         return userId;
  93.     }
  95.     public void setUserId(Integer userId) {
  96.         this.userId = userId;
  97.     }
  99.     public Integer getPublishUserId() {
  100.         return publishUserId;
  101.     }
  103.     public void setPublishUserId(Integer publishUserId) {
  104.         this.publishUserId = publishUserId;
  105.     }
  107.     public Integer getResumeType() {
  108.         return resumeType;
  109.     }
  111.     public void setResumeType(Integer resumeType) {
  112.         this.resumeType = resumeType;
  113.     }
  115.     public String getStatus() {
  116.         return status;
  117.     }
  119.     public void setStatus(String status) {
  120.         this.status = status;
  121.     }
  123.     public Date getCreateTime() {
  124.         return createTime;
  125.     }
  127.     public void setCreateTime(Date createTime) {
  128.         this.createTime = createTime;
  129.     }
  131.     public Date getOperateTime() {
  132.         return operateTime;
  133.     }
  135.     public void setOperateTime(Date operateTime) {
  136.         this.operateTime = operateTime;
  137.     }
  139.     public String getWorkYear() {
  140.         return workYear;
  141.     }
  143.     public void setWorkYear(String workYear) {
  144.         this.workYear = workYear;
  145.     }
  147.     public String getName() {
  148.         return name;
  149.     }
  151.     public void setName(String name) {
  152.         this.name = name;
  153.     }
  155.     public String getPositionName() {
  156.         return positionName;
  157.     }
  159.     public void setPositionName(String positionName) {
  160.         this.positionName = positionName;
  161.     }
  163.     public Integer getResumeId() {
  164.         return resumeId;
  165.     }
  167.     public void setResumeId(Integer resumeId) {
  168.         this.resumeId = resumeId;
  169.     }
  170. }

BOrderRepository

  1. package com.galaxy.repository;
  3. import com.galaxy.entity.BOrder;
  4. import org.springframework.data.jpa.repository.JpaRepository;
  6. /**
  7.  * @author lane
  8.  * @date 2021年07月21日 下午12:12
  9.  */
  10. public interface BOrderRepository extends JpaRepository<BOrder,Long> {
  11. }

配置信息

  2. #sharding-database-table
  3. #spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.sharding-column=company_id
  4. #spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.algorithm-expression=ds$->{company_id % 2}
  5. #spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.sharding-column=id
  6. #spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.algorithm-expression=b_order${id % 2}
  7. #spring.shardingsphere.sharding.tables.b_order.actual-data-nodes=ds${0..1}.b_order${0..1}
  8. #spring.shardingsphere.sharding.tables.b_order.key-generator.column=id
  9. #spring.shardingsphere.sharding.tables.b_order.key-generator.type=SNOWFLAKE

测试代码

  1.    @Autowired
  2.     BOrderRepository orderRepository;
  3.     @Test
  4.     @Repeat(100)
  5.     public void testShardingBOrder(){
  6.         Random random = new Random();
  7.         int companyId = random.nextInt(100);
  8.         BOrder order = new BOrder();
  9.         order.setDel(false);
  10.         order.setCompanyId(companyId);
  11.         order.setPositionId(3242342);
  12.         order.setUserId(2222);
  13.         order.setPublishUserId(1111);
  14.         order.setResumeType(1);
  15.         order.setStatus("AUTO");
  16.         order.setCreateTime(new Date());
  17.         order.setOperateTime(new Date());
  18.         order.setWorkYear("2");
  19.         order.setName("lg");
  20.         order.setPositionName("Java");
  21.         order.setResumeId(23233);
  22.         orderRepository.save(order);
  23.     }

测试结果

不同数据库按照公司 id 进行分库，可以看到 lane1(ds0)库的公司 ID 全是 偶数，lane2(ds1)库的公司 ID 全是奇数

Sharding JDBC 实战 - 图7

对于同一个库拆分的不同表数据则是按照 ID 进行分片，可以看到下图中 b_order0 的 ID 全是偶数，而 b_order1 的 ID 全是奇数

Sharding JDBC 实战 - 图8

读写分离实现

正常读写分离一般是一台主库负责数据的写入，两台从库负责数据的读取，足以满足多数情况下了

当然了 shardingJDBC 无法实现数据的同步，仍要自己去配置数据库的同步，可以参考文章{{MySQL 安装及主从异步、半同步、同步实现}}

这次按照 city 表进行操作。lane1 库作为主库写入数据，lane2 库作为从库实现数据的读取

配置信息

application.properties

  1. spring.profiles.active=master-slave
  2. spring.shardingsphere.props.sql.show=true

application-master-slave.properties

  1. #datasource
  2. spring.shardingsphere.datasource.names=master,slave0
  4. spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
  5. spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
  6. spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://localhost:3306/lane1
  7. spring.shardingsphere.datasource.master.username=root
  8. spring.shardingsphere.datasource.master.password=root
  10. spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
  11. spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
  12. spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://localhost:3306/lane2
  13. spring.shardingsphere.datasource.slave0.username=root
  14. spring.shardingsphere.datasource.slave0.password=root
  16. #master-slave
  17. spring.shardingsphere.masterslave.name=datasource
  18. spring.shardingsphere.masterslave.master-data-source-name=master
  19. spring.shardingsphere.masterslave.slave-data-source-names=slave0
  20. spring.shardingsphere.masterslave.load-balance-algorithm-type=ROUND_ROBIN
  22. spring.shardingsphere.sharding.tables.city.key-generator.column=id
  23. spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE

测试代码

  1. package dao;
  3. import com.galaxy.RunBootApplication;
  4. import com.galaxy.entity.City;
  5. import com.galaxy.repository.CityRepository;
  6. import org.junit.Test;
  7. import org.junit.runner.RunWith;
  8. import org.springframework.beans.factory.annotation.Autowired;
  9. import org.springframework.boot.test.context.SpringBootTest;
  10. import org.springframework.test.context.junit4.SpringRunner;
  12. import java.util.List;
  14. /**
  15.  * @author lane
  16.  * @date 2021年07月21日 下午3:35
  17.  */
  18. @RunWith(SpringRunner.class)
  19. @SpringBootTest(classes = RunBootApplication.class)
  20. public class TestMasterSlave {
  22.     @Autowired
  23.    private  CityRepository cityRepository;
  25.     @Test
  26.     public void testAdd(){
  27.         City city = new City();
  28.         city.setName("shanghai1");
  29.         city.setProvince("shanghai1");
  30.         cityRepository.save(city);
  31.     }
  33.     @Test
  34.     public void testFind(){
  35.         List<City> list = cityRepository.findAll();
  36.         list.forEach(city->{
  37.             System.out.println(city.getId()+" "+city.getName()+" "+city.getProvince());
  38.         });
  39.     }
  41. }

测试结果

执行添加之后，只在主库添加了一条数据

Sharding JDBC 实战 - 图9

执行查询则是只在从库查询到了一条数据，成功实现了读写分离

Sharding JDBC 实战 - 图10

代码指定路由 Hint 实现

仍然采用 city 表来实现，在代码中指定路由到具体某一个数据库

Java 代码

  1. package com.galaxy.hint;
  3. import org.apache.shardingsphere.api.sharding.hint.HintShardingAlgorithm;
  4. import org.apache.shardingsphere.api.sharding.hint.HintShardingValue;
  6. import java.util.ArrayList;
  7. import java.util.Collection;
  9. /**
  10.  * @author lane
  11.  * @date 2021年07月21日 下午4:50
  12.  */
  13. public class MyHintShardingAlgorithm implements HintShardingAlgorithm<Long> {
  14.     @Override
  15.     public Collection<String> doSharding(
  16.             Collection<String> availableTargetNames,
  17.             HintShardingValue<Long> shardingValue) {
  18.         Collection<String> result = new ArrayList<>();
  19.         //each是数据源ds0和ds1
  20.         for (String each : availableTargetNames){
  21.             for (Long value : shardingValue.getValues()){
  22.                 //value是自己传入的值，根据传入的值来匹配数据源
  23.                 if(each.endsWith(String.valueOf(value % 2))){
  24.                     result.add(each);
  25.                 }
  26.             }
  27.         }
  28.         return result;
  29.     }
  30. }

配置文件

application.properties

  2. spring.profiles.active=hint-database
  3. spring.shardingsphere.props.sql.show=true

application-hint-database.properties

  1. #datasource
  2. spring.shardingsphere.datasource.names=ds0,ds1
  4. spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
  5. spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
  6. spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/lane1?useUnicode=true&characterEncoding=utf8
  7. spring.shardingsphere.datasource.ds0.username=root
  8. spring.shardingsphere.datasource.ds0.password=root
  10. spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
  11. spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
  12. spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/lane2?useUnicode=true&characterEncoding=utf8
  13. spring.shardingsphere.datasource.ds1.username=root
  14. spring.shardingsphere.datasource.ds1.password=root
  15. #hint
  16. spring.shardingsphere.sharding.tables.city.database-strategy.hint.algorithm-class-name=com.galaxy.hint.MyHintShardingAlgorithm

测试代码

  1. package dao;
  3. import com.galaxy.RunBootApplication;
  4. import com.galaxy.entity.City;
  5. import com.galaxy.repository.CityRepository;
  6. import org.apache.shardingsphere.api.hint.HintManager;
  7. import org.junit.Test;
  8. import org.junit.runner.RunWith;
  9. import org.springframework.boot.test.context.SpringBootTest;
  10. import org.springframework.test.context.junit4.SpringRunner;
  12. import javax.annotation.Resource;
  13. import java.util.List;
  15. /**
  16.  * @author lane
  17.  * @date 2021年07月21日 下午4:52
  18.  */
  20. @RunWith(SpringRunner.class)
  21. @SpringBootTest(classes = RunBootApplication.class)
  22. public class TestHintAlgorithm {
  24.     @Resource
  25.     private CityRepository cityRepository;
  27.     @Test
  28.     public void test1(){
  29.         HintManager hintManager = HintManager.getInstance();
  30.         //强制路由到ds${xx%2}既是 ds2%2 = ds0
  31.         hintManager.setDatabaseShardingValue(2L);
  32.         List<City> list = cityRepository.findAll();
  33.         list.forEach(city->{
  34.             System.out.println(city.getId()+" "+city.getName()+" "+city.getProvince());
  35.         });
  36.     }
  38. }

测试结果

查询的是 ds0 也就是 lane1 库里的 City 数据两条

Sharding JDBC 实战 - 图11

数据脱敏(加解密)实现

通过根据逻辑列在数据库中添加明文列（非必须）和密文列（必须）实现数据加密存储和数据解密查询效果

新建用户表实现密码的脱敏

创建 SQL

  1. CREATE TABLE `c_user` (
  2.   `Id` bigint(11) NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(256) DEFAULT NULL,
  4.   `pwd_plain` varchar(256) DEFAULT NULL,
  5.   `pwd_cipher` varchar(256) DEFAULT NULL,
  6.   PRIMARY KEY (`Id`)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Java 代码实现

CUser

  1. package com.galaxy.entity;
  3. import javax.persistence.*;
  4. import java.io.Serializable;
  6. /**
  7.  * @author lane
  8.  * @date 2021年07月21日 下午6:33
  9.  */
  10. @Entity
  11. @Table(name = "c_user")
  12. public class CUser implements Serializable {
  14.     @Id
  15.     @Column(name = "id")
  16.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  17.     private long id;
  19.     @Column(name = "name")
  20.     private String name;
  22.     @Column(name = "pwd")//逻辑列名
  23.     private String pwd;
  25.     public long getId() {
  26.         return id;
  27.     }
  29.     public void setId(long id) {
  30.         this.id = id;
  31.     }
  33.     public String getName() {
  34.         return name;
  35.     }
  37.     public void setName(String name) {
  38.         this.name = name;
  39.     }
  41.     public String getPwd() {
  42.         return pwd;
  43.     }
  45.     public void setPwd(String pwd) {
  46.         this.pwd = pwd;
  47.     }
  48. }

Repository

  1. package com.galaxy.repository;
  3. import com.galaxy.entity.CUser;
  4. import org.springframework.data.jpa.repository.JpaRepository;
  6. import java.util.List;
  8. /**
  9.  * @author lane
  10.  * @date 2021年07月21日 下午6:35
  11.  */
  12. public interface CUserRepository extends JpaRepository<CUser,Long> {
  14.     List<CUser> findByPwd(String pwd);
  15. }

配置文件

application.properties

  2. spring.profiles.active=encryptor
  3. spring.shardingsphere.props.sql.show=true

application-encryptor.properties

  1. #datasource
  2. spring.shardingsphere.datasource.names=ds0,ds1
  4. spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
  5. spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
  6. spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/lane1?useUnicode=true&characterEncoding=utf8
  7. spring.shardingsphere.datasource.ds0.username=root
  8. spring.shardingsphere.datasource.ds0.password=root
  10. spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
  11. spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
  12. spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/lane2?useUnicode=true&characterEncoding=utf8
  13. spring.shardingsphere.datasource.ds1.username=root
  14. spring.shardingsphere.datasource.ds1.password=root
  16. #sharding-database
  17. #分片策略inline 分片键id 表达式id/2=ds0、ds1
  18. spring.shardingsphere.sharding.tables.c_user.database-strategy.inline.sharding-column=id
  19. spring.shardingsphere.sharding.tables.c_user.database-strategy.inline.algorithm-expression=ds$->{id % 2}
  21. #id
  22. spring.shardingsphere.sharding.tables.c_user.key-generator.column=id
  23. spring.shardingsphere.sharding.tables.c_user.key-generator.type=SNOWFLAKE
  24. #encrypt
  25. spring.shardingsphere.encrypt.tables.c_user.columns.pwd.plain-column=pwd_plain
  26. spring.shardingsphere.encrypt.tables.c_user.columns.pwd.cipher-column=pwd_cipher
  28. spring.shardingsphere.encrypt.encryptors.la_pwd.type=aes
  29. spring.shardingsphere.encrypt.encryptors.la_pwd.props.aes.key.value=1234
  31. spring.shardingsphere.encrypt.tables.c_user.columns.pwd.encryptor=la_pwd
  32. #是否使用密文查询 默认true,false是指明文查询
  33. spring.shardingsphere.props.query.with.cipher.column=true

测试类

  1. package dao;
  3. import com.galaxy.RunBootApplication;
  4. import com.galaxy.entity.CUser;
  5. import com.galaxy.repository.CUserRepository;
  6. import org.junit.Test;
  7. import org.junit.runner.RunWith;
  8. import org.springframework.beans.factory.annotation.Autowired;
  9. import org.springframework.boot.test.context.SpringBootTest;
  10. import org.springframework.test.annotation.Repeat;
  11. import org.springframework.test.context.junit4.SpringRunner;
  13. import java.util.List;
  14. import java.util.Random;
  16. /**
  17.  * @author lane
  18.  * @date 2021年07月21日 下午6:41
  19.  */
  20. @SpringBootTest(classes = RunBootApplication.class)
  21. @RunWith(SpringRunner.class)
  22. public class TestEncryptor {
  24.     @Autowired
  25.     private CUserRepository userRepository;
  27.     @Test
  28.     @Repeat(20)
  29.     public void testAdd(){
  30.         CUser user = new CUser();
  31.        int i =  new Random().nextInt(20);
  32.         user.setName("tiger"+ i);
  33.         user.setPwd("abc"+i);
  34.         userRepository.save(user);
  35.     }
  37.     @Test
  38.     public void testFind(){
  39.         List<CUser> list = userRepository.findByPwd("abc");
  40.         list.forEach(cUser -> {
  41.             System.out.println(cUser.getId()+" "+cUser.getName()+" "+cUser.getPwd());
  42.         });
  43.     }
  46. }

测试结果

可以看到存储两列分别是明文密码和密文密码

不过在分库雪花片算法生成主键这方面有问题，依然按照默认的数据库 auto_increment 的生成策略

Sharding JDBC 实战 - 图12

执行查询语句

Sharding JDBC 实战 - 图13

实现分布式事务 XA

XA 是强一致性，Saga 是最终一致性，2PC，3PC 都是分布式事务规范，需要具体实现如 Seata

本地实现按照 lane1 position 和 lane2 的 position_detail 来实现

依赖添加

父 pom.xml

  1.   <dependency>
  2.             <groupId>org.apache.shardingsphere</groupId>
  3.             <artifactId>sharding-transaction-xa-core</artifactId>
  4.             <version>${shardingsphere.version}</version>
  5.         </dependency>

子 pom.xml 添加

  1.         <dependency>
  2.             <groupId>org.apache.shardingsphere</groupId>
  3.             <artifactId>sharding-transaction-xa-core</artifactId>
  4.         </dependency>

配置修改

application.properties

  1. spring.profiles.active=sharding-database
  2. spring.shardingsphere.props.sql.show=true

application-sharding-database.properties

注意这次指定了分别为 lane1 和 lane2 并没有按 id 分片

  1. #datasource
  2. spring.shardingsphere.datasource.names=ds0,ds1
  4. spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
  5. spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
  6. spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/lane1?useUnicode=true&characterEncoding=utf8
  7. spring.shardingsphere.datasource.ds0.username=root
  8. spring.shardingsphere.datasource.ds0.password=root
  10. spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
  11. spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
  12. spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/lane2?useUnicode=true&characterEncoding=utf8
  13. spring.shardingsphere.datasource.ds1.username=root
  14. spring.shardingsphere.datasource.ds1.password=root
  16. #sharding-database
  17. #分片策略inline 分片键id 表达式id/2=ds0、ds1
  18. spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
  19. spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=ds$->{0}
  21. spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.sharding-column=id
  22. spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.algorithm-expression=ds$->{1}
  24. #id
  25. spring.shardingsphere.sharding.tables.position.key-generator.column=id
  26. spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
  27. #spring.shardingsphere.sharding.tables.position.key-generator.type=LANEKEY
  29. spring.shardingsphere.sharding.tables.position_detail.key-generator.column=id
  30. spring.shardingsphere.sharding.tables.position_detail.key-generator.type=SNOWFLAKE
  32. #BoardCast
  33. #spring.shardingsphere.sharding.broadcast-tables=city
  34. #spring.shardingsphere.sharding.tables.city.key-generator.column=id
  35. #spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE
  38. #sharding-database-table
  39. #spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.sharding-column=company_id
  40. #spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.algorithm-expression=ds$->{company_id % 2}
  41. #spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.sharding-column=id
  42. #spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.algorithm-expression=b_order${id % 2}
  43. #spring.shardingsphere.sharding.tables.b_order.actual-data-nodes=ds${0..1}.b_order${0..1}
  44. #spring.shardingsphere.sharding.tables.b_order.key-generator.column=id
  45. #spring.shardingsphere.sharding.tables.b_order.key-generator.type=SNOWFLAKE

代码实现

启动类

  1. package com.galaxy;
  3. import org.springframework.boot.SpringApplication;
  4. import org.springframework.boot.autoconfigure.SpringBootApplication;
  5. import org.springframework.transaction.annotation.EnableTransactionManagement;
  7. /**
  8.  * @author lane
  9.  * @date 2021年07月20日 下午7:31
  10.  */
  11. @SpringBootApplication
  12. @EnableTransactionManagement
  13. public class RunBootApplication {
  15.     /*public static void main(String[] args) {
  16.         SpringApplication.run(RunBootApplication.class,args);
  17.     }*/
  18. }

测试类

  2. /**
  3.  * @author lane
  4.  * @date 2021年07月22日 下午4:43
  5.  */
  6. @RunWith(SpringRunner.class)
  7. @SpringBootTest(classes = RunBootApplication.class)
  8. public class TestShardingTransaction {
  10.     @Resource
  11.     private PositionRepository positionRepository;
  13.     @Resource
  14.     private PositionDetailRepository positionDetailRepository;
  16.     @Test
  17. //    @Transactional
  18. //    @ShardingTransactionType(TransactionType.XA)
  19.     public void test1(){
  20. //        TransactionTypeHolder.set(TransactionType.XA);
  21. //        for (int i=1;i<=5;i++){
  22.         int i=1;
  23.             Position position = new Position();
  24.             position.setName("root"+i);
  25.             position.setSalary("1000000");
  26.             position.setCity("beijing");
  27.             positionRepository.save(position);
  28.             if (i==2){
  29.                 throw new RuntimeException("人为制造异常");
  30.             }
  32.             PositionDetail positionDetail = new PositionDetail();
  33.             positionDetail.setPid(position.getId());
  34.             positionDetail.setDescription("this is a root "+i);
  35.             positionDetailRepository.save(positionDetail);
  37.     }
  38. }

正常情况的结果，两个不同的库都添加了一条数据

Sharding JDBC 实战 - 图14

修改测试代码 i=2 后如下，此时会出现异常，一个库的 position 执行成功，另一个库 position_detail 执行失败

  3. /**
  4.  * @author lane
  5.  * @date 2021年07月22日 下午4:43
  6.  */
  7. @RunWith(SpringRunner.class)
  8. @SpringBootTest(classes = RunBootApplication.class)
  9. public class TestShardingTransaction {
  11.     @Resource
  12.     private PositionRepository positionRepository;
  14.     @Resource
  15.     private PositionDetailRepository positionDetailRepository;
  17.     @Test
  18.     @Transactional
  19.     //@ShardingTransactionType(TransactionType.XA)
  20.     public void test1(){
  21.    //      TransactionTypeHolder.set(TransactionType.XA);
  22.   //        for (int i=1;i<=5;i++){
  23.         int i=2;
  24.             Position position = new Position();
  25.             position.setName("root"+i);
  26.             position.setSalary("1000000");
  27.             position.setCity("beijing");
  28.             positionRepository.save(position);
  29.             if (i==2){
  30.                 throw new RuntimeException("人为制造异常");
  31.             }
  33.             PositionDetail positionDetail = new PositionDetail();
  34.             positionDetail.setPid(position.getId());
  35.             positionDetail.setDescription("this is a root "+i);
  36.             positionDetailRepository.save(positionDetail);
  38.     }
  39. }

添加异常后的结果

Sharding JDBC 实战 - 图15

删除刚才的数据，再次修改添加如下代码

  1.  TransactionTypeHolder.set(TransactionType.XA);

再次测试，可以看到成功回滚了

Sharding JDBC 实战 - 图16

Sharding JDBC 实战 - 图17

实际上搞得我有点尴尬 😅，因为理论上来说，添加注解也可以实现异常回滚的，然而事实上并没有，只有在代码中添加才成功实现了异常回滚。