基于SpringBoot和ShardingSphere实现分库分表

文章目录

基于SpringBoot和ShardingSphere实现分库分表

构建项目依赖

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 http://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.2.1.RELEASE</version>
        <relativePath/>
    </parent>
    <groupId>com.study</groupId>
    <artifactId>sharding-sphere</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sharding-sphere</name>
    <description>基于SpringBoot和ShardingSphere分库分表实战</description>

    <properties>
        <java.version>1.8</java.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <maven.compiler.encoding>UTF-8</maven.compiler.encoding>
    </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>
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.20</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.12.0</version>
        </dependency>

        <!--Mybatis-Plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.5</version>
        </dependency>
        <!--shardingsphere start-->
        <!-- for spring boot -->
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>
        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.8</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.70</version>
        </dependency>
    </dependencies>

    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

验证时如果出现以下错误：

java.lang.IllegalStateException: Failed to load ApplicationContext

	at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:132)
	at org.springframework.test.context.support.DefaultTestContext.getApplicationContext(DefaultTestContext.java:123)
	at org.springframework.test.context.web.ServletTestExecutionListener.setUpRequestContextIfNecessary(ServletTestExecutionListener.java:190)
	at org.springframework.test.context.web.ServletTestExecutionListener.prepareTestInstance(ServletTestExecutionListener.java:132)
	at org.springframework.test.context.TestContextManager.prepareTestInstance(TestContextManager.java:244)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.createTest(SpringJUnit4ClassRunner.java:227)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner$1.runReflectiveCall(SpringJUnit4ClassRunner.java:289)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.methodBlock(SpringJUnit4ClassRunner.java:291)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:246)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: java.lang.IllegalStateException: Failed to load property source from location 'classpath:/application.yml'
	at org.springframework.boot.context.config.ConfigFileApplicationListener$Loader.load(ConfigFileApplicationListener.java:545)
	at org.springframework.boot.context.config.ConfigFileApplicationListener$Loader.loadForFileExtension(ConfigFileApplicationListener.java:494)
	at org.springframework.boot.context.config.ConfigFileApplicationListener$Loader.load(ConfigFileApplicationListener.java:464)
	at org.springframework.boot.context.config.ConfigFileApplicationListener$Loader.lambda$null$7(ConfigFileApplicationListener.java:443)
	at java.lang.Iterable.forEach(Iterable.java:75)
	at org.springframework.boot.context.config.ConfigFileApplicationListener$Loader.lambda$load$8(ConfigFileApplicationListener.java:443)
	at java.lang.Iterable.forEach(Iterable.java:75)
	at org.springframework.boot.context.config.ConfigFileApplicationListener$Loader.load(ConfigFileApplicationListener.java:440)
	at org.springframework.boot.context.config.ConfigFileApplicationListener$Loader.lambda$load$0(ConfigFileApplicationListener.java:335)
	at org.springframework.boot.context.config.FilteredPropertySource.apply(FilteredPropertySource.java:54)
	at org.springframework.boot.context.config.ConfigFileApplicationListener$Loader.load(ConfigFileApplicationListener.java:323)
	at org.springframework.boot.context.config.ConfigFileApplicationListener.addPropertySources(ConfigFileApplicationListener.java:214)
	at org.springframework.boot.context.config.ConfigFileApplicationListener.postProcessEnvironment(ConfigFileApplicationListener.java:198)
	at org.springframework.boot.context.config.ConfigFileApplicationListener.onApplicationEnvironmentPreparedEvent(ConfigFileApplicationListener.java:188)
	at org.springframework.boot.context.config.ConfigFileApplicationListener.onApplicationEvent(ConfigFileApplicationListener.java:176)
	at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:172)
	at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:165)
	at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:139)
	at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:127)
	at org.springframework.boot.context.event.EventPublishingRunListener.environmentPrepared(EventPublishingRunListener.java:76)
	at org.springframework.boot.SpringApplicationRunListeners.environmentPrepared(SpringApplicationRunListeners.java:53)
	at org.springframework.boot.SpringApplication.prepareEnvironment(SpringApplication.java:345)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:308)
	at org.springframework.boot.test.context.SpringBootContextLoader.loadContext(SpringBootContextLoader.java:125)
	at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContextInternal(DefaultCacheAwareContextLoaderDelegate.java:99)
	at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:124)
	... 24 more
