資料庫中介軟體sharding-jdbc實現讀寫分離

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);
	}
}

執行引導類，測試讀寫分離，我們首先插入一條資料

控制檯輸出如下：

可以看到插入是在主節點中進行的。
現在我們查詢該資料

控制檯輸出如下：

查詢是在從節點中進行的。
這就實現了讀寫分離。