ShardingSphere是一套开源的分布式数据库中间件解决方案组成的生态圈,它由Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar(计划中)这3款相互独立的产品组成
这里我主要用到了Sharding-JDBC的分库功能及路由配置来实现分布式数据库,下面上代码:
引入的maven包,pom.xml配置:
<?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.butterfly</groupId>
<artifactId>sharing</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sharing</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
<sharding-sphere.version>4.0.0-RC2</sharding-sphere.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- for spring boot -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<scope>provided</scope>
</dependency>
<!-- for spring namespace -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>${sharding-sphere.version}</version>
</dependency>
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.31</version>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
</dependency>
<!-- 通用Mapper -->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>2.0.2</version>
</dependency>
<!-- 分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.12</version>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
<resource>
<directory>src/main/resources</directory>
</resource>
</resources>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.6</version>
<configuration>
<configurationFile>${basedir}/src/main/resources/generatorConfig.xml</configurationFile>
<overwrite>true</overwrite>
<verbose>true</verbose>
</configuration>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.31</version>
</dependency>
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper</artifactId>
<version>4.0.3</version>
</dependency>
</dependencies>
</plugin>
</plugins>
</build>
</project>
主要引入sharding-jdbc-spring-boot-starter和sharding-jdbc-spring-namespace这两个包,采用HikariCP作为连接池
实例表结构:
CREATE TABLE `esm_trade_calendar` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`title` varchar(200) NOT NULL,
`content` varchar(1000) DEFAULT NULL,
`type` char(4) NOT NULL COMMENT '日历事件类型:私有事件/公共事件',
`classifyId` bigint(20) DEFAULT NULL,
`startDate` varchar(10) NOT NULL,
`startTime` varchar(10) NOT NULL,
`endDate` varchar(10) NOT NULL,
`endTime` varchar(10) NOT NULL,
`createTime` datetime DEFAULT NULL,
`createUser` bigint(20) DEFAULT NULL,
`updateTime` datetime DEFAULT NULL,
`updateUser` bigint(20) DEFAULT NULL COMMENT '更新人',
`orgId` bigint(20) NOT NULL,
`dataStatus` tinyint(4) NOT NULL COMMENT '1正常/0删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=393345718556819457 DEFAULT CHARSET=utf8 COMMENT='交易日历';
这里以两个不同ip的数据库作为分布式数据库场景,将这张表分别建到这两个数据库里,application.yml数据源配置如下:
spring:
shardingsphere:
datasource:
names: ds0,ds1
ds0:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://local1:3306/test1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&&useSSL=false
username: test1
password: 123456
connection-timeout: 10000
maximum-pool-size: 100
minimum-idle: 10
read-only: false
ds1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.jdbc.Driver
jdbc-url: jdbc:mysql://local2:3306/test2?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&&useSSL=false
username: test2
password: 123456
connection-timeout: 10000
maximum-pool-size: 100
minimum-idle: 10
read-only: false
sharding:
tables: #数据分片规则配置,可配置多个logic_table_name
esm_trade_calendar: #逻辑表名称
actual-data-nodes: ds$->{0..1}.esm_trade_calendar #由数据源名 + 表名组成,以小数点分隔。多个表以逗号分隔,支持inline表达式。缺省表示使用已知数据源与逻辑表名称生成数据节点。用于广播表(即每个库中都需要一个同样的表用于关联查询,多为字典表)或只分库不分表且所有库的表结构完全一致的情
database-strategy: #分库策略,缺省表示使用默认分库策略,以下的分片策略只能选其一
inline: #行表达式分片策略
sharding-column: id #分片列名称
algorithm-expression: ds${id % 2} #分片算法行表达式,这里采用尾数取模
key-generator:
column: id #自增列名称,缺省表示不使用自增主键生成器
type: SNOWFLAKE #自增列值生成器类型,缺省表示使用默认自增列值生成器。可使用用户自定义的列值生成器或选择内置类型:SNOWFLAKE/UUID/LEAF_SEGMEN
props: #属性配置, 注意:使用SNOWFLAKE算法,需要配置worker.id与max.tolerate.time.difference.milliseconds属性
worker.id: 1
max.tolerate.time.difference.milliseconds: 60
default-data-source-name: ds0 #未配置分片规则的表将通过默认数据源定位
props: #属性配置
sql: #是否开启SQL显示,默认值: false
show: true
mapper:
style: normal #字段转换方式 这里采用默认,因为表字段没有采用下划线结构不需要下划线转大写,所以直接默认
利用tk.mybaitis自动生成代码,生成器配置如下:
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<properties resource="db.properties" />
<context id="Mysql" targetRuntime="MyBatis3Simple" defaultModelType="flat">
<property name="beginningDelimiter" value="`" />
<property name="endingDelimiter" value="`" />
<plugin type="tk.mybatis.mapper.generator.MapperPlugin">
<!--自定义通用Mapper接口,将来所有生成的mapper接口都会继承这个接口-->
<property name="mappers"
value="tk.mybatis.mapper.common.BaseMapper,tk.mybatis.mapper.common.MySqlMapper" />
</plugin>
<jdbcConnection driverClass="${jdbc.driver}"
connectionURL="${jdbc.url}"
userId="${jdbc.username}"
password="${jdbc.password}">
</jdbcConnection>
<javaModelGenerator targetPackage="${targetModelPackage}"
targetProject="${targetJavaProject}" />
<sqlMapGenerator targetPackage="${targetXMLPackage}"
targetProject="${targetJavaProject}" />
<javaClientGenerator targetPackage="${targetMapperPackage}"
targetProject="${targetJavaProject}" type="XMLMAPPER" />
<table tableName="esm_trade_calendar" domainObjectName="TradeCalendar" mapperName="TradeCalendarDao">
<!--使用实际列名-->
<property name="useActualColumnNames" value="true"/>
</table>
</context>
</generatorConfiguration>
生成的dao如下:
package com.butterfly.sharing.dao;
import com.butterfly.sharing.entity.TradeCalendar;
import tk.mybatis.mapper.common.BaseMapper;
import tk.mybatis.mapper.common.MySqlMapper;
import java.util.List;
public interface TradeCalendarDao extends BaseMapper<TradeCalendar>,MySqlMapper<TradeCalendar> {
}
项目目录结构如下:
启动类配置如下:
package com.butterfly.sharing;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.ComponentScan;
import tk.mybatis.spring.annotation.MapperScan;
@SpringBootApplication
@ComponentScan("com.butterfly.sharing.service.impl")
@MapperScan("com.butterfly.sharing.dao")
public class SharingApplication {
public static void main(String[] args) {
SpringApplication.run(SharingApplication.class, args);
}
}
注意这里用到了通用mapper,自动生成的代码都是继承tk.mabits的BaseMapper,所以@MapperScan引用tk.mybatis.spring.annotation.MapperScan进行扫描
最后写个SharingApplicationTests测试用例进行测试
package com.butterfly.sharing;
import com.butterfly.sharing.entity.TradeCalendar;
import com.butterfly.sharing.service.TradeCalendarService;
import com.github.pagehelper.PageInfo;
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;
@RunWith(SpringRunner.class)
@SpringBootTest
public class SharingApplicationTests {
@Autowired
private TradeCalendarService service;
@Test
public void contextLoads() {
for (int i = 0; i <= 99; i++) {
TradeCalendar entity = new TradeCalendar();
entity.setOrgId(1l);
entity.setTitle("测试" + i);
entity.setType("1");
entity.setStartDate("2018-09-11");
entity.setEndDate("2018-09-11");
entity.setStartTime("2018-09-11");
entity.setEndTime("2018-09-11");
entity.setDataStatus((byte) 1);
service.insert(entity);
}
//测试分页
PageInfo pageInfo = service.page();
pageInfo.getList().forEach(o -> System.out.println(o.toString()));
}
}
TradeCalendarServiceImpl
package com.butterfly.sharing.service.impl;
import com.butterfly.sharing.dao.TradeCalendarDao;
import com.butterfly.sharing.entity.TradeCalendar;
import com.butterfly.sharing.service.TradeCalendarService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.shardingsphere.transaction.annotation.ShardingTransactionType;
import org.apache.shardingsphere.transaction.core.TransactionType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import java.sql.SQLException;
import java.util.List;
@Service
public class TradeCalendarServiceImpl implements TradeCalendarService {
@Autowired
private TradeCalendarDao dao;
@Override
//采用本地事务
@ShardingTransactionType(TransactionType.BASE)
@Transactional(rollbackFor = SQLException.class)
public void insert(TradeCalendar entity) {
dao.insertUseGeneratedKeys(entity);
}
@Override
public PageInfo<TradeCalendar> page() {
PageHelper.startPage(2,10);
List<TradeCalendar> list = dao.selectBy();
return new PageInfo<>(list);
}
}
插入数据结果:
可以看到是根据路由配置的尾数取模分片算法进行分库插入,分布式主键采用雪花算法保证唯一。
分页测试结果:
可以看到逻辑sql为SELECT * from esm_trade_calendar ORDER BY id LIMIT ?, ? ,实际处理为Actual SQL: ds0 ::: SELECT * from esm_trade_calendar ORDER BY id LIMIT ?, ? ::: [0, 20]
: Actual SQL: ds1 ::: SELECT * from esm_trade_calendar ORDER BY id LIMIT ?, ? ::: [0, 20]两条sql,最后进行组装生成最终结果。
由于SQL语法灵活复杂,分布式数据库和单机数据库的查询场景又不完全相同,难免有和单机数据库不兼容的SQL出现。可以参考文章https://shardingsphere.apache.org/document/current/cn/features/sharding/use-norms/sql/