本文介紹SpringBoot使用當(dāng)當(dāng)Sharding-JDBC進(jìn)行讀寫分離博肋。

1.有關(guān)Sharding-JDBC

本文還是基于當(dāng)當(dāng)網(wǎng)Sharding-Jdbc的依賴偷拔，與上一篇使用Sharding-Jdbc進(jìn)行分庫(kù)分表依賴一致彪见，并且本文大致內(nèi)容與上一篇文章相似黔衡，建議先查看我的另一篇在查看這篇會(huì)簡(jiǎn)單許多届谈，傳送門《SpringBoot使用Sharding-JDBC分庫(kù)分表》晓褪。

這里需要特殊介紹的是魔吐，使用Sharding-JDBC進(jìn)行讀寫分離的時(shí)候扎筒，只允許設(shè)置一個(gè)主庫(kù)，從庫(kù)的話可以設(shè)置多個(gè)酬姆，訪問(wèn)策略的話從源碼上看只有兩種輪詢（ROUND_ROBIN）和隨機(jī)（RANDOM）嗜桌。

源碼代碼如下:

package com.dangdang.ddframe.rdb.sharding.api.strategy.slave;

public enum MasterSlaveLoadBalanceStrategyType {
    ROUND_ROBIN(new RoundRobinMasterSlaveLoadBalanceStrategy()),
    RANDOM(new RandomMasterSlaveLoadBalanceStrategy());

    private final MasterSlaveLoadBalanceStrategy strategy;

    public static MasterSlaveLoadBalanceStrategyType getDefaultStrategyType() {
        return ROUND_ROBIN;
    }

    private MasterSlaveLoadBalanceStrategyType(MasterSlaveLoadBalanceStrategy strategy) {
        this.strategy = strategy;
    }

    public MasterSlaveLoadBalanceStrategy getStrategy() {
        return this.strategy;
    }
}

2.本文場(chǎng)景

由于本地環(huán)境并沒(méi)有使用Mysql主從復(fù)制，只是創(chuàng)建了三個(gè)庫(kù)辞色，其中database0作為主庫(kù)骨宠，database1和database2作為從庫(kù)。主庫(kù)進(jìn)行增刪改操作相满，從庫(kù)進(jìn)行查詢操作层亿，如下圖為本文數(shù)據(jù)庫(kù)的三個(gè)表。

image

如上圖分別是三個(gè)數(shù)據(jù)庫(kù)中的user表立美，其中master-user為database0數(shù)據(jù)庫(kù)中的user表匿又，salve-user1為database1中的user表，salve-user2為database2中的user表建蹄。

3.代碼實(shí)現(xiàn)

本文使用SpringBoot2.0.3碌更，SpringData-JPA裕偿，Druid連接池，和當(dāng)當(dāng)?shù)膕harding-jdbc痛单。

3.1 建表SQL

創(chuàng)建表和數(shù)據(jù)庫(kù)的SQL如下所示击费，這里默認(rèn)在從庫(kù)內(nèi)分別插入了一條數(shù)據(jù)，name值分別存放dalaoyang1和dalaoyang2便于區(qū)分桦他。

