全网最全最详细的Sharding-JDBC入门

背景

面对日益增加的系统访问量，数据库的吞吐量面临着巨大瓶颈。对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说，将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大的改善。

如图，将数据库执行读写操作由一台变成两台：

Sharding-JDBC介绍

Sharding-JDBC定位为轻量级Java框架,在Java的JDBC层提 供的额外服务。它使用客户端直连数据库,以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

使用Sharding-JDBC可以在程序中轻松的实现数据库读写分离。

1、适用于任何基于JDBC的ORM框架，如: JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。

2、支持任何第三方的数据库连接池，如: DBCP, C3PO, BoneCP, Druid, HikariCP等。

3、支持任意实现JDBC规范的数据库。目前支持MySQL, Oracle, SQLServer, PostgreSQL以及任何遵循SQL92标准的数据库。

Sharding-JDBC入门案例

准备工作，MySQL必须实现主从复制

1、安装MySQL：

Linux-18-软件安装_安装MySQL_哔哩哔哩_bilibili

2、实现主从复制：

项目优化Day2-03-MySQL主从复制_配置主库Master&从库Slave_哔哩哔哩_bilibili

开始使用Sharding-JDBC实现读写分离

1、创建一个maven工程，导入maven坐标

<?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.4.5</version>
        <relativePath/>
        <!--   lookup parent from repository   -->
    </parent>

    <groupId>com.sj</groupId>
    <artifactId>sharding-jdbc</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.20</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.76</version>
        </dependency>
        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>2.6</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.23</version>
        </dependency>
        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <version>2.4.5</version>
            </plugin>
        </plugins>
    </build>
    
</project>

2、编写yml配置文件

server:
  port: 7777
spring:
  shardingsphere:
    datasource:
      names:
        master,slave
      # 主数据源
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.11.101:3306/test?characterEncoding=utf-8
        username: root
        password: root
      # 从数据源
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://192.168.11.102:3306/test?characterEncoding=utf-8
        username: root
        password: root
    masterslave:
      # 读写分离配置
      load-balance-algorithm-type: round_robin  #轮询
      # 最终数据源名称
      name: dataSource
      # 主库数据源名称
      master-data-source-name: master
      # 从库数据源名称列表，多个逗号隔开
      slave-data-source-names: slave
    props:
      sql:
        #开启sql显示，默认为false
        show: true
  main:
    allow-bean-definition-overriding: true
mybatis-plus:
  configuration:
    #在映射实体或者属性时，将数据库中表名和字段名中的下划线去掉，按照驼峰命名法映射
    map-underscore-to-camel-case: true
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      id-type: ASSIGN_ID

3、编写dao层，pojo层，service层，controller层，启动类进行测试

dao层：

package com.sj.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.sj.pojo.User;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserMapper extends BaseMapper<User> {
}

pojo层：

package com.sj.pojo;

import lombok.Data;

import java.io.Serializable;

@Data
public class User implements Serializable {
    private static final long serialVersionUID = 1L;
    private Long id;
    private String name;
    private Integer age;
    private String address;
}

service层：

package com.sj.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.sj.pojo.User;

import java.util.List;

public interface UserService extends IService<User> {

    User saveUser(User user);

    String deleteUser(Long id);

    String updateUser(User user);

    User getUserById(Long id);

    List<User> listUsers(User user);
}

package com.sj.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.sj.dao.UserMapper;
import com.sj.pojo.User;
import com.sj.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.List;

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

    @Autowired
    private DataSource dataSource;

    @Resource
    private UserMapper userMapper;

    /**
     * 新增用户
     * @param user
     * @return
     */
    @Override
    public User saveUser(User user) {
        userMapper.insert(user);
        return user;
    }

    /**
     * 删除用户
     * @param id 用户id
     */
    @Override
    public String deleteUser(Long id) {
        int deleteUser = userMapper.deleteById(id);
        return deleteUser > 0 ? "删除用户成功！！！" : "删除用户失败！！！";
    }

    /**
     * 修改用户信息
     * @param user
     * @return
     */
    @Override
    public String updateUser(User user) {
        int updateUser = userMapper.updateById(user);
        return updateUser > 0 ? "修改用户成功！！！" : "修改用户失败！！！";
    }

    /**
     * 根据id查询用户信息
     * @param id 用户id
     * @return
     */
    @Override
    public User getUserById(Long id) {
        return userMapper.selectById(id);
    }

    /**
     * 根据条件查询用户信息
     * @param user
     * @return
     */
    @Override
    public List<User> listUsers(User user) {
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(user.getId()!=null,User::getId,user.getId());
        queryWrapper.eq(user.getName()!=null,User::getName,user.getName());
        return userMapper.selectList(queryWrapper);
    }
}

controller层：

package com.sj.controller;

import com.sj.pojo.User;
import com.sj.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private UserService userService;

    @PostMapping
    public User saveUser(User user) {
        return userService.saveUser(user);
    }

    @DeleteMapping("/{id}")
    public String deleteUser(@PathVariable Long id) {
        return userService.deleteUser(id);
    }

    @PutMapping
    public String updateUser(User user){
        return userService.updateUser(user);
    }

    @GetMapping("/{id}")
    public User getUserById(@PathVariable Long id){
        return userService.getUserById(id);
    }

    @GetMapping("/list")
    public List<User> listUsers(User user){
        return userService.listUsers(user);
    }
}

启动类：

package com.sj;

import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@Slf4j
@SpringBootApplication
public class ShardingJdbcApplication {
    public static void main(String[] args) {
        SpringApplication.run(ShardingJdbcApplication.class,args);
        log.info("项目启动成功......");
    }
}

查询测试：

localhost:7777/user/123

新增测试：

localhost:7777/user?name=陈东&age=19

修改测试：

localhost:7777/user?name=东升&age=19&id=1588946291784720386

删除测试：

localhost:7777/user/1588946291784720386