Sharding-JDBC 概览
Sharding-JDBC 定位为轻量级Java框架,在Java的JDBC层提供的额外服务。 它使用客户端直连数据库,以jar包形式提供服务,无需额外部署和依赖,可理解为增强版的JDBC驱动,完全兼容JDBC和各种 ORM框架。
适用于任何基于Java的ORM框架,如:JPA, Hibernate, Mybatis, Spring JDBC Template或 直接使用JDBC。 基于任何第三方的数据库连接池,如:DBCP, C3P0, BoneCP, Druid, HikariCP等。 支持任意实现JDBC规范的数据库。目前支持MySQL,Oracle,SQLServer和 PostgreSQL
读写分离
本文以实现了mysql主从复制为前提,通过spirngboot整合,实现读写分离
在主节点创建订单表
create table t_order(orderId int primary key auto_increment,orderTime int,customerId int);
编写java客户端代码
pom文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.1.3.RELEASE</version>
<relativePath/>
</parent>
<groupId>com.sunyuqi</groupId>
<artifactId>sharding-jdbc-study</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>sharding-jdbc-study</name>
<description>Demo project for Spring Boot</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.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.6</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.0.0-RC1</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.14</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
配置文件
mybatis:
configuration:
map-underscore-to-camel-case: true
spring:
main:
allow-bean-definition-overriding: true
shardingsphere:
datasource:
names: ds0,ds1
ds0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.130.128:3306/testdb?useSSL=false&serverTimezone=UTC
username: root
password: qwe123
maxPoolSize: 50
minPoolSize: 1
ds1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://192.168.130.129:3306/testdb?useSSL=false&serverTimezone=UTC
username: root
password: qwe123
maxPoolSize: 50
minPoolSize: 1
masterslave:
# 读写分离配置
load-balance-algorithm-type: round_robin
# 最终的数据源名称
name: ds0
# 主库数据源名称
master-data-source-name: ds0
# 从库数据源名称列表,多个逗号分隔
slave-data-source-names: ds1
props:
# 开启SQL显示,默认false
sql:
show: true
ds0为主节点,用于写数据,ds1为从节点,用于读数据。
model
package com.sunyuqi.model;
public class Order {
private Long orderId;
private String orderTime;
private Long customerId;
public Long getOrderId() {
return orderId;
}
public void setOrderId(Long orderId) {
this.orderId = orderId;
}
public String getOrderTime() {
return orderTime;
}
public void setOrderTime(String orderTime) {
this.orderTime = orderTime;
}
public Long getCustomerId() {
return customerId;
}
public void setCustomerId(Long customerId) {
this.customerId = customerId;
}
}
dao
package com.sunyuqi.dao;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import com.sunyuqi.model.Order;
@Mapper
public interface OrderDao {
@Insert("insert into t_order(orderTime,customerId) values(UNIX_TIMESTAMP(#{orderTime}),#{customerId})")
void addOrder(Order o);
@Select("select orderId,FROM_UNIXTIME(orderTime) as orderTime,customerId from t_order where orderId=#{id}")
Order get(@Param("id")Long orderId);
}
service:
package com.sunyuqi.service;
import com.sunyuqi.dao.OrderDao;
import com.sunyuqi.model.Order;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
@Service
public class OrderService {
@Autowired
private OrderDao orderDao;
public void addOrder(Order o) {
this.orderDao.addOrder(o);
}
public Order getOrder(Long orderId) {
return this.orderDao.get(orderId);
}
}
controller
package com.sunyuqi.controller;
import com.sunyuqi.model.Order;
import com.sunyuqi.service.OrderService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
@Controller
@RequestMapping("/order")
public class OrderController {
@Autowired
private OrderService orderService;
@RequestMapping("add")
@ResponseBody
public String addOrder(Order order) {
order.setOrderTime(LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
this.orderService.addOrder(order);
return "添加成功!!";
}
@RequestMapping("get")
@ResponseBody
public Order getOrder(Long orderId) {
return this.orderService.getOrder(orderId);
}
}
引导类:
package com.sunyuqi;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
public class ShardingJdbcStudyApplication {
public static void main(String[] args) {
SpringApplication.run(ShardingJdbcStudyApplication.class, args);
}
}
运行引导类,测试读写分离,我们首先插入一条数据
控制台输出如下:
可以看到插入是在主节点中进行的。
现在我们查询该数据
控制台输出如下:
查询是在从节点中进行的。
这就实现了读写分离。