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

最新推荐文章于 2024-04-03 11:23:05 发布

在奋斗的大道

最新推荐文章于 2024-04-03 11:23:05 发布

阅读量2.7k

点赞数

分类专栏： spring Data JPA

本文链接：https://blog.csdn.net/zhouzhiwengang/article/details/108570883

版权

spring Data JPA 专栏收录该内容

17 篇文章 1 订阅

订阅专栏

关于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标准的数据库。

官方文档地址:https://shardingsphere.apache.org/document/legacy/4.x/document/cn/overview/#sharding-jdbc

本文场景：

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

主库：

丛库：

建表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>
		<!--springboot web 基础模块 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>
		<!-- springboot test 测试框架 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<!-- springboot aop 基础模块 -->
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-aop</artifactId>
		</dependency>
		<!--servlet-api -->
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>javax.servlet-api</artifactId>
			<version>${javax.servlet-api.version}</version>
		</dependency>
		<!--jpa orm 模块 -->
		<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>
		<!--数据库连接池druid 模块 -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid-spring-boot-starter</artifactId>
			<version>${druid-spring-boot-starter.version}</version>
		</dependency>
		<!-- sharding-jdbc 实现读写分离 -->
	 	<dependency>
            <groupId>com.dangdang</groupId>
            <artifactId>sharding-jdbc-core</artifactId>
            <version>1.5.4</version>
        </dependency>
		<!-- Swagger2 -->
		<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>
		<!-- fastjson -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>fastjson</artifactId>
			<version>1.2.48</version>
		</dependency>
		<!-- apache common 工具包 -->
		<!--common-lang 常用工具包 -->
		<dependency>
			<groupId>commons-lang</groupId>
			<artifactId>commons-lang</artifactId>
			<version>${commons-lang.version}</version>
		</dependency>
		<!--commons-lang3 工具包 -->
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-lang3</artifactId>
			<version>${commons-lang3.version}</version>
		</dependency>

		<!--commons-codec 加密工具包 -->
		<dependency>
			<groupId>commons-codec</groupId>
			<artifactId>commons-codec</artifactId>
			<version>${commons-codec.version}</version>
		</dependency>
		<!--commons-net 网络工具包 -->
		<dependency>
			<groupId>commons-net</groupId>
			<artifactId>commons-net</artifactId>
			<version>${commons-net.version}</version>
		</dependency>
		<!--common-io 工具包 -->
		<dependency>
			<groupId>commons-io</groupId>
			<artifactId>commons-io</artifactId>
			<version>${commons-io.version}</version>
		</dependency>
		<!--common-collection 工具包 -->
		<dependency>
			<groupId>commons-collections</groupId>
			<artifactId>commons-collections</artifactId>
			<version>${commons-collections.version}</version>
		</dependency>
		<!--common-fileupload 工具包 -->
		<dependency>
			<groupId>commons-fileupload</groupId>
			<artifactId>commons-fileupload</artifactId>
			<version>${common-fileupload.version}</version>
		</dependency>
		<!-- common-text 工具包 -->
		<dependency>
			<groupId>org.apache.commons</groupId>
			<artifactId>commons-text</artifactId>
			<version>${commons-text.version}</version>
		</dependency>
		<!-- lomback 工具包 -->
		<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());
		});
		
		
	}
	

}

写库执行插入正常:

读库读取数据异常:

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

在奋斗的大道

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
Spring-Data-JPA +Sharding-jdbc+druid数据库连接池实现数据库读写分离

关于Sharding-jdbc 简介定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。适用于任何基于JDBC的ORM框架，如：JPA, Hibernate, Mybatis, Spring JDBC Template或直接使用JDBC。支持任何第三方的数据库连接池，如：DBCP, C3P0, BoneCP, Druid, HikariCP等。支持
复制链接

扫一扫