- 使用 Spring Initializr 创建 Spring Boot 应用程序
- 在Spring Boot中配置Cassandra
- 在 Spring Boot 上配置 Tomcat 连接池
- 将Camel消息路由到嵌入WildFly的Artemis上
以下实例基于shardingsphere 4.1.0 + SpringBoot 2.2.5.RELEASE版本
依赖导入:
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.compile.sourceEncoding>UTF-8</project.compile.sourceEncoding>
<springboot.version>2.2.5.RELEASE</springboot.version>
<shardingsphere.version>4.1.0</shardingsphere.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>${springboot.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
<version>${springboot.version}</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<version>${springboot.version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.13</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>${shardingsphere.version}</version>
</dependency>
</dependencies>
场景:通过id字段取余分片到两个数据库
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
</dependency>
spring.shardingsphere.datasource.names=ds0,ds1
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/ds_0?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=0490218292
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ds_1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=0490218292
spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=ds$->{id % 2}
@Test
public void testAdd(){
for (int i = 0; i <= 20; i++) {
Position position=new Position();
position.setId((long) i);
position.setName("lagou"+i);
position.setSalary("1000");
position.setCity("beijing");
positionRepository.save(position);
}
}
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
增加id设置:
#id设置
spring.shardingsphere.sharding.tables.position.key-generator.column=id
spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
@Test
public void testAdd(){
for (int i = 0; i <= 20; i++) {
Position position=new Position();
position.setName("lagou"+i);
position.setSalary("1000");
position.setCity("beijing");
positionRepository.save(position);
}
}
public class MyCustomId implements ShardingKeyGenerator {
@Override
public Comparable<?> generateKey() {
return System.currentTimeMillis()+new Random().nextInt(100000);
}
@Override
public String getType() {
//自定义一个名称
return "MYID";
}
@Override
public Properties getProperties() {
return null;
}
@Override
public void setProperties(Properties properties) {
}
}
在resources下创建META-INF/services目录,并创建一个文件,文件名为:org.apache.shardingsphere.spi.keygen.ShardingKeyGenerator
里面写自定义主键生成器的全类名
#id设置
spring.shardingsphere.sharding.tables.position.key-generator.column=id
spring.shardingsphere.sharding.tables.position.key-generator.type=MYID
场景:职位表(position)和职位详情表(position_detail)是关联的两个表,关联关系是:position_detail.pid = position.id,那么我们期望在插入数据后,根据职位Id进行查询时能够只查询一个库,而不是笛卡尔积的进行查询。
spring.shardingsphere.datasource.names=ds0,ds1
spring.shardingsphere.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds0.jdbc-url=jdbc:mysql://localhost:3306/ds_0?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.ds0.username=root
spring.shardingsphere.datasource.ds0.password=0490218292
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:mysql://localhost:3306/ds_1?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=0490218292
#职位表设置
spring.shardingsphere.sharding.tables.position.database-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.position.database-strategy.inline.algorithm-expression=ds$->{id % 2}
#id设置
spring.shardingsphere.sharding.tables.position.key-generator.column=id
spring.shardingsphere.sharding.tables.position.key-generator.type=SNOWFLAKE
#职位表详情设置
spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.sharding-column=pid
spring.shardingsphere.sharding.tables.position_detail.database-strategy.inline.algorithm-expression=ds$->{pid % 2}
#id设置
spring.shardingsphere.sharding.tables.position_detail.key-generator.column=id
spring.shardingsphere.sharding.tables.position_detail.key-generator.type=SNOWFLAKE
可以看出position的id的分片策略和position_detail的pid的分片策略一致。
2. 测试
@Test
public void testQueryPosition(){
Object positionAndDetailById = positionRepository.findPositionAndDetailById(730545854473043968L);
System.out.println(positionAndDetailById);
}
可以看出,只查询了一个库:
场景:城市表属于基础表,数据量不大,每个库都可以存一样的数据。
#广播表设置
spring.shardingsphere.sharding.broadcast-tables=city
spring.shardingsphere.sharding.tables.city.key-generator.column=id
spring.shardingsphere.sharding.tables.city.key-generator.type=SNOWFLAKE
@Test
public void testAddCity(){
City city=new City();
city.setName("成都");
city.setProvince("四川");
cityRepository.save(city);
}
和之前的不同,这一条数据的插入,两个库都有。且ID也是一致的。
场景:我们有一个订单表,可以根据公司id(companyId)进行分库,然后在根据id进行分表。
#订单表分库且分表
spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.sharding-column=company_id
spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.algorithm-expression=ds$->{company_id%2}
spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.algorithm-expression=b_order${id % 2}
spring.shardingsphere.sharding.tables.b_order.actual-data-nodes=ds${0..1}.b_order${0..1}
#id设置
spring.shardingsphere.sharding.tables.b_order.key-generator.column=id
spring.shardingsphere.sharding.tables.b_order.key-generator.type=SNOWFLAKE
@Test
@Repeat(100)
public void testAddBOrder(){
BOrder bOrder=new BOrder();
bOrder.setDel(false);
bOrder.setCompanyId(new Random().nextInt(10));
bOrder.setPositionId(23);
bOrder.setUserId(22);
bOrder.setPublishUserId(11);
bOrder.setResumeType(1);
bOrder.setStatus("AUTO");
bOrder.setCreateTime(new Date());
bOrder.setOperateTime(new Date());
bOrder.setWorkYear("2");
bOrder.setName("lagou");
bOrder.setPositionName("Java");
bOrder.setResumeId(23443);
bOrderRepository.save(bOrder);
}
我们发现数据插入到了ds_0.b_order0、ds_0.b_order1、ds_1.b_order0、ds_1.b_order1四个node里面。
spring.shardingsphere.datasource.names=master,slave0
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://localhost:3306/ds_0?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=0490218292
spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://localhost:3306/ds_0_slave?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false&serverTimezone=UTC
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=0490218292
#读写分离
spring.shardingsphere.masterslave.name=datasource
spring.shardingsphere.masterslave.master-data-source-name=master
spring.shardingsphere.masterslave.slave-data-source-names=slave0
#多个读库时的负载均衡策略
spring.shardingsphere.masterslave.load-balance-algorithm-type=ROUND_ROBIN
@Test
public void test(){
List<City> all = cityRepository.findAll();
all.forEach(x->System.out.println(x));
}
分库分表+读写分离的参数配置
#数据源
spring.shardingsphere.datasource.names=master0,slave0,slave1,master1,slave2,slave3
spring.shardingsphere.datasource.master0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master0.jdbc-url=jdbc:mysql://localhost:3306/master0?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master0.username=root
spring.shardingsphere.datasource.master0.password=root
spring.shardingsphere.datasource.slave0.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave0.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave0.jdbc-url=jdbc:mysql://localhost:3306/slave0?useSSL=false
spring.shardingsphere.datasource.slave0.username=root
spring.shardingsphere.datasource.slave0.password=root
spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://localhost:3306/slave1?useSSL=false
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=root
spring.shardingsphere.datasource.master1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master1.jdbc-url=jdbc:mysql://localhost:3306/master1?useUnicode=true&characterEncoding=utf-8&useSSL=false
spring.shardingsphere.datasource.master1.username=root
spring.shardingsphere.datasource.master1.password=root
spring.shardingsphere.datasource.slave2.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave2.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave2.jdbc-url=jdbc:mysql://localhost:3306/slave2?useSSL=false
spring.shardingsphere.datasource.slave2.username=root
spring.shardingsphere.datasource.slave2.password=root
spring.shardingsphere.datasource.slave3.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave3.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave3.jdbc-url=jdbc:mysql://localhost:3306/slave3?useSSL=false
spring.shardingsphere.datasource.slave3.username=root
spring.shardingsphere.datasource.slave3.password=root
#分库分表
spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.sharding-column=company_id
spring.shardingsphere.sharding.tables.b_order.database-strategy.inline.algorithm-expression=master$->{company_id % 2}
spring.shardingsphere.sharding.tables.b_order.actual-data-nodes=master$->{0..1}.b_order$->{0..1}
spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.sharding-column=id
spring.shardingsphere.sharding.tables.b_order.table-strategy.inline.algorithm-expression=b_order$->{id % 2}
#读写分离
spring.shardingsphere.sharding.master-slave-rules.master0.master-data-source-name=master0
spring.shardingsphere.sharding.master-slave-rules.master0.slave-data-source-names=slave0, slave1
spring.shardingsphere.sharding.master-slave-rules.master1.master-data-source-name=master1
spring.shardingsphere.sharding.master-slave-rules.master1.slave-data-source-names=slave2, slave3
在一些应用场景中,分片条件并不存在于SQL,而存在于外部业务逻辑。因此需要提供一种通过在外部业务代码中指定路由配置的一种方式,在ShardingSphere中叫做Hint。如果使用Hint指定了强制分片路由,那么SQL将会无视原有的分片逻辑,直接路由至指定的数据节点操作。
使用场景:
public class MyHintShardingAlgorithm implements HintShardingAlgorithm<String> {
@Override
public Collection<String> doSharding(Collection<String> collection, HintShardingValue<String> hintShardingValue) {
Collection<String> result=new ArrayList<>();
if(hintShardingValue.getValues().contains("master")){
((ArrayList<String>) result).add("master");
}else {
((ArrayList<String>) result).add("slave0");
}
return result;
}
}
spring.shardingsphere.sharding.tables.city.database-strategy.hint.algorithm-class-name=com.mmc.sharding.hint.MyHintShardingAlgorithm
@Test
public void testHint(){
HintManager hintManager = HintManager.getInstance();
hintManager.addDatabaseShardingValue("city","master");
// hintManager.setMasterRouteOnly();
List<City> all = cityRepository.findAll();
all.forEach(x->System.out.println(x));
}
还可以使用hintManager.setMasterRouteOnly()指定仅路由到主库。
测试过程中发现Hint的自定义策略和读写分离配置有冲突。配置了读写分离后自定义Hint类不生效了,仅hintManager.setMasterRouteOnly()还可以用。
脱敏配置分为如下几个:数据源配置,加密器配置,脱敏表配置以及查询属性配置,其详情如下图所示:
CREATE TABLE `c_user` (
`Id` bigint(11) NOT NULL AUTO_INCREMENT,
`name` varchar(256) DEFAULT NULL,
`pwd_plain` varchar(256) DEFAULT NULL,
`pwd_cipher` varchar(256) DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
@Entity
@Table(name = "c_user")
public class CUser implements Serializable {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "name")
private String name;
/**
* 逻辑列名
*/
@Column(name = "password")
private String password;
}
#定义数据表真实明文列
#spring.shardingsphere.encrypt.tables.c_user.columns.password.plain-column=pwd_plain
#定义数据表真实密文列
spring.shardingsphere.encrypt.tables.c_user.columns.password.cipher-column=pwd_cipher
#定义加密器,名称为lagou_pwd
spring.shardingsphere.encrypt.encryptors.lagou_pwd.type=aes
spring.shardingsphere.encrypt.encryptors.lagou_pwd.props.aes.key.value=1234
#指定加密器,password是逻辑列名,与实体类中的字段对应
spring.shardingsphere.encrypt.tables.c_user.columns.password.encryptor=lagou_pwd
@Test
public void testEncrypt(){
CUser cUser=new CUser();
cUser.setName("阿百川");
cUser.setPassword("123456");
cUserRepository.save(cUser);
}
@Test
public void testQueryByPassword(){
List<CUser> byPassword = cUserRepository.findByPassword("123456");
System.out.println(byPassword);
}
数据库存放的已经是密文了,通过明文密码也可以查询到数据了。
仅仅需要在测试方法上加上两个注解:
@Transactional(rollbackFor = Exception.class)
@ShardingTransactionType(TransactionType.XA)
TransactionType有XA、BASE、LOCAL三种
@Test
@Transactional(rollbackFor = Exception.class)
@ShardingTransactionType(TransactionType.XA)
public void testAddDetail(){
for (int i = 0; i <= 3; i++) {
Position position=new Position();
position.setName("lagou"+i);
position.setSalary("1000");
position.setCity("beijing");
positionRepository.save(position);
if(i==3){
throw new RuntimeException();
}
PositionDetail positionDetail=new PositionDetail();
positionDetail.setPid(position.getId());
positionDetail.setDescription("详情");
positionDetailRepository.save(positionDetail);
}
}
书山有路勤为径,学海无涯苦作舟
这个问题在这里已经有了答案: Oracle: merging two different queries into one, LIKE & IN (1 个回答) 8年前关闭。 我有以下代码: case
我查阅过此页面:http://dev.mysql.com/doc/refman/5.1/en/case.html以及这个,但无法获得一个简单的程序来工作...... 更新:为了明确我想要做什么:我想从
有什么办法可以优化下面的查询吗? SELECT DATE_FORMAT(a.duedate,'%d-%b-%y') AS dte, duedate, SUM(CASE WHEN (typeofnoti
我进退两难,以下 SQL 查询的结果是什么以及它是如何工作的: SELECT ... CASE WHEN (a.FIELD=1 AND b.FIELD=2) THEN 1 WHEN
问题:输入年,月,打印对应年月的日历。 示例: 问题分析: 1,首先1970年是Unix系统诞生的时间,1970年成为Unix的元年,1970年1月1号是星期四,现在大多的手机的日历功能只能显
**摘要:**介绍了Angular中依赖注入是如何查找依赖,如何配置提供商,如何用限定和过滤作用的装饰器拿到想要的实例,进一步通过N个案例分析如何结合依赖注入的知识点来解决开发编程中会遇到的问题。 本
我想拥有自动伴侣类apply case 类的构造函数来为我执行隐式转换,但无法弄清楚如何这样做。我到处搜索,我能找到的最接近的答案是 this问题(我将解释为什么它不是我在下面寻找的)。 我有一个看起
您好,我已经浏览了“多列案例”问题,但没有看到与此相同的内容,所以我想我应该问一下。 基本上我有两个我想要连接的表(都是子查询的结果)。它们具有相同的列名称。如果我加入他们的 ID 和 SELECT
我发现了一些类型推断的非直觉行为。因此,语义等效代码的工作方式不同,具体取决于编译器推断出的有关函数返回类型的信息。当您在最小单元测试中重现此案例时,或多或少会清楚发生了什么。但我担心在编写框架代码时
CREATE TABLE test ( sts_id int , [status1] int , [status2] int , [status3] int , [status4] int ) INS
我有以下声明: SELECT Dag AS Dag, CASE Jaar WHEN 2013 THEN Levering END AS '2013', CASE
我想做的是为所有高于平均时间、平均时间和低于平均时间的游乐设施获取平均tip_portion。所以返回3行。当我运行它时,它显示: ERROR: missing FROM-clause entry
我正在尝试设置一个包含以下字段的报告: 非常需要报告来显示日期、该日期内的总记录(因此我按日期分组),然后按小时计算 12 小时工作日(从上午 8 点到晚上 8 点)我需要计算记录在这些时间内出现的时
我有这个查询 SELECT users.name FROM users LEFT JOIN weapon_stats ON users.id = weapon_stats.zp_id WHERE we
我正在尝试按收视率等级获取不同视频的计数。我有下表: vid_id views 1 6 1 10 1 900 2 850 2 125000
假设我有一个如下所示的 SQL 语句: select supplier, case when platform in (5,6) then 'mobile' when p
我有一个表测试 TestNumber (int primary key) InactiveBitwise (int) 我执行以下命令: UPDATE tests SET CASE WH
我有一个像这样的表(name=expense): id amount date 1 -1687 2014-01-02 00:00:00.0 2 11000 2014-01-02 0
我有一个 multimap 定义 typedef std::pair au_pair; //vertices typedef std::pair acq_pair; //ch qlty specifi
我有一个有点像枚举的类,它的每个实例都有一个唯一的 int 值,该值从 0 开始并在每个新实例时递增。 class MyEnumLikeClass { static int NextId =
我是一名优秀的程序员,十分优秀!