关于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 的