3、上代码
1、pom.xml配置引入maven依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!--springboot整合mybatis的依赖 -->
<!-- https: // mvnrepository测试数据/artifact/org.mybatis.spring.boot/mybatis-spring-boot-starter -->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version> 2.1 . 4 </version>
</dependency>
<!-- https: // mvnrepository测试数据/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<!-- 这里用的是sharding-jdbc-spring-boot-starter 需要注意的是,此时druid不能用spring-boot-starter版本的,需要用正常的包: -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version> 1.1 . 10 </version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version> 3.1 . 0 .M1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!-- https: // mvnrepository测试数据/artifact/org.projectlombok/lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<scope>provided</scope>
</dependency>
2、在application.yml中配置引用数据源及分库分表信息
mybatis.config-location: classpath:META-INF/mybatis- config.xml
spring:
profiles:
active: sharding -tbl- ms
main:
allow -bean-definition-overriding: true
sharding:
jdbc:
### 数据库
dataSource:
### 数据库的别名
names: ds -master- 0 ,ds-master- 1 ,ds-master- 0 -slave- 0 ,ds-master- 0 -slave- 1 ,ds-master- 1 -slave- 0 ,ds-master- 1 -slave- 1
# 主库1 ,master数据库
ds -master- 0 :
### 数据源类别
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql: // 192.168.8.162:3306/test1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
username: root
password: root
maxPoolSize: 20
### 主库1从库1 ,slave数据库
ds -master- 0 -slave- 0 :
### 数据源类别
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql: // 192.168.8.134:3306/test1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
username: root
password: root
maxPoolSize: 20
### 主库1从库1 ,slave数据库
ds -master- 0 -slave- 1 :
### 数据源类别
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql: // 192.168.8.176:3306/test1?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
username: root
password: root
maxPoolSize: 20
# 主库2 ,master数据库
ds -master- 1 :
### 数据源类别
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql: // 192.168.8.162:3306/test2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
username: root
password: root
maxPoolSize: 20
### 主库2从库1 ,slave数据库
ds -master- 1 -slave- 0 :
### 数据源类别
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql: // 192.168.8.134:3306/test2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
username: root
password: root
maxPoolSize: 20
### 主库2从库2 ,slave数据库
ds -master- 1 -slave- 1 :
### 数据源类别
type: com.alibaba.druid.pool.DruidDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql: // 192.168.8.176:3306/test2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8
username: root
password: root
maxPoolSize: 20
props:
sql:
show: true
config:
masterslave: # 配置读写分离
# 配置从库选择策略，提供轮询与随机，这里选择用轮询 // random 随机 // round-robin 轮询
load-balance-algorithm-type: round- robin
name: datasource
sharding:
master -slave- rules:
ds_0:
###配置的是主库的数据库名，本案例为ds -master- 0 ，其中ds_0为分区名。
master -data-source-name: ds-master- 0
###配置的是从库的数据库名，本案例为ds -master- 0 -slave- 0 ,ds-master- 0 -slave- 1
slave -data-source-names: ds-master- 0 -slave- 0 ,ds-master- 0 -slave- 1
ds_1:
###配置的是主库的数据库名，本案例为ds -master- 1 ，其中ds_1为分区名。
master -data-source-name: ds-master- 1
###配置的是从库的数据库名，本案例为ds -master- 1 -slave- 0 ,ds-master- 1 -slave- 1
slave -data-source-names: ds-master- 1 -slave- 0 ,ds-master- 1 -slave- 1
tables:
###需要分表的表名
t_user:
###配置的分表信息，真实的数据库信息。ds_0.t_user_$ ->{ 0 … 3 }，表示读取ds_0数据源的user_0、user_1、user_2、user_3。
actual -data-nodes: ds_$->{ 0 .. 1 }.t_user_$->{ 0 .. 3 }
database - strategy:
standard:
###是配置数据分库的策略的类，这里是自定义的类MyDBPreciseShardingAlgorithm
precise -algorithm- class - name: com.demo.shardingjdbc.MyDBPreciseShardingAlgorithm
###配置的数据分表的字段，是根据id来分的
sharding - column: id
table - strategy:
standard:
###是配置数据分表的策略的类，这里是自定义的类MyTablePreciseShardingAlgorithm
precise -algorithm- class - name: com.demo.shardingjdbc.MyTablePreciseShardingAlgorithm
###配置的数据分表的字段，是根据id来分的
sharding -column: id
3、配置分库分表分片规则（结合application.yml）
分库规则（结合pplication.yml中database-strategy）
package com.demo.shardingjdbc;
import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
import java.util.Collection;
/* *
* 自定义分片算法
*
* @author hzy
*
*/
public class MyDBPreciseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
for (String tableName : availableTargetNames) {
if (tableName.endsWith(shardingValue.getValue() % 2 + "" )) {
return tableName;
}
}
throw new IllegalArgumentException();
}
}
分表规则（结合pplication.yml中table-strategy）
package com.demo.shardingjdbc;
import io.shardingsphere.api.algorithm.sharding.PreciseShardingValue;
import io.shardingsphere.api.algorithm.sharding.standard.PreciseShardingAlgorithm;
import java.util.Collection;
/* *
* 自定义分片算法
*
* @author hzy
*
*/
public class MyTablePreciseShardingAlgorithm implements PreciseShardingAlgorithm<Integer> {
@Override
public String doSharding(Collection<String> availableTargetNames, PreciseShardingValue<Integer> shardingValue) {
for (String tableName : availableTargetNames) {
if (tableName.endsWith(shardingValue.getValue() % 4 + "" )) {
return tableName;
}
}
throw new IllegalArgumentException();
}
}
4、mybatis操作数据库配置
User.java
package com.demo.shardingjdbc.entity;
import java.io.Serializable;
import lombok.Data;
@Data
public class User implements Serializable {
private static final long serialVersionUID = - 1205226416664488559L ;
private Integer id;
private String name;
private String sex;
}
mapper层
package com.demo.shardingjdbc.mapper;
import org.apache.ibatis.annotations.Mapper;
import com.demo.shardingjdbc.entity.User;
import java.util.List;
@Mapper
public interface UserMapper {
Integer addUser(User user);
List <User> list();
}
mybatis配置文件mybatis-config.xml
<?xml version= " 1.0 " encoding= " UTF-8 " ?>
<! DOCTYPE configuration
PUBLIC " -//mybatis.org//DTD Config 3.0//EN "
" http://mybatis.org/dtd/mybatis-3-config.dtd " >
<configuration>
<typeAliases>
<package name= " com.demo.shardingjdbc.entity " />
</typeAliases>
<mappers>
<mapper resource= " META-INF/mappers/User.xml " />
</mappers>
</configuration>
user.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.demo.shardingjdbc.mapper.UserMapper " >
<resultMap id= " baseResultMap " type= " com.demo.shardingjdbc.entity.User " >
<result column= " id " property= " id " jdbcType= " INTEGER " />
<result column= " name " property= " name " jdbcType= " VARCHAR " />
<result column= " sex " property= " sex " jdbcType= " VARCHAR " />
</resultMap>
<insert id= " addUser " parameterType= " com.demo.shardingjdbc.entity.User " >
INSERT INTO t_user (
id, name, sex
)
VALUES (
#{id,jdbcType = INTEGER},
#{name,jdbcType = VARCHAR},
#{sex,jdbcType = VARCHAR}
)
</insert>
< select id= " list " resultMap= " baseResultMap " >
SELECT u. * FROM t_user u order by u.id
</ select >
</mapper>
5、service层
package com.demo.shardingjdbc.service.impl;
import com.demo.shardingjdbc.entity.User;
import com.demo.shardingjdbc.mapper.UserMapper;
import com.demo.shardingjdbc.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public Integer addUser(User user) {
// 强制路由主库
return userMapper.addUser(user);
}
@Override
public List<User> list() {
return userMapper.list();
}
}
6、controller层
package com.demo.shardingjdbc.controller;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import com.demo.shardingjdbc.entity.User;
import com.demo.shardingjdbc.service.UserService;
import lombok. extern .slf4j.Slf4j;
@RestController
@Slf4j
public class UserController {
@Autowired
private UserService userService;
@GetMapping( " /users " )
public Object list() {
return userService.list();
}
@GetMapping( " /add " )
public Object add() {
int num= 0 ;
for ( int i= 1 ;i<= 300 ;i++ ) {
User user = new User();
user.setId(i);
user.setName( " hzy " + (i));
String sex =(i% 2 == 0 )? " 男 " : " 女 " ;
user.setSex(sex);
int resutl= userService.addUser(user);
log.info( " insert: " +user.toString()+ " result: " + resutl);
num =num+ resutl;
}
return num;
}
}
完成。在浏览器上执行localhost:8080/add，然后去数据库中查询，可以看到test1.t_user_0、test1.t_user_2、test2.t_user_1、test2.t_user_3分别插入了数据。
然后访问localhost:8080/users，可以查询数据库中四个表中的所有数据。可见Sharding-JDBC在插入数据的时候，根据数据分库分表策略，将数据存储在不同库不同表中，查询时将数据库从多个表中查询并聚合。
Spring Boot整合Sharding-JDBC实现分库分表+读写分离io.shardingsphere(4)
标签：uid efi random rop efault author rod epo work
查看更多关于Spring Boot整合Sharding-JDBC实现分库分表+读写分离io.shardingsp的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did117824