CREATE DATABASE database0;
USE database0;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`(
    id bigint(64) not null,
    city varchar(20) not null,
    name varchar(20) not null,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE DATABASE database1;
USE database1;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`(
    id bigint(64) not null,
    city varchar(20) not null,
    name varchar(20) not null,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `database1`.`user`(`id`, `city`, `name`) VALUES (101, 'beijing', 'dalaoyang1');

CREATE DATABASE database2;
USE database2;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`(
    id bigint(64) not null,
    city varchar(20) not null,
    name varchar(20) not null,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `database2`.`user`(`id`, `city`, `name`) VALUES (102, 'beijing', 'dalaoyang2');

3.2 依賴文件

新建項(xiàng)目蔫巩，依賴文件還是當(dāng)當(dāng)?shù)膕harding-jdbc-core依賴和druid連接池，完整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.0.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.dalaoyang</groupId>
    <artifactId>springboot2_shardingjdbc_dxfl</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springboot2_shardingjdbc_dxfl</name>
    <description>springboot2_shardingjdbc_dxfl</description>

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

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </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>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.9</version>
        </dependency>
        <!-- sharding-jdbc -->
        <dependency>
            <groupId>com.dangdang</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>1.5.4</version>
        </dependency>
    </dependencies>

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

</project>

3.3 配置信息

在配置信息中配置了三個(gè)數(shù)據(jù)庫(kù)的信息和JPA的簡(jiǎn)單配置圆仔。

##Jpa配置
spring.jpa.database=mysql
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none

##數(shù)據(jù)庫(kù)配置
##數(shù)據(jù)庫(kù)database0地址
database0.url=jdbc:mysql://localhost:3306/database0?characterEncoding=utf8&useSSL=false
##數(shù)據(jù)庫(kù)database0用戶名
database0.username=root
##數(shù)據(jù)庫(kù)database0密碼
database0.password=root
##數(shù)據(jù)庫(kù)database0驅(qū)動(dòng)
database0.driverClassName=com.mysql.jdbc.Driver
##數(shù)據(jù)庫(kù)database0名稱
database0.databaseName=database0

##數(shù)據(jù)庫(kù)database1地址
database1.url=jdbc:mysql://localhost:3306/database1?characterEncoding=utf8&useSSL=false
##數(shù)據(jù)庫(kù)database1用戶名
database1.username=root
##數(shù)據(jù)庫(kù)database1密碼
database1.password=root
##數(shù)據(jù)庫(kù)database1驅(qū)動(dòng)
database1.driverClassName=com.mysql.jdbc.Driver
##數(shù)據(jù)庫(kù)database1名稱
database1.databaseName=database1

##數(shù)據(jù)庫(kù)database2地址
database2.url=jdbc:mysql://localhost:3306/database2?characterEncoding=utf8&useSSL=false
##數(shù)據(jù)庫(kù)database1用戶名
database2.username=root
##數(shù)據(jù)庫(kù)database1密碼
database2.password=root
##數(shù)據(jù)庫(kù)database1驅(qū)動(dòng)
database2.driverClassName=com.mysql.jdbc.Driver
##數(shù)據(jù)庫(kù)database1名稱
database2.databaseName=database2

3.4 啟動(dòng)類

上一篇文章說(shuō)到在啟動(dòng)類加入了@EnableAutoConfiguration去除數(shù)據(jù)庫(kù)自動(dòng)配置，當(dāng)時(shí)也沒(méi)太注意蔫劣，其實(shí)可以直接在@SpringBootApplication注解上去除數(shù)據(jù)庫(kù)自動(dòng)配置坪郭，剩下的和上一篇一樣，使用@EnableTransactionManagement開(kāi)啟事務(wù)脉幢，使用@EnableConfigurationProperties注解加入配置實(shí)體歪沃，啟動(dòng)類完整代碼如下所示。

package com.dalaoyang;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@EnableTransactionManagement(proxyTargetClass = true)
@EnableConfigurationProperties
public class Springboot2ShardingjdbcDxflApplication {

    public static void main(String[] args) {
        SpringApplication.run(Springboot2ShardingjdbcDxflApplication.class, args);
    }

}

3.5 實(shí)體類和數(shù)據(jù)庫(kù)操作層

User實(shí)體類嫌松。

package com.dalaoyang.entity;

import lombok.Data;

import javax.persistence.*;

@Entity
@Table(name="user")
@Data
public class User {

    @Id
    private Long id;

    private String city;

    private String name;
}

UserRepository類沪曙。

package com.dalaoyang.repository;

import com.dalaoyang.entity.User;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User,Long> {
}

3.6 數(shù)據(jù)庫(kù)參數(shù)類

數(shù)據(jù)庫(kù)配置類，Database0Config萎羔。

package com.dalaoyang.database;

import com.alibaba.druid.pool.DruidDataSource;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;

/**
 * @author yangyang
 * @date 2019/1/30
 */
@Data
@ConfigurationProperties(prefix = "database0")
@Component
public class Database0Config {
    private String url;
    private String username;
    private String password;
    private String driverClassName;
    private String databaseName;

    public DataSource createDataSource() {
        DruidDataSource result = new DruidDataSource();
        result.setDriverClassName(getDriverClassName());
        result.setUrl(getUrl());
        result.setUsername(getUsername());
        result.setPassword(getPassword());
        return result;
    }
}

數(shù)據(jù)庫(kù)配置類液走，Database1Config。

package com.dalaoyang.database;

import com.alibaba.druid.pool.DruidDataSource;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;

/**
 * @author yangyang
 * @date 2019/1/30
 */
@Data
@ConfigurationProperties(prefix = "database1")
@Component
public class Database1Config {
    private String url;
    private String username;
    private String password;
    private String driverClassName;
    private String databaseName;

    public DataSource createDataSource() {
        DruidDataSource result = new DruidDataSource();
        result.setDriverClassName(getDriverClassName());
        result.setUrl(getUrl());
        result.setUsername(getUsername());
        result.setPassword(getPassword());
        return result;
    }
}

數(shù)據(jù)庫(kù)配置類贾陷，Database2Config缘眶。

package com.dalaoyang.database;

import com.alibaba.druid.pool.DruidDataSource;
import lombok.Data;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;

/**
 * @author yangyang
 * @date 2019/1/30
 */
@Data
@ConfigurationProperties(prefix = "database2")
@Component
public class Database2Config {
    private String url;
    private String username;
    private String password;
    private String driverClassName;
    private String databaseName;

    public DataSource createDataSource() {
        DruidDataSource result = new DruidDataSource();
        result.setDriverClassName(getDriverClassName());
        result.setUrl(getUrl());
        result.setUsername(getUsername());
        result.setPassword(getPassword());
        return result;
    }
}

3.7 讀寫分離配置

創(chuàng)建一個(gè)DataSourceConfig類來(lái)設(shè)置讀寫分離，這里其實(shí)也與分庫(kù)分表類似髓废，也可以在分庫(kù)分表的基礎(chǔ)上進(jìn)行讀寫分離巷懈，需要?jiǎng)?chuàng)建一個(gè)Map集合來(lái)接收從庫(kù)。在創(chuàng)建數(shù)據(jù)源時(shí)需要傳入五個(gè)參數(shù)慌洪，分別是：

• name：數(shù)據(jù)源名稱
• masterDataSourceName：主庫(kù)數(shù)據(jù)源名稱
• masterDataSource：主數(shù)據(jù)源
• slaveDataSourceMap：從數(shù)據(jù)源集合
• strategyType：訪問(wèn)策略

當(dāng)然顶燕，也可以使用其他方法創(chuàng)建數(shù)據(jù)源，本文代碼如下：

package com.dalaoyang.database;


import com.dangdang.ddframe.rdb.sharding.api.MasterSlaveDataSourceFactory;
import com.dangdang.ddframe.rdb.sharding.api.strategy.slave.MasterSlaveLoadBalanceStrategyType;
import com.dangdang.ddframe.rdb.sharding.keygen.DefaultKeyGenerator;
import com.dangdang.ddframe.rdb.sharding.keygen.KeyGenerator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 * @author yangyang
 * @date 2019/1/29
 */
@Configuration
public class DataSourceConfig {

    @Autowired
    private Database0Config database0Config;

    @Autowired
    private Database1Config database1Config;

    @Autowired
    private Database2Config database2Config;

    @Bean
    public DataSource getDataSource() throws SQLException {
        return buildDataSource();
    }

    private DataSource buildDataSource() throws SQLException {
        //設(shè)置從庫(kù)數(shù)據(jù)源集合
        Map<String, DataSource> slaveDataSourceMap = new HashMap<>();
        slaveDataSourceMap.put(database1Config.getDatabaseName(), database1Config.createDataSource());
        slaveDataSourceMap.put(database2Config.getDatabaseName(), database2Config.createDataSource());

        //獲取數(shù)據(jù)源對(duì)象
        DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource("masterSlave",database0Config.getDatabaseName()
                ,database0Config.createDataSource(), slaveDataSourceMap, MasterSlaveLoadBalanceStrategyType.getDefaultStrategyType());
        return dataSource;
    }


    @Bean
    public KeyGenerator keyGenerator() {
        return new DefaultKeyGenerator();
    }

}

3.8 Controller

Controller做為測(cè)試類蒋譬，創(chuàng)建兩個(gè)方法割岛，save方法和getAll方法愉适，其中：

• save方法用于測(cè)試主庫(kù)的插入和修改
• getAll方法用于測(cè)試讀數(shù)據(jù)

UserController類如下所示犯助。

package com.dalaoyang.controller;

import com.dalaoyang.entity.User;
import com.dalaoyang.repository.UserRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class UserController {

    @Autowired
    private UserRepository userRepository;

    @GetMapping("save")
    public void save(){
        User user = new User();
        user.setId(100L);
        user.setName("dalaoyang");
        user.setCity("beijing");
        userRepository.save(user);
    }

    @GetMapping("getAll")
    public Object getAll(){
        return userRepository.findAll();
    }
}

4.測(cè)試

4.1 測(cè)試主庫(kù)

使用postman訪問(wèn)http://localhost:8080/save，控制臺(tái)如圖所示维咸。

image

再次訪問(wèn)剂买，如圖惠爽。

image

主鍵沖突了，其實(shí)這是由于插入的時(shí)候使用的database0瞬哼，但是查詢使用的是database1和database2婚肆，但是我在從庫(kù)內(nèi)并沒(méi)有ID是100的數(shù)據(jù)，所以JPA判定我為插入坐慰，但是數(shù)據(jù)庫(kù)內(nèi)缺有這樣的數(shù)據(jù)较性。

我們接下來(lái)測(cè)試一下查詢。訪問(wèn)http://localhost:8080/getAll

image

再次訪問(wèn)结胀，如圖赞咙。

image

證明從庫(kù)的讀取是正常的，接下來(lái)修改從庫(kù)的ID為100糟港。然后訪問(wèn)http://localhost:8080/save攀操，查看控制臺(tái)如圖。

image

因?yàn)榇嬖诹薎D為100的數(shù)據(jù)秸抚，所以SQL為修改語(yǔ)句速和。

5.源碼

源碼地址：https://gitee.com/dalaoyang/springboot_learn/tree/master/springboot2_shardingjdbc_dxfl