数据库中间件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);
	}
}

运行引导类,测试读写分离,我们首先插入一条数据
在这里插入图片描述
控制台输出如下:
在这里插入图片描述
可以看到插入是在主节点中进行的。
现在我们查询该数据
在这里插入图片描述
控制台输出如下:
在这里插入图片描述
查询是在从节点中进行的。
这就实现了读写分离。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值