前言
上文我们介绍Sharding-JDBC 分库分表(ShardingSphere 数据分片 ||链接:https://blog.csdn.net/weixin_38003389/article/details/90518112),我是用的是 java的配置方式,缺点就是有代码入侵性,其实我也完全可以用配置实现的。本文就给大家介绍一下 Sharding-JDBC 基于 配置进行读写分离。
正文
开始之前我们有两项很重要的准备工作
第一项是在两台网络互通的 Centos7服务器安装mysql 5.7,请点我写好的安装步骤:https://blog.csdn.net/weixin_38003389/article/details/90696337
第二项是把这两台 mysql 配置成主从,请点我写好的配置步骤:
https://blog.csdn.net/weixin_38003389/article/details/90717879
以上工作都做好了之后我们来看一下怎么用 sharding-jdbc 配置来实现读写分离的。
创建一个maven 工程,pom文件如下
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.5.RELEASE</version>
<relativePath/>
</parent>
<groupId>sharding-master-slave</groupId>
<artifactId>sharding-master-slave</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>spring-boot-sharding-table</name>
<description>基于 Spring Boot 2.1.5 使用sharding-sphere + JdbcTemplate 实现读写分离</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>3.1.0</version>
</dependency>
<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>3.1.0</version>
</dependency>
</dependencies>
核心配置类
# 配置真实数据源
sharding.jdbc.datasource.names=master,slave
#主数据库
sharding.jdbc.datasource.master.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.master.hikari.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.master.jdbc-url=jdbc:mysql://10.8.18.73:3306/master?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
sharding.jdbc.datasource.master.username=root
sharding.jdbc.datasource.master.password=123456
# 从数据库
sharding.jdbc.datasource.slave.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.slave.hikari.driver-class-name=com.mysql.cj.jdbc.Driver
sharding.jdbc.datasource.slave.jdbc-url=jdbc:mysql://10.8.18.74:3306/master?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
sharding.jdbc.datasource.slave.username=root
sharding.jdbc.datasource.slave.password=123456
# 配置读写分离
# 配置从库选择策略,提供轮询与随机,这里选择用轮询
sharding.jdbc.config.masterslave.load-balance-algorithm-type=round_robin
sharding.jdbc.config.masterslave.name=ms
sharding.jdbc.config.masterslave.master-data-source-name=master
sharding.jdbc.config.masterslave.slave-data-source-names=slave
# 开启SQL显示,默认值: false,注意:仅配置读写分离时不会打印日志
sharding.jdbc.config.props.sql.show=true
spring.main.allow-bean-definition-overriding=true
测试
下面的类均是为了测试读写分离写的
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
@RestController
public class OrderController {
@Autowired
private OrderDao orderDao;
@RequestMapping(path = "/createOrder/{userId}/{orderId}", method = {RequestMethod.GET})
public String createOrder(@PathVariable("userId") Integer userId, @PathVariable("orderId") Integer orderId) {
Order order = new Order();
order.setOrderId(orderId);
order.setUserId(userId);
orderDao.createOrder(order);
return "success";
}
@RequestMapping(path = "/{userId}", method = {RequestMethod.GET})
public List<Order> getOrderListByUserId(@PathVariable("userId") Integer userId) {
return orderDao.getOrderListByUserId(userId);
}
@RequestMapping(path = "/", method = {RequestMethod.GET})
public List<Order> getOrderListByUserId() {
return orderDao.getOrderList();
}
}
public interface OrderDao {
List<Order> getOrderListByUserId(Integer userId);
List<Order> getOrderList();
void createOrder(Order order);
}
@Service
public class OrderDaoImpl implements OrderDao {
@Autowired
JdbcTemplate jdbcTemplate;
@Override
public List<Order> getOrderListByUserId(Integer userId) {
// StringBuilder sqlBuilder = new StringBuilder();
// sqlBuilder
// .append("select order_id, user_id from order where user_id=? ");
// return jdbcTemplate.query(sqlBuilder.toString(), new Object[]{userId},
// new int[]{Types.INTEGER}, new BeanPropertyRowMapper<Order>(
// Order.class));
return null;
}
@Override
public List<Order> getOrderList() {
String sql = "select order_id, user_id from order_0 ";
return jdbcTemplate.query(sql, new BeanPropertyRowMapper<Order>(
Order.class));
}
@Override
public void createOrder(Order order) {
StringBuffer sb = new StringBuffer();
sb.append("insert into order_0(user_id, order_id)");
sb.append("values(");
sb.append(order.getUserId()).append(",");
sb.append(order.getOrderId());
sb.append(")");
jdbcTemplate.update(sb.toString());
}
}
下面我们启动项目进行数据的增加和查询就可以了,观察控制台的打印信息,即我增加的时候数据源连接 master,我查询的时候数据源连接slave,读写分离成功。
在这个过程还碰到了很多坑,大多是在mysql配置问题,
首先拿到 root 是必须的,如果项目启动报错信息是与服务器连接超时时,我给定以下解决思路
- 本地与服务器网络是否通
- 防火墙是否关闭
- mysql 连接账号是否允许外置连接
github 地址:https://github.com/362460453/sharding-master-slave
如果你们碰到其他的问题可以自行百度或加我微信 。