ShardingSphere-jdbc 分库分表踩坑实记

pom文件

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">
   <modelVersion>4.0.0</modelVersion>
   <parent>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-parent</artifactId>
      <version>2.1.9.RELEASE</version>
      <relativePath/> <!-- lookup parent from repository -->
   </parent>
   <groupId>com.example</groupId>
   <artifactId>demo</artifactId>
   <version>0.0.1-SNAPSHOT</version>
   <name>demo</name>
   <description>Demo project for Spring Boot</description>
   <properties>
      <java.version>1.8</java.version>
      <mybatis-plus.version>3.1.1</mybatis-plus.version>
      <sharding-sphere.version>4.0.0-RC2</sharding-sphere.version>
   </properties>
   <dependencies>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-web</artifactId>
      </dependency>
      <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
         <version>8.0.15</version>
      </dependency>
      <dependency>
         <groupId>org.apache.shardingsphere</groupId>
         <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
         <version>${sharding-sphere.version}</version>
      </dependency>
      <dependency>
         <groupId>org.apache.shardingsphere</groupId>
         <artifactId>sharding-jdbc-spring-namespace</artifactId>
         <version>${sharding-sphere.version}</version>
      </dependency>
      <dependency>
         <groupId>org.projectlombok</groupId>
         <artifactId>lombok</artifactId>
         <optional>true</optional>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-test</artifactId>
         <scope>test</scope>
         <exclusions>
            <exclusion>
               <artifactId>xmlunit-core</artifactId>
               <groupId>org.xmlunit</groupId>
            </exclusion>
         </exclusions>
      </dependency>
      <dependency>
         <groupId>javax.xml.bind</groupId>
         <artifactId>jaxb-api</artifactId>
         <version>2.4.0-b180830.0359</version>
      </dependency>
      <dependency>
         <groupId>com.sun.xml.bind</groupId>
         <artifactId>jaxb-osgi</artifactId>
         <version>2.4.0-b180830.0438</version>
      </dependency>
      <dependency>
         <groupId>com.sun.activation</groupId>
         <artifactId>javax.activation</artifactId>
         <version>1.2.0</version>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-jdbc</artifactId>
      </dependency>
      <dependency>
         <groupId>org.apache.commons</groupId>
         <artifactId>commons-lang3</artifactId>
      </dependency>
      <dependency>
         <groupId>com.alibaba</groupId>
         <artifactId>fastjson</artifactId>
         <version>1.2.58</version>
      </dependency>
   </dependencies>
   <build>
      <plugins>
         <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
         </plugin>
      </plugins>
   </build>
   <repositories>
      <repository>
         <id>central</id>
         <name>Maven2 Central Repository</name>
         <layout>default</layout>
         <url>http://repo1.maven.org/maven2</url>
      </repository>
      <repository>
         <id>unidal.releases</id>
         <url>http://unidal.org/nexus/content/repositories/releases/</url>
      </repository>
   </repositories>
</project>

配置文件

从配置文件可以看出配置了两个数据库,每个数据库各存放了两个表:t_order_0和t_order_1,用于简单测试

server.port=8080
# 配置ds0 和ds1两个数据源
spring.shardingsphere.datasource.names = ds0,ds1
#ds0 配置
spring.shardingsphere.datasource.ds0.type = com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url = jdbc:mysql://localhost:3306/local_dev?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
spring.shardingsphere.datasource.ds0.username = root
spring.shardingsphere.datasource.ds0.password =
#ds1 配置
spring.shardingsphere.datasource.ds1.type = com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name = com.mysql.cj.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url = jdbc:mysql://localhost:3306/test?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
spring.shardingsphere.datasource.ds1.username = root
spring.shardingsphere.datasource.ds1.password =
# 分库策略 根据id取模确定数据进哪个数据库
spring.shardingsphere.sharding.default-database-strategy.inline.sharding-column = id
spring.shardingsphere.sharding.default-database-strategy.inline.algorithm-expression = ds$->{id % 2}
# 具体分表策略
# 节点 ds0.t_order_0,ds0.t_order_1,ds1.t_order_0,ds1.t_order_1
spring.shardingsphere.sharding.tables.t_order.actual-data-nodes = ds$->{0..1}.t_order_$->{0..1}
# 分表字段id
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.sharding-column = id
# 分表策略 根据id取模,确定数据最终落在那个表中
spring.shardingsphere.sharding.tables.t_order.table-strategy.inline.algorithm-expression = t_order_$->{id % 2}
# 使用SNOWFLAKE算法生成主键
spring.shardingsphere.sharding.tables.t_order.key-generator.column = id
spring.shardingsphere.sharding.tables.t_order.key-generator.type = SNOWFLAKE
#spring.shardingsphere.sharding.binding-tables=t_order
spring.shardingsphere.props.sql.show = true

这样就算配置完成了,启动不会报错.

坑来了

如果执行数据库操作,就会开始报错

按照正常处理逻辑,boot说没有,那么我们就添加一个依赖

<dependency>
   <groupId>javax.xml.bind</groupId>
   <artifactId>jaxb-api</artifactId>
   <version>2.4.0-b180830.0359</version>
</dependency>

添加以后重新启动执行,依然会有新的错误产品.

找了一天的错误,在github上找到了解决方案:

添加依赖

<dependency>
   <groupId>javax.xml.bind</groupId>
   <artifactId>jaxb-api</artifactId>
   <version>2.4.0-b180830.0359</version>
</dependency>
<dependency>
   <groupId>com.sun.xml.bind</groupId>
   <artifactId>jaxb-osgi</artifactId>
   <version>2.4.0-b180830.0438</version>
</dependency>
<dependency>
   <groupId>com.sun.activation</groupId>
   <artifactId>javax.activation</artifactId>
   <version>1.2.0</version>
</dependency>

方案链接:

https://github.com/eclipse-ee4j/jaxb-api/issues/78