springboot+mybatisplus+sharding-jdbc分库分表实例项⽬实践
现在Java项⽬使⽤mybatis多⼀些，所以我也做了⼀个springboot+mybatisplus+sharding-jdbc分库分表项⽬例⼦分享给⼤家。
要是⽤的springboot+jpa可以看这篇⽂章：
其它的框架内容不做赘述，直接上代码。
数据准备
装备两个数据库。并在两个库中建表，建表sql如下：
DROP TABLE IF EXISTS `t_user_0`;
CREATE TABLE `t_user_0` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`order_id` BIGINT(20) DEFAULT '0' COMMENT '顺序编号',
`user_id` BIGINT(20) DEFAULT '0' COMMENT '⽤户编号',
`user_name` varchar(32) DEFAULT NULL COMMENT '⽤户名',
`pass_word` varchar(32) DEFAULT NULL COMMENT '密码',
`nick_name` varchar(32) DEFAULT NULL COMMENT '倪名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DROP TABLE IF EXISTS `t_user_1`;
CREATE TABLE `t_user_1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`order_id` BIGINT(20) DEFAULT '0' COMMENT '顺序编号',
`user_id` BIGINT(20) DEFAULT '0' COMMENT '⽤户编号',
`user_name` varchar(32) DEFAULT NULL COMMENT '⽤户名',
`pass_word` varchar(32) DEFAULT NULL COMMENT '密码',
`nick_name` varchar(32) DEFAULT NULL COMMENT '倪名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
POM配置
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</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>
</dependency>
<dependency>
<groupId>com.dangdang</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>1.5.4</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.3</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.44</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- mybatis-plus begin -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatisplus-spring-boot-starter</artifactId>
<version>${mybatisplus-spring-boot-starter.version}</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
<version>${mybatisplus.version}</version>
</dependency>
<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity</artifactId>
<version>${velocity.version}</version>
</dependency>
<!-- mybatis-plus end -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.51</version>
</dependency>
</dependencies>
application.properties配置
abled=false
pe=com.alibaba.druid.pool.DruidDataSource
spring.jdbc1.sql.jdbc.Driver
spring.jdbc1.url=jdbc:mysql://localhost:3306/mazhq?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
spring.jdbc1.username=root
spring.jdbc1.password=123456
tionProperties=config.decrypt=true;druid.stat.slowSqlMillis=3000;druid.stat.logSlowSql=true;Sql=true spring.jdbc1.filters=stat
spring.jdbc1.maxActive=100
spring.jdbc1.initialSize=1
spring.jdbc1.maxWait=15000
spring.jdbc1.minIdle=1
spring.jdbc1.timeBetweenEvictionRunsMillis=30000
spring.jdbc1.minEvictableIdleTimeMillis=180000
spring.jdbc1.validationQuery=SELECT 'x'
stWhileIdle=true
stOnBorrow=false
stOnReturn=false
spring.jdbc1.poolPreparedStatements=false
spring.jdbc1.maxPoolPreparedStatementPerConnectionSize=20
veAbandoned=true
veAbandonedTimeout=600
spring.jdbc1.logAbandoned=false
tionInitSqls=
pe=com.alibaba.druid.pool.DruidDataSource
spring.jdbc2.sql.jdbc.Driver
spring.jdbc2.url=jdbc:mysql://localhost:3306/liugh?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
spring.jdbc2.username=root
spring.jdbc2.password=123456
tionProperties=config.decrypt=true;druid.stat.slowSqlMillis=3000;druid.stat.logSlowSql=true;Sql=true spring.jdbc2.filters=stat
spring.jdbc2.maxActive=100
spring.jdbc2.initialSize=1
spring.jdbc2.maxWait=15000
spring.jdbc2.minIdle=1
spring.jdbc2.timeBetweenEvictionRunsMillis=30000
spring.jdbc2.minEvictableIdleTimeMillis=180000
spring.jdbc2.validationQuery=SELECT 'x'
stWhileIdle=true
stOnBorrow=false
stOnReturn=false
spring.jdbc2.poolPreparedStatements=false
spring.jdbc2.maxPoolPreparedStatementPerConnectionSize=20
veAbandoned=true
veAbandonedTimeout=600
spring.jdbc2.logAbandoned=false
tionInitSqls=
mybatis-plus.mapper-locations=classpath:/com/mazhq/web/mapper/xml/*l
#1：数据库ID⾃增  2:⽤户输⼊id  3：全局唯⼀id(IdWorker)  4：全局唯⼀ID(uuid)
mybatis-plus.global-config.id-type=3
mybatis-plus.global-config.db-column-underline=true
fresh-mapper=true
#配置的缓存的全局开关
#延时加载的开关
#开启的话，延时加载⼀个属性时会加载该对象全部属性，否则按需加载属性
#打印sql语句，调试⽤
分库分表最主要有⼏个配置：
1. 有多少个数据源（2个：database0和database1）
@Data
@ConfigurationProperties(prefix = "spring.jdbc1")
public class ShardDataSource1 {
private String driverClassName;
private String url;
private String username;
private String password;
private String filters;
private int maxActive;
private int initialSize;
private int maxWait;
private int minIdle;
private int timeBetweenEvictionRunsMillis;
private int minEvictableIdleTimeMillis;
private String validationQuery;
private boolean testWhileIdle;
private boolean testOnBorrow;
private boolean testOnReturn;
private boolean poolPreparedStatements;
private int maxPoolPreparedStatementPerConnectionSize;
private boolean removeAbandoned;
private int removeAbandonedTimeout;
private boolean logAbandoned;
private List<String> connectionInitSqls;
private String connectionProperties;
}
  2. ⽤什么列进⾏分库以及分库算法
/
**
* @author mazhq
* @date 2019/8/7 17:23
*/
public class DataBaseShardingAlgorithm implements SingleKeyDatabaseShardingAlgorithm<Long> {
@Override
public String doEqualSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) {
for (String each : databaseNames) {
if (dsWith(Long.Value().toString()) % 2 + "")) {
return each;
}
}
throw new IllegalArgumentException();
}
@Override
public Collection<String> doInSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<>(databaseNames.size());
for (Long value : Values()) {
for (String tableName : databaseNames) {
if (dsWith(value % 2 + "")) {
result.add(tableName);
}
}
}
return result;
}
@Override
public Collection<String> doBetweenSharding(Collection<String> databaseNames, ShardingValue<Long> shardingValue) {        Collection<String> result = new LinkedHashSet<>(databaseNames.size());
Range<Long> range = (Range<Long>) ValueRange();
for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
for (String each : databaseNames) {
if (dsWith(i % 2 + "")) {
result.add(each);
}
}
}
return result;
}
}
  3. ⽤什么列进⾏分表以及分表算法
/**
* @author mazhq
* @Title: TableShardingAlgorithm
* @date 2019/8/12 16:40
*/
public class TableShardingAlgorithm implements SingleKeyTableShardingAlgorithm<Long> {
@Override
public String doEqualSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) {
for (String each : tableNames) {
if (Value() % 2 + "")) {
return each;
}
}
throw new IllegalArgumentException();
}
@Override
public Collection<String> doInSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) {
Collection<String> result = new LinkedHashSet<>(tableNames.size());
for (Long value : Values()) {
for (String tableName : tableNames) {
if (dsWith(value % 2 + "")) {
result.add(tableName);
}
}
}
return result;
}
@Override
public Collection<String> doBetweenSharding(Collection<String> tableNames, ShardingValue<Long> shardingValue) {        Collection<String> result = new LinkedHashSet<>(tableNames.size());
Range<Long> range = (Range<Long>) ValueRange();
for (Long i = range.lowerEndpoint(); i <= range.upperEndpoint(); i++) {
for (String each : tableNames) {
if (dsWith(i % 2 + "")) {
result.add(each);
}
}
}
return result;
}
}
  4. 每张表的逻辑表名和所有物理表名和集成调⽤
/**
* @author mazhq
* @Title: DataSourceConfig
* @date 2019/8/7 17:05
*/
@Configuration
@EnableTransactionManagement
@ConditionalOnClass(DruidDataSource.class)
@EnableConfigurationProperties({ShardDataSource1.class, ShardDataSource2.class})
public class DataSourceConfig {
@Autowired
private ShardDataSource1 dataSource1;
@Autowired
private ShardDataSource2 dataSource2;
/**
* 配置数据源0，数据源的名称最好要有⼀定的规则，⽅便配置分库的计算规则
* @return
*/
private DataSource db1() throws SQLException {
DB1(dataSource1);
}
/**
* 配置数据源1，数据源的名称最好要有⼀定的规则，⽅便配置分库的计算规则
* @return
*/
private DataSource db2() throws SQLException {
DB2(dataSource2);
}
/**
* 配置数据源规则，即将多个数据源交给sharding-jdbc管理，并且可以设置默认的数据源，
* 当表没有配置分库规则时会使⽤默认的数据源
* @return
*/
@Bean
public DataSourceRule dataSourceRule() throws SQLException {
Map<String, DataSource> dataSourceMap = new HashMap<>();
dataSourceMap.put("dataSource0", this.db1());
dataSourceMap.put("dataSource1", this.db2());
return new DataSourceRule(dataSourceMap, "dataSource0");
}
/**
* 配置数据源策略和表策略，具体策略需要⾃⼰实现
* @param dataSourceRule
* @return
*/
jpa mybatis@Bean
public ShardingRule shardingRule(@Qualifier("dataSourceRule") DataSourceRule dataSourceRule){
//具体分库分表策略
TableRule orderTableRule = TableRule.builder("t_user")
.actualTables(Arrays.asList("t_user_0", "t_user_1"))
.tableShardingStrategy(new TableShardingStrategy("order_id", new TableShardingAlgorithm()))
.dataSourceRule(dataSourceRule)
.build();
//绑定表策略，在查询时会使⽤主表策略计算路由的数据源，因此需要约定绑定表策略的表的规则需要⼀致，可以⼀定程度提⾼效率        List<BindingTableRule> bindingTableRuleList = new ArrayList<BindingTableRule>();
bindingTableRuleList.add(new BindingTableRule(Arrays.asList(orderTableRule)));
return ShardingRule.builder().dataSourceRule(dataSourceRule)
.tableRules(Arrays.asList(orderTableRule))
.bindingTableRules(bindingTableRuleList)
.databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new DataBaseShardingAlgorithm()))
.tableShardingStrategy(new TableShardingStrategy("order_id", new TableShardingAlgorithm()))
.build();
}
/**
* 创建sharding-jdbc的数据源DataSource，MybatisAutoConfiguration会使⽤此数据源
* @param shardingRule
* @return
* @throws SQLException
*/
@Bean
public DataSource shardingDataSource(@Qualifier("shardingRule") ShardingRule shardingRule) throws SQLException {
ateDataSource(shardingRule);
}
private DruidDataSource getDB1(ShardDataSource1 shardDataSource1) throws SQLException {
DruidDataSource ds = new DruidDataSource();
ds.DriverClassName());
ds.Url());
ds.Username());
ds.Password());
ds.Filters());
ds.MaxActive());
ds.InitialSize());
ds.MaxWait());
ds.MinIdle());
ds.TimeBetweenEvictionRunsMillis());
ds.MinEvictableIdleTimeMillis());
ds.ValidationQuery());
ds.setTestWhileIdle(shardDataSource1.isTestWhileIdle());
ds.setTestOnBorrow(shardDataSource1.isTestOnBorrow());
ds.setTestOnReturn(shardDataSource1.isTestOnReturn());
ds.setPoolPreparedStatements(shardDataSource1.isPoolPreparedStatements());
ds.setMaxPoolPreparedStatementPerConnectionSize(
ds.setRemoveAbandoned(shardDataSource1.isRemoveAbandoned());
ds.RemoveAbandonedTimeout());
ds.setLogAbandoned(shardDataSource1.isLogAbandoned());
ds.ConnectionInitSqls());
ds.ConnectionProperties());
return ds;
}
private DruidDataSource getDB2(ShardDataSource2 shardDataSource2) throws SQLException {
DruidDataSource ds = new DruidDataSource();

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。