1.项目结构
2.jdk版本1.8, sharding-sphere版本 3.0.0.M3, mysql版本1.8, springboot版本1.5.18.RELEASE
3.直接上代码
pom.xml
<?xmlversion="1.0"?>
<project
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0http://maven.apache.org/xsd/maven-4.0.0.xsd"
xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>net.popo</groupId>
<artifactId>middleware</artifactId>
<version>1.0.0-SNAPSHOT</version>
</parent>
<artifactId>middleware-sharding-sphere</artifactId>
<name>middleware-sharding-sphere</name>
<url>http://maven.apache.org</url>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<dependencies>
<dependency>
<artifactId>middleware-utils</artifactId>
<groupId>net.popo</groupId>
<version>1.0.0-SNAPSHOT</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
</dependency>
</dependencies>
</project>
application-sharding.yml
sharding:
jdbc:
datasource:
names:master,slave #数据源名称,多数据源以逗号分隔
master:
driver-class-name:com.mysql.cj.jdbc.Driver
url:jdbc:mysql://10.2.98.14:3306/predb?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
username:xxxx
password:xxxx
type:com.alibaba.druid.pool.DruidDataSource
filters:stat
initial-size:1
min-idle:1
max-active:20
max-wait:60000
time-between-eviction-runs-millis:60000
min-evictable-idle-time-millis:300000
validation-query:SELECT 'x'
test-while-idle:true
test-on-borrow:false
test-on-return:false
pool-prepared-statements:false
max-pool-prepared-statement-per-connection-size:20
slave:
driver-class-name:com.mysql.cj.jdbc.Driver
url:jdbc:mysql://10.2.98.174:3306/predb?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true&useSSL=false
username:xxxx
password:xxxx
type:com.alibaba.druid.pool.DruidDataSource
filters:stat
initial-size:1
min-idle:1
max-active:20
max-wait:60000
time-between-eviction-runs-millis:60000
min-evictable-idle-time-millis:300000
validation-query:SELECT 'x'
test-while-idle:true
test-on-borrow:false
test-on-return:false
pool-prepared-statements:false
max-pool-prepared-statement-per-connection-size:20
config:
sharding:
props:
sql:
show:true
tables:# 分表规则
demo:# 要分的表
actual-data-nodes:sharding_db.demo$->{0..2} # 哪个库的哪些表(子表),分为3个表demo0,demo1,demo2
key-generator-column-name:id # 主键
table-strategy:# 分表策略
inline:# 行表达式策略
shardingColumn:mobile # 用于分表的字段
algorithmExpression:demo$->{mobile % 3} # 分表规则表达式,0-3-6-9落到demo0,1-4-7-10落到demo1,2-5-8落到demo2
master-slave-rules:# 主从配置规则
sharding_db: # 所有数据库的集合
master-data-source-name:master # 主
slave-data-source-names:slave # 从
application.yml
spring:
profiles:
include:sharding
mybatis:
#mapper-locations: classpath*:mybatis/*.xml #用xml的形式必须要配置
type-aliases-package:net.popo.*.po
configuration:
call-setters-on-nulls:true
Demo.java
packagenet.popo.demo.po;
publicclassDemo {
privateLong id;
privateString name;
privateLong mobile;
publicLong getId() {
returnid;
}
publicvoidsetId(Long id) {
this.id= id;
}
publicString getName() {
returnname;
}
publicvoidsetName(String name) {
this.name= name== null? null: name.trim();
}
publicLong getMobile() {
returnmobile;
}
publicvoidsetMobile(Long mobile) {
this.mobile= mobile;
}
}
DemoMapper.java
package net.popo.demo.dao;
import net.popo.demo.po.Demo;
import org.apache.ibatis.annotations.Insert;
public interface DemoMapper {
@Insert({
"insert into demo (id, name, ",
"mobile)",
"values (#{id,jdbcType=BIGINT}, #{name,jdbcType=VARCHAR}, ",
"#{mobile,jdbcType=BIGINT})"
})
int insert(Demo record);
}
TestServiceImpl.java
package net.popo.demo.service;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import net.popo.demo.dao.DemoMapper;
import net.popo.demo.po.Demo;
@Service
public class TestServiceImpl {
@Autowired
private DemoMapper testMapper;
public int save(Demo test) {
return testMapper.insert(test);
}
}
TestController.java
package net.popo.demo.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import net.popo.demo.po.Demo;
import net.popo.demo.service.TestServiceImpl;
import net.popo.util.SnowFlakeOrder;
@RestController
public class TestController {
@Autowired
private TestServiceImpl testServiceImpl;
@RequestMapping("/test")
public int test() {
Demo test = new Demo();
test.setId(SnowFlakeOrder.getUniqueId("133xxxxxxxx", 3)); // 根据手机号对3取模存到对应的表中
test.setMobile(133xxxxxxxxl);
System.out.println(test.getId());
System.out.println("该条数据应该落在 :" + test.getMobile() % 3 + " 的表中");
test.setName("hoopopo");
return testServiceImpl.save(test);
}
}