Spring-Data-JPA +Sharding-jdbc+druid数据库连接池实现数据库读写分离

我会带着你远行 2023-10-10 11:33 27阅读 0赞

## 关于Sharding-jdbc 简介 ##

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

*  适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。
 *  支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。
 *  支持任意实现JDBC规范的数据库。目前支持MySQL，Oracle，SQLServer，PostgreSQL以及任何遵循SQL92标准的数据库。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70][]

官方文档地址:[https://shardingsphere.apache.org/document/legacy/4.x/document/cn/overview/\#sharding-jdbc][https_shardingsphere.apache.org_document_legacy_4.x_document_cn_overview_sharding-jdbc]

## 本文场景： ##

由于本地环境并没有使用Mysql主从复制，只是创建了二个库，其中database0作为主库，database1作为从库。主库进行增删改操作，从库进行查询操作，如下图为各个数据库的三张表。

主库：

![2020091400264074.png][]

![20200914002702867.png][]

丛库：

![20200914002547545.png][]

### 建表SQL ###

CREATE TABLE IF NOT EXISTS `author` (
      `id` bigint(10) NOT NULL AUTO_INCREMENT,
      `name` varchar(32) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    
    CREATE TABLE IF NOT EXISTS `book` (
      `id` bigint(10) NOT NULL AUTO_INCREMENT,
      `name` varchar(32) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
    
    CREATE TABLE IF NOT EXISTS `book_author` (
      `book_id` bigint(10) NOT NULL,
      `author_id` bigint(10) NOT NULL,
      KEY `author_id` (`author_id`),
      KEY `book_id` (`book_id`),
      CONSTRAINT `author_id` FOREIGN KEY (`author_id`) REFERENCES `author` (`id`),
      CONSTRAINT `book_id` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

## Spring-Data-JPA源码： ##

### pom.xml 文件依赖 ###

<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>com.digipower</groupId>
    		<artifactId>digipower-ureport</artifactId>
    		<version>0.0.1-SNAPSHOT</version>
    	</parent>
    	<artifactId>digipower-oa</artifactId>
    
    	
    	<properties>
    		<javax.servlet-api.version>3.1.0</javax.servlet-api.version>
    		<mybatis-spring-boot-starter.version>1.3.2</mybatis-spring-boot-starter.version>
    		<mysql-connector-java.version>8.0.11</mysql-connector-java.version>
    		<druid-spring-boot-starter.version>1.1.9</druid-spring-boot-starter.version>
    		<commons-lang.version>2.6</commons-lang.version>
    		<commons-codec.version>1.10</commons-codec.version>
    		<commons-lang3.version>3.9</commons-lang3.version>
    		<commons-net.version>3.6</commons-net.version>
    		<commons-io.version>2.6</commons-io.version>
    		<commons-collections.version>3.2.1</commons-collections.version>
    		<commons-text.version>1.8</commons-text.version>
    		<common-fileupload.version>1.3.1</common-fileupload.version>
    	</properties>
    
    	<dependencies>
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-web</artifactId>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-test</artifactId>
    			<scope>test</scope>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-aop</artifactId>
    		</dependency>
    		
    		<dependency>
    			<groupId>javax.servlet</groupId>
    			<artifactId>javax.servlet-api</artifactId>
    			<version>${javax.servlet-api.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.springframework.boot</groupId>
    			<artifactId>spring-boot-starter-data-jpa</artifactId>
    		</dependency>
    		<dependency>
    			<groupId>mysql</groupId>
    			<artifactId>mysql-connector-java</artifactId>
    			<version>${mysql-connector-java.version}</version>
    			<scope>runtime</scope>
    		</dependency>
    		
    		<dependency>
    			<groupId>com.alibaba</groupId>
    			<artifactId>druid-spring-boot-starter</artifactId>
    			<version>${druid-spring-boot-starter.version}</version>
    		</dependency>
    		
    	 	<dependency>
                <groupId>com.dangdang</groupId>
                <artifactId>sharding-jdbc-core</artifactId>
                <version>1.5.4</version>
            </dependency>
    		
    		<dependency>
    			<groupId>io.springfox</groupId>
    			<artifactId>springfox-swagger2</artifactId>
    			<version>2.7.0</version>
    		</dependency>
    		<dependency>
    			<groupId>io.springfox</groupId>
    			<artifactId>springfox-swagger-ui</artifactId>
    			<version>2.7.0</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>com.alibaba</groupId>
    			<artifactId>fastjson</artifactId>
    			<version>1.2.48</version>
    		</dependency>
    		
    		
    		<dependency>
    			<groupId>commons-lang</groupId>
    			<artifactId>commons-lang</artifactId>
    			<version>${commons-lang.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.apache.commons</groupId>
    			<artifactId>commons-lang3</artifactId>
    			<version>${commons-lang3.version}</version>
    		</dependency>
    
    		
    		<dependency>
    			<groupId>commons-codec</groupId>
    			<artifactId>commons-codec</artifactId>
    			<version>${commons-codec.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>commons-net</groupId>
    			<artifactId>commons-net</artifactId>
    			<version>${commons-net.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>commons-io</groupId>
    			<artifactId>commons-io</artifactId>
    			<version>${commons-io.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>commons-collections</groupId>
    			<artifactId>commons-collections</artifactId>
    			<version>${commons-collections.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>commons-fileupload</groupId>
    			<artifactId>commons-fileupload</artifactId>
    			<version>${common-fileupload.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.apache.commons</groupId>
    			<artifactId>commons-text</artifactId>
    			<version>${commons-text.version}</version>
    		</dependency>
    		
    		<dependency>
    			<groupId>org.projectlombok</groupId>
    			<artifactId>lombok</artifactId>
    			<version>1.18.2</version>
    			<scope>provided</scope>
    		</dependency>
    	</dependencies>
    </project>

### 资源文件定义和程序入口 ###

application.properties

# 指定服务端口
    server.port=9098
    # 指定服务 名称
    # server.context-path=/ureport
    #jpa配置
    spring.jpa.hibernate.ddl-auto=none
    spring.jpa.show-sql=true
    # MyBatis mysql8 主从配置
    #spring.datasource.url=jdbc:mysql://127.0.0.1:3306/oasys?serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true&allowMultiQueries=true&nullCatalogMeansCurrent=true
    #spring.datasource.username=root
    #spring.datasource.password=123456
    #spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
    
    ##数据库配置
    ##数据库database0地址
    database0.url=jdbc:mysql://127.0.0.1:3306/oasys?serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true&allowMultiQueries=true&nullCatalogMeansCurrent=true
    ##数据库database0用户名
    database0.username=root
    ##数据库database0密码
    database0.password=123456
    ##数据库database0驱动
    database0.driverClassName=com.mysql.cj.jdbc.Driver
    ##数据库database0名称
    database0.databaseName=oasys
    
    ##数据库database1地址
    database1.url=jdbc:mysql://127.0.0.1:3306/oasys_write?serverTimezone=Asia/Shanghai&useSSL=false&allowPublicKeyRetrieval=true&allowMultiQueries=true&nullCatalogMeansCurrent=true
    ##数据库database1用户名
    database1.username=root
    ##数据库database1密码
    database1.password=123456
    ##数据库database1驱动
    database1.driverClassName=com.mysql.cj.jdbc.Driver
    ##数据库database1名称
    database1.databaseName=oasys_write
    # Druid 配置
    # 初始化时建立物理连接的个数
    spring.datasource.druid.initial-size=5
    # 最大连接池数量
    spring.datasource.druid.max-active=30
    # 最小连接池数量
    spring.datasource.druid.min-idle=5
    # 获取连接时最大等待时间，单位毫秒
    spring.datasource.druid.max-wait=60000
    # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
    spring.datasource.druid.time-between-eviction-runs-millis=60000
    # 连接保持空闲而不被驱逐的最小时间
    spring.datasource.druid.min-evictable-idle-time-millis=300000
    # 用来检测连接是否有效的sql，要求是一个查询语句
    spring.datasource.druid.validation-query=SELECT 1 FROM DUAL
    # 建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
    spring.datasource.druid.test-while-idle=true
    # 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
    spring.datasource.druid.test-on-borrow=false
    # 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
    spring.datasource.druid.test-on-return=false
    # 是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大，比如说oracle。在mysql下建议关闭。
    spring.datasource.druid.pool-prepared-statements=true
    # 要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。
    spring.datasource.druid.max-pool-prepared-statement-per-connection-size=50
    # 配置监控统计拦截的filters，去掉后监控界面sql无法统计
    #spring.datasource.druid.filters=stat,wall
    # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
    spring.datasource.druid.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
    # 合并多个DruidDataSource的监控数据
    spring.datasource.druid.use-global-data-source-stat=true
    # 配置sql 注入方式
    spring.datasource.druid.filters=stat,log4j
    
    #日志文件配置
    logging.config=classpath:logback.xml

package com.zzg;
    
    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.data.jpa.repository.config.EnableJpaRepositories;
    import org.springframework.transaction.annotation.EnableTransactionManagement;
    
    @SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
    @EnableTransactionManagement(proxyTargetClass = true)
    @EnableConfigurationProperties
    @EnableJpaRepositories(basePackages = {"com.zzg.dao"})
    public class Application {
    
    	public static void main(String[] args) {
    		// TODO Auto-generated method stub
    		SpringApplication.run(Application.class, args);
    	}
    
    }

### 实体层和dao层 ###

package com.zzg.entity;
    
    import java.io.Serializable;
    import java.util.Set;
    
    import javax.persistence.Column;
    import javax.persistence.Entity;
    import javax.persistence.FetchType;
    import javax.persistence.GeneratedValue;
    import javax.persistence.Id;
    import javax.persistence.ManyToMany;
    import javax.persistence.Table;
    
    @SuppressWarnings("serial")
    @Entity
    @Table(name="author")
    public class Author implements Serializable {
    	@Id
        @GeneratedValue
        private Long id;
    
    	@Column(name="name")
        private String name;
    
        @ManyToMany(mappedBy = "authors", fetch=FetchType.EAGER)
        private Set<Book> books;
    
        public Author() {
            super();
        }
    
        public Author(String name) {
            super();
            this.name = name;
        }
    
        public Long getId() {
            return id;
        }
    
        public void setId(Long id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public Set<Book> getBooks() {
            return books;
        }
    
        public void setBooks(Set<Book> books) {
            this.books = books;
        }
    
        @Override
        public String toString() {
        	 return "id is:" + id + "name is:" + name;
        }
    }

package com.zzg.entity;
    
    import java.io.Serializable;
    import java.util.HashSet;
    import java.util.Set;
    
    import javax.persistence.CascadeType;
    import javax.persistence.Column;
    import javax.persistence.Entity;
    import javax.persistence.FetchType;
    import javax.persistence.GeneratedValue;
    import javax.persistence.Id;
    import javax.persistence.JoinColumn;
    import javax.persistence.JoinTable;
    import javax.persistence.ManyToMany;
    import javax.persistence.Table;
    
    @SuppressWarnings("serial")
    @Entity
    @Table(name="book")
    public class Book implements Serializable {
    	@Id
        @GeneratedValue
        private Long id;
    	
    	@Column(name="name")
        private String name;
    
        @ManyToMany(cascade = CascadeType.ALL, fetch=FetchType.EAGER)
        @JoinTable(name = "book_author", joinColumns = {
                @JoinColumn(name = "book_id", referencedColumnName = "id")}, inverseJoinColumns = {
                @JoinColumn(name = "author_id", referencedColumnName = "id")})
        private Set<Author> authors;
    
        public Book() {
            super();
        }
    
        public Book(String name) {
            super();
            this.name = name;
            this.authors = new HashSet<>();
        }
    
        public Book(String name, Set<Author> authors) {
            super();
            this.name = name;
            this.authors = authors;
        }
    
        public Long getId() {
            return id;
        }
    
        public void setId(Long id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public Set<Author> getAuthors() {
            return authors;
        }
    
        public void setAuthors(Set<Author> authors) {
            this.authors = authors;
        }
    
        @Override
        public String toString() {
            return "id is:" + id + "name is:" + name;
        }
    }

package com.zzg.dao;
    
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.stereotype.Repository;
    
    import com.zzg.entity.Author;
    
    @Repository
    public interface AuthorRepository extends JpaRepository<Author, Long> {
    
    }

package com.zzg.dao;
    
    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.stereotype.Repository;
    
    import com.zzg.entity.Book;
    
    @Repository
    public interface BookRepository extends JpaRepository<Book, Long> {
    	public Book findById(Long id);
    }

### Sharding-jdbc 读写分离核心配置: ###

package com.zzg.config;
    
    import javax.sql.DataSource;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.stereotype.Component;
    import com.alibaba.druid.pool.DruidDataSource;
    import lombok.Data;
    
    /**
     * 主库(写库)
     * @author Administrator
     *
     */
    @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;
    	}
    }

package com.zzg.config;
    
    import javax.sql.DataSource;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.stereotype.Component;
    import com.alibaba.druid.pool.DruidDataSource;
    import lombok.Data;
    
    /**
     * 丛库(读库)
     * @author Administrator
     *
     */
    @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;
    	}
    }

package com.zzg.config;
    
    import java.sql.SQLException;
    import java.util.HashMap;
    import java.util.Map;
    
    import javax.sql.DataSource;
    
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.core.env.Environment;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import com.dangdang.ddframe.rdb.sharding.api.MasterSlaveDataSourceFactory;
    import com.dangdang.ddframe.rdb.sharding.api.strategy.slave.MasterSlaveLoadBalanceStrategyType;
    
    /**
     * sharding-jdbc 数据库读写分离
     * 
     * @author Administrator
     *
     */
    @Configuration
    public class ShardingJdbcConfig {
    
    	@Autowired
    	private Database0Config database0Config;
    
    	@Autowired
    	private Database1Config database1Config;
    
    	@Bean
    	public DataSource getDataSource() throws SQLException {
    		return buildDataSource();
    	}
    
    	private DataSource buildDataSource() throws SQLException {
    		// 设置从库数据源集合
    		Map<String, DataSource> slaveDataSourceMap = new HashMap<>();
    		slaveDataSourceMap.put(database1Config.getDatabaseName(), database1Config.createDataSource());
    
    
    		// 获取数据源对象
    		DataSource dataSource = MasterSlaveDataSourceFactory.createDataSource("masterSlave",
    				database0Config.getDatabaseName(), database0Config.createDataSource(), slaveDataSourceMap,
    				MasterSlaveLoadBalanceStrategyType.getDefaultStrategyType());
    		return dataSource;
    	}
    
    }

### 功能测试(test)定义: ###

package com.zzg.test;
    
    import java.util.Arrays;
    import java.util.Set;
    
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    import org.springframework.test.context.junit4.SpringRunner;
    
    import com.zzg.annotation.TargetDateSource;
    import com.zzg.config.DataSourceConfig;
    import com.zzg.dao.AuthorRepository;
    import com.zzg.dao.BookRepository;
    import com.zzg.entity.Author;
    import com.zzg.entity.Book;
    
    @RunWith(SpringRunner.class)
    @SpringBootTest
    public class ManyToManyTest {
    
    	
    	@Autowired
    	private AuthorRepository authorRepository;
    	
    	@Autowired
    	private BookRepository bookRepository;
    	
        // 写库
    	@Test
    	public void insertManyToMany() {
    		
    		Author lewis = new Author("Lewis");
            Author mark = new Author("Mark");
            Author peter = new Author("Peter");
    
            Book spring = new Book("Spring in Action");
            spring.getAuthors().addAll(Arrays.asList(lewis, mark));
    
            Book springboot = new Book("Spring Boot in Action");
            springboot.getAuthors().addAll(Arrays.asList(lewis, peter));
    
            bookRepository.save(Arrays.asList(spring, springboot));
    		
    	}
    	
        // 读库
    	@Test
    	public void selectManyToMany() {
    		Book book = bookRepository.findOne(2L);
    		System.out.println(book.toString());
    		
    		book.getAuthors().stream().forEach(item->{
    			System.out.println(item.toString());
    		});
    		
    		
    	}
    	
    
    }

写库执行插入正常:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70 1][]

读库读取数据异常:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70 2][]

造成查询异常的原因是由于读写库的数据没有进行主从同步，导致查询指定数据提示空指针异常，这也进一步验证我们的读写分离的功能是OK 的

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200914001834758.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n,size_16,color_FFFFFF,t_70
[https_shardingsphere.apache.org_document_legacy_4.x_document_cn_overview_sharding-jdbc]: https://shardingsphere.apache.org/document/legacy/4.x/document/cn/overview/#sharding-jdbc
[2020091400264074.png]: https://img-blog.csdnimg.cn/2020091400264074.png
[20200914002702867.png]: https://img-blog.csdnimg.cn/20200914002702867.png
[20200914002547545.png]: https://img-blog.csdnimg.cn/20200914002547545.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200914003850122.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200914003920422.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pob3V6aGl3ZW5nYW5n,size_16,color_FFFFFF,t_70