Caused by: org.yaml.snakeyaml.error.YAMLException: java.nio.charset.MalformedInputException: Input length = 2
	at org.yaml.snakeyaml.reader.StreamReader.update(StreamReader.java:218)
	at org.yaml.snakeyaml.reader.StreamReader.ensureEnoughData(StreamReader.java:176)
	at org.yaml.snakeyaml.reader.StreamReader.ensureEnoughData(StreamReader.java:171)
	at org.yaml.snakeyaml.reader.StreamReader.peek(StreamReader.java:126)
	at org.yaml.snakeyaml.scanner.ScannerImpl.scanToNextToken(ScannerImpl.java:1177)
	at org.yaml.snakeyaml.scanner.ScannerImpl.fetchMoreTokens(ScannerImpl.java:287)
	at org.yaml.snakeyaml.scanner.ScannerImpl.checkToken(ScannerImpl.java:227)
	at org.yaml.snakeyaml.parser.ParserImpl$ParseImplicitDocumentStart.produce(ParserImpl.java:195)
	at org.yaml.snakeyaml.parser.ParserImpl.peekEvent(ParserImpl.java:158)
	at org.yaml.snakeyaml.parser.ParserImpl.checkEvent(ParserImpl.java:148)
	at org.yaml.snakeyaml.composer.Composer.checkNode(Composer.java:72)
	at org.yaml.snakeyaml.constructor.BaseConstructor.checkData(BaseConstructor.java:114)
	at org.yaml.snakeyaml.Yaml$1.hasNext(Yaml.java:543)
	at org.springframework.beans.factory.config.YamlProcessor.process(YamlProcessor.java:160)
	at org.springframework.beans.factory.config.YamlProcessor.process(YamlProcessor.java:134)
	at org.springframework.boot.env.OriginTrackedYamlLoader.load(OriginTrackedYamlLoader.java:75)
	at org.springframework.boot.env.YamlPropertySourceLoader.load(YamlPropertySourceLoader.java:50)
	at org.springframework.boot.context.config.ConfigFileApplicationListener$Loader.loadDocuments(ConfigFileApplicationListener.java:562)
	at org.springframework.boot.context.config.ConfigFileApplicationListener$Loader.load(ConfigFileApplicationListener.java:518)
	... 49 more
Caused by: java.nio.charset.MalformedInputException: Input length = 2
	at java.nio.charset.CoderResult.throwException(CoderResult.java:281)
	at sun.nio.cs.StreamDecoder.implRead(StreamDecoder.java:339)
	at sun.nio.cs.StreamDecoder.read(StreamDecoder.java:178)
	at java.io.InputStreamReader.read(InputStreamReader.java:184)
	at org.yaml.snakeyaml.reader.UnicodeReader.read(UnicodeReader.java:125)
	at org.yaml.snakeyaml.reader.StreamReader.update(StreamReader.java:183)
	... 67 more

在 pom 文件中添加以下内容：

<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<maven.compiler.encoding>UTF-8</maven.compiler.encoding>

创建数据库表

t_order 表：

CREATE TABLE `t_order_0` (
  `order_id` bigint(20) NOT NULL,
  `order_no` varchar(15) COLLATE utf8mb4_bin DEFAULT NULL,
  `order_time` datetime DEFAULT NULL,
  `order_money` decimal(20,2) DEFAULT NULL,
  `order_status` char(1) COLLATE utf8mb4_bin DEFAULT NULL,
  `order_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `t_order_1` (
  `order_id` bigint(20) NOT NULL,
  `order_no` varchar(15) COLLATE utf8mb4_bin DEFAULT NULL,
  `order_time` datetime DEFAULT NULL,
  `order_money` decimal(20,2) DEFAULT NULL,
  `order_status` char(1) COLLATE utf8mb4_bin DEFAULT NULL,
  `order_user_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

t_order_item 表：

CREATE TABLE `biz_order_item_1` (
  `order_item_id` bigint(20) NOT NULL,
  `order_id` bigint(20) DEFAULT NULL,
  `goods_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `goods_num` int(11) DEFAULT NULL,
  `order_user_id` int(11) DEFAULT NULL,
  `goods_price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `biz_order_item_2` (
  `order_item_id` bigint(20) NOT NULL,
  `order_id` bigint(20) DEFAULT NULL,
  `goods_name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `goods_num` int(11) DEFAULT NULL,
  `order_user_id` int(11) DEFAULT NULL,
  `goods_price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`order_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

数据库表结构如下图所示：
在这里插入图片描述

注意，不能将主键的生成规则设置成自增长，需要按照一定规则来生成主键，这里使用 ShardingSphere 中的 SNOWFLAKE （雪花算法）来生成主键。

编写实体类

t_order 表实体类

package com.study.shardingsphere.entity;

import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;
import java.util.Date;

/**
 * @ClassName Order
 * @Description TODO t_order 表实体类
 * @Author 听秋
 * @Date 2022/5/13 19:52
 * @Version 1.0
 **/
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName(value = "t_order")
public class Order {

    @TableId
    private Long orderId;

    private String orderNo;

    private Date orderTime;

    private BigDecimal orderMoney;

    private String orderStatus;

    private Integer orderUserId;
}

t_order_item 表实体类

package com.study.shardingsphere.entity;

import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.math.BigDecimal;

/**
 * @ClassName Item
 * @Description TODO t_order_item 表实体类
 * @Author 听秋
 * @Date 2022/5/13 19:52
 * @Version 1.0
 **/
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName(value = "t_order_item")
public class OrderItem {

    @TableId
    private Long orderItemId;

    private Long orderId;

    private String goodsName;

    private int goodsNum;

    private Integer orderUserId;

    private BigDecimal goodsPrice;
}

注意，@TableName(value = “t_order_item”) 和 @TableName(value = “t_order”) 注解是必须要的，不然会验证时出现如下错误：

line 1:12 no viable alternative at input 'order'
line 1:12 mismatched input 'order' expecting {TRUNCATE, POSITION, VIEW, ANY, OFFSET, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, BOOLEAN, DATE, TIME, TIMESTAMP, YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MICROSECOND, MAX, MIN, SUM, COUNT, AVG, CURRENT, ENABLE, DISABLE, INSTANCE, DO, DEFINER, CASCADED, LOCAL, CLOSE, OPEN, NEXT, NAME, TYPE, TABLES, TABLESPACE, COLUMNS, FIELDS, INDEXES, STATUS, MODIFY, VALUE, DUPLICATE, FIRST, LAST, AFTER, OJ, ACCOUNT, USER, ROLE, START, TRANSACTION, WITHOUT, ESCAPE, SUBPARTITION, STORAGE, SUPER, TEMPORARY, THAN, UNBOUNDED, SIGNED, UPGRADE, VALIDATION, ROLLUP, SOUNDS, UNKNOWN, OFF, ALWAYS, COMMITTED, LEVEL, NO, PASSWORD, PRIVILEGES, ACTION, ALGORITHM, AUTOCOMMIT, BTREE, CHAIN, CHARSET, CHECKSUM, CIPHER, CLIENT, COALESCE, COMMENT, COMPACT, COMPRESSED, COMPRESSION, CONNECTION, CONSISTENT, DATA, DISCARD, DISK, ENCRYPTION, END, ENGINE, EVENT, EXCHANGE, EXECUTE, FILE, FIXED, FOLLOWING, GLOBAL, HASH, IMPORT_, LESS, MEMORY, NONE, PARSER, PARTIAL, PARTITIONING, PERSIST, PRECEDING, PROCESS, PROXY, QUICK, REBUILD, REDUNDANT, RELOAD, REMOVE, REORGANIZE, REPAIR, REVERSE, SESSION, SHUTDOWN, SIMPLE, SLAVE, VISIBLE, INVISIBLE, ENFORCED, AGAINST, LANGUAGE, MODE, QUERY, EXTENDED, EXPANSION, VARIANCE, MAX_ROWS, MIN_ROWS, SQL_BIG_RESULT, SQL_BUFFER_RESULT, SQL_CACHE, SQL_NO_CACHE, STATS_AUTO_RECALC, STATS_PERSISTENT, STATS_SAMPLE_PAGES, ROW_FORMAT, WEIGHT_STRING, COLUMN_FORMAT, INSERT_METHOD, KEY_BLOCK_SIZE, PACK_KEYS, PERSIST_ONLY, BIT_AND, BIT_OR, BIT_XOR, GROUP_CONCAT, JSON_ARRAYAGG, JSON_OBJECTAGG, STD, STDDEV, STDDEV_POP, STDDEV_SAMP, VAR_POP, VAR_SAMP, AUTO_INCREMENT, AVG_ROW_LENGTH, DELAY_KEY_WRITE, ROTATE, MASTER, BINLOG, ERROR, SCHEDULE, COMPLETION, EVERY, HOST, SOCKET, PORT, SERVER, WRAPPER, OPTIONS, OWNER, RETURNS, CONTAINS, SECURITY, INVOKER, TEMPTABLE, MERGE, UNDEFINED, DATAFILE, FILE_BLOCK_SIZE, EXTENT_SIZE, INITIAL_SIZE, AUTOEXTEND_SIZE, MAX_SIZE, NODEGROUP, WAIT, LOGFILE, UNDOFILE, UNDO_BUFFER_SIZE, REDO_BUFFER_SIZE, HANDLER, PREV, ORGANIZATION, DEFINITION, DESCRIPTION, REFERENCE, FOLLOWS, PRECEDES, IMPORT, CONCURRENT, XML, DUMPFILE, SHARE, CODE, CONTEXT, SOURCE, CHANNEL, CLONE, AGGREGATE, INSTALL, COMPONENT, UNINSTALL, RESOURCE, EXPIRE, NEVER, HISTORY, OPTIONAL, REUSE, MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR, MAX_CONNECTIONS_PER_HOUR, MAX_USER_CONNECTIONS, RETAIN, RANDOM, OLD, ISSUER, SUBJECT, CACHE, GENERAL, SLOW, USER_RESOURCES, EXPORT, RELAY, HOSTS, FLUSH, RESET, RESTART, IO_THREAD, SQL_THREAD, SQL_BEFORE_GTIDS, SQL_AFTER_GTIDS, MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE, RELAY_LOG_POS, SQL_AFTER_MTS_GAPS, UNTIL, DEFAULT_AUTH, PLUGIN_DIR, STOP, IDENTIFIER_}



org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: java.lang.NullPointerException
### The error may involve com.study.shardingsphere.dao.OrderMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO order  ( order_id, order_no, order_time, order_money, order_status, order_user_id )  VALUES  ( ?, ?, ?, ?, ?, ? )
### Cause: java.lang.NullPointerException

	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:77)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy73.insert(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
	at com.baomidou.mybatisplus.core.override.PageMapperMethod.execute(PageMapperMethod.java:68)
	at com.baomidou.mybatisplus.core.override.PageMapperProxy.invoke(PageMapperProxy.java:64)
	at com.sun.proxy.$Proxy80.insert(Unknown Source)
	at com.study.shardingsphere.ShardingSphereApplicationTests.addOrder(ShardingSphereApplicationTests.java:46)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
	at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
	at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
	at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
	at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
	at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
	at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
	at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
	at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: java.lang.NullPointerException
### The error may involve com.study.shardingsphere.dao.OrderMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO order  ( order_id, order_no, order_time, order_money, order_status, order_user_id )  VALUES  ( ?, ?, ?, ?, ?, ? )
### Cause: java.lang.NullPointerException
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
	... 36 more
Caused by: java.lang.NullPointerException
	at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
	at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitTableName(MySQLVisitor.java:214)
	at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitTableName(MySQLVisitor.java:121)
	at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$TableNameContext.accept(MySQLStatementParser.java:9214)
	at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
	at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitInsert(MySQLDMLVisitor.java:152)
	at org.apache.shardingsphere.sql.parser.mysql.visitor.impl.MySQLDMLVisitor.visitInsert(MySQLDMLVisitor.java:127)
	at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$InsertContext.accept(MySQLStatementParser.java:1090)
	at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)
	at org.apache.shardingsphere.sql.parser.SQLParserEngine.parse0(SQLParserEngine.java:80)
	at org.apache.shardingsphere.sql.parser.SQLParserEngine.parse(SQLParserEngine.java:61)
	at org.apache.shardingsphere.underlying.route.DataNodeRouter.createRouteContext(DataNodeRouter.java:97)
	at org.apache.shardingsphere.underlying.route.DataNodeRouter.executeRoute(DataNodeRouter.java:89)
	at org.apache.shardingsphere.underlying.route.DataNodeRouter.route(DataNodeRouter.java:76)
	at org.apache.shardingsphere.underlying.pluggble.prepare.PreparedQueryPrepareEngine.route(PreparedQueryPrepareEngine.java:54)
	at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.executeRoute(BasePrepareEngine.java:96)
	at org.apache.shardingsphere.underlying.pluggble.prepare.BasePrepareEngine.prepare(BasePrepareEngine.java:83)
	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.prepare(ShardingPreparedStatement.java:183)
	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:143)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
	... 42 more

yml 文件配置

server:
  port: 8080

spring:
  application:
    name: sharding-sphere
  main:
    allow-bean-definition-overriding: true

  shardingsphere:
    datasource:
      names: ds0,ds1 # 定义两个数据源 ds0, ds1
      # 配置数据源具体内容，包含连接池，驱动，地址，用户名和密码
      # ds0 数据源配置
      ds0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/ds0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: root
      # ds1 数据源配置
      ds1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/ds1?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: root
    # 指定数据库分布情况，数据库里面表分布情况
    sharding:
      tables:
        # 设置逻辑表为 t_order
        t_order:
          actual-data-nodes: ds$->{0..1}.t_order_$->{0..1} # 真实数据节点
          # 指定 t_order 表里面主键 order_id 生成策略 为 SNOWFLAKE（雪花算法）
          key-generator:
            column: order_id
            type: SNOWFLAKE
          # 指定数据库分片策略，根据 order_id 进行分库，是偶数则添加到 ds0，是奇数则添加到 ds1
          database-strategy:
            inline:
              sharding-column: order_id
              algorithm-expression: ds$->{order_id % 2}
          # 指定数据表分片策略，根据 order_user_id 进行分表，是偶数则添加到 t_order_0，是奇数则添加到 t_order_1
          table-strategy:
            inline:
              sharding-column: order_user_id
              algorithm-expression: t_order_$->{order_user_id%2}
        t_order_item:
          actual-data-nodes: ds$->{0..1}.t_order_item_$->{0..1}
          key-generator:
            column: order_item_id
            type: SNOWFLAKE
          database-strategy:
            inline:
              sharding-column: order_id
              algorithm-expression: ds$->{order_id % 2}
          table-strategy:
            inline:
              sharding-column: order_user_id
              algorithm-expression: t_order_item_$->{order_user_id%2}
      # 设置绑定关系
      binding-tables: t_order,t_order_item
      # 默认数据源，即未配置分表规则的表数据存储表
      default-data-source: ds0
    # 打开 sql 输出日志
    props:
      sql:
        show: true
# mybatis配置
mybatis-plus:
  mapper-locations: classpath*:mapper/*.xml

编写 dao 文件

OrderMapper

package com.study.shardingsphere.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.study.shardingsphere.entity.Order;

/**
 * @ClassName OrderMapper
 * @Description TODO
 * @Author 听秋
 * @Date 2022/5/13 19:55
 * @Version 1.0
 **/
public interface OrderMapper extends BaseMapper<Order> {

}

OrderItemMapper

package com.study.shardingsphere.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.study.shardingsphere.entity.OrderItem;


/**
 * @Author 听秋
 * @Description //TODO
 * @Date 10:47 2022/5/16
 * @Param 
 * @return 
 **/
public interface OrderItemMapper extends BaseMapper<OrderItem> {

}

编写测试用例

package com.study.shardingsphere;

import com.study.shardingsphere.dao.OrderItemMapper;
import com.study.shardingsphere.dao.OrderMapper;
import com.study.shardingsphere.entity.Order;
import com.study.shardingsphere.entity.OrderItem;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.RandomStringUtils;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.math.BigDecimal;
import java.util.Date;

/**
 * @ClassName ShardingSphereApplicationTest
 * @Description TODO 测试用例
 * @Author 听秋
 * @Date 2022/5/13 20:06
 * @Version 1.0
 **/
@SpringBootTest
@RunWith(SpringRunner.class)
@Slf4j
public class ShardingSphereApplicationTests {

    @Autowired
    private OrderMapper orderMapper;

    @Autowired
    private OrderItemMapper itemMapper;

    @Test
    public void addOrder(){
        // 插入订单
        for (int i = 0; i < 1000; i++) {
            Order order = new Order();
            order.setOrderMoney(new BigDecimal(100 + i));
            order.setOrderNo(System.currentTimeMillis()/1000+"");
            order.setOrderStatus("0");
            order.setOrderUserId(i);
            order.setOrderTime(new Date());
            orderMapper.insert(order);
            log.info("orderId 为：{}", order.getOrderId());

            // 插入订单item
            OrderItem item = new OrderItem();
            item.setGoodsName("iphone 12 手机");
            item.setGoodsNum(Integer.valueOf(RandomStringUtils.randomNumeric(4)));
            item.setGoodsPrice(new BigDecimal(1200 + i));
            item.setOrderId(order.getOrderId());
            item.setOrderUserId(i);
            itemMapper.insert(item);
        }
    }

/**
     * 使用@Test注解时,被其标识的方法必须是(void)无返回值类型
     * @return
     */
    @Test
    public void getOrder() {
        List<Order> orders = orderMapper.getOrder();
        System.out.println("分库分表中总计包含" + orders.size() + "条数据" );
    }

}

OrderMapper 接口

package com.study.shardingsphere.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.study.shardingsphere.entity.Order;

import java.util.List;

/**
 * @ClassName OrderMapper
 * @Description TODO
 * @Author 听秋
 * @Date 2022/5/13 19:55
 * @Version 1.0
 **/
public interface OrderMapper extends BaseMapper<Order> {

    List<Order> getOrder();
}

OrderMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.study.shardingsphere.dao.OrderMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.study.shardingsphere.entity.Order">
        <result column="order_id" property="orderId" />
        <result column="order_no" property="orderNo" />
        <result column="order_time" property="orderTime" />
        <result column="order_money" property="orderMoney" />
        <result column="order_status" property="orderStatus" />
        <result column="order_user_id" property="orderUserId" />
    </resultMap>

    <select id="getOrder" resultType="com.study.shardingsphere.entity.Order">
        select
            *
        from
            t_order
    </select>

</mapper>

注意：OrderMapper.xml 中的查询的表名为 t_order，与 yml 配置文件中的逻辑表一致。

验证

  • 启动测试用例中的 addOrder 方法，会看到如下日志内容：

在这里插入图片描述
MySQL 库表数据如下（以 ds0 库为例）：

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

四个 order 表中的数据总计 2000 条。

  • 启动测试用例中的 getOrder 方法，会看到如下日志内容：

在这里插入图片描述

版权声明：本文为TQ20160412原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。